FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Updating multiple MySQL rows at once





welshsteve
Hi everyone, I'm hoping someone can help me with this. I'm pretty new to PHP really and I'm trying to adapt a script I've used on another page (successfully). This is what I have.

FORM PAGE
Code:

<?php
$con = mysql_connect("SERVER","USER","PASS");
if (!$con)
  {
  die('Could not connect: ' . mysql_error() . '<br /><br />Please contact the website administrator');
  }

mysql_select_db("DATE", $con);

$sql = "SELECT * FROM Div_Prem ORDER BY Team";
$result = mysql_query($sql);
if (!$result)
echo mysql_error();

echo "<table>
<tr>
<th>Team</th>
<th>Pld</th>
<th>W</th>
<th>F</th>
<th>Ded</th>
</tr>";
       
while($row = mysql_fetch_array($result))
  {
    echo "<tr>";
    echo "<td>" . $row['Team'] . "<input type='hidden' name='Team[]' value='" . $row['Team'] . "' /></td>";
    echo "<td><input type='text' style='width:20px;' name='Pld[]' value='" . $row['Pld'] . "' /></td>";
    echo "<td><input type='text' style='width:20px;' name='W[]' value='" . $row['W'] . "' /></td>";
    echo "<td><input type='text' style='width:20px;' name='F[]' value='" . $row['F'] . "' /></td>";
    echo "<td'><input type='text' style='width:20px;' name='Ded[]' value='" . $row['Ded'] . "' /></td>";
  echo "</tr>";
  }
echo "</table>";
echo "<input type='submit' name='submit' value='Update Table' />";
?>


PROCESSING PAGE
Code:

<?php
$con = mysql_connect("SERVER","USER","PASS");
if (!$con)
  {
  die('Could not connect: ' . mysql_error() . '<br /><br />Please contact the website administrator');
  }

mysql_select_db("DATE", $con);

$team = $_POST['Team'];
$pld = $_POST['Pld'];
$w = $_POST['W'];
$f = $_POST['F'];
$ded = $_POST['Ded'];
// run through the array and for each entry in the array, call this variable "$name"
foreach ($team as $Team){
     if ($Team !== ''){
          $sql = sprintf("UPDATE `Div_Prem` (`Team`,`Pld`,`W`,`F`,`Ded`) VALUES('%s','%s','%s','%s','%s');", $Team, $pld, $w, $f, $ded);
// sprintf will substitute the %s' for the variables listed at the end of the statement
          echo $sql;
          echo "<br /><br />";
          $qry = mysql_query($sql) or die("Error updating table<br />: ".mysql_error());
     }

echo "<p>Table updated successfully.</p>";
echo "<p><a href='admin.php'>Back to main admin page</a></p>";

mysql_close($con);
?>

This is what I get on the page (I have "echo" on for the SQL so I can see what SQL is running).

UPDATE `Div_Prem` (`Team`,`Pld`,`W`,`F`,`Ded`) VALUES('Bishops Tachbrook','Array','Array','Array','Array');

Error updating table
: 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 '(`Team`,`Pld`,`W`,`F`,`Ded`) VALUES('Bishops Tachbrook','Array','Array','Array',' at line 1


It only seems to be producing one SQL statement, and not one for each record in the table.

I'm such a newb and stuck on this

I normally do all this admin through phpMyAdmin, but I'm going away soon so am making a more extensive "site based", and easier to understand admin area for my temporary stand in while I'm away.
jmraker
I'm sure you have the sql update syntax wrong.
http://dev.mysql.com/doc/refman/5.0/en/update.html

You can't update multiple records with different values in one statement. You can only update multiple records to the same value

Code:
UPDATE table SET ok=1 WHERE number1>10

or you can do the same math on multiple records

Code:
UPDATE table SET viewed=viewed+1 WHERE number1>10

Where it'll add 1 to "viewed" on multiple records

The insert statement can insert multiple records with different data
http://dev.mysql.com/doc/refman/5.0/en/insert.html
welshsteve
Hi, I realised that myself eventually, and have now sorted it.

Code:

<?php
$con = mysql_connect("SERVER","USER","PASS");
if (!$con)
  {
  die('Could not connect: ' . mysql_error() . '<br /><br />Please contact the website administrator');
  }

mysql_select_db("DATA", $con);
$team = $_POST['Team'];
$pld = $_POST['Pld'];
$w = $_POST['W'];
$f = $_POST['F'];
$ded = $_POST['Ded'];
$id = $_POST['ID'];
// run through the array and for each entry in the array, call this variable "$name"
for ($i=0; $i<sizeof($team); $i++){
// start a variable called $i at 0, make it count the size of the $team array and stop there, and make $i bigger by one each time

if ($team[$i] !== ''){
         $sql = sprintf("UPDATE `Div_A` SET `Team`='%s',`Pld`='%s',`W`='%s',`F`='%s',`Ded`='%s' WHERE Div_Prem.ID='%s';", $team[$i], $pld[$i], $w[$i], $f[$i], $ded[$i], $id[$i]);
// sprintf will substitute the %s' for the variables listed at the end of the statement
/*
echo $sql;
echo "<br /><br />";
*/
         $qry = mysql_query($sql) or die("Error updating table<br />: ".mysql_error());
    }

}echo "<p>Table updated successfully.</p>";
echo "<p><a href='admin.php'>Back to main admin page</a></p>";
campuspk
CREATE TABLE `test_mysql` (
`id` int(4) NOT NULL auto_increment,
`name` varchar(65) NOT NULL default '',
`lastname` varchar(65) NOT NULL default '',
`email` varchar(65) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=7 ;

--
-- Dumping data for table `test_mysql`
--

INSERT INTO `test_mysql` VALUES (1, 'Billly', 'Blueton', 'bb5@phpeasystep.com');
INSERT INTO `test_mysql` VALUES (2, 'Jame', 'Campbell', 'jame@somewhere.com');
INSERT INTO `test_mysql` VALUES (3, 'Mark', 'Jackson', 'mark@phpeasystep.com');
INSERT INTO `test_mysql` VALUES (4, 'Linda', 'Travor', 'lin65@phpeasystep.com');
INSERT INTO `test_mysql` VALUES (5, 'Joey', 'Ford', 'fordloi@somewhere.com');
INSERT INTO `test_mysql` VALUES (6, 'Sidney', 'Gibson', 'gibson@phpeasystep.com');
Related topics
windows xp
Help for sending 2+ cookies in one page.
Free Mac FTP Software
The way we create Art will never be the same!
Resize a batch of images
All About WoW
space elevator?
multiple mysql connections
Posting Data to multiple sites at once?
mysql insert into multiple tables at once?
Looping through multiple mysql results
how does on crop multiple frames at once?
Renaming Multiple Files At Once (Windows)
Need info on large mysql databases
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.