Can I update multiple rows in table at once from data displayed in a table?

bajangerry · February 26, 2010

Hi Guys,

I need some help here understanding how best to update multiple rows in a table at once. I have a table which displays a number of columns, one of which I need to have the users enter some data in and when they have completed all the rows then they should click a button and the database table should be updated with the new data. I am stumped as to how to achieve this multiple row update at once. The code I have so far simple displays the data and I can not figure out where to go from here:

Any help gratefully accepted!

<?php

include "lib/config.php";

top();

$no=$_GET['no'];

?>

<html>

<head>

<title>Mobile Calls</title>

</style>

</head>

<body>

<br>

<div align="center"> <font size = "3" color="#FF0000"><strong>Calls made by Mobile Number: <?echo ($no)?> </strong></font></div><br>

<tr>

<td nowrap><div align="center"> <font size="3"><strong>Location</strong></font></div></td>

<td nowrap><div align="center"> <font size="3"><strong>Called No</strong></font></div></td>

<td nowrap><div align="center"> <font size="3"><strong>System Time</strong></font></div></td>

<td nowrap><div align="center"> <font size="3"><strong>Call Length</strong></font></div></td>

<td nowrap><div align="center"> <font size="3"><strong>Account Code</strong></font></div></td>

</tr>

<?

$result = mysql_query("SELECT * FROM mobile WHERE `service` = '$no'");

while ($row = mysql_fetch_object($result)){

?>

<td><div align="center"><font size="3"><?php echo ($row->location);?></font></div></td>

<td><div align="center"><font size="3"><?php echo ($row->dialed);?></font></div></td>

<td><div align="center"><font size="3"><?php echo ($row->duration);?></font></div></td>

<td><div align="center"><input name="accode" type="text" id="accode" size="6"value=<?php echo ($row->accountcode);?>></font></div></td>

</tr>

<?

}

?>

</table>

</body>

</html>

schilly · February 26, 2010

put a count in your while and name each textfield accode$count. also add a hidden with some kind of identifier for that row. name='hidden$count' and value='$mobile_id'.

then when you submit cycle through the post array. check for any accode fields and update according to the hidden field id.

bajangerry · March 1, 2010

Thanks schilly, that makes sense so I will give that a try and let you know how it goes.

bajangerry · March 1, 2010

schilly,

I think I need some further help with this as I am having trouble figuring out how to "cycle" through the post array, can you explain what you mean by that? I have got this working if I have one entry in the table and I can update the field fine that way but once there are more than one row in teh table I am having a problem.

Goat · March 1, 2010

schilly,

I think I need some further help with this as I am having trouble figuring out how to "cycle" through the post array, can you explain what you mean by that? I have got this working if I have one entry in the table and I can update the field fine that way but once there are more than one row in teh table I am having a problem.

If you output this as html code:

<input type="text" name="location[12]">

Browser will send output as array. You can use foreach or something to iterate trough it and execute mass update query.

Goat

bajangerry · March 1, 2010

Goat,

My problem here is ensuring that the update is applied to the correct row in the table which does NOT have a unique identifier(stupid I know, but I inherited it this way). So I am hoping to send multiple fields so that the update query will be able to use them to confirm that the row is the correct one to update. That being the case I am having a problem understanding how I can get these multiple fields back out of the array with the FOREACH to be able to use them in the update query. This is what I have so far now with the update query commented out and the foreach results echoed to screen.

<?php

include "lib/config.php";

top();

$no=$_GET['no'];

if(isset($_POST['send']))

{

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

echo $key.": ". $value;

echo "<br>";

//$update = "UPDATE mobile SET accountcode = '$code' WHERE dialed = '$dialed' AND location = '$location' AND date = '$date' AND time = '$time' AND duration = '$duration'";

//$result = mysql_query($update) or die(mysql_error());

//flush();

}

?>

<html>

<head>

<title>Mobile Calls</title>

</style>

</head>

<html>

<body>

<br>

<div align="center"> <font size = "3" color="#FF0000"><strong>Calls made by Mobile Number: <?echo ($no)?> </strong></font></div><br>

<tr>

<td nowrap><div align="center"> <font size="3"><strong>Location</strong></font></div></td>

<td nowrap><div align="center"> <font size="3"><strong>Called No</strong></font></div></td>

<td nowrap><div align="center"> <font size="3"><strong>System Time</strong></font></div></td>

<td nowrap><div align="center"> <font size="3"><strong>Call Length</strong></font></div></td>

<td nowrap><div align="center"> <font size="3"><strong>Account Code</strong></font></div></td>

</tr>

<?

$result = mysql_query("SELECT * FROM mobile WHERE `service` = '$no'");

$count += 0;

while ($row = mysql_fetch_object($result)){

$count += 1;

?>

<td><div align="center"><font size="3"><?php echo ($row->location);?></font></div></td>

<td><div align="center"><font size="3"><?php echo ($row->dialed);?></font></div></td>

<td><div align="center"><font size="3"><?php echo ($row->duration);?></font></div></td>

<td><div align="center"><input name="accode<? echo $count;?>" type="text" id="accode" size="6"value=<?php echo ($row->accountcode);?>></font></div></td>

<input type="hidden" name="count<? echo $count;?>" value="<?php echo $count?>">

<input type="hidden" name="date<? echo $count;?>" value="<?php echo ($row->date)?>">

<input type="hidden" name="location<? echo $count;?>" value="<?php echo ($row->location)?>">

<input type="hidden" name="dialed<? echo $count;?>" value="<?php echo ($row->dialed)?>">

<input type="hidden" name="time<? echo $count;?>" value="<?php echo ($row->time)?>">

<input type="hidden" name="duration<? echo $count;?>" value="<?php echo ($row->duration)?>">

</tr>

<?

}

?>

</table>

<br>

</form>

</body>

</html>

Goat · March 1, 2010

You say you don't have primary key in your table? Really strange, I am not mysql expert, but I doubt that you can even create table with no primary key.

however if you type something like:

<input name="accode[<? echo $count;?>]" type="text" size="6"value=<?php echo ($row->accountcode);?>>

browser WILL output it as array. You can use same thing for all hidden fields. Then you can use something like this for iteration:


for($counter = 0; $counter < sizeof($_POST['accode']); $counter++)
{
  $sql = "UPDATE table SET accode='".$_POST['accode'][$counter]."' WHERE date='".$_POST['date'][$counter]."' AND " ///......
  mysql_query($query);
}

Goat

bajangerry · March 1, 2010

Goat,

I added a primary key to the table (mysql will allow you to create table with no indexes although why you would do so I do not know) as a column called "id" which will auto increment. I still am not getting the results I expect though, even when I echo the count value I get nothing showing up:

<?php

include "lib/config.php";

top();

$no=$_GET['no'];

if(isset($_POST['send']))

{

for($counter = 0; $counter < sizeof($_POST['accode']); $counter++)

{

echo "size of array = ".sizeof($_POST['accode'])."<br>";

$update = "UPDATE mobile SET accountcode='".$_POST['accode'][$counter]."' WHERE id ='".$_POST['id'][$counter]."' ";

$result = mysql_query($update) or die(mysql_error());

//flush();

}

?>

<html>

<head>

<title>Mobile Calls</title>

</style>

</head>

<html>

<body>

<br>

<div align="center"> <font size = "3" color="#FF0000"><strong>Calls made by Mobile Number: <?echo ($no)?> </strong></font></div><br>

<tr>

<td nowrap><div align="center"> <font size="3"><strong>Location</strong></font></div></td>

<td nowrap><div align="center"> <font size="3"><strong>Called No</strong></font></div></td>

<td nowrap><div align="center"> <font size="3"><strong>System Time</strong></font></div></td>

<td nowrap><div align="center"> <font size="3"><strong>Call Length</strong></font></div></td>

<td nowrap><div align="center"> <font size="3"><strong>Account Code</strong></font></div></td>

</tr>

<?

$result = mysql_query("SELECT * FROM mobile WHERE `service` = '$no'");

$count += 0;

while ($row = mysql_fetch_object($result)){

$count += 1;

?>

<td><div align="center"><font size="3"><?php echo ($row->location);?></font></div></td>

<td><div align="center"><font size="3"><?php echo ($row->dialed);?></font></div></td>

<td><div align="center"><font size="3"><?php echo ($row->duration);?></font></div></td>

<td><div align="center"><input name="accode<? echo $count;?>" type="text" id="accode" size="6"value=<?php echo ($row->accountcode);?>></font></div></td>

</tr>

<?

}

?>

</table>

<br>

</form>

</body>

</html>

Goat · March 1, 2010

Add [] in accode input name so html ti generates shows as <input name="accode[22]" .... Also you need "" at value property. You can use print_r($_POST); to see exactly what data form sends back to you.

Goat

bajangerry · March 1, 2010

Sorry goat but I am not totally confused by whatyou mean in your last post :confused

"Add [] in accode input name"... where exactly do you mean?

I got the output printing with the print_r($_POST); and I can see data is being passed but still not getting it into the table... : :-[

Goat · March 1, 2010

instead of

<input name="accode<? echo $count;?>"

write

<input name="accode[<? echo $count;?>]"

schilly · March 1, 2010

are you getting an sql error?

echo $update in that for loop so you know what you're updating.

can you post a print_r of the $_POST array?

bajangerry · March 1, 2010

Ok, did that so that the output from my " print_r($_POST);" give me:

Array ( [accode] => Array ( [1] => 99999 [2] => 12345 ) [id] => Array ( [1] => 1 [2] => 2 ) [send] => Update )

which actually updates the first row in the table but not the second for some reason. I have the following:

if(isset($_POST['send']))

{

for($counter = 0; $counter < sizeof($_POST['accode']); $counter++)

{

$update = "UPDATE mobile SET accountcode='".$_POST['accode'][$counter]."' WHERE id ='".$_POST['id'][$counter]."' ";

$result = mysql_query($update) or die(mysql_error());

//flush();

}

Any suggestion as to why the second row does not get updated?

Goat · March 1, 2010

My fault. The counter should start from one , and it should go to sizeof.

for($counter = 1; $counter <= sizeof($_POST['accode']); $counter++)

either that or change other part of code, so id and other array start from 0.

Goat

bajangerry · March 1, 2010

That's it Goat!!! Thank you it is working now I think, will do a few more check and confirm in a minute or two

Goat · March 1, 2010

And I think it should be $count = 0; , not $count += 0;

Goat

bajangerry · March 1, 2010

Thanks Goat and schilly, it works great now, have learnt a lot from this process.

Sign In

Can I update multiple rows in table at once from data displayed in a table?

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

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information