Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I've tried this query with both commas and "AND" statements as pictured below. I get a syntax error

Something went wrong.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 'are available 24/7 by phone and email to answer any questions and to assist you ' at line 1

every time I try this query:

$sql = mysql_query("UPDATE general
    SET bookabandheading = $_POST[bookabandheading 
    AND bookaband = $_POST[bookaband]
    AND contactus = $_POST[contactus]
    AND aboutuslisten = $_POST[aboutuslisten]
    AND contactusheading = $_POST[contactusheading]
    AND nightclubsheading = $_POST[nightclubsheading]
    AND acousticheading = $_POST[acousticheading]
    AND schoolsheading = $_POST[schoolsheading]
    AND privateheading = $_POST[privateheading]
    AND concertsheading = $_POST[concertsheading]
    AND festivalsheading = $_POST[festivalsheading]
    AND submissions = $_POST[submissions]
    AND interns = $_POST[interns]
    AND managementbio = $_POST[managementbio]
    AND latestnews = $_POST[latestnews]
    AND artistofthemonth = $_POST[artistofthemonth]
    AND artistofthemonthphoto = $_POST[artistofthemonthphoto]
    AND artistofthemonthid = $_POST[artistofthemonthid]
    AND listentoourartists = $_POST[listentoourartists]
    AND musicianswanted = $_POST[musicianswanted]
    AND aboutus = $_POST[aboutus]
    AND bshowcases = $_POST[bshowcases]
    AND bandavails = $_POST[bandavails]");

The query worked in a different database on another VPS, but I just migrated servers and it no longer works. Any help is greatly appeciated!

share|improve this question
 
If you could paste here the exact error message, that would help solving the problem a lot. –  aorcsik Oct 14 at 23:29
 
Something went wrong.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 'are available 24/7 by phone and email to answer any questions and to assist you ' at line 1 –  user2880653 Oct 14 at 23:32
 
This error means, that one of the fields in $_POST contains the string ...are available 24/7... and so on, and raw, unescaped, unquoted strings in sql queries are not allowed. –  aorcsik Oct 14 at 23:35

4 Answers

up vote 1 down vote accepted

While the main problem is that you missed the closing bracket after bookamandheading, still I would like to advise you to refactor this request for example like this:

$keys = array("bookabandheading", "bookaband", "contactus", "aboutuslisten",
              "contactusheading", "nightclubsheading", "acousticheading",
              "schoolsheading", "privateheading", "concertsheading",
              "festivalsheading", "submissions", "interns", "managementbio",
              "latestnews", "artistofthemonth", "artistofthemonthphoto",
              "artistofthemonthid", "listentoourartists", "musicianswanted",
              "aboutus", "bshowcases", "bandavails");
$set = array();
foreach ($keys as $key) {
    $set[] = sprintf(" %s = '%s' ", $key, mysql_escape_string($_POST[$key]));
}
$sql = mysql_query("UPDATE general SET " . implode(", ", $set));

It is much easier to maintain and also a bit more secure by escaping the input.

Update: add where statement example

$where = array();
$where[] = sprintf(" some_string = '%s' ", mysql_escape_string($some_string));
$where[] = sprintf(" some_integer = %d ", $some_integer);
$where = " WHERE " . implode(" AND ", $where);
$sql = mysql_query("UPDATE general SET " . implode(", ", $set) . " " . $where);
share|improve this answer
 
You sir, are correct. MUCH easier to maintain –  user2880653 Oct 15 at 0:33
 
is it possible to add a where clause to this sort of statement? –  user2880653 Oct 15 at 22:45
 
Of course, added some example to the answer –  aorcsik Oct 16 at 6:25
up vote 0 down vote 
$_POST[bookabandheading

change to

$_POST[bookabandheading]
share|improve this answer
 
Same syntax error exists even after that change. –  user2880653 Oct 14 at 23:18
up vote 0 down vote

I see 3 things wrong with this:

  • Raw POST data in your query - at the very least user mysql_real_escape_string
  • The parameters look like strings so should have quotes around them
  • There's no WHERE option, so you'll update every row in that table
share|improve this answer
 
I still can't get it to work when updated 2 or more fields after trying quotes around the strings. –  user2880653 Oct 14 at 23:25
up vote 0 down vote

You have a few errors:

  • Syntax error. Change

    $_POST[bookabandheading to $_POST[bookabandheading]

  • This is also incredibly prone to SQL injections. You should be using mysqli, but if you are set on mysql (which is deprecated as of 5.5.0), you should escape each $_POST variable using mysql_real_escape_string().

  • Each $_POST variable needs to bee parameterized using quotes a well. So, an example:

    $_POST['bookabandheading'] (do this for all $_POST variables)

share|improve this answer
 
I switched the connection string to mysqli and it works now... –  user2880653 Oct 14 at 23:42
 
I wonder how you got that idea from this answer. –  aorcsik Oct 14 at 23:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.