helen_w_lee — 2012-03-17T19:15:59-04:00 — #1
I've been searching this forum and using google but can't find exactly what I'm looking for. I know it's possible because I've seen it on a website but the programming language used is WAY above my head and I can't figure it out.
I have a search form set up so that people can search the db using 3 different columns - either country, last name or groups judged. Amazingly enough they all work! <G> The "groups judged" search is done from a drop-down menu of the 7 groups plus all-breed status. The other two are user-entered text fields. Each one has its own submit button.
Now I'd like to refine it, so that a person could choose a group from the group drop-down menu AND choose a country, then click the submit button to see a list of all, say, hound judges from Australia (or whatever combination).
Can someone please point me in the right direction for a tutorial on doing this? Unless, of course, it's so simple that you could type in 3 or 4 lines of code so I can smack my head and go "duh". :>)
litebearer — 2012-03-17T19:33:32-04:00 — #2
if you are searching one table, hint: in your query --- WHERE x = value1 AND y = value2
helen_w_lee — 2012-03-17T20:22:54-04:00 — #3
Thanks litebearer. Yes, it's one table - that's another whole avenue I want to try to go down in the future. <G>
Thanks for the tip - I got it to work!!! Miracles do happen. :>) For some reason your tip jogged my brain over to realizing that I can put 2 fields on a form and they'll both get passed to the destination page.
An off-shoot of getting that to work is that the penny finally dropped and I've realized that I can put all 3 fields on one form and point to just one destination page, instead of having a separate destination for each searchable field. I tried it and even if I only put info in one of the fields, the correct records still display.
Thanks SO much for kicking my brain out of its rut and getting me going in the right direction!!
helen_w_lee — 2012-03-17T23:40:21-04:00 — #4
Thanks again for getting me on track with this. I've now added a 4th option and have been testing and am finding something weird happening. After scrolling through the names in the db to see which have duplicates, I've entered those names into the Last Name field, with no other values chosen. Most of the time the search comes up with the correct # of records but sometimes it doesn't.
For example, there are 7 Smiths but the search only finds 5, dropping the first and the last. Then when I searched on Anderson it found Anderson-Wulff but NOT just plain Anderson. There are 2 Hugs in the db but it found none. I thought that might be because Hug is only 3 characters but it also wouldn't find either of the people with the last name of Winnie, so length isn't the problem. I even went into one of the missing records and copy/pasted the last name from the record which was found but the 2nd one is never found.
What could be causing it to be so flakey? Often the record it finds is the second occurrence, skipping the first. Isn't this rather weird?
... 10 minutes later. It would appear that when a field is allowed to be empty (null), the db automatically puts NULL in the field upon creation. I can go into the record and uncheck the Null box and the record can then be found. Is there any way to NOT have the word NULL in the field? I tried going into the structure of the table and removing NULL from Default2 but it just puts it right back. I'm pretty sure this is the problem. Any solutions anyone can think of?
litebearer — 2012-03-18T01:53:43-04:00 — #5
Would be helpful to see your code
helen_w_lee — 2012-03-18T12:52:39-04:00 — #6
The code is fine, litebearer - it was the NULL value which was in one of the search fields which was causing the problem.
The table in question was originally a "flat file" - ie an html page with alphabet links to the various letters of the alphabet. Since it grew so big (298 people) and I was learning how to use php/mysql, I decided to convert over. I cleaned out all the html coding, took everything into Excel, made sure everything was in the right column, then imported into the table. During that process, I guess, mysql put NULL into any empty fields - or rather, ticked a box beside the field and that tick generated the NULL, I guess.
I've found, though, that when I use my new form to enter a record into the table, the table leaves blank fields blank - no NULL - so the searches work. Last night I sorted the table on the culprit field and manually removed the tick mark on the blank fields and now the search works just fine.
Thanks again for your initial help with the WHERE clause - it totally made the lights come on.