Thanks for the "thinking through this" exercise. I solved my problem (and a couple of others) with your help and some additional sweat.
Mike ----- Original Message ----- From: "Nick Stuart" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, May 14, 2002 5:37 PM Subject: Re: query for search on mysql database > Maybe :) > Ok, I'm still a bit confused on how you are doing it, but it sounds like > to much workfor me :) Here is what I would do. > You have your search.php which holds the search form. You > take whatever the values of the fields the user enters and post them to a > secondpage..everything ok so far. > After you do that I would pull use those variables that got > posted from my form and generate the SQL "on-the-fly". By on the fly all I > mean is that thevariables are the only parts that are changing. You can leave them all in > there as it wont hurtto have a blank variable you just end up with a '%' > Also, you probably already know this but > when you post a form object to another page you can access it by using the > form objects name. IEsay you have <INPUT TYPE=text NAME=subject> on your search page and you > post it to anotherpage...you can then access it by simply using $subject. I'm sure you > already know this justrefreshing myself here too. > > Hope this helps...prolly just telling you stuff you > already know but just trying to help. > > -Nick > > > After reading and re-reading your reply, > it seems as if I made it sound > > like I was generating the sql on-the-fly. As I said in my reply > to > > your reply, the sql sits in the script the variables get sent to from > > the form page > (using method="POST" action="results.php"). The data the > > users enters in the fork > fields on the search.php page get sent, as the > > superglobal variable array elements to the > pre-existing sql. I want to > > grab whatever data the user enters, even the first letter of a > last > > name (even if all other fields are left blank) and return the records > > meeting the > criterion/criteria. > > > > Am I off my rocker? > > > > > > ----- 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
