[SATLUG] MySQL Query problem

[J. Patrick Lanigan]

Ed Coates wrote:
> I have my cgi script get params from the forms page, and information
> is being passed with no problem.  However, when the query is run, if
> the first name is left blank, the results return all record that don't
> have a first name entered into the table also.  This isn't what I'm
> hoping to accomplish.  I'm hoping that if you enter only a last name,
> then any record with that last name be returned.  Here's the query:
> 
> $sql  = qq {select ccb_name,first_name,last_name,phone,email };
> $sql .= qq {from ccb };
> $sql .= qq {where last_name like "$last_name" or first_name like
> "$first_name" };
> $sql .= qq {or ccb_name like "$ccb_name" order by first_name,ccb_name};
> 
> I'm guess that it's something to do with the like statement, but is
> there another way around it?

Try this:

SELECT ccb_name, first_name, last_name, phone, email
FROM ccb
WHERE
  (last_name NOT REGEX '^[ ]+$' AND last_name LIKE "$last_name") OR
  (first_name NOT REGEX '^[ ]+$' AND first_name LIKE "$first_name") OR
  (ccb_name NOT REGEX '^[ ]+$' AND ccb_name LIKE "$ccb_name")
ORDER BY
  first_name,ccb_name;

HTH,
Patrick