[SOLVED] updating multiple rows in a table in mysql

gevo12321 · July 4, 2007

ok so im making this form that updates multiple rows in the mysql database but the thing is that the number of rows is a variable so once it may be one row once it may be 31 rows. is there anyway i can do this using a while loop? or even is there a way to do this period?

trq · July 4, 2007

You may not need a while loop depending on your data.

UPDATE tbl SET fld = 'new value' WHERE id IN(1,3,4,5,6);

You'll need to be more specific / post some code.

gevo12321 · July 4, 2007

well thats the thing i dont have a code, not yet anyway, because i dont know how i would do it

i was thinking of

mysql_query("UPDATE layout SET  name='$name', source='$source'
WHERE id='$id'");

and just putting it in a while loop but the $name, $source and $id change for each row and i just dont know what i could do to make it work. because it is not always a set number of rows.

i really appreciate ur help

thx

GingerRobot · July 4, 2007

Where is the data going to be coming from? A form? When/why will the number of rows change?

Without anymore details, we will largely be guessing, but assuming you're using a form, and the data is in arrays, a while or foreach loop will do fine:

<?php
foreach($ids as $value){
$name = $names[$value];
$source = $sources[$value];
mysql_query("UPDATE `layout` SET `name`='$name', `source`='$source' WHERE `id`='$id'") or die(mysql_error());
}
?>

So this is assuming you have 3 arrays(ids, names, sources) where the id being updated is the key of the names and sources array. But it all depends on how you want to do this.

gevo12321 · July 4, 2007

ok

so it is a form and the form has the following code:

<?php
require_once('../connect.php');

$query = mysql_query("SELECT * FROM layout") or die(mysql_error());

echo "<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\"
\"http://www.w3.org/TR/html4/loose.dtd\">
<html>
<head>
<title>E Layout</title>
<meta http-equiv=\"Content-Type\" content=\"text/html; charset=iso-8859-1\">
</head>
<body>
<a href=\"eenglish.htm\">English<a> > <a href=\"eeditlayout.php\">E Layout<a>
<hr>
<form action=\"esubmiteditlayout.php\" method=\"post\">
<table width=\"100%\"  border=\"0\">";
while($row=mysql_fetch_object($query))
{
$id=$row->id;
$name=$row->name;
$source=$row->source;
echo "
  <tr>
    <td valign=\"top\" width=\"2%\"><input type=\"checkbox\" name=\"checkbox";
echo $id;
echo "\"></td>
    <td valign=\"top\" width=\"5%\">Button ";
echo $id; 
echo ":</td>
    <td valign=\"top\" width=\"10%\"><input type=\"text\" name=\"button";
echo $id;
echo "\" value=\"";
echo $name;
echo "\"/></td>
    <td valign=\"top\" width=\"8%\">Button ";
echo $id;
echo " Source:</td>
    <td valign=\"top\" align=\"left\"><input type=\"text\" name=\"button";
echo $id;
echo "src\" value=\"";
echo $source;
echo "\"/></td>
  </tr>";
}
echo "
</table>
<input type=\"submit\" name=\"edit\" value=\"Edit\">
<input type=\"submit\" name=\"insert\" value=\"Insert\">
<input type=\"submit\" name=\"del\" value=\"Delete\" onclick=\"return deldel()\">
<script language=\"javascript\">
function deldel()
{
   var agree=confirm(\"Are you sure you want to delete the checked buttons from the Side Bar?\");
   if(agree)
      return true;
   else
      return false;
}
</script>
</form>
</body>
</html>";

mysql_close($link);
?>

the number of rows will change depending on weather i want to add new rows or delete some.

i probably do need to use arrays but I'm completely new to this and I've tried reading about arrays but i haven't a single clue as to what they do.

I'm completely dazed and confused

plz help me

gevo12321 · July 4, 2007

bump

gevo12321 · July 5, 2007

bump again

suma237 · July 5, 2007

where is the code of edit

gevo12321 · July 5, 2007

i dont have a code for edit

thats what i was trying to figure out how to do

gevo12321 · July 5, 2007

bump

gevo12321 · July 5, 2007

yay another bump

xyn · July 5, 2007

This is what I would do...

I would update say an name by ID; so the first thing

you would do is check your database and display

a form with all names and.

check_name.php

<?php
include("connect.php");
$sql = mysql_query("SELECT * 
FROM ` account`");
while($data = mysql_fetch_array($sql))
{
    echo('<form method="post" action="edit.php">');
    echo('<input type="hidden" name="id[]">');
    echo('<input type="text" name="name">');
    echo('<input type="submit" value="submit">
    echo('</form>');
}
?>

now then, time to update every change you have made

edit.php (exactly how Thorpe stated)

<?php
$List = implode(",", $_POST['id']);
mysql_query("UPDATE `accounts` 
SET `name`='".$_POST['name']."' 
WHERE `id` IN ($List);
?>

Now

gevo12321 · July 5, 2007

thx for the help but what the above code does is it has a submit button for each row. i want just one submit button for all the rows

xyn · July 5, 2007

ok. then move the submit button...

<?php
include("connect.php");
$sql = mysql_query("SELECT * 
FROM ` account`");

echo('<form method="post" action="edit.php">');
while($data = mysql_fetch_array($sql))
{
    echo('<input type="hidden" name="id[]">');
    echo('<input type="text" name="name">');
}
echo('<input type="submit" value="submit">
echo('</form>');
?>[/code'];


Tudh done.

gevo12321 · July 5, 2007

ok thx u rock

Sign In

[SOLVED] updating multiple rows in a table in mysql

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information