Problem with dropdown menu populated from MySQL table

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
hawkontvn
Forum Newbie
Posts: 4
Joined: Tue May 11, 2010 12:46 pm

Problem with dropdown menu populated from MySQL table

Post by hawkontvn »

Hey :)
I'm quite new to php and mysql so dont judge me too harsh please :)

I have a project going on - a book library website. The website is a three-column website which is supposed to have general stuff in the middle, ads on the right side, and login/search stuff on the left side. I'm using "include('x.php')" on my index.php to include the left and right columns onto the main site. At the moment im lost in how to fix the search stuff i'll be having on the left column of the site.

I'm trying to create a dropdown menu which populates itself with my "category" table from my database. Book categories are supposed to come down when you access the menu, and when you press a submit button after choosing a category, all books ('book'-table in my db) that are within that category are supposed to show up in my middle column of my index.php site. Any help would be appreciated. I'll post some of the code that I messed around with, but I doubt its any close to what would be best doing. Thanks in advance! :)

This is part of my 'left_col.php' which is included in index.php:

Code: Select all

<h2>Quick search</h2>
                                <?php
                                $categories = mysql_query("SELECT categoryID AS ci,
                                    categoryName AS cn FROM category
                                    ORDER BY categoryName");

                                while ($row = mysql_fetch_array($categories))
                                {
                                    $id = $row['ci'];
                                    $cat = $row['cn'];
                                    $options.="<option value='$id'>".$cat."</option>";
                                }
                                ?>

                                <form method="get" action="/biblio/">
                                    <select name="showcat">
                                        <option value="">
                                            Choose category
                                            <?=$options?>
                                        </option>
                                    </select>
                                    <input type="submit" value="Search" />
                                </form>

                                <?
                                echo $_GET['showcat'];
                                ?>
And here is my index.php which probably is really wrong, since I can't get this thing working :p

Code: Select all

<?php
                                $list_cats = mysql_query("SELECT * FROM category
                                    ORDER BY categoryName;");

                                while($row = mysql_fetch_array($list_cats))
                                {
                                    $cid = $row['categoryID'];
                                    $cname = $row['categoryName'];
                                }

                                if($_GET['showcat'])
                                {
                                    $cid = $_GET['showcat'];
                                    $cname = $_GET['showcat'];

                                    echo "<br /><p><b>Categories in our database:</b></p>";

                                    $query = "SELECT * FROM category
                                    WHERE categoryID = $cid";

                                    $res = mysql_query($query) or die(mysql_error());

                                    while($row = mysql_fetch_array($res))
                                    {
                                        $cid = $row['categoryID'];
                                        $cname = $row['categoryName'];

                                        echo "????";
                                    }
                                }

                                ?>
Thanks for any help :)
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Problem with dropdown menu populated from MySQL table

Post by Benjamin »

In this code, you are overwriting data from the previous row with data from the current row. Also, mysql_fetch_assoc is better to use.

Code: Select all

                                while($row = mysql_fetch_array($list_cats))
                                {
                                    $cid = $row['categoryID'];
                                    $cname = $row['categoryName'];
                                }
Use an array instead:

Code: Select all

$records = array();

while ($row = mysql_fetch_assoc($list_cats)) {
    $records[] = $row;
}
If $_GET['showcat'] what? If it's set? If it's true? If it's 0000? It's good practice to be explicit and verbose.

Code: Select all

if($_GET['showcat'])
Should be more like:

Code: Select all

if (isset($_GET['showcat']) && preg_match('#^\d+$#', $_GET['showcat'])) {
Did you know user input is a very large security risk?

Code: Select all

                                    $query = "SELECT * FROM category
                                    WHERE categoryID = $cid";
Should be:

Code: Select all

$query = "SELECT * FROM category WHERE categoryID = '" . mysql_real_escape_string($cid) . "'"
So next you want to display the categories right?

Code: Select all

                                    while($row = mysql_fetch_array($res))
                                    {
                                        $cid = $row['categoryID'];
                                        $cname = $row['categoryName'];

                                        echo "????";
                                    }
Should be something like:

Code: Select all

while ($row = mysql_fetch_assoc($res)) {
    echo "<a href=\"viewCategory?category={$row['categoryID']}\">{$row['categoryName']}</a>";
}
Hope that helps. :drunk:
Post Reply