[RESOLVED] How to Update multiple rows using single form and submit?

madhead29 · Feb 23, 2009

Hi all, Here is what I am trying to do:

Basicly I want to get the information from all rows in the database and output them into a form. It would display all the information e.g. title stuff like that but only the sort field will be editable. I have read that an array would do this? but to be honest I dont even know where to start. Also i would like it to be editable then update using 1 submit button that update all records. I have also attached an image showing what is looks like. Please note the title part would be from the database e.g. Image 1. Thanks all.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Sort Order System</title>
<?php $area = $_GET['area']; ?>
<?php 
include('db_connect.php');
$db=opendatabase();
?>
</head>
<body>

<?php
$sql="SELECT id,title,sort_order FROM gallery WHERE area=$area ORDER BY sort_order ASC;";
$result=mysql_query($sql); 

// Count table rows 
$count=mysql_num_rows($result); 
?>
<table width="500" border="0" cellspacing="1" cellpadding="0"> 
<form name="form1" method="post" action=""> 
<tr> 
<td> 
<table width="500" border="0" cellspacing="1" cellpadding="0"> 


<tr> 
<td align="center"><strong>Id</strong></td> 
<td align="center"><strong>Title</strong></td> 
<td align="center"><strong>Sort Order</strong></td>

</tr> 
<?php 
while($rows=mysql_fetch_array($result)){ 
?> 
<tr> 
<td><? $id[]=$rows['id']; ?><? echo $rows['id']; ?></td> 
<td><? echo $rows['title']; ?></td> 
<td><input name="sort_order[]" type="text" id="sort_order" value="<? echo $rows['sort_order']; ?>"></td> 
</tr> 
<?php 
} 
?> 
<tr> 
<td colspan="4" align="center"><input type="submit" name="Submit" value="Submit"></td> 
</tr> 
</table> 
</td> 
</tr> 
</form> 
</table> 
<?php 
// Check if button name "Submit" is active, do this 
if($Submit){ 
for($i=0;$i<$count;$i++){ 
$sql1="UPDATE gallery SET sort_order='$sort_order[$i]' WHERE id='$id[$i]'"; 
$result1=mysql_query($sql1); 
} 
} 

if($result1){ 
header("location:sort.php"); 
} 
mysql_close(); 
?> 

</body>
</html>

I am using the above code at the moment, it displays the information i want but when i submit the pages just does nothing without changing the values i think this is because its using register globals which i have never used before as i normally use super globals as they are more secure, well so i have read. Should i be using an array for this? I dont even know where to begin using arrays. Any help would be great. Thanks.

sneakyimp · Feb 23, 2009

I haven't read your code, but if you are curious about how to create arrays by submitting a form you may find this helpful.

Step 1: Create your form and use square brackets in your input names

// form.html
<form action="handler.php" method="post">
<input type="text" name="foo[1]"><br>
<input type="text" name="foo[2]"><br>
<input type="text" name="foo[3]"><br>
<input type="text" name="foo[4]"><br>
<input type="text" name="foo[5]"><br>
<input type="text" name="foo[6]"><br>
</form>

Then in your code that handles the forum submission, you'll find that the variable $POST contains an array item 'foo':

// handler.php
print_r($_POST);

// you can loop through the items in $_POST['foo'] like this:
foreach($_POST['foo'] as $key => $value) {
  echo "foo[$key]=$value<br>";
}

The whole trick is to design your form so that the ids from your database end up in the brackets.

sneakyimp · Feb 23, 2009

Also, register_globals is BAD. Do not rely on register_globals. Turn it off and do things using $POST or $GET.

madhead29 · Feb 23, 2009

Hi thanks for the reply I am doing abit of what you said here is my updated code:

<?php
$sql="SELECT id,title,sort_order FROM gallery WHERE area=$area ORDER BY sort_order ASC;";
$result=mysql_query($sql);
$entries = mysql_num_rows($result);

if(isset($_POST['submit'])){
$id = $_POST['id'];	
$sort_order = $_POST['sort_order'];  

for($i=0;$i<$entries;$i++){ 
mysql_query("UPDATE gallery SET sort_order='$sort_order[$i]' WHERE id='$id[$i]'") or 

die(mysql_error());; 
}
}
?>

<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>" name="form1"> 
<table cellspacing="1" cellpadding="0"> 
<tr> 
<td>Name</td> 
<td>Title</td> 
</tr> 

<?php
while($info = mysql_fetch_assoc($result)){ 
?>

<input type="hidden" name="id[]" value="<? echo $info['id'];?>" /> 
<tr> 
<td><? echo $info['title'];?></td> 
<td><input type="text" name="sort_order[]" value="<? echo $info['sort_order'];?>" /></td> 
</tr> 

<?php
}
?>

<tr> 
<td align="center"><input type="submit" name="submit" value="Submit" /></td>
</tr>

</table> 
</form>

<?php 
mysql_close(); 
?>

But its not putting the code how i want it here is the html output of 1 of the input fields:

<input type="hidden" name="id[]" value="120" /> 
<tr> 
<td>Unreal Character 1</td> 
<td><input type="text" name="sort_order[]" value="2" /></td> 
</tr>

So its not getting the info into the right places.

and then when i press submit i get to errors:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /gallery-admin/sort.php on line 17

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /gallery-admin/sort.php on line 39

thanks

sneakyimp · Feb 23, 2009

You haven't bothered to check for error conditions when you run your query. Apparently there's a problem with it. Check the documentation for [man]mysql_query[/man] for more information.

madhead29 · Feb 23, 2009

OK thanks will do.

madhead29 · Feb 24, 2009

Ive sorted it thanks to your example sneakyimp.

Here is my code now:

sort.php

<?php
$sql="SELECT id,title,sort_order FROM gallery WHERE area=$area ORDER BY sort_order ASC;";
$result=mysql_query($sql); 

// Count table rows 
$count=mysql_num_rows($result); 
?>
<table width="500" border="0" cellspacing="1" cellpadding="0"> 
<form name="form1" method="post" action="sort-backend.php">
<tr> 
<td><strong>Id</strong></td> 
<td><strong>Title</strong></td> 
<td><strong>Sort Order</strong></td>
</tr> 
<tr>
<?php
while ($row = mysql_fetch_array($result)){ ?>
	<tr>
    <td><?php echo $row['id']; ?></td>
	<td><?php echo $row['title']; ?></td>
    <td><input type="text" name="foo[<?php echo $row['id']; ?>]" value='<?php echo $row['sort_order'];?>' /></td>
    <tr>
    <? } ?>
<tr> 
<td colspan="4" align="center"><input type="submit" name="Submit" value="Submit"></td> 
</tr> 

</form> 
</table>

sort-backend.php:

<?php $sort_order=$_POST['sort_order']; ?>
<?php 
foreach($_POST['foo'] as $key=>$value){ 
$sql1="UPDATE gallery SET sort_order='$value' WHERE id='$key'" or die(mysql_error());
$result1=mysql_query($sql1); 
}
echo "update complete";
?>

I just want to make sure all this is corrent and done in a secure way?

Also is it possible to do this using one .php file instead of using sort.php and sort-backend.php?

Just to make sure none of this is using register_globals is it? Dont think i have them turned on anyway.

Thanks for your help sneakyimp

markfc · Feb 24, 2009

You sure can, take the code from sort-backend.php and add it to the top of sort.php

if(isset($_POST['submit'])){

update sql blah blah

}

similar to how you did it in the code from your first post.

Putting it at the top ensures that when you view the table below, you know you'll be using the latest updated data.

madhead29 · Feb 24, 2009

Hi thanks for the reply im not sure if i understood you correctly, is this code corrent:

<?php 
if(isset($_POST['submit'])){
foreach($_POST['foo'] as $key=>$value){ 
$sql1="UPDATE gallery SET sort_order='$value' WHERE id='$key'" or die(mysql_error());
$result1=mysql_query($sql1); 
}
echo "update complete";
}
?>
<?php
$sql="SELECT id,title,sort_order FROM gallery WHERE area=$area ORDER BY sort_order ASC;";
$result=mysql_query($sql); 

// Count table rows 
$count=mysql_num_rows($result); 
?>

<table width="500" border="0" cellspacing="1" cellpadding="0"> 
<form name="form1" method="post" action="">
<tr> 
<td><strong>Id</strong></td> 
<td><strong>Title</strong></td> 
<td><strong>Sort Order</strong></td>
</tr> 
<tr>
<?php
while ($row = mysql_fetch_array($result)){ ?>
	<tr>
    <td><?php echo $row['id']; ?></td>
	<td><?php echo $row['title']; ?></td>
    <td><input type="text" name="foo[<?php echo $row['id']; ?>]" value='<?php echo $row['sort_order'];?>' /></td>
    <tr>
    <? } ?>
<tr> 
<td colspan="4" align="center"><input type="submit" name="Submit" value="Submit"></td> 
</tr> 

</form> 
</table>
</body>
</html>

Using the code above the page just refreshes and does nothing.

djjjozsi · Feb 24, 2009

think of this line:
this is a mistake.

$sql1="UPDATE gallery SET sort_order='$value' WHERE id='$key'" or die(mysql_error());

read my signature.

madhead29 · Feb 24, 2009

Well I don't understand how as that is the same update code I used when it was too separate files and it worked fine. Do you know why its not correct?

djjjozsi · Feb 24, 2009

$sql1="UPDATE gallery SET sort_order='$value' WHERE id='$key'" or die(mysql_error());

If you create a string, a normal variable you should not use the OR DIE functions. see ?

And in your query:

r FROM gallery WHERE area=$area ORDER BY sort_order ASC;

Around the $area variable you forget to used the apostrofe .

madhead29 · Feb 24, 2009

Right thanks I have made the changes but it dont change anything it still just refreshes. The code is now:

<?php 
if(isset($_POST['submit'])){
foreach($_POST['foo'] as $key=>$value){ 
$sql1="UPDATE gallery SET sort_order='$value' WHERE id='$key'";
$result1=mysql_query($sql1); 
}
echo "update complete";
}
?>
<?php
$sql="SELECT id,title,sort_order FROM gallery WHERE area='$area' ORDER BY sort_order ASC;";
$result=mysql_query($sql); 

// Count table rows 
$count=mysql_num_rows($result); 
?>

<table width="500" border="0" cellspacing="1" cellpadding="0"> 
<form name="form1" method="post" action="">
<tr> 
<td><strong>Id</strong></td> 
<td><strong>Title</strong></td> 
<td><strong>Sort Order</strong></td>
</tr> 
<tr>
<?php
while ($row = mysql_fetch_array($result)){ ?>
	<tr>
    <td><?php echo $row['id']; ?></td>
	<td><?php echo $row['title']; ?></td>
    <td><input type="text" name="foo[<?php echo $row['id']; ?>]" value='<?php echo $row['sort_order'];?>' /></td>
    <tr>
    <? } ?>
<tr> 
<td colspan="4" align="center"><input type="submit" name="Submit" value="Submit"></td> 
</tr> 

</form> 
</table>

djjjozsi · Feb 24, 2009

$_POST['submit'] is undefined in your code, see the variable name in the form, its called: Submit

the variable: $area in your code is not set, called undefined index.

Hello,jjozsi.

madhead29 · Feb 24, 2009

thank you so much its working! its ok area is defined further up the page. Do you think its best always to use 1 .php file when updating,adding or deleting instead of using 2? Also is the script secure?, apart from ive not checked the user input yet which i will add now.

Thanks again.

djjjozsi · Feb 24, 2009

Do you think its best always to use 1 .php file when updating,adding or deleting instead of using 2?

if you use one file your code would be oranized.

Also is the script secure?

You've better donthis if you were used mysql_real_escape_string if the user input is a string, and use the sprintf string function to build your query a better way.
see on php.nethe modifiers: %s and %d (s - string, d is a deciml number)

if(isset($_POST['submit'])){
foreach($_POST['foo'] as $key=>$value){
$sql1=sprintf("UPDATE gallery SET sort_order='%s' WHERE id='%d'" , mysql_real_escape_string($value) , $key);
$result1=mysql_query($sql1);
}

Hello, jjozsi.

madhead29 · Feb 24, 2009

OK Thanks I will look into those.

djjjozsi · Feb 24, 2009

madhead29;10904375 wrote:
OK Thanks I will look into those.

Your welcome.
Don't forget to set this thread as resolved if its already solved.

madhead29 · Feb 24, 2009

How do i mark it as solved? Is there are button of something?

madhead29 · Feb 24, 2009

Found it thanks.

[RESOLVED] How to Update multiple rows using single form and submit?

Mmadhead29

Ssneakyimp

Ssneakyimp

Mmadhead29

Ssneakyimp

Mmadhead29

Mmadhead29

Mmarkfc

Mmadhead29

Ddjjjozsi

Mmadhead29

Ddjjjozsi

Mmadhead29

Ddjjjozsi

Mmadhead29

Ddjjjozsi

Mmadhead29

Ddjjjozsi

Mmadhead29

Mmadhead29