Page 1 of 1

Help-looping through a database using php and sql

Posted: Sat Nov 26, 2011 7:46 pm
by Alexandria
I am trying to loop through all the tables in my database and select everything that has the same type. The following code works expect it only prints out the first entry it finds. For example if someone selects "chicken" to view recipes from it pulls out the first chicken recipe in that the database and thats it. I need it to loop through and get all recipes with type chicken. I have looked up different ways to approach this and none have worked. The for loop I tried using in this one is wrong as well so if anyone has a good approach for this please let me know! Also in my html code all the category's have the same name of "cate" but different values. If that helps.

Code: Select all

     <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html>
    <head>
    </head>
    <body>

        <?php
        $db_server = "xxxxxxxxx:3306";
        $db_username = "xxxxx";
        $db_password = "xxxxxxx";

        $con = mysql_connect($db_server, $db_username, $db_password);
        if (!$con) {
            die('Could not connect: ' . mysql_error());
        }

        $database = "Recipes";

        $er = mysql_select_db($db_username);
        if (!$er) {
            print ("Error - Could not select the database");
            exit;
        }
        //take info from search and looks through the database for recipe
        $cate = $_POST['cate'];
        $query = mysql_query("SELECT * FROM Recipes WHERE type ='$cate'");
        $row = mysql_fetch_array($query);
  	while($row = mysql_fetch_array($query)){
      
        ?>


        <!-- Prints out the recipe -->

        <table border ="1">
            <tr> <h1>
                    <?php
                    
                    print $row['title'];
                    ?>
                </h1>
            </tr>
            <tr>
                <td>
                    <p> Ingredients: </p>
                    <?php
                    print $row['num_of_1'] . " ";
                    print $row['measure_1'] . " ";
                    print $row['ingredient_1'] . " ";
                    print ("<br />");
                    print $row['num_of_2'] . " ";
                    print $row['measure_2'] . " ";
                    print $row['ingredient_2'] . " ";
                    print ("<br />");
                    print $row['num_of_3'] . " ";
                    print $row['measure_3'] . " ";
                    print $row['ingredient_3'] . " ";
                    print ("<br />");
                    print $row['num_of_4'] . " ";
                    print $row['measure_4'] . " ";
                    print $row['ingredient_4'] . " ";
                    print ("<br />");
                    print $row['num_of_5'] . " ";
                    print $row['measure_5'] . " ";
                    print $row['ingredient_5'] . " ";
                    print ("<br />");
                    print $row['num_of_6'] . " ";
                    print $row['measure_6'] . " ";
                    print $row['ingredient_6'] . " ";
                    print ("<br />");
                    print $row['num_of_7'] . " ";
                    print $row['measure_7'] . " ";
                    print $row['ingredient_7'] . " ";
                    print ("<br />");
                    print $row['num_of_8'] . " ";
                    print $row['measure_8'] . " ";
                    print $row['ingredient_8'] . " ";
                    print ("<br />");
                    print $row['num_of_9'] . " ";
                    print $row['measure_9'] . " ";
                    print $row['ingredient_9'] . " ";
                    print ("<br />");
                    print $row['num_of_10'] . " ";
                    print $row['measure_10'] . " ";
                    print $row['ingredient_10'] . " ";
                    print ("<br />");
                    ?>
                </td>           
                <td>
                    <p> Instructions: </p>
                    <?php
                    print $row['instructions'];
                    print ("<br />");
                    ?>
                </td>
            </tr>
}
        </table>
        <br />

        <p> 
            Like this Recipe? Rate It!
            <input type = "submit" value = "Good Ramen" />
        </p>
        <br />
        <br />

        <p>Add a Comment: </p>
        <textarea name = "comment" rows = "10" cols = "100" > </textarea>
        <br />
        <input type = "submit" value = "Add Comment" />
        <br />
        <p> Comments: </p>
        <?php
        $comment_query = ("SELECT *");
        while ($comment_query) {
            date_default_timezone_get('UPC');
            print ($row['username'] . " ");
            print ($row['date_added']);
            print ($row['comment']);
            print ("<br />");
        }}
        
        ?>

    </body>
</html>

Re: Help-looping through a database using php and sql

Posted: Sat Nov 26, 2011 8:32 pm
by califdon
I have edited your original posting to remove the database connection credentials (very dangerous!) and enclose your code in [ syntax ] tags to make it more readable.

You have serious misconceptions about HTML, PHP and MySQL. You would virtually never "loop" through a database in PHP code; that's what SQL queries are for. In any case, there is no for loop in the code you showed. I don't understand your statement:
Also in my html code all the category's have the same name of "cate" but different values.
Before I removed it, the URL of the database server indicated that it was a school, so I assume that this is a class assignment. I won't attempt to replace your instructor here, but you need to understand basic principles of HTML, PHP and MySQL before you can completely re-purpose a script like this to do something it wasn't written to do. Here are a few suggestions for reading:
http://www.w3schools.com/sql/default.asp
http://www.sqlcourse.com/intro.html
http://php.net/manual/en/tutorial.php
http://www.anyexample.com/programming/p ... s_html.xml
http://www.phpeveryday.com/articles/PHP ... -P281.html
http://www.w3schools.com/php/php_mysql_select.asp

What you will be looking for is to understand how to return the exact data you need, using SQL. In your case, it will be all rows in a table (NOT "all the tables in my database") that have a particular category or type. Then how you display those rows in your web page, using PHP to dynamically create the HTML that displays an HTML table.