[RESOLVED] php variable or looping problem

[DerrickE]

Basically my database is setup like this:

sections -> id,sid,name
menu -> item,sid

the menu sid corresponds with the section id

The code below is supposed to pull the section id from the sections table and then for every section, it is supposed to pull the menu item that belongs in that section.

Section1 has 3 items
item1 from section1
item2 from section1
item3 from section1

Section2 has 1 item
item1 from section2
item2 from section2

Section3 has 4 items
item1 from section3
item2 from section3
item3 from section3
item4 from section3

The first loop works fine, it picks up the correct section ids.
The second loop to pick up the actual items in the section fails. It acts like it is looping through(ie. every section gets the # of items it actually has) But the data is from the 1st section only. ie, the 2nd and 3rd and 4th sections all contain the data from the 1st section.

It actually looks like this: (please note the section# from which it is pulling the item....different from above)

Section1 has 3 items
item1 from section1
item2 from section1
item3 from section1

Section2 has 1 item
item1 from section1
item2 from section1

Section3 has 4 items
item1 from section1
item2 from section1
item3 from section1

$sql = "SELECT * FROM sections where rid='$rid' ORDER BY sid";
$content .= $sql."<br>";
$modextra = "<hr><h1>Non-Sectioned</h1>";
$result = mysql_query($sql) or die(mysql_error());
$nrows = mysql_num_rows($result);
while($row = mysql_fetch_array($result)) {
    $secid[] = $row['id'];
    $sname[] = $row['name'];
}
for($j = 0; $j < $nrows; $j++) {
$content .= "<hr><h1>$sname[$j]</h1>";
$content .= "<TABLE BORDER=1 cellpadding=5 cellspacing=0 align=center width=100%>\n";
$sql = "SELECT * FROM menu where rid='$rid' AND sid='${$secid[$j]}' ORDER BY id";
$content .= $sql."<br>";
$result = mysql_query($sql) or die(mysql_error());
$rows = mysql_num_rows($result);
while($row = mysql_fetch_array($result)) {
    $id[] = $row['id'];
    $iname[] = $row['iname'];
    $idesc[] = $row['idesc'];
    $iprice[] = $row['iprice'];
    $iextra1[] = $row['extra1'];
    $iextra2[] = $row['extra2'];
    $isid[] = $row['sid'];

}
for($i = 0; $i < $rows; $i++) {
    $content .= " <TR>\n";
    $a = $i;
            $content .= "<TD valign=top width=200><font style=\"font-size:8pt;\"><b>ID:</b> ". $id[$a] ."<br><b><a name=\"". $id[$a] ."\">Name:</a></b> ". $iname[$a]."<br><b>Price: </b>".$iprice[$a]."</font></td><td><font style=\"font-size:8pt;\"><b>Extra1: </b>".$iextra1[$a]."<br><b>Extra2: </b>".$iextra2[$a]."</font></td><td><b>Description:</b><br> ". $idesc[$a] ."</td><td><input type=button value='Delete Item' onclick=\"document.location='menuitemdelete.php?i=confirm&rid=".$rid."&id=$id[$a]'\"><br><input type=button value='Modify Item' onclick=\"document.location='menu_itemmod.php?id=$id[$a]'\"></td>";
    $content .= " </TR>\n";
}
$content .= "</TABLE>\n";

[troybtj]

You are missing a final closing brace at the end of the outer for loop.

Use {PHP} tags instead of {code} tags and it will syntax highlight your code.

Looks like it should work:

$sql = "SELECT * FROM sections where rid='$rid' ORDER BY sid";
$content .= $sql."<br>";
$modextra = "<hr><h1>Non-Sectioned</h1>";
$result = mysql_query($sql) or die(mysql_error());
$nrows = mysql_num_rows($result);
while($row = mysql_fetch_array($result)) {
    $secid[] = $row['id'];
    $sname[] = $row['name'];
}
for($j = 0; $j < $nrows; $j++) {  // ** This Brace was left open **
$content .= "<hr><h1>$sname[$j]</h1>";
$content .= "<TABLE BORDER=1 cellpadding=5 cellspacing=0 align=center width=100%>\n";
$sql = "SELECT * FROM menu where rid='$rid' AND sid='${$secid[$j]}' ORDER BY id";
$content .= $sql."<br>";
$result = mysql_query($sql) or die(mysql_error());
$rows = mysql_num_rows($result);
while($row = mysql_fetch_array($result)) {
    $id[] = $row['id'];
    $iname[] = $row['iname'];
    $idesc[] = $row['idesc'];
    $iprice[] = $row['iprice'];
    $iextra1[] = $row['extra1'];
    $iextra2[] = $row['extra2'];
    $isid[] = $row['sid'];

}
for($i = 0; $i < $rows; $i++) { 
    $content .= " <TR>\n";
    $a = $i;
            $content .= "<TD valign=top width=200><font style=\"font-size:8pt;\"><b>ID:</b> ". $id[$a] ."<br><b><a name=\"". $id[$a] ."\">Name:</a></b> ". $iname[$a]."<br><b>Price: </b>".$iprice[$a]."</font></td><td><font style=\"font-size:8pt;\"><b>Extra1: </b>".$iextra1[$a]."<br><b>Extra2: </b>".$iextra2[$a]."</font></td><td><b>Description:</b><br> ". $idesc[$a] ."</td><td><input type=button value='Delete Item' onclick=\"document.location='menuitemdelete.php?i=confirm&rid=".$rid."&id=$id[$a]'\"><br><input type=button value='Modify Item' onclick=\"document.location='menu_itemmod.php?id=$id[$a]'\"></td>";
    $content .= " </TR>\n";
}
$content .= "</TABLE>\n";

}//Added this brace

[DerrickE]

I had the final bracket in there, I forgot to copy it though.

When I add in "echo $sql", it displays the correct sql statement to pull the items for the right section, but when it is displaying the items, it keeps showing section "1"'s items over and over for the rest of the sections.

[troybtj]

If you change:

$isid[] = $row['sid'];

to

echo $row['sid']';

Just to make sure the query is correct.

[DerrickE]

Ya in my code I have:

$content .= $sql."<br>";

It echoes the right sql statement

[ahundiak]

$sql = "SELECT * FROM menu where rid='$rid' AND sid='${$secid[$j]}' ORDER BY id";

You have an extra $ in your sid= assignment. And does menu really have a rid column?

Your practice of copying all the results into a set of arrays adds unnecessary complications. Just use $row['id'] and be done with it.

as a debugging tool, use print_r($row) to verify your query is actually returning the expected results.

Finally, you really don't need to do all those multiple queries. A single query in which the menu table is joined to the section table will do the trick and simplify things considerably.

[DerrickE]

<?php

sql = "SELECT * FROM sections where rid='$rid' ORDER BY sid";

$content .= $sql."<br>";

$modextra = "<hr><h1>Non-Sectioned</h1>";

$result = mysql_query($sql) or die(mysql_error());

$nrows = mysql_num_rows($result);

while($row = mysql_fetch_array($result)) {



    $content .= '<hr><h1>' . $row['name'] . '</h1>

                <TABLE BORDER=1 cellpadding=5 cellspacing=0 align=center width=100%>' . "\n";



    $sql = "SELECT * FROM menu where rid='$rid' AND sid='{$row['id']}' ORDER BY id";



    $content .= $sql . '<br />';



    $result2 = mysql_query($sql) or die(mysql_error());



        while ($row2 = mysql_fetch_assoc($result2))



            $content .= '<tr>

                            <td valign="top" width="200" style="font-size: 8pt"><strong>ID : </strong>' . $row2['id'] . '<br />

                                                                                <strong><a name="' . $row2['id'] . '">Name : </a></strong>' . $row2['iname'] . '<br />

                                                                                <strong>Price : </strong> ' . $row2['iprice'] . '

                            </td><td style="font-size: 8pt">                    <strong>Extra1 : </strong> ' . $row2['extra1'] . '<br />

                                                                                <strong>Extra2 : </strong> ' . $row2['extra2'] . '

                            </td><td style="font-size: 8pt">                    <strong>Description : </strong> ' . $row2['idesc'] . '

                            </td><td style="font-size: 8pt">                    <input type="button" value="Delete Item" onclick="document.location=\'menuitemdelete.php?i=confirm&amp;rid=' . $rid . '&amp;id=' . $row2['id'] . '" /><br />

                                                                                <input type="button" value="Modify Item" onclick="document.location=\'menu_itemmod.php?id=' . $row2['id'] . '" />

                            </td>

                        </tr>';





}



$content .= '</table>';

?>

Newer code without 4 loops and it works.