Help-looping through a database using php and sql

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Alexandria
Forum Newbie
Posts: 1
Joined: Sat Nov 26, 2011 7:40 pm

Help-looping through a database using php and sql

Post 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>
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

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

Post 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.
Post Reply