[SOLVED] MySQL result is empty, but the query is correct

[tjodolv]

Hello there. I have written a script that grabs info from a database, and outputs it to a form. I figured I'd tidy things up a bit, and use a function to handle the queries, where I had previously hardcoded almost the same query several times.

 

The original code, which works and outputs the html I want: (a list of names)

/* index.php */
<?php
echo '<select name="name" size="1">' . "\n";
//get the list of people from the database
$sql = "SELECT * FROM persons WHERE id > 0 ORDER BY first_name";
$result = mysql_query($sql, $connection);
$numRows = mysql_num_rows($result);
//output result as <option>
for($i = 0; $i < $numRows; $i++) {
$row = mysql_fetch_array($result);
//get the first letter of the last name
$last_name = $rad['last_name'];
$initial = $last_name[0];
//the actual output
echo "\t" . '<option value="' . $rad['first_name'] . ' ' . $rad['last_name'] . '">' . $rad['first_name'] . ' ' . $initial . '</option>' . "\n";
}
echo '</select>' . "\n";
?>

 

This query however, is repeated several places on my site, with different content, so I put it in a function, in a separate file:

/* function.inc.php */
<?php
function dbQuery() {
global $connection;
$numArg = func_num_args();
$select = func_get_arg(0);
$table = func_get_arg(1);
$sql = "SELECT $select FROM $table";
if ($numArg > 2) {
	$conditions = func_get_arg(2);
	$sql .= " WHERE $conditions";
}
if ($numArg > 3) {
	$sort = func_get_arg(3);
	$sql .= " ORDER BY $sort";
}
$result = mysql_query($sql, $connection);
$numRows = mysql_num_rows($result);
//these are to see the actual output I would presume the MySQL server receives
var_dump(func_get_args());
echo $sql;
}
?>

 

Back in the index.php, I call this function with arguments as described below:

/* index.php */
<?php
include "functions.inc.php";

echo '<select name="name" size="1">' . "\n";
//get the list of people from the database
dbQuery("*", "persons", "id > 0", "first_name");
        //output result as <option>
for($i = 0; $i < $numRows; $i++) {
$row = mysql_fetch_array($result);
//get the first letter of the last name
$last_name = $row['last_name'];
$initial = $last_name[0];
//the actual output
echo "\t" . '<option value="' . $row['first_name'] . ' ' . $row['last_name'] . '">' . $row['first_name'] . ' ' . $initial . '</option>' . "\n";
}
echo '</select>' . "\n";
?>

 

This does not work for some reason. The <select> is empty - however according to the var_dump(); and echo $sql; (in code snippet #2 from the top) the script does output this, which is printed to the page:

array(4) {
  [0]=>
  string(1) "*"
  [1]=>
  string(19) "persons"
  [2]=>
  string(6) "id > 0"
  [3]=>
  string(7) "first_name"
}
SELECT * FROM persons WHERE id > 0 ORDER BY first_name

 

I am completely lost...

[soycharliente]

Looks like you're calling your array $row, and then using $rad to access it.

 

$row = mysql_fetch_array($result);
//get the first letter of the last name
$last_name = $rad['last_name'];

 

Does that help?

[Psycho]

Your problem is due to variable scope. Any variables you create in your function are not available outside your function.

[tjodolv]

Looks like you're calling your array $row, and then using $rad to access it.

 

$row = mysql_fetch_array($result);
//get the first letter of the last name
$last_name = $rad['last_name'];

 

Does that help?

ah, no.. I translated the code to English for this forum, as all the variables had Norwegian names. That one just slipped past me. Fixed it now.. Checking mjdamato's suggestion now..

[Psycho]

Try this. The function will return back the result set from the query.

<?php
function dbQuery($select, $table, $conditions="", $sort="") {
global $connection;

$sql = "SELECT $select FROM $table";

if ($conditions) { $sql .= " WHERE $conditions"; }
if ($sort) { $sql .= " ORDER BY $sort"; }

return mysql_query($sql, $connection);
}
?>

 

<?php
include "functions.inc.php";

echo '<select name="name" size="1">' . "\n";

//get the list of people from the database
$result = dbQuery("*", "persons", "id > 0", "first_name");

//output result as <option>
while ($row = mysql_fetch_array($result) ) {
$value  = $rad['first_name'] . ' ' . $rad['last_name'];
$option = $rad['first_name'] . ' ' . $rad['last_name'][0];
//the actual output
echo "\t" . '<option value="' . $value . '">' . $option . '</option>' . "\n";
}

echo '</select>' . "\n";
?>

[tjodolv]

Worked great! Thank you very much

 

Impressive responsetime here btw

 

(I can't seem to modify my first post to show [solved] ) nevermind, saw the button now...