[PHP-DB] Searching results of results
Hi there everyone, Say I do a simple search as follows: $query = SELECT * FROM search WHERE (description LIKE '%$test%' OR state LIKE '%$test%' OR city LIKE '%$test%' OR fname LIKE '%$test%') AND (category = '$category' AND country = '$country' AND type = '$type') ORDER BY city ASC LIMIT $offset, $item_perpage; and this brings up 1000 results, how can I then do another search based on THESE search results only? That is, it only searches the results it currently has and doesn't search the DB for other entries at the same time? Thanks for your help Chris
Re: [PHP-DB] Searching results of results
You could select these results into a temporary table and then query that table. Look at the mysql.com docs and check out the CREATE TEMPORARY TABLE. You can select data right into the table and the table is erased when the connection closes. But first it looks like cleaning up your query would probably be the best way to get what you want. try something like $query = SELECT * FROM search WHERE ; if ($description) { $query .= description LIKE '$description%' AND ; } if ($state) { $query .= state = '$state' AND ; } if ($city) { $query .= city = '$city' AND ; } if ($fname) { $query .= fname = '$fname' AND ; } $query .= (category = '$category' AND country = '$country' AND type = '$type') ORDER BY city ASC LIMIT $offset, $item_perpage; have fun, olinux --- Chris Payne [EMAIL PROTECTED] wrote: Hi there everyone, Say I do a simple search as follows: $query = SELECT * FROM search WHERE (description LIKE '%$test%' OR state LIKE '%$test%' OR city LIKE '%$test%' OR fname LIKE '%$test%') AND (category = '$category' AND country = '$country' AND type = '$type') ORDER BY city ASC LIMIT $offset, $item_perpage; and this brings up 1000 results, how can I then do another search based on THESE search results only? That is, it only searches the results it currently has and doesn't search the DB for other entries at the same time? Thanks for your help Chris __ Do You Yahoo!? Yahoo! Sports - live college hoops coverage http://sports.yahoo.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Searching results of results
Hi there, Thanks for your help on this it's appreciated, i'll look into temporary tables tomorrow and see what I can do. Just curious, why is the method you have shown below better than the method I used previously? Is it a speed thing or is it more logical? I am learning different methods of doing things so I would really appreciate your input :-) Thanks for everything. Chris You could select these results into a temporary table and then query that table. Look at the mysql.com docs and check out the CREATE TEMPORARY TABLE. You can select data right into the table and the table is erased when the connection closes. But first it looks like cleaning up your query would probably be the best way to get what you want. try something like $query = SELECT * FROM search WHERE ; if ($description) { $query .= description LIKE '$description%' AND ; } if ($state) { $query .= state = '$state' AND ; } if ($city) { $query .= city = '$city' AND ; } if ($fname) { $query .= fname = '$fname' AND ; } $query .= (category = '$category' AND country = '$country' AND type = '$type') ORDER BY city ASC LIMIT $offset, $item_perpage; have fun, olinux --- Chris Payne [EMAIL PROTECTED] wrote: Hi there everyone, Say I do a simple search as follows: $query = SELECT * FROM search WHERE (description LIKE '%$test%' OR state LIKE '%$test%' OR city LIKE '%$test%' OR fname LIKE '%$test%') AND (category = '$category' AND country = '$country' AND type = '$type') ORDER BY city ASC LIMIT $offset, $item_perpage; and this brings up 1000 results, how can I then do another search based on THESE search results only? That is, it only searches the results it currently has and doesn't search the DB for other entries at the same time? Thanks for your help Chris __ Do You Yahoo!? Yahoo! Sports - live college hoops coverage http://sports.yahoo.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Searching results of results
I use a method I'd like to share: For building queries with many conditions you first create an array like so $select = SELECT *; if (isset($cond)) unset($cond); if ($desc) $cond[] = desc LIKE '$desc%'; if ($state) $cond[] = state LIKE '$state%'; if ($city) $cond[] = city LIKE '$city%'; if (isset($cond)) $cond = WHERE .implode( AND ,$cond); $order= ORDER BY city; $limit= LIMIT $offset, $item_perpage; $query = $select.$cond.$order.$limit; Regards, Remco - Original Message - From: Chris Payne [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, March 20, 2002 6:58 AM Subject: Re: [PHP-DB] Searching results of results Hi there, Thanks for your help on this it's appreciated, i'll look into temporary tables tomorrow and see what I can do. Just curious, why is the method you have shown below better than the method I used previously? Is it a speed thing or is it more logical? I am learning different methods of doing things so I would really appreciate your input :-) Thanks for everything. Chris You could select these results into a temporary table and then query that table. Look at the mysql.com docs and check out the CREATE TEMPORARY TABLE. You can select data right into the table and the table is erased when the connection closes. But first it looks like cleaning up your query would probably be the best way to get what you want. try something like $query = SELECT * FROM search WHERE ; if ($description) { $query .= description LIKE '$description%' AND ; } if ($state) { $query .= state = '$state' AND ; } if ($city) { $query .= city = '$city' AND ; } if ($fname) { $query .= fname = '$fname' AND ; } $query .= (category = '$category' AND country = '$country' AND type = '$type') ORDER BY city ASC LIMIT $offset, $item_perpage; have fun, olinux --- Chris Payne [EMAIL PROTECTED] wrote: Hi there everyone, Say I do a simple search as follows: $query = SELECT * FROM search WHERE (description LIKE '%$test%' OR state LIKE '%$test%' OR city LIKE '%$test%' OR fname LIKE '%$test%') AND (category = '$category' AND country = '$country' AND type = '$type') ORDER BY city ASC LIMIT $offset, $item_perpage; and this brings up 1000 results, how can I then do another search based on THESE search results only? That is, it only searches the results it currently has and doesn't search the DB for other entries at the same time? Thanks for your help Chris __ Do You Yahoo!? Yahoo! Sports - live college hoops coverage http://sports.yahoo.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php