Page 1 of 1

List all records by default and filter if querystring

Posted: Tue Sep 18, 2007 12:03 am
by buddyq
Hi everyone,

I am using Dreamweaver and PHP and I just have a page that lists all products. I have a dropdown box that I can get to filter records based on an ID passed through querystring. I can't get the SQL right to default to list all records if there is no ID passed. I can make it one way or the other but can't seem to get both. Can someone help me with my SQL if thats the problem?

Here is my SQL portion:

Code: Select all

$colname_rsProduct = "0";
if (isset($_GET['cat'])) {
  $colname_rsProduct = $_GET['cat'];
}
mysql_select_db($database_connWPA, $connWPA);
$query_rsProduct = sprintf("SELECT inventory.id, inventory.title, inventory.description, inventory.price, inventory.category AS 'cat', inventory.image1, inventory.image2, inventory.image3, categories.category FROM inventory INNER JOIN categories ON inventory.category = categories.id WHERE inventory.category= %s ORDER BY price ASC", GetSQLValueString($colname_rsProduct, "int"));
$rsProduct = mysql_query($query_rsProduct, $connWPA) or die(mysql_error());
$row_rsProduct = mysql_fetch_assoc($rsProduct);
$totalRows_rsProduct = mysql_num_rows($rsProduct);
Thanks for any help and let me know if you need anything else.

Buddy

Posted: Tue Sep 18, 2007 1:31 am
by GeertDD
I guess if there's no cat passed through the querystring, you should drop the SQL WHERE clause completely.

Posted: Tue Sep 18, 2007 1:37 am
by buddyq
I was thinking of doing that...but how do I do that?

if $_GET['cat'] = append where clause

else No where clause...how do I do that with a sql statement?

UPDATE***

I got it to work! It's working like a charm now. I had put this:

Code: Select all

$query_rsProduct = "SELECT inventory.id, inventory.title, inventory.description, inventory.price, inventory.category AS 'cat', inventory.image1, inventory.image2, inventory.image3, categories.category FROM inventory INNER JOIN categories ON inventory.category = categories.id ";
if (isset($_GET['cat'])) {
  $colname_rsProduct = $_GET['cat'];
  $query_rsProduct .= "WHERE inventory.category = $colname_rsProduct ORDER BY inventory.price ASC";
}else{
  $query_rsProduct .="ORDER BY inventory.price ASC";
}
$query_rsProduct = sprintf($query_rsProduct);

thanks!

Buddy

Re: List all records by default and filter if querystring

Posted: Tue Sep 18, 2007 1:59 am
by ryos
You might be able to do it using straight SQL, but it would probably be ugly. How about something like this?

Code: Select all

$colname_rsProduct = "0";
$where_clause = '';

if (isset($_GET['cat'])) {
  $colname_rsProduct = $_GET['cat'];
  $where_clause = ' WHERE inventory.category = '. GetSQLValueString($colname_rsProduct, "int");
}
mysql_select_db($database_connWPA, $connWPA);
$query_rsProduct = sprintf("SELECT inventory.id, inventory.title, inventory.description, inventory.price, inventory.category AS 'cat', inventory.image1, inventory.image2, inventory.image3, categories.category FROM inventory INNER JOIN categories ON inventory.category = categories.id%s ORDER BY price ASC", $where_clause);
$rsProduct = mysql_query($query_rsProduct, $connWPA) or die(mysql_error());
$row_rsProduct = mysql_fetch_assoc($rsProduct);
$totalRows_rsProduct = mysql_num_rows($rsProduct);
There are a couple of things to note here:
1) $where_clause starts life as an empty string and is only given a value if your condition is satisfied. We can therefore just concatenate it into your query string without worrying about its value; if there was no cat passed, it won't change the default query in any way. This is a handy way to conditionally modify output strings, especially if there are multiple conditions to satisfy.
2) Note the space at the beginning of $where_clause. You could move that into the SQL query string (before the %s) if you prefer; you'll just have an extra space in your default query that will get ignored.
3) I assume 'GetSQLValueString()' processes inputs to protect against SQL injection; if not, a simple cast to int works wonders on integer-valued parameters (all strings go to zero).

Good luck!