Problem (syntax error) with updating multiple rows

[rain13]

hello If I do this:

UPDATE general SET Value='true' WHERE Setting='ALLOWBBCODE';UPDATE general SET Value='false' WHERE Setting='ALLOWSMILES';UPDATE general SET Value='0' WHERE Setting='LASTUPDATE';UPDATE general SET Value='0' WHERE Setting='MAXSIGNATURELEN';UPDATE general SET Value='' WHERE Setting='SITENAME';UPDATE general SET Value='Y/M/d H:i:s' WHERE Setting='TIMEFORMAT';UPDATE general SET Value='20' WHERE Setting='USERSPERPAGE';

I get:

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 general SET Value='true' WHERE Setting='ALLOWSMILES';UPDATE general SET ' at line 2

If I pase this sql code to phpmyadmin I get this:

UPDATE general SET Value='true' WHERE Setting='ALLOWBBCODE';# MySQL returned an empty result set (i.e. zero rows).UPDATE general SET Value='true' WHERE Setting='ALLOWSMILES';# 1 row affected.UPDATE general SET Value='0' WHERE Setting='LASTUPDATE';# MySQL returned an empty result set (i.e. zero rows).UPDATE general SET Value='0' WHERE Setting='MAXSIGNATURELEN';# MySQL returned an empty result set (i.e. zero rows).UPDATE general SET Value='' WHERE Setting='SITENAME';# MySQL returned an empty result set (i.e. zero rows).UPDATE general SET Value='Y/M/d H:i:s' WHERE Setting='TIMEFORMAT';# MySQL returned an empty result set (i.e. zero rows).UPDATE general SET Value='20' WHERE Setting='USERSPERPAGE';# MySQL returned an empty result set (i.e. zero rows).

When I replace

UPDATE general SET Value='' WHERE Setting='SITENAME';

; with

UPDATE general SET Value='abc' WHERE Setting='SITENAME';

then it says # 1 row affected. Also when I remove all other lines and leave only

UPDATE general SET Value='' WHERE Setting='SITENAME'

then it says # 1 row affected even if Value='' but if if I have SITENAME with empty value in long query like a one above then I get that empty result again. Does anyone know what is causing these errors and how I could fix these? I could use for loop in PHP but I would like to send it all as 1 query.

[niche]

Are you using mysql in a php query?

Edited September 29, 2012 by niche

[rain13]

what do you mean by that? I use mysql_query($update); where $update is string which's value is the following:

UPDATE general SET Value='true' WHERE Setting='ALLOWBBCODE';UPDATE general SET Value='false' WHERE Setting='ALLOWSMILES';UPDATE general SET Value='0' WHERE Setting='LASTUPDATE';UPDATE general SET Value='0' WHERE Setting='MAXSIGNATURELEN';UPDATE general SET Value='' WHERE Setting='SITENAME';UPDATE general SET Value='Y/M/d H:i:s' WHERE Setting='TIMEFORMAT';UPDATE general SET Value='20' WHERE Setting='USERSPERPAGE';

Edited September 29, 2012 by SoItBegins

[birbal]

mysql_query() cant execute mutiple query at once. there is mysqli::multi_query() for that. check the manualhttp://php.net/mysqli.multi_query

[niche]

That's why I asked.

[rain13]

Thankyou. Is there function to get value $link parameter? I could use $link = mysqli_connect but then I would have to use global $link; in my function. Also what's the difference between mysql_*() and mysqli_*() functions? If I used mysql_connect then can I still use that link in mysqli functions?

Edited September 29, 2012 by SoItBegins

[birbal]

I could use $link = mysqli_connect but then I would have to use global $link; in my function.

wwhich function? i am not sure what you mean.

If I used mysql_connect then can I still use that link in mysqli functions?

it could work but it is wise to not mixup both api and mysql resource. as both are different db engine it would be possible that internal database resource would not be exact same for both

Also what's the difference between mysql_*() and mysqli_*() functions?

mysql_*() is for previous mysql engine. and later one is after mysql 5.1. mysql is obsolete now use mysqli or pdo.

[rain13]

Thanks.1 more question: if I use mysqli will my code still work with older SQL/PHP?

[birbal]

it will work as long server php has mysqli enabled and have newer mysql database engine 5.1 and later. php newer version instllation forom php.net have mysqli by default.