PHP/MySQL Problem

Question

Why does this only print the sites specific content under the first site, and doesn't do it for the other 2?

<?php
        echo 'NPSIN Data will be here soon!';

        // connect to DB
        $dbhost = 'localhost';
        $dbuser = 'root';
        $dbpass = 'root';

        $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to DB');

        $dbname = 'npsin';

        mysql_select_db($dbname);

        // get number of sites
        $query = 'select count(*) from sites';
        $result = mysql_query($query) or die ('Query failed: ' . mysql_error());

        $resultArray = mysql_fetch_array($result);
        $numSites = $resultArray[0];

        echo "<br><br>";

        // get all sites
        $query = 'select site_name from sites';
        $result = mysql_query($query);

        // get site content
        $query2 = 'select content_name, site_id from content';
        $result2 = mysql_query($query2);

        // get site files


        // print info
        $count = 1;
        while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
                echo "Site $count: ";
                echo "$row[0]";
                echo "<br>";
                $contentCount = 1;
                while ($row2 = mysql_fetch_array($result2, MYSQL_NUM)) {
                        $id = $row2[1];
                        if ($id == ($count - 1)) {
                                echo "Content $contentCount: ";
                                echo "$row2[0]";
                                echo "<br>";
                        }

                        $contentCount++;
                }

                $count++;
        }
?>

Answer 1

The problem is that you assume that once your finished looking for the row with the same id as the site row, that it'll reset the $result2 query to the beginning. This means that after you find your first row (unless you were to sort the two queries), that the second pass of the while loop wouldn't have any results left. You should consider caching the inner while loop first and then using an array lookup to get the value.

An even better solution would involve a join from sites to content which wouldn't require this complex matching process. Joins are a VERY important part of SQL, I highly suggest learning how to use them. http://dev.mysql.com/doc/refman/5.0/en/join.html