Updating multiple MySQL fields with a single query via PHP

Question

I'm trying to update multiple fields in a MySQL database using PHP. The variables are passed using Ajax from an HTML form. For some reason the query appears to be failing and I can't figure out why.

I've checked that the variables have been passed correctly and all is OK, so I think there is a problem with the SQL query. I've tried going over it with a fine toothcomb but can't for the life of me figure out what's wrong! I know I'm probably missing something obvious but can anyone help me out?

Thanks!

Here is the PHP code:

<?php

//Connect to database
include_once('../../dbconnect.php');

//Retrieve Variables from AJAX call
$name = $_POST['name'];
$size = $_POST['changesize'];
$delivery = $_POST['changedelivery'];
$venue = $_POST['changevenue'];
$level = $_POST['changelevel'];
$modules = $_REQUEST['changemodules'];
$insertmodules = json_decode(stripslashes($modules), true);

//Update database using variables
mysql_query ("UPDATE users SET level=$level, size=$size, delivery=$delivery, venue=$venue, mod1=$insertmodules[0], mod2=$insertmodules[1], mod3=$insertmodules[2], mod4=$insertmodules[3], mod5=$insertmodules[4], mod6=$insertmodules[5], mod7=$insertmodules[6], mod8=$insertmodules[7], mod9=$insertmodules[8], mod10=$insertmodules[9] WHERE name=$name") 
 or die (mysql_error);

//Return Data
echo "Course updated for $name";

?>

or die(mysql_error()); will produce a meaningful response in case of an error. Note the () — N.B.
– N.B., Commented Feb 8, 2013 at 10:30
Get the basics right. Set ini_set("display_errors", "on") and error_reporting(E_ALL). You will then be able to locate and fix the error yourself. — Salman Arshad
– Salman Arshad, Commented Feb 8, 2013 at 10:30

Your Common Sense · Accepted Answer · 2013-02-08 10:30:24Z

2

mysql_error() is a function, not constant. Add braces and see what's going on.

Most likely you don't format your values properly.

For strings, for example, you have to both

enclose it in quotes
escape these quotes inside

while you're doing neither.

For numbers you have to cast them to the proper type explicitly.

answered Feb 8, 2013 at 10:30

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sergey Onishchenko · Accepted Answer · 2013-02-08 12:18:42Z

1

for example if $_POST['changelevel'] contains string, you should use the quotes. And you should implement this rule to all of your variables.

UPDATE users
SET level='$level', size='$size', delivery='$delivery', venue='$venue', mod1=$insertmodules[0], mod2=$insertmodules[1], mod3=$insertmodules[2], mod4=$insertmodules[3], mod5=$insertmodules[4], mod6=$insertmodules[5], mod7=$insertmodules[6], mod8=$insertmodules[7], mod9=$insertmodules[8], mod10=$insertmodules[9] 
WHERE name=$name")

Using without quotes permitted only with integer values.

edited Feb 8, 2013 at 12:18

answered Feb 8, 2013 at 10:30

Sergey Onishchenko

7,9595 gold badges51 silver badges52 bronze badges

Collectives™ on Stack Overflow

Updating multiple MySQL fields with a single query via PHP

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related