update database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
djmystic82
Forum Newbie
Posts: 3
Joined: Wed Jun 02, 2004 1:54 pm

update database

Post by djmystic82 »

I'm trying to get this script working:

$sql = "UPDATE $table_name
SET
data = \"$data1\"
WHERE id = \"1\"

data = \"$data2\"
WHERE id = \"2\"

";

The section in bold is a second set of data I want to update at the same time as $data1. Without the bold section there the statement works fine. I would like to know how to either build a repeat loop into the $data1 section, or how to extend the string to update more than one thing at once.
jason
Site Admin
Posts: 1767
Joined: Thu Apr 18, 2002 3:14 pm
Location: Montreal, CA
Contact:

Post by jason »

Code: Select all

<?php
$sql = "UPDATE $table_name SET data = '$data1' WHERE id = '1' OR data = '$data2' WHERE id = '2'"
?>
Hint: echo mysql_error();
djmystic82
Forum Newbie
Posts: 3
Joined: Wed Jun 02, 2004 1:54 pm

Post by djmystic82 »

I tried putting the OR in like so:

$sql = "UPDATE $table_name
SET
data = \"$data1\"
WHERE id = \"1\"
OR
data = \"$data2\"
WHERE id = \"2\"

";


$result = @mysql_query($sql,$connection) or die("Couldn't execute query.");

The result died with the extra script. I also tried changing the " for ' like your script shows & this died also.

Is it possible to make a loop script to substitute for the numbers?
Last edited by djmystic82 on Thu Jun 03, 2004 10:54 am, edited 1 time in total.
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post by magicrobotmonkey »

i would try it with an AND instead
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

using Jason's giant hint:

$result = @mysql_query($sql,$connection) or die(mysql_error());

it should tell you exactly what's wrong.
djmystic82
Forum Newbie
Posts: 3
Joined: Wed Jun 02, 2004 1:54 pm

Post by djmystic82 »

Ok I get this message using: (mysql_error());

You have an error in your SQL syntax near 'WHERE id = "2" ' at line 7

with the script like this:

$sql = "UPDATE $table_name
SET
data = \"$data1\"
WHERE id = \"1\"
AND
data = \"$data2\"
WHERE id = \"2\"
";

$result = @mysql_query($sql,$connection) or die(mysql_error());

being a bit of a newbie i can't see what is wrong.

note: the script works fine if written like this:

$sql = "UPDATE $table_name
SET
data = \"$data1\"
WHERE id = \"1\"
";

$result = @mysql_query($sql,$connection) or die(mysql_error());
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post by magicrobotmonkey »

echo out that query just before you submit it to make sure it looks right, and i think i was wrong about AND
Post Reply