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
– Bnjmn, Commented Jan 20, 2011 at 1: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
– djq, Commented Jan 20, 2011 at 1: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
– Marc B, Commented Jan 20, 2011 at 1:34
Yes they are defined ( I just updated my question) - I'm trying mysql_query( " query" ) now. Thanks for the suggestion. — djq
– djq, Commented Jan 20, 2011 at 1:38

jeroen · Accepted Answer · 2011-01-20 01:40:49Z

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, 2011 at 1:40

answered Jan 20, 2011 at 1:20

jeroen

91.8k22 gold badges117 silver badges132 bronze badges

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

1 Comment

djq Over a year ago

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.

2 revs · Accepted Answer · 2017-05-23 10:00:04Z

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.

Collectives™ on Stack Overflow

Problem with MySQL query

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related