DaniWeb Forum Index > Web Development > PHP

Issue with the LIKE expression and php

Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: May 2005

Posts: 1

Reputation: tristan is an unknown quantity at this point

Solved Threads: 0

tristan

Offline

Newbie Poster

Issue with the LIKE expression and php

May 15th, 2005

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:

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

And thus the query goes:

[PHP]$query = "SELECT pet FROM table WHERE (pet LIKE '$pet')";[/PHP]

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

[PHP]if (!$mw || @$mw == "" || (@$mw != "Woman" && @$mw !="Man")) $mw = "%";[/PHP]

And the query:

[PHP]$query = "SELECT mw from table WHERE (mw LIKE '$mw')";[/PHP]

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.

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
SELECT mw,pet FROM table WHERE (mw LIKE '$mw') AND (pet LIKE '$pet') AND ...
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

•

Join Date: Apr 2005

Posts: 138

Reputation: sarahk is an unknown quantity at this point

Solved Threads: 1

sarahk

Offline

Junior Poster

Re: Issue with the LIKE expression and php

May 16th, 2005

Build the query up by appending to the string.

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
//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 ';
}
//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