CODEWITHSUNDEEP
phppostgresql
RushFan2112
RushFan2112

Reputation: 325

Filtering my postgreSQL table

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.

The Code

Drop down box code

<p>Sort by Category</p>

   <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>  
   <input  type="submit" name="submit" value="Search"/>
   </select>

Table code:

<div id="tablebox">

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

The Question

Any chance any of you could quickly look over my code to see if there are any obvious mistakes, it'll be much appreciated!

Upvotes: 1

Views: 61

Answers (1)

Christian
Christian

Reputation: 7320

Change:

$res = pg_query($conn, "SELECT Foodtype, Manufacturer, Description, Price 
                                        FROM food $where
                                        ORDER BY Category ASC");

to

$res = pg_query($conn, "SELECT Foodtype, Manufacturer, Description, Price 
                                        FROM food " . 
                                        $where . 
                                        " ORDER BY Category ASC");

Upvotes: 1

Related Questions