CODEWITHSUNDEEP
phpmysql
djq
djq

Reputation: 15286

Problem with MySQL query

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?

Upvotes: 1

Views: 69

Answers (2)

Johan
Johan

Reputation: 76537

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.

Upvotes: 0

jeroen
jeroen

Reputation: 91734

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.

Upvotes: 3

Related Questions