PHP - Updating Multiple Rows In One Page.

Hi All,

Can't get this code working. the statement works fine directly on the table, just not when executed from the PHP file:

Code: [Select]
<strong>Multi Update</strong><br>

<?php
$host="localhost"; // Host name
$username="user_name"; // Mysql username
$password="Password"; // Mysql password
$db_name="dbname"; // Database name
$tbl_name="tablename"; // 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");

$sql="SELECT 
  mik1vm_product.product_id,
  mik1vm_product.product_sku,
  mik1vm_product.product_name,
  mik1vm_product.product_in_stock,
  mik1vm_product.product_in_stock_cardiff
FROM $tbl_name";
$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>Product Id</strong></td>
<td align="center"><strong>SKU</strong></td>
<td align="center"><strong>Name</strong></td>
<td align="center"><strong>In Stock</strong></td>
<td align="center"><strong>In Stock Cardiff</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td align="center"><? $product_id[]=$rows['product_id']; ?><? echo $rows['product_id']; ?></td>
<td align="center"><input name="product_sku[]" type="text" id="product_sku" value="<? echo $rows['product_sku']; ?>"></td>
<td align="center"><input name="product_name[]" type="text" id="product_name" value="<? echo $rows['product_name']; ?>"></td>
<td align="center"><input name="product_in_stock[]" type="text" id="product_in_stock" value="<? echo $rows['product_in_stock']; ?>"></td>
<td align="center"><input name="product_in_stock_cardiff[]" type="text" id="product_in_stock_cardiff" value="<? echo $rows['product_in_stock_cardiff']; ?>"></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 $tbl_name SET product_sku='$product_sku[$i]', product_name='$product_name[$i]', product_in_stock='$product_in_stock[$i]' , product_in_stock_cardiff='$product_in_stock_cardiff[$i]' WHERE product_id='$product_id[$i]'";
$result1=mysql_query($sql1);
}
}

if($result1){
header("location:test.php");
}
mysql_close();
?>
The page loads fine, but just doesn't update the DB.

Thanks in advance for any help.

Hi,

I've created an admin page that lists all piano sheet music that has been uploaded to my server. It displays the ID (autoincrement), Userid (user that uploaded sheet), Artist (name of the band) and Title (name of the song).
On this page, I can either delete the row completely, or click on "Uploaded", which just sets it's uploaded column to 'yes'.
Now I'm working on a button called "Uploaded All". This will set the uploaded column on all of the rows on the page to 'yes'. This way I don't have to click Uploaded for every single row. (view attached image if you are unsure what I am talking about)

I'm not getting any errors when clicking "Uploaded All". When clicking it, it doesn't update the table rows in the database as it should. Take a look at my code:

Tip: The area of the code that I'm currently having troubles with is here

  $usersids[$i] = $row['id'];
  $i++;
  }

  echo "
  <tr>
    <td align='center' width='10' colspan='6'><a href='uploadedsheets.php?confirm=all'>Uploaded All</a></td>
  </tr>";
 
  if ($confirm=="all")
{
    $i = 0;
    while($row = mysql_fetch_array($result))
  {
    mysql_query('UPDATE `upload` SET `uploaded`="yes" WHERE id = ' . $usersids[$i]);
    $i++;
  }
    echo "<SCRIPT language='JavaScript'><!--
  window.location='uploadedsheets.php';//-->
</SCRIPT>";
}

Inside the above code after the if statement, I tried echoing out $usersids, and it showed correctly. So the problem must be in the update query or the array.


Full Code:

<?php
session_start();

include_once('../inc/connect.php');
include_once('../inc/admin.php');

if(!isset($_SESSION['sort_counter']))
{$_SESSION['sort_counter'] = 1;}

if(($_SESSION['sort_counter']%2) == 0){ //test even value
  $sortcount = "DESC";
}else{ //odd value
  $sortcount = "";
}

$result = mysql_query("SELECT * FROM upload WHERE uploaded='no' ORDER BY id");

$unconfirmedquery = mysql_query("SELECT uploaded FROM upload WHERE uploaded='no'");
$unconfirmedcount = mysql_num_rows($unconfirmedquery);

$confirmedquery = mysql_query("SELECT uploaded FROM upload WHERE uploaded='yes'");
$confirmedcount = mysql_num_rows($confirmedquery);

$sort = $_GET['sort'];
$delete = $_GET['delete'];
$confirm = $_GET['confirm'];

/////////////////////////////////

if ($sort=='id'){

    // $result = mysql_query("SELECT * FROM users ORDER BY id"); 
$result = mysql_query("SELECT * FROM upload WHERE uploaded='no' ORDER BY id $sortcount");
$_SESSION['sort_counter'] = $_SESSION['sort_counter'] + 1; //increment after every run
 
}
if ($sort=='userid'){

    // $result = mysql_query("SELECT * FROM users ORDER BY username");
$result = mysql_query("SELECT * FROM upload WHERE uploaded='no' ORDER BY userid $sortcount");
$_SESSION['sort_counter'] = $_SESSION['sort_counter'] + 1; //increment after every run
}
if ($sort=='artist'){

    // $result = mysql_query("SELECT * FROM users ORDER BY email");
$result = mysql_query("SELECT * FROM upload WHERE uploaded='no' ORDER BY artist $sortcount");
$_SESSION['sort_counter'] = $_SESSION['sort_counter'] + 1; //increment after every run
}
if ($sort=='title'){

    // $result = mysql_query("SELECT * FROM users ORDER BY email");
$result = mysql_query("SELECT * FROM upload WHERE uploaded='no' ORDER BY title $sortcount");
$_SESSION['sort_counter'] = $_SESSION['sort_counter'] + 1; //increment after every run
}
if ($sort=='file'){

    // $result = mysql_query("SELECT * FROM users ORDER BY email");
$result = mysql_query("SELECT * FROM upload WHERE uploaded='no' ORDER BY file $sortcount");
$_SESSION['sort_counter'] = $_SESSION['sort_counter'] + 1; //increment after every run
}

/// FIX THIS AREA
if ($confirm=="true" && isset($_GET['id']))
{
    mysql_query('UPDATE `upload` SET `uploaded`="yes" WHERE id = ' . (int)$_GET['id']);
    echo "<SCRIPT language='JavaScript'><!--
  window.location='uploadedsheets.php';//-->
</SCRIPT>";
} 
if ($delete=="true" && isset($_GET['id']))
{
    mysql_query('DELETE FROM `upload` WHERE id = ' . (int)$_GET['id']);   
    echo "<SCRIPT language='JavaScript'><!--
  window.location='uploadedsheets.php';//-->
</SCRIPT>";
} 
if ($delete=="false" && isset($_GET['id']))
{
    echo "<SCRIPT language='JavaScript'><!--
  window.location='uploadedsheets.php';//-->
</SCRIPT>";
} 

/////////////////////////////////

echo "
<html>
<head>
<title>Uploaded Sheets</title>
<style>
a:link{
text-decoration: none;
color: #519904;
}
a:visited{
text-decoration: none;
color: #519904;
}
a:hover{
text-decoration: none;
color: #4296ce;
}
#headcont{
width: 900px;
height: 20px;
margin-left: auto;
margin-right: auto;
}
#unconfirmed{
    width: 450px;
    text-align: center;
    float: left;
    background-color: #cccccc;
}
#confirmed{
    width: 450px;
    text-align: center;
    float: left;
    background-color: #72A4D2;
}
</style>
</head>
<body>
";

include_once('../inc/navadmin.php');

echo "<div style='font-size: 28px; text-align: center;'>Uploaded Sheets</div>
<div id='headcont'>
<div id='unconfirmed'>Not Uploaded Sheets: ".$unconfirmedcount."</div>
<div id='confirmed'>Uploaded Sheets: ".$confirmedcount."</div>
</div><br />
<table border='1' align='center'>
<tr>
<th bgcolor='#cccccc'><a href='uploadedsheets.php?sort=id'>ID</a></th>
<th bgcolor='#cccccc'><a href='uploadedsheets.php?sort=userid'>UserID</a></th>
<th bgcolor='#cccccc'><a href='uploadedsheets.php?sort=artist'>Artist</a></th>
<th bgcolor='#cccccc'><a href='uploadedsheets.php?sort=title'>Title</a></th>
<th bgcolor='#cccccc'><a href='uploadedsheets.php'>Uploaded</a></th>
<th bgcolor='#cccccc'><a href='uploadedsheets.php'>Delete</a></th>
</tr>";
echo "<script type='text/javascript'>
function show_delete()
{
var r=confirm('Delete?');
if (r==true)
{
// Delete
return true;
}
else
{
// Don't Delete
return false;
}
}
";

echo "
function show_undelete()
{
var r=confirm('Undelete?');
if (r==true)
{
// Undelete
return true;
}
else
{
// Don't Undelete
return false;
}
}
</script>";

$usersids = "";
$i = 0;

while($row = mysql_fetch_array($result))
  {
 
  //
  $active = $row['active'];
  $color = "#ffffff";
  $deleted = "Delete";
  if ($active=='no'){
  $color = "#f43636";
  $deleted = "Undelete";
  $active = "false";
  $alert = "show_undelete";
  }
  else{
  $active = "true";
  $alert = "show_delete";
  }
  //
 
  echo "<tr>";
  echo "<td align='center' width='40' bgcolor='$color'>" .$row['id']. "</td>";
  echo "<td align='center' width='40'>" .$row['userid']. "</td>";
  echo "<td align='center' width='230'>".ucwords($row['artist'])."</td>";
  echo "<td align='center' width='230'>".ucwords($row['title'])."</td>";
  echo "<td align='center' width='10'><a href='uploadedsheets.php?confirm=true&id=" .$row['id'] . "'>Uploaded</a></td>";
  echo "<td align='center' width='10'><a href='uploadedsheets.php?delete=$active&id=" .$row['id']. "' onclick='return $alert()'>$deleted</a></td>";
  echo "</tr>";
  $usersids[$i] = $row['id'];
  $i++;
  }

  echo "
    $usersids[0]
  <tr>
    <td align='center' width='10' colspan='6'><a href='uploadedsheets.php?confirm=all'>Uploaded All</a></td>
  </tr>";
 
  if ($confirm=="all")
{
    $i = 0;
    while($row = mysql_fetch_array($result))
  {
    mysql_query('UPDATE `upload` SET `uploaded`="yes" WHERE id = ' . $usersids[$i]);
    $i++;
  }
    echo "<SCRIPT language='JavaScript'><!--
  window.location='uploadedsheets.php';//-->
</SCRIPT>";
} 
 
echo "</table>";



// Footer
echo "
</body>
</html>
";

?>

I am trying to update multiple rows in a mysql db through php but I am getting an error message in this line:

$size = count($_POST['uid']);

My db field names are uid (which autoincrements and does not need to be updated), fname, lname and email fields. Here is a link for the project I need to develophttp://users.cis.fiu.edu/~vagelis/classes/COP5725/project.htm

Here is the content for the first page:

Code: [Select]
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head>
<style type="text/css">

body {
margin:50px 0px; padding:0px;
text-align:center;
font:13px Tahoma,Geneva,sans-serif
}

#content {
width:1000px;
margin:0px auto;
text-align:left;
padding:15px;
border:1px dashed #333;
background-color:#eee;
}
</style>
<link rel="icon" href="http://www.mysite.com/favicon.ico" type="image/vnd.microsoft.icon" />
</head>
<body>
<div id='content'><h3><center>Update Information</center></h3>
<?php

//create a connection
$connection = mysql_connect("localhost","root","r2d2c3po");

//test the connection
if (!$connection)
{
die("Database connection failed: " . mysql_error());
}
//select database to use
mysql_select_db("music_social_network", $connection);
//setup query
$sql = "SELECT * FROM `user` ORDER BY uid";
//get results from query or die
$result = mysql_query($sql, $connection) or die(mysql_error());
//fetch rows from query
$rows = mysql_fetch_assoc($result);
?>

<?php 

// find out how many records there are to update 
$size = count($_POST['uid']); 

// start a counter in order to number the input fields for each record 
$i = 0; 
print "<table width='100%' border='0' cellspacing='1' cellpadding='0'><tr><td>"; 
// open a form 
print "<form name='namestoupdate' method='post' action='update.php'> 
<table width='100%' border='0' cellspacing='1' cellpadding='1'><tr> 

<td align='center'><strong>User ID</strong></td> 
<td align='center'><strong>First Name</strong></td> 
<td align='center'><strong>Last Name</strong></td> 
<td align='center'><strong>Email</strong></td> 
</tr>\n"; 

// start a loop to print all of the users with their information 
// the mysql_fetch_array function puts each record into an array. each time it is called, it moves the array counter up until there are no more records left 
while ($Update = mysql_fetch_array($result)) { 
print "</tr>\n"; 
// assuming you have three important columns (the index (id), the course name (course), and the book info (bookinfo)) 
// start displaying the info; the most important part is to make the name an array (notice bookinfo[$i]) 
print "<td align='center'><p>{$Update['uid']}</p></td>\n"; 
print "<td align='center'><input type='text' fname='fname[$i]' value='{$Update['fname']}' /></td>"; 
print "<td align='center'><input type='text' size='40' lname='lname[$i]' value='{$Update['lname']}' /></td>\n"; 
print "<td align='center'><input type='text' size='40' email='email[$i]' value='{$Update['email']}' /></td>\n"; 
print "</tr>\n"; 
// add 1 to the count, close the loop, close the form, and the mysql connection 
++$i; 
} 
print "<tr> 
<td colspan='4' align='center'><input type='submit' value='submit' />"; 
print "</td> 
</tr> 
</table> 
</td> 
</tr> 
</form> 
</table>"; 
mysql_close($connection); 
?><br /><br /><div></body>
</html>

here is the content for the update.php page:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head>
<style type="text/css">

body {
margin:50px 0px; padding:0px;
text-align:center;
font:13px Tahoma,Geneva,sans-serif
}

#content {
width:1000px;
margin:0px auto;
text-align:left;
padding:15px;
border:1px dashed #333;
background-color:#eee;
}
</style>
<link rel="icon" href="http://www.mysite.com/favicon.ico" type="image/vnd.microsoft.icon" />
</head>
<body>
<div id='content'><h3><center>Success! </center></h3>
<table width='100%' border='0' cellspacing='1' cellpadding='0'><tr><td>


<table width='100%' border='0' cellspacing='1' cellpadding='1'>
<tr>

<td align='center'><strong>User ID</strong></td>
<td align='center'><strong>First Name</strong></td>
<td align='center'><strong>Last Name</strong></td>
<td align='center'><strong>Email</strong></td>
</tr>
<?php 

//create a connection
$connection = mysql_connect("localhost","root","r2d2c3po");

//test the connection
if (!$connection)
{
die("Database connection failed: " . mysql_error());
}
//select database to use
mysql_select_db("music_social_network", $connection);
//setup query
$sql = "SELECT * FROM `user` ORDER BY uid";
//get results from query or die
$result = mysql_query($sql, $connection) or die(mysql_error());
//fetch rows from query
$rows = mysql_fetch_assoc($result);




// find out how many records there are to update 
$size = count($_POST['uid']); 

// start a loop in order to update each record 
$i = 0; 
while ($i < $size) { 
// define each variable 
$uid = $_POST['uid'][$i]; 
$fname = $_POST['fname'][$i]; 
$lname = $_POST['lname'][$i]; 
$email = $_POST['email'][$i]; 

// do the update and print out some info just to provide some visual feedback 

$query = "UPDATE `user` SET `fname` = '$fname', `lname` = '$lname', `email` = '$email' WHERE `uid` = '$uid' "; 
mysql_query($sql) or die ("Error in query: $query"); 
print " 

</tr> 
<td align='left'><p>$uid</p></td> 
<td align='left'>$fname</td> 
<td align='left'>$fname</td> 
<td align='left'>$lname</td> 
</tr> 
"; 
++$i; 
} 
mysql_close($connection); 
?>
<tr>
<td colspan='4' align='center'>
</td>
</tr>
</table>
</td>
</tr>

</table></div></body></html>

Mod edit: [code] . . . [/code] tags added.

Hi, I have a row called code in mysql data base now what I want to do is something like this

$rand=rand(1111,9999);

$code=mysql_query("UPDATE member SET code='$rand' WHERE clan_id='1'");

At the moment its updating the code row with the same random number. What I want to achieve is a unique number for each member.

For instance

Currently if the $rand=1289 lets suppose

Every code row will become 1289  I want the code rows to be different to each other. I tried a few things up my sleeve but no luck. How can i do this? thanks any help much appreciated.

hello friend,

I use these code for update my database with radio button checked or unchecked through array but my code could not update the server data please help me :::

my code is :::

<?php
                            //include configuration file for connection
                            include_once('config.php');
                            $sql = "select * from ATTENDANCE";
                            $result=mysql_query($sql);
                            $count=mysql_num_rows($result);
                         ?>
                         <form name="form1" method="post" action="<? echo $_SERVER['REQUEST_URI']; ?>">
                            <table style="text-align: left; padding: 5px;" cellpadding="0px" cellspacing="0px">
                           <tbody>
                              <tr>
                                 <th style="text-align: center; padding: 5px; border: 1px #000000 solid;">Student ID</th>
                                 <th style="text-align: center; padding: 5px; border: 1px #000000 solid;">Student Name</th>
                                 <th style="text-align: center; padding: 5px; border: 1px #000000 solid;">Student Present</th>
                                 <th style="text-align: center; padding: 5px; border: 1px #000000 solid;">Student Absent</th>
                                 <th style="text-align: center; padding: 5px; border: 1px #000000 solid;">Comment</th>
                              </tr>
                              <?php
                                 while($rows=mysql_fetch_array($result))
                                 {
                              ?>                                                   
                              <tr>
                                 <td class="table1">
                                    <? $id[] = $rows['STUDENT_ID']; ?><? echo $rows['STUDENT_ID']; ?>
                                 </td>
                                 <td class="table1">
                                    <? echo $rows['STUDENT_NAME']; ?>
                                 </td>                                                      
                                 <td id="present">
                                    <input type="radio" name="present<? echo $rows['STUDENT_ID']; ?>" checked="checked" value="PRESENT">Present            
                                 </td>
                                 <td id="absent">                                    
                                    <input type="radio" name="present<? echo $rows['STUDENT_ID']; ?>" value="ABSENT">Absent
                                 </td>                              
                                 <td style="text-align: left; padding: 5px; border: 1px #000000 solid; height: 33px;">
                                    <? echo $rows['COMMENT']; ?>
                                 </td>                                                   
                              </tr>
                              <?php }?>
                              <tr>
                                 <td colspan="5" style="vertical-align:middle; text-align: center;"><br><br>
                                    <input id="Submit" type="submit" name="Submit" value="Submit" style="text-align: center; background-color: #000000; color: #ffffff; border: 1px #000000 solid;">
                                 </td>
                              </tr>                                                                                 
                           </tbody>
                        </table>
                     </form>   
                     <?php
                        if($_POST['$Submit'])
                        {
                           foreach($_POST['STUDENT_ID'] as $id)
                           {                               
                              $sql = "UPDATE ATTENDANCE SET STUDENT_PRESENT = '".$_POST["present".$id]."' WHERE STUDENT_ID = '".$id."' ";
                              $result = mysql_query($sql);
                           }
                        }
                        if($result)
                        {    print_r ($_POST);                        
                           header("location:report.php");                           
                        }
                        else
                        {
                           echo "Your entry is not completed at this time.............";
                        }
                     ?>