Member Avatar for tristan

Hello,

I am having a members search page, where you can look up members by searching on e.g. age, interests, country etc. If the user does not specify e.g. an age range, then all ages should be included in the search. But if a specific age range is specified, then only this age range should be included.

I have tried solving it with a LIKE expression, where I use the wildcard "%" if nothing is specified. This sometimes works correctly, but other times it produces a wrong output by ignoring some of the specifications. I cannot figure out as to why this happens, but guess that it has something to do with variables being kept when I try a new search, so that the variables become messed together.

Some sample code:

if (!$pet || @$pet == "" || (@$pet != "Cat" && @$pet != "Dog" && @$pet != "Horse" && @$pet != "Lion" && @$pet != "Whale" && @$pet != "Dolphin" && @$pet != "Elephant" && @$pet != "Mouse")) $pet = "%";

And thus the query goes:

$query = "SELECT pet FROM table WHERE (pet LIKE '$pet')";

The "funny" thing is that it works for choosing gender (man or woman):

if (!$mw || @$mw == "" || (@$mw != "Woman" && @$mw !="Man")) $mw = "%";

And the query:

$query = "SELECT mw from table WHERE (mw LIKE '$mw')";

To clarify things, I have only one query for the whole search. The man/woman specification works every time, but not the longer one with more options to choose from.

SELECT mw,pet FROM table WHERE (mw LIKE '$mw') AND (pet LIKE '$pet') AND ...

Does anyone have a better solution for enabling searches where you can leave certain fields blank in order to obtain any value, and at the same time return rows where a specified value has been provided by the user?

Thank you very much in advance. I have spent a whole day just with this issue.

Nico

  • 2 Contributors
  • 1 Reply
  • 211 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by sarahk
Member Avatar for sarahk

Build the query up by appending to the string.

//assume all variables are safe and have addslashes()
$query = "select * from `table` ";
$joiner = ' where ';
if (!empty($mw))
{ 
  $query .= $joiner . "`mw` = '{$mw}' ";
  $joiner ' and ';
}
if (!empty($pet))
{
  $query .= $joiner . "`pet` = '{$pet}' ";
  $joiner = ' and ';
}

and so on until the query is complete

I don't think you necessarily need to use like but if you do then have % to mark where the like is used.

Sarah

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.