* MikeParton > 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;
If any of your $-variables are empty, the criteria will be ... LIKE '%', and this will match all rows, and return all rows, because you use OR. Change it to AND, and it should work as expected. If OR is what you want, you should only check the fields where the user actually have entered something... but you should probably use AND... If a user enters a first name and a single letter in the last field, he would probably expect to get persons with the entered first name and a last name starting with the provided letter, not all persons with that first name and all persons with a last name starting with the single letter. :) -- 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
