#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Location
    Melbourne Australia
    Posts
    3
    Rep Power
    0
    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?

    ------------------
    michael stange
  2. #2
  3. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    If you can't leave it null, just give a default for the field when you define the table.
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Location
    Melbourne Australia
    Posts
    3
    Rep Power
    0
    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.

    ------------------
    michael stange
  6. #4
  7. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    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";
    $add=0;
    while (list($key,$value)=each($search_term))
    {
    if ($value)
    {
    if (!$add) $query.=" AND";
    $add++;
    $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.

    HTH
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Location
    Melbourne Australia
    Posts
    3
    Rep Power
    0
    K Thanks, I'll give it a try.
    It sounds like a very good solution...

Similar Threads

  1. ADO_ACCESS & complex query
    By largeinnit in forum PHP Development
    Replies: 0
    Last Post: January 1st, 2004, 03:57 PM
  2. newbie with postgres
    By edwinx in forum PostgreSQL Help
    Replies: 5
    Last Post: October 9th, 2003, 05:21 PM
  3. Simple keyboard problem
    By BusterKirk in forum Visual Basic Programming
    Replies: 5
    Last Post: October 4th, 2003, 12:44 PM
  4. grep'ing out data below a key word
    By kasparaitis in forum Perl Programming
    Replies: 4
    Last Post: August 18th, 2003, 11:20 PM
  5. $GLOBALS array
    By JunkCookie in forum PHP Development
    Replies: 5
    Last Post: June 18th, 2003, 11:39 AM

IMN logo majestic logo threadwatch logo seochat tools logo