Update Multiple database

rdztech

Dear friends,

I have the code below, but when I run it the database is not updated.. anybody can suggest me what's went wrong... :o

<strong>Update multiple rows in mysql</strong><br>

<?php

mysql_connect("localhost", "warisanm_fleet", "warisan007") or die(mysql_error());
echo "Connected to MySQL<br />";
mysql_select_db("warisanm_fleet") or die(mysql_error());
echo "Connected to Database";

$sql="SELECT * FROM fleet";
$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>Lorry No</strong></td>
<td align="center"><strong>Trailer No</strong></td>
<td align="center"><strong>Logo No</strong></td>
<td align="center"><strong>Location</strong></td>
<td align="center"><strong>Type</strong></td>
<td align="center"><strong>Road Tax Expiry</strong></td>
<td align="center"><strong>Puspakom Expiry</strong></td>
<td align="center"><strong>Mileage</strong></td>
<td align="center"><strong>Shell Card Limit</strong></td>
<td align="center"><strong>Revenue</strong></td>
<td align="center"><strong>Tyre</strong></td>
<td align="center"><strong>Maintenance</strong></td>
<td align="center"><strong>Fuel Consumption</strong></td>
<td align="center"><strong>Driver Name</strong></td>
<td align="center"><strong>Remarks</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td align="center"><input name="Lorry_No[]" type="text" id="Lorry_No" value="<? echo $rows['Lorry_No']; ?>"></td>
<td align="center"><input name="Trailer_No[]" type="text" id="Trailer_No" value="<? echo $rows['Trailer_No']; ?>"></td>
<td align="center"><input name="Logo_No[]" type="text" id="Logo_No" value="<? echo $rows['Logo_No']; ?>"></td>
<td align="center"><input name="Location[]" type="text" id="Location" value="<? echo $rows['Location']; ?>"></td>
<td align="center"><input name="Type[]" type="text" id="Type" value="<? echo $rows['Type']; ?>"></td>
<td align="center"><input name="Road_Tax_Expiry[]" type="text" id="Road_tax_Expiry" value="<? echo $rows['Road_Tax_Expiry']; ?>"></td>
<td align="center"><input name="Puspakom_Expiry[]" type="text" id="Puspakom_Expiry" value="<? echo $rows['Puspakom_Expiry']; ?>"></td>
<td align="center"><input name="Mileage[]" type="text" id="Mileage" value="<? echo $rows['Mileage']; ?>"></td>
<td align="center"><input name="Shell_Card_Limit[]" type="text" id="Shell_Card_Limit" value="<? echo $rows['Shell_Card_Limit']; ?>"></td>
<td align="center"><input name="Revenue[]" type="text" id="Revenue" value="<? echo $rows['Revenue']; ?>"></td>
<td align="center"><input name="Tyre[]" type="text" id="Tyre" value="<? echo $rows['Tyre']; ?>"></td>
<td align="center"><input name="Maintenance[]" type="text" id="Maintenance" value="<? echo $rows['Maintenance']; ?>"></td>
<td align="center"><input name="Fuel_Consumption[]" type="text" id="Fuel_Consumption" value="<? echo $rows['Fuel_Consumption']; ?>"></td>
<td align="center"><input name="Driver_Name[]" type="text" id="Driver_Name" value="<? echo $rows['Driver_Name']; ?>"></td>
<td align="center"><input name="Remarks[]" type="text" id="Remarks" value="<? echo $rows['Remarks']; ?>"></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 fleet SET Trailer_No='QQQ', Logo_No='88', Location='Filipine', Road_Tax_Expiry='$Road_Tax_Expiry[$i]', Puspakom_Expiry='$Puspakom_Expiry[$i]', Mileage='8888', Shell_Card_Limit='$Shell_Card_Limit[$i]', Revenue='$Revenue[$i]' WHERE id='$i'";
$result1=mysql_query($sql1);

}
}

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

djjjozsi

$Submit is an undefined variable in your code.

you mean $_POST["Submit"] ?

djjjozsi

to tell you the truth, the method you're doing this multiple update is not a good way!

first, you should save the record's primary key instead into each table row.

remember the foreach structure instead of that for cycle. Just think of a case, if you deleted a couple of records from the table, you will have holes in your id list.

how about doing this:

<input type="hidden" name="ids[]" value="$id">
<input type="text" name="fieldname1[$id]" value="$row['fieldname1']">
<input type="text" name="fieldname2[$id]" value="$row['fieldname2']">

and now, try to apply an update string.

foreach($_POST["ids"] AS $id)
{
$fieldname1=$_POST["fieldname1"][$id];
$fieldname2=$_POST["fieldname2"][$id];
$sql="update tablename set fieldname1='$fieldname1', fieldname2='$fieldname2' WHERE id=$id";
//run your sql here
}

And its gone!
Never trust full codes, i have always add mistakes in them.

rdztech

Hmm, still struggling....

<strong>Update multiple rows in mysql</strong><br>

<?php

mysql_connect("localhost", "warisanm_fleet", "warisan007") or die(mysql_error());
echo "Connected to MySQL<br />";
mysql_select_db("warisanm_fleet") or die(mysql_error());
echo "Connected to Database";

$sql="SELECT * FROM fleet";
$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"><strong>id</strong></td>
<td align="center"><strong>Lorry No</strong></td>
<td align="center"><strong>Location</strong></td>

</tr>

<?php

while($rows=mysql_fetch_array($result)){
?>
<tr>
<input type="hidden" name="ids[]" value="$id">
<input type="text" name="Lorry_No[$id]" value="$row['Lorry_No']">
<input type="text" name="Location[$id]" value="$row['Location']">
</tr>

<?php
}
?>
<tr>
<td colspan="4" align="center"><input type="submit" name="Submit" value="Submit"></td>
</tr>
</table>
</td>
</tr>
</form>
</table>

<?php
foreach($POST["id"] AS $id)
{
$Lorry_No=$POST["Lorry_No"][$id];
$Location=$_POST["Location"][$id];
$sql="update fleet set Lorry_No='$Lorry_No', Location='$Location' WHERE id=$id";
//run your sql here
$result1=mysql_query($sql);
}

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

rdztech

My latest trial as below:

http://www.warisanmovers.com/operations/fms/updatetry.php
username : webmaster
password : webmaster11

problem : table is not updated 😕

<strong>Update multiple rows in mysql</strong><br> 

<?php

mysql_connect("localhost", "warisanm_fleet", "warisan007") or die(mysql_error());
echo "Connected to MySQL<br />";
mysql_select_db("warisanm_fleet") or die(mysql_error());
echo "Connected to Database";

$sql="SELECT * FROM fleet";
$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>Lorry No</strong></td>
<td align="center"><strong>Trailer No</strong></td>
<td align="center"><strong>Logo No</strong></td>
<td align="center"><strong>Location</strong></td>
<td align="center"><strong>Type</strong></td>
<td align="center"><strong>Road Tax Expiry</strong></td>
<td align="center"><strong>Puspakom Expiry</strong></td>
<td align="center"><strong>Mileage</strong></td>
<td align="center"><strong>Shell Card Limit</strong></td>
<td align="center"><strong>Revenue</strong></td>
<td align="center"><strong>Tyre</strong></td>
<td align="center"><strong>Maintenance</strong></td>
<td align="center"><strong>Fuel Consumption</strong></td>
<td align="center"><strong>Driver Name</strong></td>
<td align="center"><strong>Remarks</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<input type="hidden" name="ids[]" value="$id">

<td align="center"><input name="Lorry_No[id]" type="text" value="<? echo $rows['Lorry_No']; ?>"></td>

<td align="center"><input name="Trailer_No[]" type="text" id="Trailer_No" value="<? echo $rows['Trailer_No']; ?>"></td>

<td align="center"><input name="Logo_No[]" type="text" id="Logo_No" value="<? echo $rows['Logo_No']; ?>"></td>

<td align="center"><input name="Location[id]" type="text" value="<? echo $rows['Location']; ?>"></td>

<td align="center"><input name="Type[]" type="text" value="<? echo $rows['Type']; ?>"></td>
<td align="center"><input name="Road_Tax_Expiry[]" type="text"  value="<? echo $rows['Road_Tax_Expiry']; ?>"></td>
<td align="center"><input name="Puspakom_Expiry[]" type="text"  value="<? echo $rows['Puspakom_Expiry']; ?>"></td>
<td align="center"><input name="Mileage[]" type="text" value="<? echo $rows['Mileage']; ?>"></td>
<td align="center"><input name="Shell_Card_Limit[]" type="text"  value="<? echo $rows['Shell_Card_Limit']; ?>"></td>
<td align="center"><input name="Revenue[]" type="text" value="<? echo $rows['Revenue']; ?>"></td>
<td align="center"><input name="Tyre[]" type="text" value="<? echo $rows['Tyre']; ?>"></td>
<td align="center"><input name="Maintenance[]" type="text" value="<? echo $rows['Maintenance']; ?>"></td>
<td align="center"><input name="Fuel_Consumption[]" type="text"  value="<? echo $rows['Fuel_Consumption']; ?>"></td>
<td align="center"><input name="Driver_Name[]" type="text" value="<? echo $rows['Driver_Name']; ?>"></td>
<td align="center"><input name="Remarks[]" type="text" value="<? echo $rows['Remarks']; ?>"></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($_POST["Submit"]){
foreach($_POST["ids"] AS $id) 
{ 
$Lorry_No=$_POST["Lorry_No"][$id]; 
$Location=$_POST["Location"][$id]; 
$sql="update fleet set Lorry_No='$Lorry_No', Location='$Location' WHERE id=$id"; 
//run your sql here 
$result1=mysql_query($sql);

} 
}

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

djjjozsi

in your code, Lorry_No[id]

id is just a word id, not your current record's primary key number. try to echo its value instead.

And here:

value="$id">

it is not a PHP value here. where did you get $id anyway?
You should get used the difference between PHP block and HTML content.

rdztech

http://www.warisanmovers.com/operations/fms/updatemultipletry.php
username: webmaster
pw: webmaster11

problem: table not updated :rolleyes:

<strong>Update multiple rows in mysql</strong><br> 

<?php 

mysql_connect("localhost", "warisanm_fleet", "warisan007") or die(mysql_error()); 
echo "Connected to MySQL<br />"; 
mysql_select_db("warisanm_fleet") or die(mysql_error()); 
echo "Connected to Database"; 

$sql="SELECT * FROM fleet"; 
$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>Lorry No</strong></td>  

<td align="center"><strong>Location</strong></td> 
<td align="center"><strong>Type</strong></td> 
<td align="center"><strong>Mileage</strong></td> 
</tr> 

<?php 
while($rows=mysql_fetch_array($result)){ 
?> 
<tr> 
<td align="center"><input name="Lorry_No[id]" type="text" value="<? echo $rows['Lorry_No']; ?>"></td> 

<td align="center"><input name="Location[id]" type="text" value="<? echo $rows['Location']; ?>"></td> 

<td align="center"><input name="Type[id]" type="text" value="<? echo $rows['Type']; ?>"></td> 

<td align="center"><input name="Mileage[id]" type="text" value="<? echo $rows['Mileage']; ?>"></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($_POST["Submit"]){ 
foreach($_POST["ids"] AS $id) 
$Lorry_No=$_POST["Lorry_No"][$id]; 
$Location=$_POST["Location"][$id]; 
$Type=$_POST["Type"][$id];
$Mileage=$_POST["Mileage"][$id];
$sql="update fleet set Lorry_No='$Lorry_No', Location='$Location', Type='$Type' Mileage=$Mileage WHERE id=$id"; 

//run your sql here 
$result1=mysql_query($sql); 
} 

if($result1){ 
header("location:updatemultipletry.php"); 
} 

mysql_close(); 
?>

bradgrafelman

You still have the same problem djjjozsi pointed out above.

The point of using the [] bracket notation in the field names of your HTML form elements is so that you can assign the same array key/index to all of the elements related to the same entry.

Using the word "id" for every form element on the entire page obviously doesn't help at all (not to mention the fact that it doesn't make any logical sense - how can multiple items be referenced by a single key, which is supposed to be unique?). Instead, you should be using some actual id value (such as an AUTO_INCREMENT value from the D😎 that uniquely identifies the given row in the DB.

rdztech

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

any error in this example script?

bradgrafelman

rdztech;10991092 wrote:
any error in this example script?

Yes. Several.

Jacobdixon

You can find many useful tips regarding database in php manuel as well as sql manuel in .chm format
PHP Application Development

djjjozsi

In this tutorial you can see an explanation:
Update multiple records and delete

rdztech

djjjozsi;10991446 wrote:
In this tutorial you can see an explanation:
Update multiple records and delete

TQVM djjjozsi, I'll study the usefull tutorial...