Search Webmaster Help and Solution

Problem updating multiple rows at once

Hi,

Ive 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 its uploaded column to yes.
Now Im 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 dont have to click Uploaded for every single row. (view attached image if you are unsure what I am talking about)

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

Tip: The area of the code that Im currently having troubles with is here

PHP Code:   $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 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
{
// Dont Delete
return false;
}
}
;

echo
function show_undelete()
{
var r=confirm(Undelete?);
if (r==true)
{
// Undelete
return true;
}
else
{
// Dont 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>
;

?> Hi,

Ive 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 its uploaded column to yes.
Now Im 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 dont have to click Uploaded for every single row. (view attached image if you are unsure what I am talking about)

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

Tip: The area of the code that Im currently having troubles with is here

PHP Code:   $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 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
{
// Dont Delete
return false;
}
}
;

echo
function show_undelete()
{
var r=confirm(Undelete?);
if (r==true)
{
// Undelete
return true;
}
else
{
// Dont 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>
;

?>

View Complete Thread with Replies

Related Items

Query failed: connection to localhost:3354 failed (errno=111, msg=Connection refused).