Hi, > 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 One workaround is to test the values rigth there: SELECT id, first, last, email, phone, message, time FROM visitors WHERE id='$id' OR (($first <> '') and (first LIKE '$first%')) OR (($last <> '') and (last LIKE '$last%')) OR ... I haven't tested how fast this works, but it shouldn't slow down the query... Regards, Sasa »mysql, select, database« --------------------------------------------------------------------- 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
