SQL Queries in functions

[Mutley]

I had an idea instead of coding the same query to get certain fields over and over again, to do a function with the query in just once, then displaying the results by calling the function and echoing the fields/results.

 

However it doesn't seem that this is possible. If I echo the fields inside the function, they display but anything outside the function, does not.

 

Is there a way around this or another possible method to do the same technique?

[Maq]

I don't understand the problem.  You have a function with parameters to do whatever you want with.  After you execute your query you can return whatever you want...

[Barand]

Example, for a given product code, get the description and price.

function getProducData ($code)
{
    $sql = "SELECT descrip, price FROM product WHERE prodcode='$code'";
    $res = mysql_query($sql);
    $row = mysql_fetch_assoc ($res);
    return $row;
}

$prod_code = 'A012';

$result = getProducData ($prod_code);

echo "{$result['descrip']} are {$result['price']} each";

 

 

[Mutley]

Here's my code:

 

<?php
function getGames($where) {

$res = mysql_query("SELECT gameID, name FROM site_games WHERE $where");
while($row = mysql_fetch_array($res))
{
	$gameID = $row['gameID'];
	$name = $row['name'];
}
}

getGames('gameID = 1');

echo $name;// Echo from the variable out of the function
?>

 

I think I know why it's wrong, I imagine the echo has to be inside the function?

[Mchl]

Either that, or even better make function return the value.

<?php
function getGames($where) {
   $where = mysql_real_escape_string($where);  //make sure, there's no SQL injection
   $res = mysql_query("SELECT gameID, name FROM site_games WHERE $where");
   while($row = mysql_fetch_array($res))
   {
      $gameID = $row['gameID'];
      $name = $row['name'];
   }
   return $name;
}



echo getGames('gameID = 1');// Echo from the variable out of the function
?>

[flyhoney]

The reason

<?php
echo $name;
?>

doesn't work is because inside your function, you are setting $name = <something>. Therefore, $name only exists inside the function, that's called 'scope'.  If you want to get the game name, the best thing to do is write a function that will return it based on the gameID.

 

<?php
function getGameById($id) {

   $res = mysql_query("SELECT gameID, name FROM site_games WHERE gameID = '$id'");
   
   if ($row = mysql_fetch_array($res)) {
      return $row['name'];
   }
}

echo getGameById(1);

?>

[Mutley]

Thanks guys. If I have 20+ fields though, would this mean making a function for every field to echo it? Can't I just list them inside 1 function and call them individually from outside of it?

[Barand]

Thanks guys. If I have 20+ fields though, would this mean making a function for every field to echo it? Can't I just list them inside 1 function and call them individually from outside of it?

 

Do what I did in my code - return an array of the values.

[flyhoney]

I'm not sure you are 100% getting the purpose of functions.  Think of them as a black box.  You put something in (parameters), and you get something back (return values).  I don't know what your script does, but try to leverage functions to logically break the code up.

 

If you need to retrieve a row from the site_games table, and you have the ID, a function like this should do the trick:

 

<?php

function selectGameById($id) {
    $game = array();

    $sql = "SELECT * FROM site_games WHERE gameID = '$id'";
    $result = mysql_query($sql);
    
    if (!$result) {
        echo "Could not successfully run query ($sql) from DB: " . mysql_error();
        exit;
    }

    if (mysql_num_rows($result) == 1) {
        $game = mysql_fetch_assoc($result);
    }

    return $game;
}

$game = selectGameById(1);
echo $game['name'];
echo $game['whatever'];
...etc
?>

 

Just remember to filter/validate database input.