You have an error in your SQL syntax

**redgoals** · Mar 5, 2002, 16:05

ok i was trying to edit a record:

and got this:

UPDATE tones SET name='Walk on By U2', tone='4*, 2#, 1#, 4, 2#, 1#, 5#, (4), 28#, 19#, 18**#, 0, 1#, 0, 1#, 0, 599*#, 4, 28#, (1)9#, 18#, 2#, 49, 18#, 29#, 48, 19#, 28#, 4, 2#, 1#, 2#, 4, 1#, 59# edit damnit!!', tempo='225', cat='new' WHERE id=You have an error in your SQL syntax near '' at line 1

obviously the "edit damnit!!" was entered into the form to see if it is added.

the record is not updated

**qslack** · Mar 5, 2002, 16:22

Everything looks OK until the end, "id=." $id is undefined. It must not be being passed through the URL or something. It's definitely what's causing your syntax error, though.

**redgoals** · Mar 5, 2002, 16:26

the id is being passed through the url string (gh.php?id=54). ive lost count how many times ive checked the code, cant find an error.

heres the table structure if it helps in any way.

PHP Code:




+-------+--------------+------+-----+---------+----------------+

| Field | Type         | Null | Key | Default | Extra          |

+-------+--------------+------+-----+---------+----------------+

| cat   | varchar(20)  |      |     |         |                |

| id    | int(20)      |      | PRI | NULL    | auto_increment |

| name  | varchar(100) |      |     |         |                |

| tempo | int(3)       |      |     | 0       |                |

| tone  | varchar(150) |      |     |         |                |

+-------+--------------+------+-----+---------+----------------+

"id=." $id

has changed to

id=$id"

**qslack** · Mar 5, 2002, 16:42

Try $HTTP_GET_VARS['id'] instead of $id.

**redgoals** · Mar 5, 2002, 16:48

Parse error: parse error, expecting `T_STRING' or `T_VARIABLE' or `T_NUM_STRING' in /home/sites/site143/web/file.php on line 19

is this what you meant:

PHP Code:


mysql_query( "UPDATE tones SET name='$name', tone='$tone', tempo='$tempo', cat='$cat' 

WHERE id=$HTTP_GET_VARS['id']" ) or die( mysql_error() );

**qslack** · Mar 5, 2002, 16:56

To tell the truth, I haven't written PHP in a while, but I'm fairly sure that you have to access $HTTP_GET_VARS['id'] like this:

mysql_query( "UPDATE tones SET name='$name', tone='$tone', tempo='$tempo', cat='$cat'
WHERE id=". $HTTP_GET_VARS['id'] ) or die( mysql_error() );

**redgoals** · Mar 5, 2002, 17:00

ok i put some carriage returns before the "WHERE" clause in the UPDATE query and the error line changes to the line the "WHERE" clause is on.

**qslack** · Mar 5, 2002, 17:05

Was this before or after you got my previous post?

So confusing, these message boards!

Can you just post your entire code?

**redgoals** · Mar 5, 2002, 17:14

PHP Code:




<?

mysql_pconnect("localhost","user","pass");

mysql_select_db("xxx");

?>

<HTML>

<HEAD>

<TITLE>Untitled Document</TITLE>

<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">

</HEAD>



<BODY BGCOLOR="#FFFFFF" TEXT="#000000">

<? 

    if( $HTTP_POST_VARS["submit"] ) {

    $name = trim( $HTTP_POST_VARS["name"] );

    $tone = trim( $HTTP_POST_VARS["tone"] );

    $tempo = trim( $HTTP_POST_VARS["tempo"] );

    $cat = trim( $HTTP_POST_VARS["cat"] );

    $tone = addslashes($tone);

    mysql_query( "UPDATE tones SET name='$name', tone='$tone', tempo='$tempo', cat='$cat' 







//carriage returns

//error in where clause



    WHERE id=". $HTTP_GET_VARS['id'] ) or die( mysql_error() );

    } 

?> 



<? 

    $result = mysql_query( "SELECT * FROM tones WHERE id=$id" ) or die( mysql_error() );

    $myrow = mysql_fetch_array( $result ) or die( mysql_error() );

    $name = $myrow["name"];

    $tone = $myrow["tone"]; 

    $tempo = $myrow["tempo"]; 

    $cat = $myrow["cat"]; 

?>



<form method="post" action="<? {echo "$PHP_SELF";} ?>">



<TABLE WIDTH="100%" BORDER="0" CELLSPACING="0" CELLPADDING="0">



<TR> 

<TD WIDTH="6%">cat</TD>



<TD WIDTH="94%"> 



<SELECT NAME='cat'>

<OPTION VALUE="<? print( $cat ); ?>" SELECTED> 

<? print( $cat ); ?>

</OPTION>

<OPTION VALUE='new'>new 

</SELECT>



</TD>



</TR>



<TR> 

<TD WIDTH="6%">tempo</TD>

<TD WIDTH="94%"> 

<SELECT NAME='tempo'>

<OPTION VALUE="<? print( $tempo ); ?>" SELECTED> 

<? print( $tempo ); ?>

</OPTION>

<OPTION VALUE='140'>140 

</SELECT>



</TD>



</TR>



<TR> 



<TD WIDTH="6%"> title:</TD>



<TD WIDTH="94%">



<INPUT TYPE="Text" NAME="name" VALUE="<? print( $name ); ?>">



</TD>



</TR>

<TR>



<TD WIDTH="6%">article:</TD>

<TD WIDTH="94%">



<TEXTAREA NAME='tone' ROWS="20" COLS="50"><? print( $tone ); ?></TEXTAREA>



</TD>

</TR>

</TABLE>



<input type="Submit" name="submit" value="Enter information">



</form> 



</BODY>

</HTML>

**qslack** · Mar 5, 2002, 17:33

OK, hmm. Now try echoing the query.

If anyone else sees what's wrong, feel free to jump in!

**redgoals** · Mar 5, 2002, 17:40

UPDATE tones SET name='Beautiful Day by U2', tone='58** 5 48 59 (4) 58 4 2# (4)99 6#8** 5#8* 59 5 4 499 5888** 49 (4)9 6#88** 6# 5#9 * 558 5 (5)9 48 49 58 49 (4)9 6#88** 6# 5 #9* 58 2# 5 2# (5) (4) come on please update!!', tempo='70', cat='abc' WHERE id=You have an error in your SQL syntax near '' at line 1

record not updated.

**pcpresents** · Mar 5, 2002, 17:47

could it be that you forgot to close the query statement with "

WHERE id=". $HTTP_GET_VARS['id'] ) or die( mysql_error() );

shouldn't it be

WHERE id=". $HTTP_GET_VARS['id']") or die( mysql_error() );

notice the '"' after [id]"

Just a thought

**qslack** · Mar 5, 2002, 17:50

No, because we got out of the quotes after id= so we can use the . concatenating operator.

redgoals - try setting $HTTP_GET_VARS['id'] to some random number. It's definitely the "id=" ending that's causing the error. No doubt about it. Now we just need to figure out why the ID isn't getting there. It should, as long as you are accessing the page like file.php?id=1.

**redgoals** · Mar 5, 2002, 17:55

ok now where getting somewhere, i entered the same random number in both queries and it updates !!!.

now why doesnt it accept url string.

heres what i get after the record is updated

UPDATE tones SET name='Fallin by Alicia Keys', tone='18, 1, 2, 3, 59, 3, 78, 6, (7)9, 08, 28, (3)9, 08, 38, (5)9, 28, 39, 48#, 59, 3, 78, 6, 79, 5, 08, 28, (3)9, 08, 38, (5)9 update god damnit!!', tempo='90', cat='new' WHERE id=43

**pcpresents** · Mar 5, 2002, 17:56

ooops I see your point oh wise one.

**qslack** · Mar 5, 2002, 18:04

pcpresents - thanks, but I'm hardly a wise one. If you want to see real live wise ones, just hang around these forums a bit and they'll come.

redgoals - can you give me an example of how you are accessing this script? Can I have a URL? Also, try $_GET['id'] instead of $HTTP_GET_VARS['id'] if you are using PHP >= v4.1.

**redgoals** · Mar 5, 2002, 18:08

ok here is a record you can edit

link

**redgoals** · Mar 5, 2002, 18:22

ok fixed it, just echoed out the id in the select query, and addd a hidden form field at the bottom.

PHP Code:


<?

mysql_pconnect("localhost","user","pass");

mysql_select_db("xxx");

?>

<HTML>

<HEAD>

<TITLE>Untitled Document</TITLE>

<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">

</HEAD>



<BODY BGCOLOR="#FFFFFF" TEXT="#000000">

<? 

        if($submit)  {

        $name = trim( $HTTP_POST_VARS["name"] );

        $tone = trim( $HTTP_POST_VARS["tone"] );

        $tempo = trim( $HTTP_POST_VARS["tempo"] );

        $cat = trim( $HTTP_POST_VARS["cat"] );

        $tone = addslashes($tone);

$query = "UPDATE tones SET name='$name', tone='$tone', tempo='$tempo', cat='$cat' WHERE id='$ids'";



echo $query;

mysql_query( $query ) or die( mysql_error() );

    } 

    

else {

?> 



<? 

    $result = mysql_query( "SELECT * FROM tones WHERE id=$id" ) or die( mysql_error() );

    $myrow = mysql_fetch_array( $result ) or die( mysql_error() );

    $name = $myrow["name"];

    $tone = $myrow["tone"]; 

    $tempo = $myrow["tempo"]; 

    $cat = $myrow["cat"];

    $ids = $myrow["id"]; 

?>





<form method="post" action="<? {echo "$PHP_SELF";} ?>">



<TABLE WIDTH="100%" BORDER="0" CELLSPACING="0" CELLPADDING="0">



<TR> 

<TD WIDTH="6%">cat</TD>



<TD WIDTH="94%"> 



<SELECT NAME='cat'>

<OPTION VALUE="<? print( $cat ); ?>" SELECTED> 

<? print( $cat ); ?>

</OPTION>

<OPTION VALUE='new'>new 

</SELECT>



</TD>



</TR>



<TR> 

<TD WIDTH="6%">tempo</TD>

<TD WIDTH="94%"> 

<SELECT NAME='tempo'>

<OPTION VALUE="<? print( $tempo ); ?>" SELECTED> 

<? print( $tempo ); ?>

</OPTION>

<OPTION VALUE='140'>140 

</SELECT>



</TD>



</TR>



<TR> 



<TD WIDTH="6%"> title:</TD>



<TD WIDTH="94%">



<INPUT TYPE="Text" NAME="name" VALUE="<? print( $name ); ?>">



</TD>



</TR>

<TR>



<TD WIDTH="6%">article:</TD>

<TD WIDTH="94%">



<TEXTAREA NAME='tone' ROWS="20" COLS="50"><? print( $tone ); ?></TEXTAREA>



</TD>

</TR>

</TABLE>



<input type="Submit" name="submit" value="Enter information">



<INPUT TYPE="hidden" NAME="ids" VALUE="<? print( $ids ); ?>">



</form> 



</BODY>

</HTML>

id like to thankyou qslack, coludnt have done without ya. cheers mate.

**pcpresents** · Mar 5, 2002, 18:25

Not trying to be a bother but I was curious.

When I do an update statement I get to the where clause I always end it like this.

Where username='$username'");

The way your doing in your first code is that a shortcut?

**redgoals** · Mar 5, 2002, 18:29

i was having a problem getting the id when updating a record so i had to take the long way round, but i use your way normally.

**pcpresents** · Mar 5, 2002, 18:32

I actually thought about the hidden field also cause that is how I pass all information in the query string when you use PHP_SELF or the variable will show up empty.

Been there done that!

Nice to know i am finally learning something!

Congrats on getting it to work and thanks for letting me prod around.

**qslack** · Mar 5, 2002, 21:38

Hey, glad you got it to work. No problem, sorry it took so long!

Sometimes the challenge of finding bugs is fun, but I wish they'd just stop being lazy and write informative error messages, like "you need to add a hidden form field."

**jsegura** · Mar 6, 2002, 01:36

you said $id comes with the url. Maybe you forget to declare it as global $id so it's lost?.

regards,
Juan.

The SitePoint Forums have moved.

User Tag List

Thread: You have an error in your SQL syntax

Thread Tools

Display

oops

Just a question

thankyou

hope this helps

Bookmarks

Bookmarks

Posting Permissions