0

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?

Improve this question
1
  • you cannot execute this in PHPMyAdmin. Because this is PHP code, and PHPMyAdmin understand only SQL Commented Jun 3, 2010 at 9:55

3 Answers 3

Reset to default
5

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.

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

5 Comments

you forgot to add a query itself to the debug message :) and you may notice from my posts that I popularize trigger_error usage over echo. You may find it extremely useful.
@Col yup, I like trigger_error too, good point. But $query is in there, isn't it? Or am I overlooking something?
I run query in phpmyadmin by passing 1 to usermasterid and not php variable, my friend.
@Rishi can you show the parsed query? Can you replace AS DateOfBirth by `AS DateOfBirth2? (Although if it's that, it shouldn't work in PHPMyAdmin either.)
Finally I could get it. The sql query was creating problem when I was passing $userid, instead I tried to pass $_GET['q'], the value I was getting from url, and hurrrray!! it worked, thank you man, for hint.
0

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.

Improve this answer

1 Comment

If you are going to downrate me (whoever it was), at least put in a comment as to why.
-2

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 ...

Improve this answer

5 Comments

please don't ever write a query this way... it exposes various security risks, such as SQL injection attacks.
The case was not about security - it was about semantics and syntax - especcially the odd qoutes/apostrophes
even so, when we answer on Stack Overflow we need to highlight these issues to help people write secure applications.
still I cannot see why I should be punished for using my time and giving a positive answer ? Have been around in this forum for some time and try to figure out why some of you like to do that ? snippet og SQL is not even complete - and how can you know if security is a problem in this case ?
don't take it personally. Stack Overflow works by answers getting community contributions. We answer questions better as a group than we do as individuals.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.