MySQL Query Executes in phpMyAdmin but returns nothing using PHP Script

[gigpacknaxe]

Hi,

 

I have a sql query that executes perfectly in phpMyAdmin but when applied to a PHP script returns nothing. It won't even start a "while" loop. Please review code snippits below.

 

Submission Script calls function in Database Class:

$result = $database->testReturnId($_POST['years'], $_POST['model']); 
while($row = mysql_fetch_array($result))
	{
	$i = $i + 1;	
	echo "ROW " . $i . " " . $row['auto_id'] . " " . $row['auto_year'] . " " . $row['auto_year_high'] . " " . $row['auto_make'] . " " . $row['auto_model'] . "<br />";
	}

 

Database Class Function:

function testReturnId($year, $model){ 
	global $form;
	$q = 'SELECT * FROM tbl_svc_auto WHERE auto_model = "$model" AND "$year" BETWEEN auto_year AND auto_year_high';
	if(!mysql_query($q, $this->connection)){
		return false;
	}else{
		return $result = mysql_query($q, $this->connection);
	}
}

 

I have moved the query to the submission script and replaced the variables with actual data and it still will not work. I can take the same query with out the variables and input it into phpMyAdmin and it works great. On the submission script, I added a row number to the echo thinking that it would a least return the word "ROW" and it does not. I have tested each step of the execution of the query and have not returned any errors. I do not receive any errors at all even during execution. It just seems to refuse to run the "while" statement. I do not know where my problem is......Please help.

 

Thank you in advance for any assitance you can offer.

 

Joshua

[trq]

Your function is fubar. Variables are not interpolated within single quotes so your query is broken, and I'm really not sure what your trying to do with your return values.

 

You really shouldn't be mixing and matching between some database object and the pure mysql* functions outside of it either. Either make the database object feature complete or don't bother with it.

[chintansshah]

I feel string concatenation is not properly, Please replace with below string and then try.

	
$q = 'SELECT * FROM tbl_svc_auto WHERE auto_model = "'.$model.'" AND '.$year.' BETWEEN auto_year AND auto_year_high';

 

I hope it worx.

[rwwd]

Hi there,

 

As everyone has stated, the way as you have built your query isn't consistent, to make sure that you are getting what you want, try this, and then copy & paste the result into phpmyadmin & see if the result is the same as manual, if not, revise your method:-

echo $q = "SELECT * FROM `tbl_svc_auto` WHERE `auto_model` = '".$model."' AND `".$year."` BETWEEN `auto_year` AND `auto_year_high` ";

 

That should get you going, also, if there are any numerical values in the query (which I suspect there are) you don't need to quote them, else they will be treated as strings (I believe as this is referred to as a scalar) try this first and see what you get, other than that I just hope that the function works as you want it too.

 

Rw

[gigpacknaxe]

First of all, Thank you everyone for your help. This has been the best forum experiance I have had!!!

 

The problem has been resolved and it was my query. Once variable returned a string and the other was a number. I did not know there was a difference in the way you entered strings and numbers into a query string. Here is the final function (Please know it is only for testing).

 

   /**
    * Test Function
   */
   function testReturnId($year, $model){ 
      global $form;
      $q = "SELECT * FROM `tbl_svc_auto` WHERE `auto_model` = '".$model."' AND ".$year." BETWEEN `auto_year` AND `auto_year_high` ";         
      if(!mysql_query($q, $this->connection)){
         return false;
      }else{
         return $result = mysql_query($q, $this->connection);
      }
   }

 

I did not even think about echoing the query then running it in phpMyAdmin. That would have been a lot easier and led me straight to the proplem. Thank you rwwd for that idea. Once I did that, I saw exactly where my issue was and corrected it. Every tutorial and forum I looked at had different way of entering variables. Not a single person said number variables do not require quotes.

 

echo $q = "Some SQL Statement with Variables";

Then copy the echoed text to phpMyAdmin.

 

Thank you again, everyone, for all yor help.

 

Joshua

[trq]

Your function could still be refactored....

 

function testReturnId($year, $model) { 
  return mysql_query("SELECT * FROM `tbl_svc_auto` WHERE `auto_model` = '".$model."' AND ".$year." BETWEEN `auto_year` AND `auto_year_high` ", $this->connection);
}

 

Does exactly the same.