[RESOLVED] debugging sql errors

dmcglone

How can I get better output from mysql. I keep getting this error:

Error performing query: 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 'WHERE (subheaderTextID = 1)' at line 1

but I see no error in my UPDATE. (below):

<?php

$sql_update = "UPDATE subheaderText SET ";
$sql_update .= "subheaderText = '" . $REQUEST['subheaderText'] . "', ";
$sql_update .= "headerText = '" . $REQUEST['headerText'] . "', ";
$sql_update .= "page = '" . $REQUEST['page'] . "', ";
$sql_update .= "WHERE (subheaderTextID = " . $REQUEST['subheaderTextID'] . ")";

/ Passes query to database /

$result = mysql_query($sql_update, $conn);
if (!$result) {
echo("<p>Error performing query: " . mysql_error() . "</p>");
exit();
?>

everything else works as expected except for this.

laserlight

Well, the error message does say that the error is near that part. It probably lies with this line:

$sql_update .= "page = '" . $_REQUEST['page'] . "', ";

You added a comma before the WHERE, and I believe that is invalid syntax.

By the way, you should correctly escape those variables to avoid SQL injection.

dmcglone

laserlight;10897382 wrote:
Well, the error message does say that the error is near that part. It probably lies with this line:
$sql_update .= "page = '" . $_REQUEST['page'] . "', ";
You added a comma before the WHERE, and I believe that is invalid syntax.

By the way, you should correctly escape those variables to avoid SQL injection.

Thank You, Thank you!!

I have been over it a million and 1 times!!!

Weedpacket

dmcglone wrote:
How can I get better output from mysql.

One suggestion given in this thread is to write the query so that it spans several lines. Then the error message would say on which line the error was detected. Of course, it would still say what the error was near, and that wouldn't change.

Something else you can do (at the same time) is write the source with the goal of improving its legibility to humans. Say, something like

// Here is also where you add code to sanitise the input.
$subheaderText = $_REQUEST['subheaderText'];
$headerText = $_REQUEST['headerText'];
$page = $_REQUEST['page'];
$subheaderTextID = $_REQUEST['subheaderTextID'];

$sql_update = "
UPDATE subheaderText SET
	subheaderText = '$subheaderText',
	headerText = '$headerText',
	page = '$page',
WHERE subheaderTextID = $subheaderTextID";

Where I've deliberately left the error in.

dmcglone

Thanks.

Notice in my sql_update lines, I did make it a point to put them on new lines. I hate really long lines of text especially with all the crap in them. '"''""""';;''

Makes everything so unreadable and it actually in a way upsets me, because I am to a certain extinct a neat freak. So I do make it a point to make the code nice and clean and look for better ways to write messy code. :-)

dmcglone

I forgot to ask, What editor do you use to write your code?

At the time being, I am using Kate with syntax highlighting. I know of nothing better, but i'm always wondering if there is. I like Quanta, but the preview for PHP never works.

laserlight

dmcglone wrote:
Notice in my sql_update lines, I did make it a point to put them on new lines. I hate really long lines of text especially with all the crap in them. '"''""""';;''

They are on different lines in PHP, but the same line in SQL. Weedpacket's point is that by putting them on different lines in SQL, the database engine may be able to give you an error message with slightly more information.

dmcglone wrote:
I forgot to ask, What editor do you use to write your code?

At the time being, I am using Kate with syntax highlighting. I know of nothing better, but i'm always wondering if there is. I like Quanta, but the preview for PHP never works.

As a standalone text editor, Kate should be fine.