...

View Full Version : Multiple rows update



MSK7
05-12-2009, 04:58 PM
Hello all,

I want to update multiple rows of my table in database with one query.

Looking for a way to update multiple rows of a db I found a tutorial on first page of google... .

http://www.phpeasystep.com/mysql/10.html

I then followed the tutorial which is quite simple

so I modified it according to suit, but then couldn't get it to work.

Can anyone spot any obvious problems with this tutorial.

Please Suggest me any solutions regarding these or any other similar tutorial as quite simple as these.

Thanks & regards.

beedie
05-12-2009, 05:01 PM
show your code

Fumigator
05-12-2009, 05:12 PM
There's nothing wrong with that tutorial, apart from the lack of error checking on the queries, which is a noob mistake. However, it doesn't use a single query to update the entire form, it simply uses a loop to update each row, one at a time. This is really the only way to do it, if you are updating different data for each row and the rows don't share a common value (which is the case in the tutorial you pointed to).

So either get more specific what you are trying to update or live with multiple queries to get all the updates done.

MSK7
05-13-2009, 09:53 AM
Hello all,

Thanks for your precious suggestions.

Here is my code



<?php

$host="localhost"; // Host name
$username="myusername"; // Mysql username
$password="mypassword"; // Mysql password


// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("test3")or die("cannot select DB");


// Retrieve data from database
$sql="SELECT * FROM table3 ";

$result=mysql_query($sql);

?>

<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">&nbsp;</td>
<td align="center">&nbsp;</td>
<td align="center">&nbsp;</td>
<td align="center">&nbsp;</td>
</tr>
<tr>
<td align="center"><strong>Id</strong></td>
<td align="center"><strong>Client</strong></td>
<td align="center"><strong>Project</strong></td>
<td align="center"><strong>Module</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td align="center"><? $id[]=$rows['id']; ?> <? echo $rows['id']; ?> </td>
<td align="center"><input name="client[]" type="text" id="client" value="<? echo $rows['client']; ?>"> </td>
<td align="center"><input name="project[]" type="text" id="project" value="<? echo $rows['project']; ?>"></td>
<td align="center"><input name="module[]" type="text" id="module" value="<? echo $rows['module']; ?>"></td>
</tr>
<?php
}
?>
<tr>
<td colspan="4" align="center"><input type="submit" name="Submit" value="Submit new Details"></td>
</tr>
</table>
</td>
</tr>
</form>
</table>


<?php

if($Submit){

for($i=0;$i<$count;$i++){

$query = "UPDATE table3 SET client='$client[$i]', project='$project[$i]', module='$module[$i]' WHERE id='$id[$i]'";

$result2 = mysql_query($query);

}
}

if ($result2) {
header("location:mytable3preview.php");
}

mysql_close();

?>



As presently it retrieve data from DB & show their values

in related text boxes of the table perfactly.

But on Updating new values in the textboxes & submitting

them it only Refresh the page,

and does not send new data & does not Update rows in the DBase.

Please suggest me the possible corrections that can be

applied to work it correctly .


Thanks & Regards.

Fumigator
05-13-2009, 05:35 PM
Your variable $count isn't defined anywhere but it's used as the condition for your update loop.

MSK7
05-14-2009, 10:55 AM
Hello Fumigator ,

Thanks for reminding me the missed variable defination row in my code.

after defining the variable($count) also one problem arises that the query

started to delete the whole data in the table rather than updating.

I don't know, but i think that it was

due to the statement " <? $id[]=$rows['id']; ?> "

because after removal of which & modifying id row like same as other

below rows it worked ahead .

I expanded some new more field rows in the table according to my requirement .

& also defined the variables $id, $client,... etc. clearly that was not

defined before & also used " if(isset($_POST['Submit']))" in the place of

" if($Submit) " .



Any way finally it worked Successfully .

& Here is my code which finally worked .

Hope this code will also help a little bit to other users who required the

similar multiple row updates results following the same tutorial .




<?php

$host="localhost"; // Host name
$username="myusername"; // Mysql username
$password="mypassword"; // Mysql password


// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("test3")or die("cannot select DB");



// Retrieve data from database
$sql="SELECT * FROM table3 ";

$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">&nbsp;</td>
<td align="center">&nbsp;</td>
<td align="center">&nbsp;</td>
<td align="center">&nbsp;</td>
</tr>
<tr>
<td align="center"><strong>Id</strong></td>
<td align="center"><strong>Client</strong></td>
<td align="center"><strong>Project</strong></td>
<td align="center"><strong>Module</strong></td>
<td align="center"><strong>Activity</strong></td>
<td align="center"><strong>Hours</strong></td>
<td align="center"><strong>Billable</strong></td>
<td align="center"><strong>Description</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td align="center"><input name="id[]" type="text" id="id" size="3" value="<? echo $rows['id']; ?>" > </td>
<td align="center"><input name="client[]" type="text" id="client" value="<? echo $rows['client']; ?>"> </td>
<td align="center"><input name="project[]" type="text" id="project" value="<? echo $rows['project']; ?>"></td>
<td align="center"><input name="module[]" type="text" id="module" value="<? echo $rows['module']; ?>"></td>
<td align="center"><input name="activity[]" type="text" id="activity" value="<? echo $rows['activity']; ?>"></td>
<td align="center"><input name="hours[]" type="text" id="hours" value="<? echo $rows['hours']; ?>"></td>
<td align="center"><input name="billable[]" type="text" id="billable" value="<? echo $rows['billable']; ?>"></td>
<td align="center"><input name="description[]" type="text" id="description" value="<? echo $rows['description']; ?>"></td>
</tr>
<?php
}
?>
<tr>
<td colspan="4" align="center"><input type="submit" name="Submit" value="Submit new Details"></td>
</tr>
</table>
</td>
</tr>
</form>
</table>


<?php

// Variables defined

$id = $_POST['id'] ;
$client = $_POST['client'] ;
$project = $_POST['project'];
$module = $_POST['module'] ;
$activity = $_POST['activity'] ;
$hours = $_POST['hours'];
$billable = $_POST['billable'] ;
$description = $_POST['description'];


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

for($i=0;$i<$count;$i++){

$sql1 = "UPDATE `test3`.`table3` SET `client`='$client[$i]', `project`='$project[$i]', `module`='$module[$i]', `activity`='$activity[$i]', `hours`='$hours[$i]', `billable`='$billable[$i]',
`description`='$description[$i]' WHERE `table3`.`id`='$id[$i]'";

$result1 = mysql_query($sql1);

}
}

if($result1){
echo "<meta http-equiv=\"refresh\" content=\"0;URL=mytable3preview.php\">";
}


mysql_close();

?>




Thanks & Regards to all .



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum