Page 1 of 1

Problem with dropdown menu populated from MySQL table

Posted: Thu May 13, 2010 1:33 pm
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 :)

Re: Problem with dropdown menu populated from MySQL table

Posted: Wed May 19, 2010 6:12 am
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: