complex query

winey

I've built several web sites where the user could search the MySQL database, but never one as complex as this, and I could use a little insight from someone who's been there.

This is a searchable eyewear catalog that I'm adding to an existing website. I have a search form containing multiple criteria that the user can choose to search with. For example, the user can search by brand, gender, temple size, bridge size, etc., or ANY combination.

I'm using PHP/MySQL and having a problem writing the query to search the proper columns in the table based on which fields the user filled in on the search form. Would I just go with a lot of nested conditionals? It seems that there must be a better way, but I can't think what it is. I've got a ton of MySQL/PHP books, but none of them look at this problem (you would think a catalog search using multiple optional criteria would be a common need).

Any help or pointers to tutorials would be appreciated.

PHPdev

How I have acomplished this in the past, is something like this.. .

Assume the form has been posted...


$sql = "SELECT c.id, c.name";
$from = "FROM catalog c";
$where = "WHERE ";

// assumes brand is a dropdown <select>
if ((isset($_POST["brand"])) and ($_POST["brand"] != 0)) {
       $sql .= ", b.brand_name";
       $from .= ", brand b";
       $where .= " b.id = " . $_POST["brand"]
}

Basically you are checking to see what Form Elements are filled out, and dynamicly adding to your select statement for each.

PHPdev

winey

Thanks. That worked great with a little mod.

best,

Leland

winey

The fix for the complex query worked great, but here's the problem I'm having now--my client wants users to be able to search with basically no criteria and get every glasses frame in the database. This results in a long list of results that blows out the table cell--does anyone know of a way to keep the table cell from blowing out in width when the results are this long? Or a way to break the results into two rows?

Here's the address: http://www.studioadept.net/remeyewear/search.html

just leave all the 'quicksearch' pulldown menus as they are and press the first submit button and you'll see what I mean.

note: I'm adding this searchable catalog to an existing site, the none of the graphics are mine.

thanks,
Leland

PHPdev

You are probably looking for the same solutoin any catalog uses. Break the inventory into pages.

Sorry if this is what you are already doing, but when I tried the link, I got an error...

If you are having problems with the pages, (your queries on the error page look ok), let me know

PHPdev

winey

The paging is working, but that's the problem also. There's so much in the database, that the numbered page list at the bottom blows out the cell it's in so that the right side of the page extends off the screen. Setting the width of the cell or the table doesn't affect it in any way.

Perhaps what I need is the kind of pagination where the only a certain number of page result links appear at the bottom, say 10--but I don't know how to do that. Perhaps I'll play around with it to see if I can make it happen.

Here's the link again. Just make sure you click on the FIRST 'go' button.

http://www.studioadept.net/remeyewear/search.html

PHPdev

Ok... so I clicked the second go button... the first one works. I see what you mean about the massive number of pages throwing off the formating. Look at how VBulletin (the makers of this forum) They show like 5 pages, and then links to more pahes. I am not posative their formating is the most user friendly, but probably by changing the wording it may work for you