Having problem in sql query execution

Question

Hi there,

I have a problem in sql query execution.I am using this sql query:

$userid = 1;  

$sql = mysql_query("
  SELECT ID, Nm, Address, date_format(DateOfBirth, '%d%M%Y') as DateOfBirth 
  FROM PersonalDetails where UserMasterID = $userid
") or die (mysql_error());

The result appears as:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= ' at line 1

When I execute this in PHPMyAdmin it works properly. I am using mysql(5.0.5b) and PHP (5.2.6)

Can you help me please?

Answer 1

If UserMasterID is not an integer, you may need to put quotes around the value:

PersonalDetails where UserMasterID = '$userid'"

The query you are quoting above is not identical to what you run in phpMyAdmin. It contains a PHP variable. When in SQL trouble, always output and analyze the parsed query (with no references to PHP variables in them).

$query = "select ID... etc. etc.";
$result = mysql_query($query);

if (!$result) 
 echo "Error in query $query: ".mysql_error();

90% of problems can be spotted and solved that way.

Answer 2

If it runs correctly in PHPMyAdmin, but not in the PHP code, then that says to me that PHPMyAdmin is performing it's famous task of escaping and sanitizing everything it possibly can.

Change your code to this and check it.

$userid = 1;  

$sql = mysql_query("
  SELECT `ID`, `Nm`, `Address`, date_format(`DateOfBirth`, '%d%M%Y') as DateOfBirth 
  FROM `PersonalDetails` where `UserMasterID` = '{$userid}'
") or die (mysql_error());

It should run now.

Answer 3

Ehhh - why don't you concatenate ?

"SELECT `ID`, `Nm`, `Address`, date_format(`DateOfBirth`, '%d%M%Y') as DateOfBirth 
FROM `PersonalDetails` where `UserMasterID` = '" . $userid . "'";

but Joseph is spot on ...