[SOLVED] UPDATE statement issue

[Matrixkid]

Hi there,

 

I currently having an issue with certain update statements and cannot figure it out.

 

Here are the statements:

UPDATE matchups SET winner = "Erik Seidel" WHERE id = '8' LIMIT 1; 
UPDATE matchups SET winner = "Daniel Negreanu" WHERE id = '9' LIMIT 1; 
UPDATE matchups SET winner = "Jennifer Tilly" WHERE id = '10' LIMIT 1;

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 'UPDATE matchups SET winner = 'Daniel Negreanu' WHERE id = '9' LIMIT 1; UPDAT' at line 2

 

Here is the table structure:

CREATE TABLE `matchups` (
  `id` int(11) NOT NULL auto_increment,
  `event` int(11) NOT NULL,
  `comp1` varchar(255) collate latin1_german2_ci NOT NULL,
  `comp2` varchar(255) collate latin1_german2_ci NOT NULL,
  `winner` varchar(255) collate latin1_german2_ci NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=11

INSERT INTO `matchups` VALUES (10, 7, 'Tom Dwan', 'Jennifer Tilly', '');
INSERT INTO `matchups` VALUES (8, 7, 'Erik Seidel', 'Ram Vaswani', '');
INSERT INTO `matchups` VALUES (9, 7, 'Amnon Filipe', 'Daniel Negreanu', '');;

 

In phpmyadmin, If I take one of the UPDATE statements and throw explain in front of it I get an error.

If I copy and paste it into phpmyadmin all the UPDATE statements, it works.

Your SQL query has been executed successfully
SQL query: 
UPDATE matchups SET winner = "Daniel Negreanu" WHERE id = '9' LIMIT 1 ;# Affected rows:1
UPDATE matchups SET winner = "Jennifer Tilly" WHERE id = '10' LIMIT 1 ;# Affected rows:1

 

If I run the query through php it returns an error as well.

 

$query .= "UPDATE matchups SET winner = '".$value."' WHERE  (id = $key) LIMIT 1;";
$result = mysql_query($query) or die(mysql_error());

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 'UPDATE matchups SET winner = 'Daniel Negreanu' WHERE id = '9' LIMIT 1; UPDAT' at line 2

 

 

So to summarize:

1) If I copy/paste all the UPDATE statements into phpmyadmin it works

2) If I take 1 of the UPDATE statements and put explain in front of it, it doesnt work

3) Neither 1 or All of the update statements work in php

 

any help would be appreciated. this is starting to drive me crazy.

 

Cheers

 

 

[PFMaBiSmAd]

The php mysql_query() statement does not support multiple queries separated by semi-colons ;

 

Too many lazy programmers were failing to validate external data to prevent multiple queries from being injected so they prevented multiple queries from being executed.

[Ken2k7]

$query .= "UPDATE matchups SET winner = '".$value."' WHERE  id = '$key' LIMIT 1;";

[Matrixkid]

The php mysql_query() statement does not support multiple queries separated by semi-colons ;

 

Too many lazy programmers were failing to validate external data to prevent multiple queries from being injected so they prevented multiple queries from being executed.

 

How would I go about executing multiple statements then?

I basically have a form that has X amount of radio button groups..so its dynamic

 

I was then doing this:

foreach($results as $key => $value){
	$query .= "UPDATE matchup SET winner = '".$value."' WHERE id = '$key' LIMIT 1;
	";
}

 

and then just execute the $query

 

Thanks for the help!

[Ken2k7]

Execute them one at a time.

[Matrixkid]

Execute them one at a time.

 

LOL ... yeah I guess that works.

 

....its going to be a long day

[fenway]

Why not simply run the statement in the loop, instead of concat-ing?