Jump to content

Problem (syntax error) with updating multiple rows


rain13
 Share

Recommended Posts

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.

Link to comment
Share on other sites

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 by SoItBegins
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by SoItBegins
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...