Filtering my postgreSQL table

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.

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!

In $res = pg_query statementline ...FROM food $where ORDER BY... ->>> ...FROM food " . $where . " ORDER BY... — Christian
– Christian, Commented Nov 24, 2015 at 16:28
Also: Your put your input element for submit into the select. First end the select, then the sumbit. — Erwin Moller
– Erwin Moller, Commented Nov 24, 2015 at 16:30
Please post the code for your <form> tag (the one that surrounds the <select>). — Ruslan
– Ruslan, Commented Nov 24, 2015 at 16:46
@Ruslan I currently have <form action="database.php"> ... ... </form> Where database.php is the page I am already on — RushFan2112
– RushFan2112, Commented Nov 24, 2015 at 18:14
@RushFan2112 I think that is your problem. The default method for HTML forms is GET, not POST, and that would be why you are not seeing the variables in PHP's $_POST variable. Add method='POST' to your HTML form tag and try again. — Ruslan
– Ruslan, Commented Nov 24, 2015 at 19:24

Christian · Accepted Answer · 2015-11-24 16:32:03Z

1

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

answered Nov 24, 2015 at 16:32

Christian

7,3509 gold badges58 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RushFan2112 Over a year ago

I have changed this, and also changed the submit to after the select has ended, yet, nothing has changed, nor has any 'helpful' errors came up.

Collectives™ on Stack Overflow

Filtering my postgreSQL table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related