I want an OR test. If someone searches on last name and enters nothing in the other fields, I want to find the record(s). Similarly, if they enter a first name and no other data, I want to find the record(s).
The part I realize I am missing is to first test to see which fields have been filled in. Need some pointers on how to start that. Thanks! Mike ----- Original Message ----- From: "Roger Baklund" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: "MikeParton" <[EMAIL PROTECTED]> Sent: Tuesday, May 14, 2002 3:49 PM Subject: RE: query for search on mysql database > * 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
