Problem with MySQL query

Question

I'm trying to debug a MySQL query, and I have trouble understanding why one while loop in my script is not working:

// select db
mysql_select_db($dbname);

for ( $x = $latRange[0]; $x <= $latRange[1]; $x++ )
{

   for ( $y = $lngRange[0]; $y <= $lngRange[1]; $y++)
   {
    $sql="SELECT * FROM $usertable WHERE $xlookup = $x AND $ylookup = $y";

  $SQLresult = mysql_query($sql);

  while( $row = mysql_fetch_array($SQLresult) )    
    {       
       $tmpResult = $row[$popDen];                  
                     $result += $tmpResult;   
    }


 }

}

Sample values of the variables described are:

 $latRange = array(3,7);
 $lngRange = array(9,25);
 $popDen = 'ColumnNameIWant'
 $xlookup = 'Col1'
 $xlookup = 'Col2'

The logic behind my query is that it finds all combinations of x and y, gets the corresponding $popDen value, and adds it to $result. Result is defined at the start of my script, and returned by the program after this loop.

I know that the problem section is my while loop, but I don't quite understand how to fix it as I don't fully understand how mysql_fetch_array functions. I've also tried mysql_fetch_row and my query does not work with this either.

I know from commenting out various chunks of the code, and passing back other numbers that everything else works; it is just this chunk that is failing.

Are there any obvious errors that I am making?

Can you please clarify what you're doing with the $popDen variable? Also i would use a for each syntax for the final loop as that seems to mimick what you expect better. — Bnjmn, Jan 20 '11 at 01:21
The $popDen contains a string which is a column name. I'm only expecting one value to be returned; I'm new to the syntax and not sure what is best to use. — djq, Jan 20 '11 at 01:28
Are $xlookup and $ylookup defined anywhere? You're not checking if the query call succeeded at all. If it's bad, then the rest of the code will blow up as well. do at least `mysql_query(..) or die(mysql_error())` as a stopgap debugging method. — Marc B, Jan 20 '11 at 01:34
Yes they are defined ( I just updated my question) - I'm trying mysql_query( " query" ) now. Thanks for the suggestion. — djq, Jan 20 '11 at 01:38

jeroen · Accepted Answer · 2011-01-20T01:40:49.183

3

If popDen is a column in your table, you need to get it with:

$tmpResult = $row['popDen'];

and if it is the only value you need, you can simplify / speed up your sql query:

$sql="SELECT `popDen` FROM $usertable WHERE $xlookup = $i AND $ylookup = $y";

Edit: By the way, you might want to initialize your $result variable so that it has a defined / valid / known value if no rows are found.

edited Jan 20 '11 at 01:40

answered Jan 20 '11 at 01:20

jeroen

91,079
21
114
132

Thanks for the answer - that helped me neaten my code and solve my problem. I had initialized $result, but I was mangling it so badly the script was failing. – djq Jan 20 '11 at 06:22

score 0 · Answer 2 · edited May 23 '17 at 10:00

One obvious error is to use dynamic table names.
This leaves hard to close SQL-injection holes:

Use this code to plug that hole, because mysql-real_escape_string() will not help!

$allowed_tables = array('table1', 'table2');
$clas = $_POST['clas'];
if (in_array($clas, $allowed_tables)) {
    $query = "SELECT * FROM `$clas`";
}

See here for more info: How to prevent SQL injection with dynamic tablenames?
And don't forget to always enclose dynamic tablenames in backticks ` or your code will break if you happen to use a reserved word or a number for a table or column name.

Problem with MySQL query

2 Answers2