Putting a SQL query in a PHP function

Question

I've got several queries I want to run on a single page. I obviously don't want to put the actual queries in my template file, so I think what I want to do is construct a function and call it wherever I want the query results to show up. Right?

So, for example, I'll have <?php sidebar_query()?> in the sidebar, <?php footer_query()?> in the footer, etc.

So, I just make a file called functions.php, do PHP include, and put something like this in there?

<?php
    function sidebar_query(){
        $query = ("SELECT sidebarposts FROM table;");
    return $query;
}
?>

or do you use echo and not return anything?

<?php
    function sidebar_query(){
        $query = ("SELECT sidebarposts FROM table;");
        echo $query;
}
?>

Along the exact same line, I'd like to count the results that get returned, and display a 'There were X posts returned!' message below. Seems like it would make sense to put this in a function too. How would I make a 'generic' function that I could reuse for each query?

<?php
    function number_of_results(){
        $num_rows = mysql_num_rows();
        echo $num_rows;
}
?>

I'd be extremely grateful if someone could give me the theoretical gist of what I should be trying to achieve here.

Thanks for helping a beginner.

Terry

FYI - docs: Old approach mysql_query, New approaches Choosing an API — ToolmakerSteve
– ToolmakerSteve, Commented Jul 15, 2015 at 18:46

Fredrik · Accepted Answer · 2011-07-20 16:44:50Z

9

I think I get what you mean.

Return the value instead like this



    function sidebar_query(){
        $rValue = "";
        $query = ("SELECT sidebarposts FROM table;");
        $result = mysql_query($query);
        if ($row = mysql_fetch_array($result)){
            $rValue = $row['sidebarposts'];
        }
    return $rValue;
    }

Now you can echo sidebar_query(); or whatever you want to do with it.

answered Jul 20, 2011 at 16:44

Fredrik

1,41213 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Anthony Over a year ago

You would need to pass the mysql connection reference variable to the function, I believe.

joakimdahlstrom Over a year ago

@Anthony, not needed, since it uses the latest opened connection anyways.

sdot257 Over a year ago

@joakimdahlstrom, not if it's the first function he's calling.

sdot257 Over a year ago

yea let's say his index.php page calls that function first and nothing else. unless the user initiated the DB parameters elsewhere, i assume it wouldn't work. I'm basing all this off his initial question where he isn't too sure of himself. that is all.

Anthony Over a year ago

I thought it didn't have access to the connection since it was outside the function, even if it was the last open connection. If it works it works, but I always pass in my connection variable, just for best practice sake.

|

joakimdahlstrom · Accepted Answer · 2011-07-20 17:05:51Z

By doing ("SELECT sidebarposts FROM table;")you're not actually doing anything, you just have a string stored as a variable.

A simple example is

function sidebar_query()
{
    $query = mysql_query("SELECT title,post FROM table;"); //query the db
    $resArr = array(); //create the result array

    while($row = mysql_fetch_assoc($query)) { //loop the rows returned from db
        $resArr[] = $row; //add row to array
    }

    return $resArr;   
}

Then to use it you can do

$sideBarPosts = sidebar_query(); //get the result array

foreach($sideBarPosts as $post) { //loop the array
    echo '<h1>'. $post['title']. '</h1>';
    echo '<p>'. $post['post']. '</p>';
}

EDIT. I see you want to let the function print it directly, you can do that instead of returning the array, if you like.

Jérémie B · Accepted Answer · 2016-02-19 12:06:24Z

1

   function sidebar_query(){
    $query = mysql_query("SELECT * FROM table;");
    $result = $conn->query($query);
    $resArr = array(); //create the result array

    while($row = $result->fetch_assoc()) { //loop the rows returned from db
      $resArr[] = $row; //add row to array
    }
    return $resArr;   
    }

and that :

    $sideBarPosts = sidebar_query(); //get the result array

    foreach($sideBarPosts as $post) { //loop the array
      echo '<h1>'. $post['title']. '</h1>';
      echo '<p>'. $post['post']. '</p>';
    }

edited Feb 19, 2016 at 12:06

Jérémie B

11k1 gold badge30 silver badges44 bronze badges

answered Feb 19, 2016 at 11:23

Muhammad Shahzaib Malik

92 bronze badges

Comments

Tristin Baker · Accepted Answer · 2019-03-06 12:14:35Z

1

// Set Connection with database

$conn = mysql_pconnect($servername,$username,$password) or die("Can not Connect MySql Server!");

// Select database you want to use

mysql_select_db($databasename,$conn) or die("Can not select Data Base!");

// When you want to query SQL inside php function, you need to pass connection($conn) to the function as below.

function sidebar_query($condb)
   {
     $strSql = "SELECT sidebarposts FROM table";
     $result = mysql_query($strSql,$condb);
     return $result;
   }

// Call function

 sidebar_query($conn);

This is work for me as i use with my webpage.

edited Mar 6, 2019 at 12:14

Tristin Baker

498 bronze badges

answered Oct 26, 2016 at 18:53

Montree Amatasuwan

114 bronze badges

Comments

Naftali · Accepted Answer · 2011-07-20 16:38:00Z

0

Read the php doc on how to run mysql query.

Yay you have the query, now you need to do something with it:

mysql_query($query); for example might work for you :-)

answered Jul 20, 2011 at 16:38

Naftali

147k41 gold badges247 silver badges304 bronze badges

Comments

prodigitalson · Accepted Answer · 2011-07-20 17:01:24Z

You want to run the query and return the result in whatever format you want to use in your template.. so for the raw number you want to return an integer, for the sidebar posts return an array of posts OR the mysql result.

You should make the "'There were X posts returned!' " thing a completely different function which you can pass in an integer an an optional string. This way you can reuse it for a ton of stuff. for example:

function format_nb_records($nb_records, $format = 'There were %s posts returned!'){
   return sprintf($format, $nb_records);
}

Although in this case i dont think there is really enough extra logic to warrant wrapping it in a function. I would probably just do this directly in my template.

A good example of where something like this is useful is if you want to do "time in words" functionality like "Yesterday" or "10 minutes ago" then you would pass in the time calculate which string to use, format it and return it.

dragon · Accepted Answer · 2015-01-02 12:07:21Z

0

Although in this case i dont think there is really enough extra logic to warrant wrapping it in a function. I would probably just do this directly in my template.

A good example of where something like this is useful is if you want to do "time in words" functionality like "Yesterday" or "10 minutes ago" then you would pass in the time calculate which string to use, format it

answered Jan 2, 2015 at 12:07

dragon

1

Comments

matasoy · Accepted Answer · 2015-01-21 21:25:48Z

0

You need to pass database connection name to function like this,

You already have a connection name, mine is $connect_name

$connect_name= mysql_connect( 'host', 'user', 'password');

function getname($user,$db){
    $data= mysql_query("SELECT $user...", $db);
    //your codes
    //return bla bla
}

echo getname("matasoy",$connect_name);

This work for me.

I also use this for medoo.min sql system. You can use medoo with functions like this;

function randevu($tarih,$saat,$db){

    $saat_varmi = $db->count("randevu",array("AND"=>array("tarih"=>$tarih,"saat"=>$saat)));

    if($saat_varmi>0)
        return 3;
    else
        return 2;               
}

have a nice day, Murat ATASOY

answered Jan 21, 2015 at 21:25

matasoy

6971 gold badge6 silver badges7 bronze badges

Collectives™ on Stack Overflow

Putting a SQL query in a PHP function

8 Answers 8

7 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

7 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related