CODEWITHSUNDEEP
phpdatabasepostgresql
user2968617
user2968617

Reputation: 157

Returning dropdown from results in database

I am using the below code to retrieve data from a database:

  $conn = pg_connect("host=********* port=5432
  dbname=******* user=******* password=*********");
  $result = pg_query ($conn, "SELECT Column1, Column2, Column3, Price, Column4 FROM phones ORDER BY Price");
  echo "<table border='1'>";
  echo "<tr>  
            <th></th>
            <th>Column1</th>
            <th>Column2</th>
            <th>Column3</th>
            <th>Price</th>
            <th>Column4</th> </tr>";
  while ($a = pg_fetch_row($result)) {
    echo "<tr>";
    echo "<td> <form> <input type='checkbox'> </form> </td>";
    for ($j = 0; $j < pg_num_fields($result); $j++) {
      echo "<td>" . $a[$j] . "</td>";
    }
    echo "</tr>\n";
  }
echo "</table>\n";

I also have a basic form on the same page:

<form name="form2"action="" method="GET">
<select name="highlow">
    <option value="All">All</option>
    <option value="higher">higher</option>
    <option value="less">less</option>
</select>
Price:<input type="text" name="price"/>
<input type="submit" name="submit" value="Submit" />
</form>

When all is selected from the dropdown menu, I would like all results to be shown, higher= all results with price higher than that specified in text field of form and same for lower but lower than price specified.

I'm having a bit of trouble of how to implement this, is there anyway to use an IF Statement with SQL Queries to achieve it or another way, I am very new to PHP and SQL.

Any help would be great thanks.

Upvotes: 1

Views: 94

Answers (3)

Ribson
Ribson

Reputation: 15

I also agree with the joshua and Himanshu Sharma but you can also write the logic in php inside the loop.But frankly it is not efficient to place if(condition) inside the loop.For an alternative here is the code

while ($a = pg_fetch_row($result)) {
echo "<tr>";
echo "<td> <form> <input type='checkbox'> </form> </td>";
for ($j = 0; $j < pg_num_fields($result); $j++) {
if($j==4)
{if($_GET['highlow'] === 'higher')
  if($_GET[price]<=$a[$j])
  echo "<td>" . $a[$j] . "</td>";
  else
  if($_GET[price]>=$a[$j])
  echo "<td>" . $a[$j] . "</td>";
}}}

Upvotes: 1

Himanshu Sharma
Himanshu Sharma

Reputation: 254

Totally agree with Joshua, you shuold use prepared statements to prevent SQL-injections and One more suggestion (code improvement), if you move following line before while loop

$numFields = pg_num_fields($result);

Because number of fields are always same for your result set and in your existing code you are fetching each and every time of while and For loop.

may help you.

Upvotes: 1

Joshua
Joshua

Reputation: 2982

Try something like this:

$query = 'SELECT Column1, Column2, Column3, Price, Column4 FROM phones ';
if($_GET['submit']) { // name attribute of your submit button
    // we'll get here only if the submit button is used
    if($_GET['highlow'] === 'higher') {
        $query .= 'WHERE Price > ' . ((int) $_GET['price']);
    } else if($_GET['highlow'] === 'less') {
        $query .= 'WHERE Price < ' . ((int) $_GET['price']);
    }
}

$query .= ' ORDER BY Price';

$result = pg_query ($conn, $query);

Of course you should use prepared statements for protection from SQL-Injection.

Disclaimer: This is a swift made draft, shall not win a beauty contest etc etc ;)

Upvotes: 1

Related Questions