I have a similar query in a PHP script. I want to allow users to use fields in a page to search for the records. BUT, I want them to be able to enter the first few characters. I would think my query, below, would do it (the entire search works when the WHERE statement has first='$first' OR last='$last' OR....). BUT, when I search using any field (or simply click my submit button) it returns ALL records in the database. Where is my SQL flawed?
SELECT id, first, last, email, phone, message, time FROM visitors WHERE id='$id' OR (first LIKE '$first%') OR (last LIKE '$last%') OR (email LIKE '$email%') OR (phone LIKE '$phone%') OR (message LIKE '$message%') OR (time LIKE '$time%') ORDER BY id DESC; Mike ----- Original Message ----- From: "Roger Baklund" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: "Aman Raheja" <[EMAIL PROTECTED]> Sent: Tuesday, May 14, 2002 2:47 PM Subject: RE: query for search on mysql database > * Aman Raheja > > I am making a search program. I am looking for a query. > > I want to allow the user to search "Any" or "All". > > Anyhow, suppose the database contains the following > > > > Author > > -------------------- > > | Rim Torus | > > | Jerry Pal Deprado | > > | Roan P. Classy | > > ------------------- > > > > If the user searches for Rim Torus, I am though. > > But how to allow results if the search is "Jerry Classy", saying > > Any should match and have both second and third rows returned? > > You should split the user input in your script, and then check for each of > the words. Your resulting query could be something like this: > > SELECT * FROM Author WHERE > (name LIKE '%Jerry%') OR > (name LIKE '%Classy%') > > If your "Match All" search is supposed to find row 3 when searching for > "Roan Classy", you can use the same mechanism, except you use AND instead of > OR. > > Another 'trick of the trade' is to remove commas and other special > characters, so that even a search for "Classy, Roan" would find the third > row. > > Beware that these methods (LIKE '%...) makes the use of indexes impossible, > and your query may get slow if the table is big (like 100000+ rows). > > -- > Roger > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
