Narrowing search results?

[Chotor]

This question is somewhere between php help and application design.

 

I need some help with the logics and/or code.

 

Basically I need to narrow down my search results.

I have 5 arrays:

$actors

$directors

$countries

$genres

$years

These may or may not contain values (for example: "Brad Pitt", "Steven Speilberg", "USA", "UK", "Germany", "Sci-Fi", "Thriller", "2002", "2007", "2008" ...). Each array can have multiple values.

 

For those that have values I do an SQL query to find matching movieids (a number).

Then I want to narrow down the results. I want to find the common movieids for all SQL results, if there is one.  <-- *This is the problem*

 

 

I have a partially working code, but I want to rewrite it to be more elegant. That's why I'm asking you clever heads!

Can anyone help me with logics and/or code?

[ignace]

Can anyone help me with logics and/or code?

i think we all do

 

post your code

 

but i would suggest something like:

 

select * from table where field1 in (value1, value2, ..) and field2 in (value3, value4, ..)

[Chotor]

Ok, here goes. I see a couple of errors now, but I'll leave them, so you can see the original code.

$link = new mysqli("#########", "####", "####", "####");




// this is the resulting array that is sent back to the main script
$movies = Array();  

// a temporary array to have something to intersect with
$selection[] = Array();

function findmovies($table, $arr_split, $arr_cnt) {
	/*
	 * There are two methods for querying the sql because of the way the database is designed
	 * This is METHOD1 
	 * 
	 */

	$sel = Array();
	global $link;
	$moviesql = "SELECT movie_id, COUNT(*) AS cnt FROM ".$table."_map JOIN ".$table."s ON(".$table."_map.".$table."_id=".$table."s.".$table."_id)
	WHERE ".$table."s.".$table." IN('".$arr_split."')
	GROUP BY movie_id
	HAVING cnt=".$arr_cnt;

$res = $link->query($moviesql);
while ($row = $res->fetch_object()) {
	$sel[]=$row->movie_id;
	}
return $sel;
}

function findmoviesFromPeople($table, $arr_split, $arr_cnt) {
	/*
	 * There are two methods for querying the sql because of the way the database is designed
	 * This is METHOD2
	 * 
	 */

	$sel = Array();
	global $link;
	$moviesql = "SELECT movie_id, COUNT(*) AS cnt FROM ".$table."_map JOIN people ON(".$table."_map.people_id=people.people_id)
	WHERE people.people_fullname IN('".$arr_split."')
	GROUP BY movie_id
	HAVING cnt=".$arr_cnt;

$res = $link->query($moviesql);
while ($row = $res->fetch_object()) {

	$sel[]=$row->movie_id;
	}
return $sel;
}

function initializeMoviesArray() {
	$tmpmovies = Array();
	global $link;
	$msql= "SELECT movie_id FROM movie_m";
	$res = $link->query($msql);
	while ($row = $res->fetch_object()) {
		$tmpmovies[]=$row->movie_id;
	}	
return $tmpmovies;
} //end initializeMoviesArray

function printmovies(){
foreach($movies as $movienumber) {
	$sql ="SELECT title FROM movie_m WHERE movie_id=".$movienumber;
	$res = $link->query($sql);
	$row = $res->fetch_object();
	echo $row->title;
	echo "<br>";
}	
}


// ------------------ code execution ----------------




//set $movies array to all movies to avoid intersect problems
$movies = initializeMoviesArray();


// inherits $genre, $genre_split, etc. from main script

// check each category to see if this category is set. only process those with a value
if(isset($genres) && $genres!=null) {
/*
*  this is the logics: 
*  	save the resulting query in $selection
*  	intersect $selection against $movies to find matching values
*  	
*/	$selection = findmovies("genre", $genre_split, count($genres));
$movies = array_intersect($movies, $selection);
}

if(isset($country) && $country!=null) {
$selection = findmovies("country", $country_split, count($country));
// intersect again against $movies to narrow search result
$movies = array_intersect($movies, $selection);
}

if(isset($actor) && $actor!=null) {
$selection = findmoviesFromPeople("actor", $actor_split, count($actor));
// intersect again against $movies to narrow search result
$movies = array_intersect($movies, $selection);
}

if(isset($director) && $director!=null) {
$selection = findmoviesFromPeople("director", $director_split, count($director));
// intersect again against $movies to narrow search result
$movies = array_intersect($movies, $selection);
print_r($movies); // just to see what happens
}

if(isset($year) && $year!=null) {

// need findmoviesFromYears()
// intersect again against $movies to narrow search result
$movies = array_intersect($movies, $selection);
print_r($movies);
}

not very elegant...

[lonewolf217]

why is your data coming in via arrays ?  is this data being collected from a form and can each field (actors, directors, etc) have multiple values ?

 

I guess I am just confused by all those if statements at the bottom

[Chotor]

why is your data coming in via arrays ?  is this data being collected from a form and can each field (actors, directors, etc) have multiple values ?

 

I guess I am just confused by all those if statements at the bottom

You are sure right. I am confused myself. That's why I'm asking you.

 

The data come in from a url which I split into arrays. And yes, each field (array) can have several values.

For example:

$directors => "Alfred Hitchcock"

$genres => "Drama", "Mystery", "Thriller"

$countries => "US$years => "1958"

$actors => "Ellen Corby" ,"Henry Jones", "Konstantin Shayne" ,"Lee Patrick", "Tom Helmore"

 

would ultimately yield the movie:

Vertigo

 

Of course the data could be:

$directors => "Alfred Hitchcock"

$genres => "Drama", "Mystery", "Thriller"

$countries => NULL

$years => NULL

$actors => NULL

 

which in case, would yield:

Vertigo

The Lady Vanishes

Rebecca

 

or

$directors => "Alfred Hitchcock"

$genres => NULL

$countries => "USA"

$years => NULL

$actors => NULL

 

which gives:

Rear Window

Psycho

North By Northwest

Vertigo

Rebecca

Strangers on a Traind

Notorious

Shadow of a Doubt

The Lady Vanishes

[Chotor]

Anyone?

 

Help with only the logics/pseudo code is ok.

 

I need to:

Do up to 5 sql queries.

Find the values (if any) that are common in all queries.