CODEWITHSUNDEEP
phppostgresql
RushFan2112
RushFan2112

Reputation: 325

Filtering my postgreSQL table using drop down box

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

Upvotes: 0

Views: 43

Answers (1)

Jorge Campos
Jorge Campos

Reputation: 23361

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">

Upvotes: 1

Related Questions