I guess what might help is to explain that the form fields are on a page with method="POST" action=results.php The sql I provided originally is in results.php and I use the superglobal $_POST to pull the variables from the form field.
I want the query results to be based on the variables which, in turn, are based on the user input from the original page. I redid my query substituting AND for OR and it failed to return any records. ??? Thanks! Mike ----- Original Message ----- From: "Nick Stuart" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, May 14, 2002 4:51 PM Subject: Re: query for search on mysql database > I think that was what roger is saying. If you simply change the OR's to > AND's your searchwill > work as you want it to. For example some one looks for a person by the > last name of Smith.so all > the other fields will be blank and your statement would look like: > SELECT id, > first, last, > email, phone, message, time > FROM visitors > WHERE id='$id' AND ((first > LIKE '%') AND > (last LIKE 'Smith%') AND > (email LIKE '%') AND (phone LIKE '%') AND (message > LIKE '%') > AND > (time LIKE '%')) ORDER BY id DESC; > > This will return anything that has a > last name of smith. > The only thing I question now is the ID value. Will you let people use > partial ids or must they know the entire string/number. > Also if you want to use only the > entire ID you could simply > have a statement like: > SELECT id, first, last, email, phone, > message, time > FROM > visitors > WHERE id='$id' > as I'm guessing your id is the primary > key. > > > > > 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 > > <mysql- > unsubscribe- > [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
