current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute:
up vote 0 down vote favorite
1

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 '13 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 '13 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 '13 at 23:35
up vote 5 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 '13 at 0:33
    
is it possible to add a where clause to this sort of statement? – user2880653 Oct 15 '13 at 22:45
    
Of course, added some example to the answer – aorcsik Oct 16 '13 at 6:25
up vote 1 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 '13 at 23:25
    
I recommend using TEXT type instead of VARCHAR type for your MySQL database and combine it with textareas for your form instead of textboxes- this even keeps spaces in your data. – KarlosFontana Nov 28 '13 at 14:53
    
Somehow the keyword AND does not work for update statements, rather use a komma instead: UPDATE TableName SET column1='value' AND column2='value2' WHERE someColumn='some_value'; does not work. Rather use: UPDATE tableName SET column1='value', column2='value2' WHERE someColumn='some_value'; – KarlosFontana Nov 28 '13 at 14:54
up vote 1 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 '13 at 23:42
    
I wonder how you got that idea from this answer. – aorcsik Oct 14 '13 at 23:44
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 '13 at 23:18

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.