beginner help

**jeaux** · 08-17-2008, 02:26 PM

I'm trying to display a menu category followed by all the menu items that fall under that category using nested while statements.

PHP Code:


<?php

$dbhost = 'localhost';

$dbuser = 'user';

$dbpass = 'password';



$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die                      

                        ('Error connecting to mysql');



$dbname = 'Restaurant';

mysql_select_db($dbname);



$querymenucategory  = "SELECT MenuCategoryID, MenuCategoryName FROM universitymenucategory";

$resultmenucategory = mysql_query($querymenucategory);



$querymenuitem  = "SELECT MenuCategoryID, ItemName, ItemCost, ItemDescription FROM universitymenu";

$resultmenuitem = mysql_query($querymenuitem);



    echo "<!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>

<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" />

<title>Menu</title>

<link href=\"../CSSFolder/menu.css\" rel=\"stylesheet\" type=\"text/css\" />

</head>



<body>

<div id =\"container\">

  <div class =\"horbar\">

<a href=\"../index.htm\">Home</a>

<a href=\"menu.htm\">Menu</a>

<a href=\"wine.htm\">Wine</a>

<a href=\"specials.htm\">Specials</a>

<a href=\"events.htm\">Events</a>

<a href=\"map.htm\">Map</a>

<a href=\"contact.htm\">Contact</a></div>

  <div id=\"menu-container\">

    <div class=\"gutter\">\n";

    

while($row = mysql_fetch_array($resultmenucategory, MYSQL_ASSOC))

{

    echo "<h2 class =\"menu-category\">{$row['MenuCategoryName']}</h2>\n" .

         "<dl>\n";

     while($row = mysql_fetch_array($resultmenuitem, MYSQL_ASSOC))

{

    echo "<dt>{$row['ItemName']}</dt>\n" .

         "<dd class =\"price\">{$row['ItemCost']}</dd>\n" .

         "<dd class =\"ingredients\">{$row['ItemDescription']}</dd>\n";

}

    echo "</dl>\n";

}



echo "

    </div><!--end gutter-->

  </div><!--end menu-container-->

</div><!--end container-->

</body>

</html>";

?>

In the nested while loop I need for it to only print the items for that category.
This is the DB structure of the 2 tables.

The first table name is "universitymenucategory" and its fields are "MenuCategoryID, MenuCategoryName".

The second table name is "universitymenu" and its fields are "MenuCategoryID, ItemName, ItemCost, ItemDescription".

So basically I need the "MenuCategoryID" field to match as the loops iterate the DB.

If you were wondering, no this isn't a college assignment. I haven't be in school for quite some time now. The restaurant is located near Rice so they call it the university location.

I'm not a programmer, I've only read a couple of books. Please don't be too cruel.

Many thanks for your time,
Joe

**Twey** · 08-17-2008, 05:00 PM

Code:

<?php
  $dbhost = 'localhost';
  $dbuser = 'user';
  $dbpass = 'password';
  $dbname = 'Restaurant';

  $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, ItemName, ItemCost, ItemDescription'
                   . 'FROM universitymenu'
                   . 'WHERE MenuCategoryID = %d';
?>

<!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>Menu</title>
    <link href="../CSSFolder/menu.css" rel="stylesheet" type="text/css">
  </head>
  <body>
    <div id="container">
      <div class="horbar">
        <a href="../index.htm">Home</a>
        <a href="menu.htm">Menu</a>
        <a href="wine.htm">Wine</a>
        <a href="specials.htm">Specials</a>
        <a href="events.htm">Events</a>
        <a href="map.htm">Map</a>
        <a href="contact.htm">Contact</a>
      </div>
      <div id="menu-container">
        <div class="gutter">
        <?php while ($crow = mysql_fetch_array($rs_menucategories)) { ?>
          <h2 class="menu-category">
            <?php echo $crow['MenuCategoryName']; ?>
          </h2>
          <dl>
          <?php $rs_menuitems = mysql_query(sprintf($q_menuitems, $crow['MenuCategoryID']));
                while ($irow = mysql_fetch_array($rs_menuitems)) { ?>
            <dt>
              <?php echo $irow['ItemName']; ?>
            </dt>
            <dd class="price">
              <?php echo $irow['ItemCost']; ?>
            </dd>
            <dd class="ingredients">
              <?php echo $irow['ItemDescription']; ?>
            </dd>
          <?php } ?>
          </dl>
        <?php } ?>
        </div> <!-- end gutter -->
      </div> <!-- end menu-container -->
    </div> <!-- end container -->
  </body>
</html>

I'm not a programmer, I've only read a couple of books.

Then, from the top:

Code:

$querymenucategory  = "SELECT MenuCategoryID, MenuCategoryName FROM universitymenucategory";

You only ever use this variable once and it isn't necessary for code organisation. There's no point in it being there, it's a waste of memory.
If you're not going to interpolate variables into the string, don't use double quotes -- there's no need and it's slower to parse.

Code:

$querymenuitem  = "SELECT MenuCategoryID, ItemName, ItemCost, ItemDescription FROM universitymenu";
$resultmenuitem = mysql_query($querymenuitem);

You can't execute this query yet, as you don't have the necessary ID.

Code:

    echo "<!DOCTYPE html PUBLIC

Woah woah woah! Code and output should be kept as separate as possible. This means that if you find yourself echoing a big chunk of markup, you should break out of PHP parsing mode. If you find yourself echoing a medium chunk of markup, you should break out of PHP parsing mode. If you find yourself echoing a small chunk of markup, chances are you should break out of PHP parsing mode.

Code:

<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">

The Transitional line of DOCTYPEs were designed to ease the move from HTML3, some decade ago now. They're outdated. Only Strict DOCTYPEs should be used on the Web of today.
XHTML, however, is not currently supported by IE, which means that you probably shouldn't use it for pages where you don't control the browser everyone is going to be using to access your site (i.e. every internet page and many intranet ones). You can get some more info at http://www.webdevout.net/articles/beware-of-xhtml.
We're beginning to see one of the penalties of echoing a whole page of markup as one big string: having to escape every single special character.

Code:

while($row = mysql_fetch_array($resultmenucategory, MYSQL_ASSOC))

You don't need to specify MYSQL_ASSOC, the default is MYSQL_BOTH and there's negligible to no overhead to providing the numerical keys as well.

Code:

    echo "<h2 class =\"menu-category\">{$row['MenuCategoryName']}</h2>\n" .
         "<dl>\n";

I'm pretty sure you don't want an <h2> and a <dl>. I think you'd probably be better off with a <table> here -- this is what tables are for. I've left this alone in my revision of your code because I don't know what your stylesheet is doing, but that's something you should consider changing.

Code:

     while($row = mysql_fetch_array($resultmenuitem, MYSQL_ASSOC))

Oops, careful! You've overwritten your original $row variable. That's not going to give you what you think it is on the next pass.
This is about where we should be doing that original mysql_query(): we've got our ID now and can fit it into the query string so we only get the rows we need.

Good luck with your programming hobby/career of choice!

**jeaux** · 08-17-2008, 06:23 PM

Wow Twey. Thank you for such complete answers and advice. But when I run the file the menu category's show up but the menu items create this error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in myfilelocation\myfilename.php on line 46

the second line of this is line 46

PHP Code:


<?php $rs_menuitems = mysql_query(sprintf($q_menuitems, $crow['MenuCategoryID']));

                while ($irow = mysql_fetch_array($rs_menuitems)) { ?>

I don't know if this has any thing to do with the problem, but my research on sprintf revealed that it is "sprintf — Return a formatted string". MenuCategoryID is an INT.

As for your advice:

I'm pretty sure you don't want an <h2> and a <dl>. I think you'd probably be better off with a <table> here -- this is what tables are for. I've left this alone in my revision of your code because I don't know what your stylesheet is doing, but that's something you should consider changing.

I've attached 2 screenshots for some insight to what I'm doing.

screenshot.png is the results I get from your rewrite.
screenshot2.png is the result of just the html/CSS I wrote in order to map out the look.

**Twey** · 08-17-2008, 07:47 PM

The look is irrelevant -- you can style it as you wish using CSS. Semantically, it would be better off as a table.

As for the error, in this case that probably means that something is wrong with the query. Are you sure that all the field names match your database? Remember, case is important.

I don't know if this has any thing to do with the problem, but my research on sprintf revealed that it is "sprintf — Return a formatted string". MenuCategoryID is an INT.

I'm not quite sure what you're getting at here. Are you trying to say that it is impossible to insert an integer into a string? This is exactly what sprintf() was designed to do. The return value is a string, which is what your documentation is telling you -- which is just fine, since an SQL query should be a string.

**jeaux** · 08-17-2008, 08:22 PM

I checked and could find any spelling or case errors.

I have attached 2 more screenshots from the phpmyadmin columns list for the 2 tables. The attached files names are a direct copy and paste from the table list on the left of the phpmyadmin interface.

I'm not quite sure what you're getting at here. Are you trying to say that it is impossible to insert an integer into a string? This is exactly what sprintf() was designed to do. The return value is a string, which is what your documentation is telling you -- which is just fine, since an SQL query should be a string.

No, I was only trying to help resolve the problem. I wasn't familiar with sprintf()

And thanks for the NET shorttag advice on a different post. That article was very interesting.

**Twey** · 08-17-2008, 09:59 PM

OK then, let's get to debugging this. Firstly, since you're selecting every column from your tables, you can replace than long list of column names with a single '*' meaning 'everything':

Code:

  $rs_menucategories = mysql_query('SELECT * FROM universitymenucategory');

  $q_menuitems = 'SELECT * FROM universitymenu'
                   . 'WHERE MenuCategoryID = %d';

Then, add a 'print' statement in there at line 46 so we can see exactly the query we're executing:

Code:

          <?php $rs_menuitems = mysql_query(sprintf($q_menuitems, $crow['MenuCategoryID']));
                print sprintf($q_menuitems, $crow['MenuCategoryID']);
                while ($irow = mysql_fetch_array($rs_menuitems)) { ?>

And finally, at the top of the code, let's get as much error reporting as possible in case we've missed something subtle (you may want to do this for all your code, it can catch some easily-missed issues):

Code:

<?php
  error_reporting(E_ALL);

  $dbhost = 'localhost';

Also, while we're at this, defines are probably a better idea than variables for that immutable database config:

Code:

<?php
  error_reporting(E_ALL);

  define('DB_HOST', 'localhost');
  define('DB_USER', 'user');
  define('DB_PASS', 'password');
  define('DB_NAME', 'Restaurant');

  $conn = mysql_connect(DB_HOST, DB_USER, DB_PASS)
            or die('Error connecting to MySQL.');

  mysql_select_db(DB_NAME)
    or die('Error selecting database.');

  $rs_menucategories = mysql_query('SELECT * FROM universitymenucategory');

Code:

<?php
  error_reporting(E_ALL);

  define('DB_HOST', 'localhost');
  define('DB_USER', 'user');
  define('DB_PASS', 'password');
  define('DB_NAME', 'Restaurant');

  $conn = mysql_connect(DB_HOST, DB_USER, DB_PASS)
            or die('Error connecting to MySQL.');

  mysql_select_db(DB_NAME)
    or die('Error selecting database.');

  $rs_menucategories = mysql_query('SELECT * FROM universitymenucategory');

  $q_menuitems = 'SELECT * FROM universitymenu'
                   . 'WHERE MenuCategoryID = %d';
?>

<!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>Menu</title>
    <link href="../CSSFolder/menu.css" rel="stylesheet" type="text/css">
  </head>
  <body>
    <div id="container">
      <div class="horbar">
        <a href="../index.htm">Home</a>
        <a href="menu.htm">Menu</a>
        <a href="wine.htm">Wine</a>
        <a href="specials.htm">Specials</a>
        <a href="events.htm">Events</a>
        <a href="map.htm">Map</a>
        <a href="contact.htm">Contact</a>
      </div>
      <div id="menu-container">
        <div class="gutter">
        <?php while ($crow = mysql_fetch_array($rs_menucategories)) { ?>
          <h2 class="menu-category">
            <?php echo $crow['MenuCategoryName']; ?>
          </h2>
          <dl>
          <?php $rs_menuitems = mysql_query($t = sprintf($q_menuitems, $crow['MenuCategoryID']));
                print $t;
                while ($irow = mysql_fetch_array($rs_menuitems)) { ?>
            <dt>
              <?php echo $irow['ItemName']; ?>
            </dt>
            <dd class="price">
              <?php echo $irow['ItemCost']; ?>
            </dd>
            <dd class="ingredients">
              <?php echo $irow['ItemDescription']; ?>
            </dd>
          <?php } ?>
          </dl>
        <?php } ?>
        </div> <!-- end gutter -->
      </div> <!-- end menu-container -->
    </div> <!-- end container -->
  </body>
</html>

**jeaux** · 08-17-2008, 11:24 PM

Wow, this is exactly what I needed. I don't have a professor or other mentor to teach me the methods of debugging. I can't thank you enough for your time. Hopefully the end result of this thread will be a lesser dependency on external help.

The results of the file now being executed is the same as before with the addition of the results of the print statement:

Tapas Frias

SELECT * FROM universitymenuWHERE MenuCategoryID = 0
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\xampp\htdocs\Restaurants\University\index3.php on line 48

Tapas Calientes

SELECT * FROM universitymenuWHERE MenuCategoryID = 1
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\xampp\htdocs\Restaurants\University\index3.php on line 48

I'm pleased to see that at least the script is recognizing the MenuCategoryID, but I have no idea what the problem with iterating the $rs_menuitems is.

Should I have wrapped the errors messages in some tags?

Thank you,
Joe

**Twey** · 08-18-2008, 12:03 AM

I actually realised this already -- my connection was being a bit iffy, and it seems that my edit didn't get saved. The bug is here:

Code:

  $q_menuitems = 'SELECT * FROM universitymenu'
                   . 'WHERE MenuCategoryID = %d';

I forgot to add a space before the WHERE clause. It should read:

Code:

  $q_menuitems = 'SELECT * FROM universitymenu'
                   . ' WHERE MenuCategoryID = %d';

Code:

<?php
  error_reporting(E_ALL);

  define('DB_HOST', 'localhost');
  define('DB_USER', 'user');
  define('DB_PASS', 'password');
  define('DB_NAME', 'Restaurant');

  $conn = mysql_connect(DB_HOST, DB_USER, DB_PASS)
            or die('Error connecting to MySQL.');

  mysql_select_db(DB_NAME)
    or die('Error selecting database.');

  $rs_menucategories = mysql_query('SELECT * FROM universitymenucategory');

  $q_menuitems = 'SELECT * FROM universitymenu'
                   . ' WHERE MenuCategoryID = %d';
?>

<!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>Menu</title>
    <link href="../CSSFolder/menu.css" rel="stylesheet" type="text/css">
  </head>
  <body>
    <div id="container">
      <div class="horbar">
        <a href="../index.htm">Home</a>
        <a href="menu.htm">Menu</a>
        <a href="wine.htm">Wine</a>
        <a href="specials.htm">Specials</a>
        <a href="events.htm">Events</a>
        <a href="map.htm">Map</a>
        <a href="contact.htm">Contact</a>
      </div>
      <div id="menu-container">
        <div class="gutter">
        <?php while ($crow = mysql_fetch_array($rs_menucategories)) { ?>
          <h2 class="menu-category">
            <?php echo $crow['MenuCategoryName']; ?>
          </h2>
          <dl>
          <?php $rs_menuitems = mysql_query(sprintf($q_menuitems, $crow['MenuCategoryID']));
                while ($irow = mysql_fetch_array($rs_menuitems)) { ?>
            <dt>
              <?php echo $irow['ItemName']; ?>
            </dt>
            <dd class="price">
              <?php echo $irow['ItemCost']; ?>
            </dd>
            <dd class="ingredients">
              <?php echo $irow['ItemDescription']; ?>
            </dd>
          <?php } ?>
          </dl>
        <?php } ?>
        </div> <!-- end gutter -->
      </div> <!-- end menu-container -->
    </div> <!-- end container -->
  </body>
</html>

**motormichael12** · 08-18-2008, 12:03 AM

Shouldn't there be a space between universitymenu and WHERE?

SELECT * FROM universitymenuWHERE MenuCategoryID = 0

=>

SELECT * FROM universitymenu WHERE MenuCategoryID = 0

**jeaux** · 08-18-2008, 12:27 AM

In the original code there is a space. The error code that I put was a direct copy and paste from the browser output(firefox).

Correction:
That was indeed the problem.

Thanks for all your patience, time, and expertise.
Joe