RE: [PHP] Search problem
Hi, I need to build up a search module for a shop. If I make a basic search (product title for example) it is ok. $query = SELECT product_id FROM products WHERE title LIKE '%$title%'; But i need an advance search for more than one field (title, description, price, weight) The problem is that i don't know which field is filled in by the user (title, description, price or weight) I mean, the user can fill in all fields, or only price field, or title and weight etc How can i do the search? Thanks $query = SELECT product_id FROM products WHERE title LIKE '%$title%' and description LIKE '%$description%' and price like '%$price%' and weight like '%weight%'; JM -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Search problem
Jim Moseby wrote: Hi, I need to build up a search module for a shop. If I make a basic search (product title for example) it is ok. $query = SELECT product_id FROM products WHERE title LIKE '%$title%'; But i need an advance search for more than one field (title, description, price, weight) The problem is that i don't know which field is filled in by the user (title, description, price or weight) I mean, the user can fill in all fields, or only price field, or title and weight etc How can i do the search? Thanks $query = SELECT product_id FROM products WHERE title LIKE '%$title%' and description LIKE '%$description%' and price like '%$price%' and weight like '%weight%'; JM While this query would work, using a fulltext index would give you a much more powerful search. Check to see if your database offers some sort of text indexing (it probably does!) Suppose you have a product title like The Lion, the Witch, and the Wardrobe. If your user did a search on lion witch wardrobe, you'd want my example to show up. Just comparing these fields with a LIKE will not give you my result, unless you explode the search string and create several LIKE statements based the individual terms, but then you have to do three LIKE comparisons, which will probably be slower than a full text index. If you are able to use a full text index, then you can write queries like this: $sql = 'SELECT * FROM products WHERE MATCH ( product_title ) AGAINST ( ' .$_REQUEST['product_title']. ' ) AND MATCH ( product_description ) AGAINST ( ' .$_REQUEST['product_description']. ' )'; which would give you The Lion, the Witch, and the Wardrobe if search terms were lion witch wardrobe. kgt
Re: [PHP] Search problem
On Sat, May 21, 2005 1:35 pm, [EMAIL PROTECTED] said: I need to build up a search module for a shop. If I make a basic search (product title for example) it is ok. $query = SELECT product_id FROM products WHERE title LIKE '%$title%'; But i need an advance search for more than one field (title, description, price, weight) The problem is that i don't know which field is filled in by the user (title, description, price or weight) I mean, the user can fill in all fields, or only price field, or title and weight etc You can use http://php.net/strlen to determine if the user has typed anything into any given INPUT. Or, if you want a simple search with only one INPUT, something like: $query = select product_id from products where 1 = 0 ; $words = explode(' ', $input); while (list(, $word) = each($words)){ $query .= or title like '%$word%' ; $query .= or description like '%$word%' ; . . . } -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Search problem
I used to search the same way until someone on this list mentioned using a fulltext index in mysql. Doing this almost eliminated all my headaches with searching. Easiest way to create a fulltext index is to use phpMyAdmin. Once you've created the fulltext index on `title`,`description`,`price`,`weight` you can then search all columns (much faster than the other way I might add) by using a simply SQL query such as this: $sql = SELECT * FROM `products` WHERE MATCH(`title`,`description`,`price`,`weight`) AGAINST (' . mysql_escape_string($_GET['query']) . '); More on FULLTEXT can be found here: http://dev.mysql.com/doc/mysql/en/fulltext-search.html Hope that helps! -Joe W. www.joewollard.com http://www.joewollard.com [EMAIL PROTECTED] wrote: Hi, I need to build up a search module for a shop. If I make a basic search (product title for example) it is ok. $query = SELECT product_id FROM products WHERE title LIKE '%$title%'; But i need an advance search for more than one field (title, description, price, weight) The problem is that i don't know which field is filled in by the user (title, description, price or weight) I mean, the user can fill in all fields, or only price field, or title and weight etc How can i do the search? Thanks
[PHP] Search problem
Hi, I need to build up a search module for a shop. If I make a basic search (product title for example) it is ok. $query = SELECT product_id FROM products WHERE title LIKE '%$title%'; But i need an advance search for more than one field (title, description, price, weight) The problem is that i don't know which field is filled in by the user (title, description, price or weight) I mean, the user can fill in all fields, or only price field, or title and weight etc How can i do the search? Thanks
Re: [PHP] Search problem
On 5/21/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, I need to build up a search module for a shop. If I make a basic search (product title for example) it is ok. $query = SELECT product_id FROM products WHERE title LIKE '%$title%'; But i need an advance search for more than one field (title, description, price, weight) The problem is that i don't know which field is filled in by the user (title, description, price or weight) Without error checking, or security code(ie supplied code contains SQL injection vulnerability): $sql = SELECT product_id FROM products WHERE ; if($_GET['title']){ $sql_ext[] = title like '%{$_GET['title']}%' ; } if($_GET['description']){ $sql_ext[] = description like '%{$_GET['description']}%' ; } $sql .= implode( OR , $sql_ext ); I mean, the user can fill in all fields, or only price field, or title and weight etc How can i do the search? Thanks -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php