MySQL order

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jeaux
Forum Commoner
Posts: 25
Joined: Sun Aug 24, 2008 5:11 pm

MySQL order

Post by jeaux »

I don't know if this goes here or in theory but here goes.

I'm doing a restaurant site that allows the manager to change just about everything. I suppose it like a CMS. I have a MySQL DB that stores all the menu data and wine list data to be displayed for the public. There's a manager interface that allows the manager to add, delete, or edit menu categories in one table and add, delete, or edit menu items in another table. The public display page iterates the category table and displays the first category. Then it lists all the items for that category from the other table, and repeats until all categories and items are displayed.

I want to write a script now for ordering the items. I thought that I could add a numbered column and just alter the numbers of the two conflicting rows if he/she wanted the item moved up or down, but in the edit menu item script I wrote they are also allowed to alter the category for any item. This is for when an item has been on the "new additions" category for a while and it's time to move to a more permanent location. You can see that changing the category could create two items with the same order number.

Am I making this harder than it should be. I'm a noob so don't be too cruel.

Many thanks,
Joe
jeaux
Forum Commoner
Posts: 25
Joined: Sun Aug 24, 2008 5:11 pm

Re: MySQL order

Post by jeaux »

I've been thinking about this all night and the best idea I can come up with is to set up a conditional statement for when an items category has been changed.

Code: Select all

If  (category has been changed){
$sql= (find all items for that category, order them by `OrderColumn` take the greatest number);
 increment by +1;
}
 
Does that sound reasonable or is there an easier way?

Thanks
matthijs
DevNet Master
Posts: 3360
Joined: Thu Oct 06, 2005 3:57 pm

Re: MySQL order

Post by matthijs »

I think you have to be more specific with your question(s) and give some more info (probably also how your current db tables look like), before people can help.
jeaux
Forum Commoner
Posts: 25
Joined: Sun Aug 24, 2008 5:11 pm

Re: MySQL order

Post by jeaux »

That's the whole thing, I haven't added the `order` column yet. I wanted to get it straight in my head before diving in. I'm convinced that the conditional statement will work and I'll post the tables and scripts in a day or two once I work out the kinks for a critique.

Thanks
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: MySQL order

Post by califdon »

I'd suggest that you try to think of some other possible criterion for ordering the items. Is it really totally arbitrary? I have found that such situations are much rarer than we usually think. If there's a reason that managers want to change the order in a list, there's probably some other fact that drives the decision. If you can identify that fact, you may want to incorporate it into your database. I do realize that sometimes it really is arbitrary, like "this week's special." In that case, you might consider numbers that are not contiguous, so you can "insert" numbers between them. Like maybe initially you assign only even hundreds, then when you change around, you can assign even tens, etc. At least that reduces the total renumbering of all the records every time you want to do a reordering. You could also assign blocks of thousands for your different categories, requiring that an item reassigned to a different category would keep its old hundreds, tens and ones digits, with the new category thousands digit. Or something. Just quick thoughts for you.
jeaux
Forum Commoner
Posts: 25
Joined: Sun Aug 24, 2008 5:11 pm

Re: MySQL order

Post by jeaux »

Jack of Zircons you have been an amazing source of knowledge for me in these forums but the concept of this site is hassle free. I don't want to set up a schema that will eventually fail. The direction is total control over the content with my style sheets covering design to keep the site uniform. There are multiple locations with different managers and the only way I get to not have to listen to one of them complain is to relinquish control of all content. I thought that there might have been an easy solution for what I was trying to do but I guess not. In a day or two when I post the solution I worked out, I look forward to your critique.

Thank you again for your incite,
Joe
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: MySQL order

Post by califdon »

I'm glad if my ideas may have spurred your own thoughts on your project. Under the conditions you described, then, I would be tempted to place the entire issue of ordering into the managers' hands. On the managers' admin forms, I would provide maybe a drop-down list of their current items and allow them to indicate which item on the list their new item should follow. With that input, it wouldn't be hard to renumber all of them, programmatically. That would work for a new item, but you would probably need to allow them also to "move" items up or down in the list. That's tougher, in terms of programming and display, but it is certainly do-able. Many applications do this sort of thing. At least it would place the responsibility on the managers to do whatever they want. For changing between managers, you could still do what I suggested earlier, use a prefix for each manager. These sequence numbers wouldn't have to ever be visible to the managers, if you design the interface so they just see a list where they can either move items "up" or "down" or "insert after" other items. I haven't tried to program this kind of an interface, but I've seen it done in dozens of applications.
jeaux
Forum Commoner
Posts: 25
Joined: Sun Aug 24, 2008 5:11 pm

The ugliest code known to man

Post by jeaux »

OK, so as the title suggest prepare yourself for the gory details.

This is the script that lists all the Category Items and the corresponding Menu Items for each Category with a picture of an arrow between Menu Items.

Remember you have been forewarned.

Code: Select all

<?php
 
  $conn = mysql_connect($dbhost, $dbuser, $dbpass)
            or die('Error connecting to MySQL.');
 
  mysql_select_db($dbname)
            or die('Error selecting database.');
  $rs_menucategories = mysql_query('SELECT `MenuCategoryID`, `MenuCategoryName` FROM `universitymenucategory`');
 
  $q_menuitems = 'SELECT `MenuCategoryID`,`MenuItemID`,`Order`,`ItemName`, `ItemCost`, `ItemDescription`'
                   . ' FROM `universitymenu`'
                   . ' WHERE `MenuCategoryID` = %d'
                   . ' ORDER BY `Order` ASC';
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Order menu items</title>
 
<link href="../../../../CSSFolder/order.css" rel="stylesheet" type="text/css">
</head>
 
<body>
 <form name="ordermenuitem" action="ordermenuitem.php" method="POST">
    <table>
        <?php while ($crow = mysql_fetch_array($rs_menucategories)) { ?>
          <tr><td><h2 class="menu-category">
            <?php echo $crow['MenuCategoryName']; ?>
          </h2></td></tr>
          <?php $rs_menuitems = mysql_query(sprintf($q_menuitems, $crow['MenuCategoryID']));
                $count = 0;
                while ($irow = mysql_fetch_array($rs_menuitems)) { 
                $rowcount = mysql_num_rows($rs_menuitems);
                $count++;
          ?>
              <tr>
                 <td>
              <?php echo $irow['ItemName']; ?>
                 </td>
              </tr>
              <?php
              if ($count < $rowcount) {
              ?>
              <tr>
                 <td>
                    <input name="Button" type="image" class="submit" value="
                    <?php
              echo $irow['MenuItemID'];
              ?>
              " src="../../../../Images/arrow.gif">
                 </td>
              </tr>
          <?php } ?>
        <?php } ?>
      <?php } ?>
    </table>
  </form>
</body>
</html>
And this is the action form.

Code: Select all

<?php
 
  $conn = mysql_connect($dbhost, $dbuser, $dbpass)
            or die('Error connecting to MySQL.');
 
  mysql_select_db($dbname)
    or die('Error selecting database.');
    $Marker = 0;
    $OrderDown ="";
    $OrderUp ="";
if(isset($_POST['Button']))
  {
    $MenuItemID=mysql_real_escape_string(trim($_POST['Button']));
    $result=mysql_query("Select * FROM `universitymenu` WHERE `MenuItemID`='$MenuItemID'");
    $row=mysql_fetch_array($result);
    $CategoryID=$row['MenuCategoryID'];
    $AllItemsInCategory=mysql_query("Select * FROM `universitymenu` WHERE `MenuCategoryID`='$CategoryID' ORDER BY `Order` ASC");
  while ($allrow = mysql_fetch_array($AllItemsInCategory)) 
     {
       if ($Marker == 1)
          {
            $Marker = 0;
            $OrderDown = $allrow['Order'];
            $SecondID = $allrow['MenuItemID'];
          }
       if($allrow['MenuItemID']==$MenuItemID)
          {
            $OrderUp = $allrow['Order'];
            $Marker = 1;
          }
       }
   $updatequery1="UPDATE `universitymenu` SET `Order`='$OrderDown' WHERE `MenuItemID`='$MenuItemID'";
   $updatequery2="UPDATE `universitymenu` SET `Order`='$OrderUp' WHERE `MenuItemID`='$SecondID'";
                                if (!mysql_query($updatequery1,$conn))
                                  {
                                     die('Error: ' . mysql_error());
                                  }
                                echo "First record reordered";
                                if (!mysql_query($updatequery2,$conn))
                                  {
                                     die('Error: ' . mysql_error());
                                  }
                                echo "<br>Second record reordered";
                                mysql_close($conn);
 
  }
?>
Well at least it works. There is still one problem though. If the manager reloads the action page it screws up the DB. What should I do to prevent that, or even better how do I consolidate the two files into one that would just display the fact that the items where indeed switched? i.e. reload the first page.

Oh and Jack each manager gets his/hers own DB tables for their location. So really I only need this to work for one manager. Sorry for the confusion.

Many thanks,
Joe
Post Reply