Problem updating multiple rows at once

[Smudly]

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>
";

?>

 

 

 

 

[attachment deleted by admin]

[RussellReal]

I think your code is structured completely wrong

 

You should not be looping through the return results from a mysql query if you already have the ids, that is redundant at best..

 

Try something like this..

 

<?php
mysql_query("UPDATE upload SET uploaded = 'yes' WHERE id IN (".implode(',',$usersids).")");
?>

 

This is basically saying.. set all the rows that's id is in $usersids to show uploaded = yes..