Filtering my postgreSQL table using drop down box

Question

The Problem

I am having trouble filtering my table, no errors are coming up so I am unsure why it isn't working. I am wondering whether it is to do with the placement of SQL code or whether the submit button isn't coded correctly... Or even if the ive programmed it all wrong haha.

Expected Outcome

What I expect to see when press submit is for the table to change to what ever it was that submitted. For exmple, in the drop down box there is an option for 'Canned' food, if I was to press this I would expect to see the table change, showing only canned food.

What the actual outcome is

Nothing seems to be happening, and with no errors, i'm completely unsure as to why.

Here is the code for the drop down box

I am unsure whether this is the correct form for what I am trying to do. I want to stay on the same page, I only want to affect the table.

<form action="database.php">
<select name="category" id="category">
    <option value="Alcoholic">Alcohol</option>
    <option value="Canned">Canned Food</option>
    <option value="Dairy">Dairy</option>
    <option value="Dessert">Dessert</option>
    <option value="Frozen">Frozen Food</option>
    <option value="Fruit">Fruit</option>
    <option value="Junk Food">Junk Food</option>  
</select>
<input  type="submit" name="submit" value="Search"/>
</form>

Here is the code for the table and the SQL commands

<?php
    $conn = pg_connect("host=db.dcs.aber.ac.uk port=5432
                                        dbname=teaching user=csguest password=********");
// Empty var that will be populated if the form is submitted
    $where = '';

    if (isset($_POST['submit'])) {

         if (!empty($_POST['category'])) {
             // Where conditional that will be used in the SQL query
             $where = " WHERE Category = '".pg_escape_string($_POST['category'])."'";
    }

}

$res = pg_query($conn, "SELECT Foodtype, Manufacturer, Description, Price 
                                    FROM food " . $where . " ORDER BY    Category ASC");
echo "<table id=\"myTable\" border='1'>";
while ($a = pg_fetch_row($res)) {
    echo "<tr>";
    for ($j = 0; $j < pg_num_fields($res); $j++) {
        echo "<td>" . $a[$j] . "</td>";
    }

    echo "<td><form id='cart' name='cart' method='POST' action='addToBasket.php'>
                                        <input type='submit' name='Select' id='Select' value='Add To Basket'>
                                        </form></td>";
    echo "</tr>\n";
}

echo "</table>\n";
$Alcoholic = pg_query("SELECT Foodtype, Manufacturer, 
                                    Description, Price FROM food WHERE Category = 'Alcoholic'");
$Canned = pg_query("SELECT Foodtype, Manufacturer, 
                                    Description, Price FROM food WHERE Category = 'Canned'");
?>

The last two SQL statements above are supposed to filter the table, there will one for each of the drop down options

My guess is that you have a camel case problem. Does the category field on your table `food` is stored as it is on that select combo? I mean option `Alcoholic` actually is `Alcoholic` in database or `alcoholic` ? If it isn't you just need to add a lower function on your query like `Category = lower('".pg_escape_string($_POST['category'])."')"` — Jorge Campos, Nov 24 '15 at 18:59
Ok, then there are two problem, first your form does not specify a method which means it is a GET [Default form method](http://stackoverflow.com/questions/2314401/what-is-the-default-form-http-method) and in your code you are trying to read a POST parameter: `$_POST['submit']` either change it to GET or add in your form to `method="GET"` the second problem is about your ifs I see that if there is nothing sent it should show everything and if it is not, means that something wrong happened which leads me to think that your server is not set to show all — Jorge Campos, Nov 24 '15 at 19:07
I have had plenty of errors before this, i think the problem is with the form. How would i specify a method? — RushFan2112, Nov 24 '15 at 19:11
Also what is your files names (or name)? I see that your form is pointing to the database.php is the code you show on this file? — Jorge Campos, Nov 24 '15 at 19:14

score 1 · Accepted Answer · answered Nov 24 '15 at 19:16

Since the discussion on the comments lead to the solution, I'm adding it here:

Your problem is that you defined a form without a method which means that it will be by default GET and in your code you are trying to get POST variables so change your form to:

<form action="database.php" method="post">

Filtering my postgreSQL table using drop down box

1 Answers1