CODEWITHSUNDEEP
phpmysql
Chris
Chris

Reputation: 1883

Updating multiple MySQL fields with a single query via PHP

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";

?>

Upvotes: 0

Views: 10522

Answers (2)

Sergey Onishchenko
Sergey Onishchenko

Reputation: 7851

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.

Upvotes: 1

Your Common Sense
Your Common Sense

Reputation: 157839

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.

Upvotes: 2

Related Questions