Keep getting mySQL error No. 1064, but i can't seem to find the problem

Durux · 04-10-2008, 09:46 AM

Hello all, i keep geeting the 1064 mySQL error from an sql statement, yet the statement looks valid all the way through.

Here is the PHP script that should update the DB:

PHP Code:


			
function update($sql, $date){

    

    $link = aaa_connect();

    

    $sql2 = str_replace("\n", "", $sql);

    $sql2 = str_replace("\'", "'", $sql2);

        

    $result = mysql_query($sql2, $link);

    if(!$result){die('Invalid query: '.mysql_error().'<br>Error No: '.mysql_errno());}

        

    $array[0] = mysql_affected_rows();

        

    $array[1] = date('l',mktime(0,0,0,substr($date, 6, 2),substr($date, 9,2),substr($date,1,4))).' the '.date('j',mktime(0,0,0,substr($date, 6, 2),substr($date, 9,2),substr($date,1,4))).'. of '.date('F',mktime(0,0,0,substr($date, 6, 2),substr($date, 9,2),substr($date,1,4))).' '.date('Y',mktime(0,0,0,substr($date, 6, 2),substr($date, 9,2),substr($date,1,4)));

        

    mysql_close($link);

        

    return $array;

        

}

and here is the sql statement being given to the function:

Code:

UPDATE aaa_gallery SET date = '2008-04-10' WHERE hash = '7300ba4b247142ae68edc24029638129';
UPDATE aaa_gallery SET date = '2008-04-10' WHERE hash = '132f0d58ef38cd0cd91b56334b702efd';
UPDATE aaa_gallery SET date = '2008-04-10' WHERE hash = '19934c1a89b47724c1e7efe97dd84fcd';
UPDATE aaa_gallery SET date = '2008-04-10' WHERE hash = '3eecd1bf00faa7ebb3ca74bf9fbe379a';

i keep getting this error message back:

Code:

Invalid 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 '; UPDATE aaa_gallery SET date = '2008-04-10' WHERE hash = '132f0d58ef38cd0cd91b5' at line 1
Error No: 1064

Can anyone help me out with this one?

sketchMedia · 04-10-2008, 10:27 AM

not reading it or testing fully (sorry but im at work) but going off the error message it seems somewhere your script is inserting '; before the UPDATE statement, check the string inputted into the function.

if this isnt the case, i will have a look later if no1 else answers before me :)

Durux · 04-10-2008, 10:33 AM

Quote:

Originally Posted by sketchMedia

it seems somewhere your script is inserting '; before the UPDATE statement, check the string inputted into the function.

Actually no, if you look in the end of the error message it says

Code:

WHERE hash = '132f0d58ef38cd0cd91b5'

wich is the second line in the sql statement.

Jim · 04-10-2008, 10:53 AM

I says "near", this most of the times means "before". (bit difficult for people like me who dont have english as native language).

So the error is defenatelly in the first line. Since you use quotes around all text, maybe hash is a reserved SQL word?

Durux · 04-10-2008, 11:01 AM

Quote:

Originally Posted by Jim

Since you use quotes around all text, maybe hash is a reserved SQL word?

No hash is not a reserved word in mySQL, using it for fetching data in th DB without a problem...

Salathe · 04-10-2008, 11:34 AM

The standard MySQL library can only have one statement at a time. You're trying to use four UPDATE statements with a single call to mysql_query. In this case, you can manipulate the query (the WHERE clause) into just one statement since all of the rows to be updated will be given the same new date value.

Durux · 04-10-2008, 01:50 PM

Quote:

Originally Posted by Salathe

The standard MySQL library can only have one statement at a time. You're trying to use four UPDATE statements with a single call to mysql_query. In this case, you can manipulate the query (the WHERE clause) into just one statement since all of the rows to be updated will be given the same new date value.

Found that out, remade the PHP function so it it splits up the statement and then use a while loop to get all the statements into the DB.

Thanks to all people for trying to help

sketchMedia · 04-10-2008, 06:18 PM

Quote:

Actually no, if you look in the end of the error message it says
Code:
WHERE hash = '132f0d58ef38cd0cd91b5'
wich is the second line in the sql statement.

Not quite sure how to respond to that tbh.

'WHERE' is actually part of your query not an error message.

so i wanst far from the mark as salathe said you cant have 2 querys hence why
anyway glad you got it sorted.

Code:

SET date = '2008-04-10' WHERE hash = '7300ba4b247142ae68edc24029638129';
UPDATE aaa_gallery SET date = '2008-04-10' WHERE hash = '132f0d58ef38cd0cd91b56334b702efd';

failed near ';

Garrett · 04-13-2008, 07:51 PM

Quote:

Originally Posted by sketchMedia

Not quite sure how to respond to that tbh.

'WHERE' is actually part of your query not an error message.

so i wanst far from the mark as salathe said you cant have 2 querys hence why
anyway glad you got it sorted.

Code:

SET date = '2008-04-10' WHERE hash = '7300ba4b247142ae68edc24029638129';
UPDATE aaa_gallery SET date = '2008-04-10' WHERE hash = '132f0d58ef38cd0cd91b56334b702efd';

failed near ';

PHP: mysqli::multi_query - Manual

That should get your started, that's the only way I know of trying to do multiple queries by the ";" separator. (Other than doing each query in a for or while.)