getting search results if one parameter is empty

[glennn.php]

need results from a db search where a = 'value' AND b = 'any', if that makes sense.

 

"WHERE id = '2' AND name = '' gives me nothing (if the 'name' search option is left empty)...

 

thanks for your help.

 

G

[Hybride]

If you're asking for when the search is empty in one column, then:

SELECT * FROM table_name WHERE  id=2 AND ISNULL([columnname]);

If it's not null, then:

SELECT * FROM table_name WHERE id=2 AND NOT ISNULL([columnname]);

Please describe better next time what you are looking for.

[glennn.php]

If you're asking for when the search is empty in one column, then:

SELECT * FROM table_name WHERE  id=2 AND ISNULL([columnname]);

If it's not null, then:

SELECT * FROM table_name WHERE id=2 AND NOT ISNULL([columnname]);

Please describe better next time what you are looking for.

 

yeah, i didn't quite know how to word my question:

 

the Search Form has options to search, say, Name and City - if someone searches for JUST a name and ANY City, the query "WHERE name = $name AND city = $city" won't work because the variable passed is empty if they don't choose a City. So "...WHERE name = 'Smith' AND city = ' '..." returns nothing since all the city fields are populated.

 

hope that makes more sense.

 

thanks

GN

 

[jdavidbakr]

Build your query dynamically, the simplest method would be:

 

$statement = "select * from table where id = 2";
if ($_POST['name']) {
  $statement .= " and name = '".mysql_real_escape_string($_POST['name'])."'";
}
$result = mysql_query($statement);

 

Just make sure you have a valid query or check and return an error if you don't get anything submitted.

[glennn.php]

beautiful, thanks

[seaweed]

How would you do this if you were using prepared statements, since you can't build your queries and then insert them in prepared statements?

 

Say you have this query:

 

$this->stmt = $this->_dbh->prepare("SELECT * FROM table WHERE (name = :name) AND (city = :city)");

 

How would you write a conditional for city being empty? I've tried this with no luck:

 

$this->stmt = $this->_dbh->prepare("SELECT * FROM posts WHERE (name = :name) IF($city == "", '', 'AND (city = :city)");

 

 

Any ideas? I have the same problem, a value that may be left blank, but in prepared statements you can't build queries and then insert them, it errors out.

 

[jdavidbakr]

How would you do this if you were using prepared statements, since you can't build your queries and then insert them in prepared statements?

 

Say you have this query:

 

$this->stmt = $this->_dbh->prepare("SELECT * FROM table WHERE (name = :name) AND (city = :city)");

 

How would you write a conditional for city being empty? I've tried this with no luck:

 

$this->stmt = $this->_dbh->prepare("SELECT * FROM posts WHERE (name = :name) IF($city == "", '', 'AND (city = :city)");

 

 

Any ideas? I have the same problem, a value that may be left blank, but in prepared statements you can't build queries and then insert them, it errors out.

 

 

I don't normally do it that way, but can't you create the query as a variable still?

 

$statement = "SELECT * FROM table WHERE (name = :name)";
if ($city) {
  $statement .= " AND (city = :city)";
}

$this->stmt = $this->_dbh->prepare($statement);

[seaweed]

I tried that but it didn't work... if I choose a category it works, but if I don't (it is set to 0 if it is empty) I get no results...

 

$statement = "SELECT * FROM posts WHERE";

if ($this->_category == 0) {
	$statement .= " (post_price < :price) AND (post_class = :class) AND (MATCH (post_title, post_description) AGAINST (+:keywords IN BOOLEAN MODE))";
}
else {
	$statement .= " (post_cat = :category) AND (post_price < :price) AND (post_class = :class) AND 
                (MATCH (post_title, post_description) AGAINST (+:keywords IN BOOLEAN MODE))";
}

        $this->stmt = $this->_dbh->prepare($statement);

 

Say I choose the category 'apples' and I get 6 results.... if I don't choose a category, I should at least get those same 6 results plus results from all other categories with the words apples in the description or title... but if I don't choose a category I get nadda...

[seaweed]

I have no idea why the above didn't work but I put a quick hack in and it worked...

 

if ($category == 0) {
$this->_category = "5000";
$this->_whereCat = "post_cat NOT LIKE :category";
}
else {
$this->_category = $category;
$this->_whereCat = "post_cat = :category";
}

$this->stmt = $this->_dbh->prepare("SELECT * FROM posts WHERE ... AND ($this->_whereCat) AND ...");

 

That seems to work since we only have 60 categories now and will never have over 100, so any categoryid not 5000 shows up.