Solved

updating multiple rows with one form

Posted on 2011-09-21
4
261 Views
Last Modified: 2012-06-27
Hello all,

I am trying to show multiple items from a database, with the option to change the prices.  However, I'm unsure how to update.  Can you please help?  Here is a snippet of my code:

<?php
include ("databaseinfo.php")
//count number of records
$result = mysql_query("SELECT * FROM tableitems WHERE Item='$item' AND Date >= '$from_date' AND Date <= '$to_date'") or die(mysql_error());
$num_rows = mysql_num_rows($result);

if(mysql_num_rows($result) == 0) {
  echo "<center>No results were found.</center>";
}

//number you want to display per page
$page_rows=50;

//This tells us the page number of last page
$last=ceil($num_rows/$page_rows);


//determine results
//This checks to see if there's a page number.  If not, it will set it to page 1.
if(isset($_GET['pagenum'])) {
  $pagenum=$_GET['pagenum'];
}
else{
  $pagenum=1;
}

$pagenum=(int)$pagenum;
if ($pagenum>$last)
{
$pagenum=$last;
}

if ($pagenum<1)
{
$pagenum=1;
}

$offset=($pagenum - 1) * $page_rows;


echo "<center>";
echo "<b>";
echo "Prices from: ";
echo $from_date;
echo " to ";
echo $to_date;
echo "</b>";
echo "</center>";
echo "<br>";
echo "<table align=center cellpadding=\"10\" width=\"400\" border=\"1\">";
echo "<tbody>";
echo "<form action=\"update.php\" method=\"post\" name=\"form\">";

$i=1;
$result = mysql_query("SELECT * FROM tableitems WHERE
Item='$item' AND Date >= '$from_date' AND Date <= '$to_date' ORDER BY
ItemNum LIMIT $offset, $page_rows") or die(mysql_error());
while($row = mysql_fetch_array($result))
  {
$date=$row['Date'];
$date=htmlspecialchars($date);
$date_sq=mysql_real_escape_string($date);
$date8_sq=substr($date_sq, 0, -9);
$date_sq=date('m-d-Y', strtotime($date8_sq));

$date9_sq=date('Y-m-d', strtotime($date8_sq));

$price=$row['Price'];
$price=htmlspecialchars($price);
$price_sq=mysql_real_escape_string($price);

$num=$row['ItemNum'];
$num=htmlspecialchars($num);
$num_sq=mysql_real_escape_string($num);

echo "<tr><td>";
echo "<center>";
echo $item;
echo "</center>";
echo "</td><td>";
echo "<center>";
echo $date_sq;
echo "</a>";
echo "</center>";
echo "</td><td>";
echo "<center>";
echo "<input type=\"text\" name=\"price[]\" value='$price_sq'>";
echo "</center>";
echo "</td></tr>";
$i++;
  }

echo "</tbody></table>";

if ($none!=1)
{
echo " Page ";
}

for ($i=1; $i<=$last; $i++)
{
if (($i=="1") && ($i==$pagenum))
{
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=1&amp;f=$from_date&amp;t=
$to_date&amp;i=$item'>";
echo $i;
echo "</a>";
}
elseif (($i=="1") && ($i!=$pagenum))
{
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=1&amp;f=$from_date&amp;t=
$to_date&amp;i=$item'>";
echo $i;
echo "</a>";
}
elseif (($i>1) && ($i!=$last) && ($i==$pagenum))
{
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$pagenum&amp;f=$from_date&amp;t=
$to_date&amp;i=$item'>";
echo $i;
echo "</a>";
}
elseif (($i>1) && ($i!=$last) && ($i!=$pagenum))
{
$next = $pagenum+1;
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$i&amp;f=$from_date&amp;t=
$to_date&amp;i=$item'>";
echo $i;
echo "</a>";
}
elseif (($i==$last) && ($i==$pagenum))
{
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$last&amp;f=$from_date&amp;t=
$to_date&amp;i=$item'>";
echo $i;
echo "</a>";
}
elseif (($i==$last) && ($i!=$pagenum))
{
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$last&amp;f=$from_date&amp;t=
$to_date&amp;i=$item'>";
echo $i;
echo "</a>";
}
echo "<table align=center>";
echo "<tbody>";
echo "<tr>";
echo "<td>";
echo "<center>";
echo "<input type=\"submit\" name=\"submit\" value=\"Submit\"></center>";
echo "</td>";
echo "</tr>";
echo "</form>";
echo "</tbody>";
echo "</table>";
echo "</center>";
?>

Open in new window


<?php
//update.php
include ("databaseinfo.php");

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

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

$result = mysql_query("UPDATE tableitems SET Price='$price[$i]' WHERE ItemNum='$num_sq'") or die(mysql_error());  
}
}
?>

Open in new window


Thank you in advance.
0
Comment
Question by:happysunny
  • 2
4 Comments
 
LVL 13

Expert Comment

by:Hugh McCurdy
ID: 36577545
For starters, I think you need to do some homework.  I suggest you look at  http://www.w3schools.com/php/php_forms.asp

The update.php code doesn't know about the settings of variables from the first section.  Pass the information using $_POST variables.

You need to pass the number of items and we will need to do something about the variable names.  But first, see if you can get it to pass the number of variables.

0
 
LVL 4

Author Comment

by:happysunny
ID: 36577574
Yes, thank you.  I forgot that.  When I add: $price=$_POST['price']; echo $price; on the next page, I get the correct amount.  But wouldn't I need that to be an array?  And how would I get this to work?
0
 
LVL 2

Accepted Solution

by:
maricksville earned 500 total points
ID: 36577842
Hey Happy,

Having a look at your code, I'm a little confused but this might help. When you submit your form you are sending the POST variable price which is an array.

The first thing I would change is line 87 to:

echo "<input type=\"text\" name=\"id[$num]\" value='$price_sq'>";

Open in new window


You want to specify the name as the ID number of the line in the MYSQL database that you want to update. So the line above is saying 'The value of ID number ... is ...

To get the values of the key-value pairs out of the POST array we can then iterate through the ID array and update the prices based on the ID submitted.

foreach($_POST['id'] as $id => $value) {
$result = mysql_query("UPDATE tableitems SET Price='$value' WHERE ItemNum='$id' limit 1") or die(mysql_error());  
}

Open in new window


Note the added 'limit 1' to the $result line. Without the limit defined as 1 you can inadvertently update every single line of your table.

Try this and see how you go.
0
 
LVL 4

Author Closing Comment

by:happysunny
ID: 36577877
A ha!  That makes sense :)  Thank you!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now