July 21st, 2000, 10:35 PM
I'm working on a search engine using MySql database and PHP, and would like to allow users not to fill in all fields. I would like to right a generic query that in case a field was not filled in, a key word such as "any" or so would be inserted and would return all entries in the table..Is there such a key word in MySql?
July 22nd, 2000, 06:33 AM
If you can't leave it null, just give a default for the field when you define the table.
July 22nd, 2000, 06:46 AM
Thanks Rod, but thats not exactly what I ment. The user of the search engine might leave a few of the search fields empty, and when I pose the query I would like for those fields to return all entries in the matching table. For example if he wishes to search for a book and only enters the Autors name I want the my one query to fetch all the books that were writen by that Author and because he has the option to also add to the search a year of publication I want, in case it's null to put in a general key word like "any" that will just return all books from any year... Sorry if I wasn't clear with my question.
July 22nd, 2000, 02:34 PM
Ahh, I see.
Well, you could do one of two things. First, if you build your query properly it shouldn't matter if the field is blank:
$query="select * from table_name where author LIKE '%$author%' AND publisher LIKE '%$pub%'";
You see if the field was empty on the form it would send to mysql field LIKE '%%' which should match everything.
Another option is to build the query dynamically (assuming text fields are in an array):
$query="select * from table where";
if (!$add) $query.=" AND";
$query.="$key LIKE '%$value%'";
If you make the array index the same as the column in the table it makes it easier as you can see.
July 22nd, 2000, 10:23 PM
K Thanks, I'll give it a try.
It sounds like a very good solution...