954,585 Members — Technology Publication meets Social Media
Username:
Password:
Lost login information?
Have something to say? Contribute New Article Reply to this Article
2 Years Ago
0

Need Help: Multiple Updates on base of ID

This code is not getting values when I press update.
Its showing the values from Db in fields.
But at the time of update whrere I SET Values it gets null values.
I am using name of input as acmake[]
and then using for loop but its not working.

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

<?php
$host="localhost"; // Host name 
$username="root"; // Mysql username 
$password=""; // Mysql password 
$db_name="onm"; // Database name 
$tbl_name="sims_ac"; // Table name 

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");
$a='S-KTR-0139';

$sql="SELECT * FROM $tbl_name WHERE SiteID ='$a'";
$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>SiteId</strong></td>
<td align="center"><strong>AcMake</strong></td>
<td align="center"><strong>AcCapacity </strong></td>
<td align="center"><strong>AMP</strong></td>
<td align="center"><strong>qty</strong></td>


</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td align="center"><? $SiteId[]=$a; ?><? echo $a; ?></td>
<td align="center"><input name="AcMake[]" type="text" id="AcMake" value="<? echo $rows['AcMake']; ?>"></td>
<td align="center"><input name="AcCapacity[]" type="text" id="AcCapacity" value="<? echo $rows['AcCapacity']; ?>"></td>
<td align="center"><input name="AMP[]" type="text" id="AMP" value="<? echo $rows['AMP']; ?>"></td>
<td align="center"><input name="qty[]" type="text" id="qty" value="<? echo $rows['qty']; ?>"></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"]=="Submit")
{
for($i=0;$i<$count;$i++){
echo "hi";
$sql1="UPDATE $tbl_name SET AcMake='$AcMake[$i]', AcCapacity='$AcCapacity[$i]', AMP='$AMP[$i]', qty='$qty[$i]' WHERE SiteId='$a'";
$result1=mysql_query($sql1);
}
}

if($result1){
header("location:um.php");
}
mysql_close();
?>
ayesha789
Posting Pro in Training
496 posts since Jun 2009
Reputation Points: 17
Solved Threads: 7
 
2 Years Ago
0

That code in it's design is flawed. The following line will cause a lot of trouble:

$sql1="UPDATE $tbl_name SET AcMake='$AcMake[$i]', AcCapacity='$AcCapacity[$i]', AMP='$AMP[$i]', qty='$qty[$i]' WHERE SiteId='$a'";

The arrays placed into this query do not exist. From my guess you are trying to use $_POST values in some strange way. So I would suggest the following code or please explain that line.

$sql1='UPDATE `'.mysql_real_escape_string($tbl_name).'` SET AcMake="'.mysql_real_escape_string($_POST['AcMake'][$i]).'", AcCapacity="'.mysql_real_escape_string($_POST['AcCapacity'][$i]).'", AMP="'.mysql_real_escape_string($_POST['AMP'][$i]).'", qty="'.mysql_real_escape_string($_POST['qty'][$i]).'" WHERE SiteId="'.mysql_real_escape_string($a).'"';
cwarn23
Occupation: Genius
Team Colleague
3,033 posts since Sep 2007
Reputation Points: 413
Solved Threads: 259
 
2 Years Ago
0

nope. its not getting value from the form.
How i can get value from the form , because I want that value for updation.

ayesha789
Posting Pro in Training
496 posts since Jun 2009
Reputation Points: 17
Solved Threads: 7
 
2 Years Ago
0

I cleaned up the code a bit and although I don't know much about setting up the arrays in a html form I believe the complete code should look more like the following:

<strong>Update multiple rows in mysql</strong> 
<?php
$host="localhost"; // Host name 
$username="root"; // Mysql username 
$password=""; // Mysql password 
$db_name="onm"; // Database name 
$tbl_name="sims_ac"; // Table name 

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");
$a='S-KTR-0139';

$sql="SELECT * FROM $tbl_name WHERE SiteID ='$a'";
$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>SiteId</strong></td>
<td align="center"><strong>AcMake</strong></td>
<td align="center"><strong>AcCapacity </strong></td>
<td align="center"><strong>AMP</strong></td>
<td align="center"><strong>qty</strong></td>


</tr>
<?php
for ($i=0;$rows=mysql_fetch_array($result);$i++){
echo "<tr>\n";
echo '<td align="center">'.$a.'</td>'."\n";
echo '<td align="center"><input name="AcMake['.$i.']" type="text" id="AcMake" value="'.$rows['AcMake'].'"></td>'."\n";
echo '<td align="center"><input name="AcCapacity['.$i.']" type="text" id="AcCapacity" value="'.$rows['AcCapacity'].'"></td>'."\n";
echo '<td align="center"><input name="AMP['.$i.']" type="text" id="AMP" value="'.$rows['AMP'].'"></td>'."\n";
echo '<td align="center"><input name="qty['.$i.']" type="text" id="qty" value="'.$rows['qty'].'"></td>'."\n";
echo "</tr>\n";
}
?><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"]=="Submit")
{
for($i=0;$i<$count;$i++){
echo "hi";
$sql1='UPDATE `'.mysql_real_escape_string($tbl_name).'` SET AcMake="'.mysql_real_escape_string($_POST['AcMake'][$i]).'", AcCapacity="'.mysql_real_escape_string($_POST['AcCapacity'][$i]).'", AMP="'.mysql_real_escape_string($_POST['AMP'][$i]).'", qty="'.mysql_real_escape_string($_POST['qty'][$i]).'" WHERE SiteId="'.mysql_real_escape_string($a).'"';
$result1=mysql_query($sql1);
}
}
mysql_close();
?>
cwarn23
Occupation: Genius
Team Colleague
3,033 posts since Sep 2007
Reputation Points: 413
Solved Threads: 259
 
2 Years Ago
0

Now This code is geting values from the form as I change the action and use your(crawn) method.
But WHERE $a , its updating all db where id is $a with same record of last input, how I can index this $a?.

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

<?php
$host="localhost"; // Host name 
$username="root"; // Mysql username 
$password="zong1234"; // Mysql password 
$db_name="onm"; // Database name 
$tbl_name="sims_ac"; // Table name 

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");
$a='S-KTR-0139';

$sql="SELECT * FROM $tbl_name WHERE SiteID ='$a'";
$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="um.php">
<tr> 
<td>
<table width="500" border="0" cellspacing="1" cellpadding="0">


<tr>
<td align="center"><strong>SiteId</strong></td>
<td align="center"><strong>AcMake</strong></td>
<td align="center"><strong>AcCapacity </strong></td>
<td align="center"><strong>AMP</strong></td>
<td align="center"><strong>qty</strong></td>


</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td align="center"><? //$SiteId[]=$a; ?><? echo $a; ?></td>
<td align="center"><input name="AcMake[]" type="text" id="AcMake" value="<? echo $rows['AcMake']; ?>"></td>
<td align="center"><input name="AcCapacity[]" type="text" id="AcCapacity" value="<? echo $rows['AcCapacity']; ?>"></td>
<td align="center"><input name="AMP[]" type="text" id="AMP" value="<? echo $rows['AMP']; ?>"></td>
<td align="center"><input name="qty[]" type="text" id="qty" value="<? echo $rows['qty']; ?>"></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"]=="Submit")
{
for($i=0;$i<$count;$i++){
echo $a;
$b= $_POST['AcMake'][$i];
echo $b;
$c= $_POST['AcCapacity'][$i];
echo $c;
$d= $_POST['AMP'][$i];
echo $d;
$e= $_POST['qty'][$i];
echo $e;
$query=mysql_query("UPDATE $tbl_name SET AcMake='$b', AcCapacity='$c', AMP='$d', qty='$e' WHERE SiteId='$a'");


}
}

if($result1){
header("location:um.php");
}
mysql_close();
?>
ayesha789
Posting Pro in Training
496 posts since Jun 2009
Reputation Points: 17
Solved Threads: 7
 
2 Years Ago
0

Problem occurs because my ID is same and for same id I have 2 records.
In my DB there is no unique or primary key. How I can Update this record. because against id acmake is LG and Haier so I have to store records 2 times against same id.

ayesha789
Posting Pro in Training
496 posts since Jun 2009
Reputation Points: 17
Solved Threads: 7
 
2 Years Ago
0

Thanks for your help.
I inserted 1 another field in dB as Serial No and use that for updating
Now its working perfectly.

ayesha789
Posting Pro in Training
496 posts since Jun 2009
Reputation Points: 17
Solved Threads: 7
 
2 Years Ago
0

Its showing all the form exactly an, but when I press Submit is not updating Vaules in Db.
If I store Value of $a by assigning its working.
But when I request value from previoes page then its behavour is change.

<strong><p align="center">Air Conditioner Data Update</p></strong> 

<?php
$host="localhost"; // Host name 
$username="root"; // Mysql username 
$password=""; // Mysql password 
$db_name="onm"; // Database name 
$tbl_name="sims_ac"; // Table name 

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");
 $a=$_REQUEST['a']; //When I request value from prevuies page its not working.
 echo $a; //its echoing value.
//$a='1008';

$sql="SELECT * FROM $tbl_name WHERE mid(SiteID,7)='$a'";
$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="sims_ac_update.php">
<tr> 
<td>
<table class="mytable" width="500" border="0" cellspacing="1" cellpadding="0">


<tr>
<td align="center"><strong>SiteId</strong></td>
<td align="center"><strong></strong></td>
<td align="center"><strong>AcMake</strong></td>
<td align="center"><strong>AcCapacity </strong></td>
<td align="center"><strong>AMP</strong></td>
<td align="center"><strong>qty</strong></td>


</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>

<td align="center"><? //$SiteId[]=$a; ?><? echo $a; ?></td>
<td align="center"><input name="SrNo[]" type="hidden" id="SrNo" value="<? echo $rows['SrNo']; ?>"></td>
<td align="center"><input name="AcMake[]" type="text" id="AcMake" value="<? echo $rows['AcMake']; ?>"></td>
<td align="center"><input name="AcCapacity[]" type="text" id="AcCapacity" value="<? echo $rows['AcCapacity']; ?>"></td>
<td align="center"><input name="AMP[]" type="text" id="AMP" value="<? echo $rows['AMP']; ?>"></td>
<td align="center"><input name="qty[]" type="text" id="qty" value="<? echo $rows['qty']; ?>"></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($_REQUEST["Submit"]=="Submit")
{
for($i=0;$i<$count;$i++){

echo $a;
$b= $_POST['AcMake'][$i];
//echo $b;
$c= $_POST['AcCapacity'][$i];
//echo $c;
$d= $_POST['AMP'][$i];
//echo $d;
$e= $_POST['qty'][$i];
//echo $e;
$f= $_POST['SrNo'][$i];
//echo $f;
$query=mysql_query("UPDATE $tbl_name SET AcMake='$b', AcCapacity='$c', AMP='$d', qty='$e' WHERE mid(SiteId,7)='$a' AND SrNo='$f'");


}
}

if($result1){
header("location:sims_ac_update.php");
}
mysql_close();
?>
ayesha789
Posting Pro in Training
496 posts since Jun 2009
Reputation Points: 17
Solved Threads: 7
 
2 Years Ago
0

When you use the $_REQUEST variable you need to either place the data in the url or post to the page the data which will allow the page to receive the requested data.

cwarn23
Occupation: Genius
Team Colleague
3,033 posts since Sep 2007
Reputation Points: 413
Solved Threads: 259
 
2 Years Ago
0
When you use the $_REQUEST variable you need to either place the data in the url or post to the page the data which will allow the page to receive the requested data.
I am confused... Please tell me how to send data using page. Actually I am using same page. when i assign value to $a, its work perfectly like $a=1308; but when I post value from the previoes page then its not updating and behavour not understanable. Please check my code... its very difficult and I stopeed my struggle on this code. ok


.

.
.

ayesha789
Posting Pro in Training
496 posts since Jun 2009
Reputation Points: 17
Solved Threads: 7
 
2 Years Ago
0

Perhaps try accessing your page with a url variable like the following:

index.php?Submit=Submit&a=this+is+a+test+variable

That should input those variables into the $_REQUEST although posting with a form will do the same. But at the moment it seems that your page isn't receiving those variables...

cwarn23
Occupation: Genius
Team Colleague
3,033 posts since Sep 2007
Reputation Points: 413
Solved Threads: 259
 

This question has already been solved

Post: Markdown Syntax: Formatting Help
You
View similar articles that have also been tagged:
 
© 2012 DaniWeb® LLC
Home | About Us | Contact Us | Terms of Service | Advertising Opportunities
RSS Feed RSS Feed