Change display order of rows in a database via a form (similar to vB's forum manager)

Ted_Varnson

I want to be able to change the INT value (display_order) for all the rows in my table via a single form in the admin cp I am coding. This idea along the lines of vB's forum display order.

I've got it all set up and the query is working, its just that my code will only update the record with the highest display number rather than updating all the rows.

The purpose of this if you are wondering is to allow my church to add and edit sections to a given (in this case the Children's Ministry page) page and be able to decide what order the section are displayed.

The database uses four columns: id, title, content, display_order. Like I said before the problem is that it is only updating the row with the highest display_order value rather than all of them. I would like it to update all at once to make it simple for the staff at my church to update things as they need. Any help is greatly appreciated!

See the script: http://www.wildewoodbc.org/temp/children.php

Code

<?
include('global.php');
$title = 'Children\'s Page';
// ################################ ORDER SECTIONS #############################
if ($_REQUEST['do'] == 'modify' OR empty($_REQUEST['do']))
{
print("<!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>
<title>" . $sitetitle . " Control Panel - " . $title . "</title>
<meta http-equiv=\"Content-Type\" content=\"text/html; charset=iso-8859-1\" />
<link href=\"cpcss.css\" rel=\"stylesheet\" type=\"text/css\" />
<style type=\"text/css\">
<!--
body {
	SCROLLBAR-BASE-COLOR: #5075B4;
	SCROLLBAR-ARROW-COLOR: #FFFFFF;
   }

//-->
</style>
</head>

<body bgcolor=\"#EBF3FE\" leftmargin=\"0\" topmargin=\"0\" marginwidth=\"0\" marginheight=\"0\">
<table width=\"100%\" border=\"0\" align=\"center\" cellpadding=\"0\" cellspacing=\"0\">
  <tr>
    <td class=\"title\"> 
      <div align=\"right\">" . $title . "</div></td>
  </tr>
</table>
<br />
<table width=\"95%\" border=\"0\" align=\"center\" cellpadding=\"0\" cellspacing=\"0\">
  <tr>
    <td class=\"navborder\"><table width=\"100%\" border=\"0\" cellspacing=\"1\" cellpadding=\"0\">
        <tr>
          <td class=\"bodycat\">" . $title . "</td>
        </tr>

    <tr>
      <td><table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\">
          <tr>
            <td><table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"3\">
                <form name=\"updateorder\" id=\"updateorder\" method=\"post\" action=\"children.php?do=updateorder\">
                    <tr class=\"formmisc\">
	  <td>Modify Section</td>
      <td><div align=\"right\">Change Dislpay Order</div></td>
    </tr>");
$query = "SELECT * FROM page_children ORDER BY display_order ASC";
$result = mysql_query($query, $link);
$i = 1 ;
while($row = mysql_fetch_assoc($result)){
            $id = $_row['display_order'];  // ???? what is thius used for ???
            if ($i % 2 == 0) {  // so if it is an even rownumber
                 echo ('<tr class="alt1">');
            } else {
                echo ('<tr class="alt2">');
            }
            $i ++ ;
				echo ('<td>' . $row['title'] . '</td>');
				echo ('<td><div align="right"><input id="id" name="id" type="hidden" value="' . $row['id'] . '" /><input class="fields" id="display_order" name="display_order" type="text" value="' . $row['display_order'] . '" size="5" /></div></td>');
				echo ('</tr>');
} 
print("<tr> 
                        <td colspan=\"2\" class=\"formmisc\"><div align=\"center\"> 
                            <input name=\"Submit\" type=\"submit\" class=\"button\" value=\"Submit\" />
                          </div></td>
                      </tr>
                    </form>
                  </table></td>
  </tr>

</table>
</td>
        </tr>

  </table></td>
  </tr>
</table>");
print($cpfooter);
print("</body>
</html>");
}

// ###################### UPDATE THE ORDER #######################
if ($_GET['do'] == 'updateorder') {    

   $id = $_POST['id'];    

   $display_order = $_POST['display_order'];    

   $query = "UPDATE page_children SET display_order = '$display_order' WHERE id = '$id'";
   print($query);
}
?>

(Note: I know I don't have my query set up to update the database, I am printing it so that I can see what info its sending.)

dewen

you may not need

 
<input id="id" name="id" type="hidden" value="' . $row['id'] . '" />

in stead, make

<input class="fields" id="display_order" name="display_order" type="text" value="' . $row['display_order'] . '" size="5" />

into

<input class="fields" id="display_order_'.$row['display_order'].'" name="display_order_'.$row['display_order'].'" type="text" value="' . $row['display_order'] . '" size="5" />

Then you parse $POST['display_order$i'] dynamically

Ted_Varnson

Thanks for the help!

Sorry for the confusion, but can you be a litle omre specific as to wher to parse $POST['display_order$i']?

dewen

not tested:

if ($_GET['do'] == 'updateorder') {     

   for ($i=1;$i<4;$i++){
	   $this_display_order = $_POST['display_order_'.$i];
	   $query = "UPDATE page_children SET display_order = '{$this_display_order}' WHERE id = '{$i}'"; 
	   print($query); 

   }

}

dewen

sorry

$i<=4 in loop

Ted_Varnson

What if there are more than 4 records? I assume the four is for number of rows?

Also, it changed my display_order value of 1 to zero. Any idea why?

Ted_Varnson

Here is the query its spitting out

UPDATE page_children SET display_order = '' WHERE id = '1'UPDATE page_children SET display_order = '2' WHERE id = '2'UPDATE page_children SET display_order = '3' WHERE id = '3'UPDATE page_children SET display_order = '4' WHERE id = '4'UPDATE page_children SET display_order = '' WHERE id = '5'

Roger_Ramjet

In truth, your biggest problem here is in ensuring that the display order the user inputs is logical and consistent: ie 1,2,3,4 not 1,3,3,4 or some such thing. Do you see what I mean? I'm working on it now, interestingproblem that I could have a use for myself. I'll post what I come up with later.

Once you have that sorted then the update is easy peasy.

Ted_Varnson

I think it would be pkay in the database itselt, as it would just see it as a 3 and display them one after another. If you're talking about it screwing with the query though that is another thing. Thanks for your help. Its appreciated

Roger_Ramjet

OK. This should just about do it. I have chnaged some of your code, and marked the changes.

<?php
include('global.php');
$title = "Children's Page";

// *************** MY CODE FROM HERE *************************

if (isset($_POST['db_id'])) {
	$old=$_POST['old'];
	$new=$_POST['new'];
	$id=$_POST['db_id'];
	// test to see if new position is higher or lower than old position
	switch ($old-$new) {
	case < 0:  	// it is lower down the list so all in between old and new move up
	   // query to move them up
	   $sql1 = "UPDATE page_children 
	            SET display_order = display_order-1
	            WHERE display_order>$old AND display_order<=$new";
	   // query to update moved record
	   $sql2 = "UPDATE page_children
	            SET display_order = $new
	            WHERE id=$id";
	   $result = mysql_query($sql1) or die($sql1 . '<br />' . mysql_error());
	   $result = mysql_query($sql2) or die($sql2 . '<br />' . mysql_error());
 	   break;
	case > 0:  	// it is higher up the list so all in between old and new move down
	   // query to move them down
       $sql1 = "UPDATE page_children 
	            SET display_order = display_order+1
	            WHERE display_order<$old AND display_order>=$new";
	   // query to update moved record
	   $sql2 = "UPDATE page_children
	            SET display_order = $new
	            WHERE id=$id";
	   $result = mysql_query($sql1) or die($sql1 . '<br />' . mysql_error());
	   $result = mysql_query($sql2) or die($sql2 . '<br />' . mysql_error());
 	   break;   

	}
}
// **************** TO HERE ***********************************

print("<!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>
<title>" . $sitetitle . " Control Panel - " . $title . "</title>
<meta http-equiv=\"Content-Type\" content=\"text/html; charset=iso-8859-1\" />
<link href=\"cpcss.css\" rel=\"stylesheet\" type=\"text/css\" />
<style type=\"text/css\">
<!--
body {
    SCROLLBAR-BASE-COLOR: #5075B4;
    SCROLLBAR-ARROW-COLOR: #FFFFFF;
   }

//-->
</style>
</head>

<body bgcolor=\"#EBF3FE\" leftmargin=\"0\" topmargin=\"0\" marginwidth=\"0\" marginheight=\"0\">
<table width=\"100%\" border=\"0\" align=\"center\" cellpadding=\"0\" cellspacing=\"0\">
  <tr>
    <td class=\"title\">
      <div align=\"right\">" . $title . "</div></td>
  </tr>
</table>
<br />
<table width=\"95%\" border=\"0\" align=\"center\" cellpadding=\"0\" cellspacing=\"0\">
  <tr>
    <td class=\"navborder\"><table width=\"100%\" border=\"0\" cellspacing=\"1\" cellpadding=\"0\">
        <tr>
          <td class=\"bodycat\">" . $title . "</td>
        </tr>

    <tr>
      <td><table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\">
          <tr>
            <td><table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"3\">

// ************* CHOPPED OUT THE FORM *******************
// and will have one for each row

                    <tr class=\"formmisc\">
      <td>Modify Section</td>
      <td><div align=\"right\">Change Dislpay Order</div></td>
    </tr>");

// ********* MY CODE FROM HERE ************

$query = "SELECT * FROM page_children ORDER BY display_order ASC";
$result = mysql_query($query, $link);
$i = 1 ;
while($row = mysql_fetch_array($result)){
            if ($i % 2 == 0) {  // so if it is an even rownumber
                 echo '<tr class="alt1">';
            } else {
                echo '<tr class="alt2">';
            }
            $i ++ ;
                echo '<td>' . $row['title'] . '</td>
                      <td align="right">
                      <form name="updateorder" method="post" action="children.php">
                      <input type="hidden" name="db_id" value="'.$row['id'].'">
                      <input type="hidden" name="old" value="'.$row['display_order'].'">
                      <input class="fields" type="text" name="new" value="'.$row['display_order'].'" size="5" onChange="submit()" /></td>
                      </form>
                      </tr>';
}

// *********** TO HERE ***************

  </tr>

</table>
</td>
        </tr>

  </table></td>
  </tr>
</table>';
print($cpfooter);
print("</body>
</html>");
}

?>

Now, I can't guarantee that the html for the rest of the page is OK, may need some tweaking. The way thsi should work is that whenever the user edits one of the lines, ht echange is sent back to the server, the database is reordered and the page is then reloaded with the new order.

I really am not happy with the style of the original coding, full of inneccessary divs etc, and in clearing some of that out so I could see what I was doing I may have spoilt the layout. If so, then the original programmer will have to redo that part. BUT THERE WILL BE NO NEED TO CHANGE MY CODE. If oit has bugs then we can work on that seperately. Good luck and god bless.

Ted_Varnson

Parse error: parse error, unexpected '<' in /home/thekryp/public_html/ww/admin/test.php on line 13

The < and > are giving me errors

Thanks for your work.

Roger_Ramjet

Yep. That's me trying to use the VB syntax in PHP again. This will get around that, I think.


<?php
include('global.php');
$title = "Children's Page";

// *************** MY CODE FROM HERE *************************

if (isset($_POST['db_id'])) {
    $old=$_POST['old'];
    $new=$_POST['new'];
    $id=$_POST['db_id'];
    // test to see if new position is higher or lower than old position
    $x = $old-$new;
    switch (true) {
    case ($x < 0):      // it is lower down the list so all in between old and new move up
       // query to move them up
       $sql1 = "UPDATE page_children 
                SET display_order = display_order-1
                WHERE display_order>$old AND display_order<=$new";
       // query to update moved record
       $sql2 = "UPDATE page_children
                SET display_order = $new
                WHERE id=$id";
       $result = mysql_query($sql1) or die($sql1 . '<br />' . mysql_error());
       $result = mysql_query($sql2) or die($sql2 . '<br />' . mysql_error());
       break;
    case ($x > 0):      // it is higher up the list so all in between old and new move down
       // query to move them down
       $sql1 = "UPDATE page_children 
                SET display_order = display_order+1
                WHERE display_order<$old AND display_order>=$new";
       // query to update moved record
       $sql2 = "UPDATE page_children
                SET display_order = $new
                WHERE id=$id";
       $result = mysql_query($sql1) or die($sql1 . '<br />' . mysql_error());
       $result = mysql_query($sql2) or die($sql2 . '<br />' . mysql_error());
       break;   

    }
}
// **************** TO HERE ***********************************

print("<!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>
<title>" . $sitetitle . " Control Panel - " . $title . "</title>
<meta http-equiv=\"Content-Type\" content=\"text/html; charset=iso-8859-1\" />
<link href=\"cpcss.css\" rel=\"stylesheet\" type=\"text/css\" />
<style type=\"text/css\">
<!--
body {
    SCROLLBAR-BASE-COLOR: #5075B4;
    SCROLLBAR-ARROW-COLOR: #FFFFFF;
   }

//-->
</style>
</head>

<body bgcolor=\"#EBF3FE\" leftmargin=\"0\" topmargin=\"0\" marginwidth=\"0\" marginheight=\"0\">
<table width=\"100%\" border=\"0\" align=\"center\" cellpadding=\"0\" cellspacing=\"0\">
  <tr>
    <td class=\"title\">
      <div align=\"right\">" . $title . "</div></td>
  </tr>
</table>
<br />
<table width=\"95%\" border=\"0\" align=\"center\" cellpadding=\"0\" cellspacing=\"0\">
  <tr>
    <td class=\"navborder\"><table width=\"100%\" border=\"0\" cellspacing=\"1\" cellpadding=\"0\">
        <tr>
          <td class=\"bodycat\">" . $title . "</td>
        </tr>

    <tr>
      <td><table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\">
          <tr>
            <td><table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"3\">

// ************* CHOPPED OUT THE FORM *******************
// and will have one for each row

                    <tr class=\"formmisc\">
      <td>Modify Section</td>
      <td><div align=\"right\">Change Dislpay Order</div></td>
    </tr>");

// ********* MY CODE FROM HERE ************

$query = "SELECT * FROM page_children ORDER BY display_order ASC";
$result = mysql_query($query, $link);
$i = 1 ;
while($row = mysql_fetch_array($result)){
            if ($i % 2 == 0) {  // so if it is an even rownumber
                 echo '<tr class="alt1">';
            } else {
                echo '<tr class="alt2">';
            }
            $i ++ ;
                echo '<td>' . $row['title'] . '</td>
                      <td align="right">
                      <form name="updateorder" method="post" action="children.php">
                      <input type="hidden" name="db_id" value="'.$row['id'].'">
                      <input type="hidden" name="old" value="'.$row['display_order'].'">
                      <input class="fields" type="text" name="new" value="'.$row['display_order'].'" size="5" onChange="submit()" /></td>
                      </form>
                      </tr>';
}

// *********** TO HERE ***************

  </tr>

</table>
</td>
        </tr>

  </table></td>
  </tr>
</table>';
print($cpfooter);
print("</body>
</html>");
}

?>

According to the manual doing the switch that way should work. If not then we'll just change it to if-elseif

Ted_Varnson

Thanks very much. This is going to be so great. God bless