Update multiple rows from html form to mysql table

Codes here !

Moderators: macek, egami, gesf

nipster2
New php-forum User
New php-forum User
Posts: 2
Joined: Tue Nov 05, 2002 7:23 pm

Update multiple rows from html form to mysql table

Postby nipster2 » Tue Nov 05, 2002 7:29 pm

I have been looking for a while to update multiple rows in a mysql table and can not find the appropriate was to access the names of the text fields passed when i press submit. To help explain what I am trying to do here is a simplified bit of code:

<html>
<body>
<?php
$db = mysql_connect("host", "user","pass")or die("Unable to connect to MySQL");
print "Connected to MySQL<br>";
mysql_select_db("databasename",$db);
if ($submit) {
//here is where i need an appropriate loop to go through each name field
$sql = "UPDATE tablename SET name='$name' WHERE id=$id";
}
$result = mysql_query($sql);
echo "Record updated!<p></a>";
}
printf("<form method=\"post\" action=\"%s\"> \n",$PHP_SELF);
$count=0;
$result = mysql_query("SELECT * FROM tablename",$db);
while ($myrow = mysql_fetch_array($result)) {
$count=$count+1; //count number of rows
printf("<input type=\"Text\" name=\"id%s\" value=\"%s\"> \n", $count, $myrow["id"]);//put id in a text field will change it to hidden
printf("<input type=\"Text\" name=\"name%s\" value=\"%s\"><br> \n", $count, $myrow["name"]); //put name in a text field
}
?>
<input type="Submit" name="submit" value="Enter information"></td></tr>
</form>
</body>
</html>

If this does not make sense im sorry just ask for any clarifications.

Thanks alot

Justin

DutchBen
New php-forum User
New php-forum User
Posts: 36
Joined: Thu Oct 24, 2002 10:28 am
Location: Amsterdam

Postby DutchBen » Wed Nov 06, 2002 12:30 pm

Hi,

Here's a way to do it.

Code: Select all

<?
while ($myrow = mysql_fetch_array($result)) {
?>
<input type="hidden" name="id[]" value="<?=$myrow['id']?>">
<input type="Text" name="name[]" value="<?=$myrow['name']?>">
<?}?>


After submitting the form an array 'id' and an array 'name' is created (because of the [] after the name). Updating is much easier now eg.

foreach($id as $key => $value){

.........
$query = 'UPDATE table SET name='name[$key]' where id="$value"';
........

}

Hope it helps

Ben

nipster2
New php-forum User
New php-forum User
Posts: 2
Joined: Tue Nov 05, 2002 7:23 pm

Should work i know but

Postby nipster2 » Wed Nov 06, 2002 4:02 pm

Here is the whole thing cuz it should work and thanks alot for the help but im getting and error on line 9 which is the foreach line. Thanks again

<html>
<body>
<?php
$db = mysql_connect("host", "database","pass")or die("Unable to connect to MySQL");
print "Connected to MySQL<br>";
mysql_select_db("database",$db);
if ($submit) {
foreach($id as $key => $value){
$sql = 'UPDATE table SET name='name[$key]' where id="$value"';
}
$result = mysql_query($sql);
echo "Record updated!<p></a>";
}
printf("<form method=\"post\" action=\"%s\"> \n",$PHP_SELF);
$result = mysql_query("SELECT * FROM table",$db);
while ($myrow = mysql_fetch_array($result)) {
printf("<input type=\"hidden\" name=\"id[]\" value=\"%s\"> \n", $myrow['id']);
printf("<input type=\"text\" name=\"name[]\" value=\"%s\"><br> \n", $myrow['name']);
}
?>
<input type="Submit" name="submit" value="Enter information"></td></tr>
</form>
</body>
</html>

it should work not sure what im missing thanks

DutchBen
New php-forum User
New php-forum User
Posts: 36
Joined: Thu Oct 24, 2002 10:28 am
Location: Amsterdam

Re: Should work i know but

Postby DutchBen » Mon Nov 11, 2002 10:46 am

nipster2 wrote:foreach($id as $key => $value){
$sql = 'UPDATE table SET name='name[$key]' where id="$value"';
}

Sometimes you overlook the obvious.. escape your quotes like so

Code: Select all

$sql = "UPDATE table SET name='name[$key]' where id='$value'" ;


Also ... keep the mysql_query inside the foreach loop .. otherwise only the last insert will be executed.

Jacques
New php-forum User
New php-forum User
Posts: 10
Joined: Fri Oct 18, 2002 8:07 am

Turning in circle with simple insert!!!

Postby Jacques » Fri Dec 20, 2002 3:45 pm

Hello everyone,

I am attempting to insert into table2 2 values drawn from same drop down box. The values are taken from table1

I can do it with one value. The other one is driving in circle. Can anyone help me please

Thanks

Code: Select all

$sql = mysql_query("select category from catlisting") or die("Unable to process queries at this time: " . mysql_error());
//echo "$sql<br>";
echo "$sql<br>";
while ($i = mysql_fetch_array($sql))
{
$category = $i["category"];
echo "$category<br>";
$result .= "<OPTION value=\"$category\">$category</OPTION>";
}
?>
        <form action="test.php" method="post">
<?
 for($i=0;$i<3;$i++){
$i = $i + 1
?>
   <input name=category type=hidden class=input value="<?php echo $i[category]; ?>" size="50" maxlength="70">
        <select class=select name=category[]>
        <option value="" >Select category<?     echo "$result"; ?>
       </option>
        </SELECT>   
<?
}
?>
    <INPUT TYPE=SUBMIT  VALUE="Enter" ID="category">   
   </FORM>

<?
      foreach $_POST["category"] as $newcat {

//      if ($category !="")   {
      $sql1 = "INSERT INTO $mysql_cat (id, username, category) "." VALUES "." ('', '$username', "$newcat")";
echo "$sql1<br>";      
      $result = mysql_query($sql1) or die("ERROR: cannot INSERT data into database");
//      }
}
?>


Return to “mySQL & php coding”

Who is online

Users browsing this forum: No registered users and 1 guest

cron