Search Query not working.

[bmullecker]

This is for a faux stock photo website. When I only use the $query string to search for $searchSubject, everything goes golden. Once I put everything else in there though, the search function will not work at all.

 

Any help will be greatly appreciated. I've been staring at this for too long and it's starting to give me a headache.

 

PS. This is my first post here. Seems like a great community!

 

<?php

 

$searchSubject = $_POST['searchSubject'];

$searchPhotoname = $_POST['searchPhotoname'];

$searchLocation = $_POST['searchLocation'];

 

if (isset($_POST['search'])) {

 

//Select records from the database

$query = "SELECT * FROM `photos` WHERE `subject` LIKE '%" . $searchSubject . "%' OR `name` LIKE '%" . $searchPhotoname . "%' OR `location` LIKE '%" . $searchLocation . "%' ";

 

 

 

 

 

 

} else {

 

$query = "SELECT * FROM photos";

 

}

 

$result = mysql_query($query);

// count the number of rows in the database

$num = mysql_num_rows($result);

 

?>

 

 

 

If I leave it at this:

 

$query = "SELECT * FROM `photos` WHERE `subject` LIKE '%" . $searchSubject . "%'";

 

Then everything works perfect.

[Pikachu2000]

Define "will not work". Are there errors, no results, too many results, or what?

[bmullecker]

When using the shortened version of the code that I posted at the end of the original post, the subject search works. Picking a subject will display only pictures from that subject table. However, if I keep the full line of red code in there, none of the search functions work and instead, all of the pictures are displayed at once without any filtering.

[Pikachu2000]

The query will return results that match ANY of the criteria in the WHERE clause the way it's written with ORs. Maybe you meant to use ANDs? If that doesn't take care of it, post some sample data, sample search terms and the results you'd expect to get from it.

[bmullecker]

I'll give ANDs a shot and see if that works. I was trying ORs, because all of the search options are separate and don't require them being filled out to work. i.e. You can search for "Animals" as a subject, but not require "Cats" as a name or "New York" as a location, or you can use 2 of the 3, etc.

 

 

[bmullecker]

That worked brilliantly. I'm kicking myself for not trying ANDs earlier, I was just believing they would do exactly as I previously stated. Thank you so much for your help!

[Pikachu2000]

To broaden a search, you'd want to use OR because any result that matches any condition is returned, to narrow one use AND because the result must match all conditions.