mySQL select problem

Liveit

Hi

I am trying to have a number of input fields that filter down a table into a list of results but am having problems.

Let's say I have a table of movies like this


Year|Title|Main Star|Genre
2012|Total Recall|Colin Farrell|Action
2012|The Dark Night Rises|Christian Bale|Action
2008|In Bruge|Colin Farrell|Comedy


And I can filter by year, title, main star and genre and the php variables $year, $title, $main_star and $genre get passed on.

I tried SQL statements like

"SELECT * from movies WHERE year = '{$year}'
AND
title = '{$title}'
AND
main_star = '{$main_star}'
AND
genre = '{$genre}'"

but this only works when none of the fields are left blank otherwise it'll search like year = '' and every film has a year.

I tried with OR's instead of AND's but this just brings up rows that match any of the conditions and UNION does similar.

Can anyone give me a hand?

Melanoma

Not sure if this helps at all. It just some SQL with no PHP

SELECT movies.Year, movies.[TITLE], MOVIES.[GENRE]
FROM movies
WHERE (((movies.[year]) Is Not Null) AND ((movies.[title]) Is Not Null));


Here you will only see results where year and title are not blank.

I don't know if I understood your question. Will you let me know if it helps.

amen

I'm not a mySQL person (more MS SQL) but what you need to do is basically create the where clause based on the your input values then execute the statement. So if the user enters 2012 and Action you would have a string that is

[PHP]where genre = '{$genre}' and year = '{$year}'"[/PHP]
and then combine that with the select statement.

If you had only the title then you would have

[PHP]where title = '{$title}'"[/PHP]
and then combine that with the select statement.

That way you don't have to worry about Nulls and hopefully you are not searching on columns for null data.

magooly

Hey I think you need to construct your query depending on your passed args.

Something like this ropey php

$sql = "SELECT * FROM movies";
 
// if we have some input extend the query
if (!$empty($year) || !$empty($title) || !$empty($star) || !$empty($genre) )
{
                $sql .= " WHERE ";
                if (!$empty($year)
                {
                                $sql .= "year = '$year'";
                                if (!$empty($title) || !$empty($star) || !$empty($genre) )
                                {
                                                $sql .= " AND ";
                                }
                }
                if (!$empty($title)
                {
                                $sql .= "title = '$title'";
                                if (!$empty($star) || !$empty($genre) )
                                {
                                                $sql .= " AND ";
                                }
                }
                if (!$empty($star)
                {
                                $sql .= "star = '$star'";
                                if (!$empty($genre))
                                {
                                                $sql .= " AND ";
                                }
                }
                if (!$empty($genre)
                {
                                $sql .= "star = '$star'";
 
                }
               
                $sql.=" ORDER by year;";
}
 
db->execute($sql)

magooly

Oopps last case should be

  if (!$empty($genre)
                {
                                $sql .= "genre = '$genre'";
 
                }

Sherfin

What about

"SELECT * from movies
WHERE (year is null or year = '{$year}')
AND
(title is null or title = '{$title}')
AND
(main_star is null or main_star = '{$main_star}')
AND
(genre is null or genre = '{$genre}'")

Beano

The number of replies here where the poster hasnt read the OP is shocking.

Magooleys approach seems best if you are doing it in PHP but it seems unncessarily complicated and not really suited to expansion if another parameter is required. But at least it answers the OP.

Sherfin

Beano wrote: »

The number of replies here where the poster hasnt read the OP is shocking.

Magooleys approach seems best if you are doing it in PHP but it seems unncessarily complicated and not really suited to expansion if another parameter is required. But at least it answers the OP.

A bit harsh ? No?? There are only 4 replies, only one of which you approve of, which leaves 3

amen wrote: »

I'm not a mySQL person (more MS SQL) but what you need to do is basically create the where clause based on the your input values then execute the statement.

Amen says they're not a mySQL person. Their post makes sense to me, in that if there is a program or code behind then the 'where' part of the statement can be built up before execution.

Melanoma wrote: »

Not sure if this helps at all. It just some SQL with no PHP

SELECT movies.Year, movies.[TITLE], MOVIES.[GENRE]
FROM movies
WHERE (((movies.[year]) Is Not Null) AND ((movies.[title]) Is Not Null));


Here you will only see results where year and title are not blank.

I don't know if I understood your question. Will you let me know if it helps.

Mel states he/she is unsure of the question and that it will only return results where the two fields are not blank


Which just leaves my own reply. Apologies OP, I should have stated that I was looking at this just as a MSSQL query.
My post was probably a bit short, but still trying to get used to this posting and only trying to help.

So, out of the 4 replies (hardly a shocking number) I can only say that I did indeed read the post a few times to try and understand it.

Melanoma

Sherfin wrote: »

A bit harsh ? No?? There are only 4 replies, only one of which you approve of, which leaves 3



Amen says they're not a mySQL person. Their post makes sense to me, in that if there is a program or code behind then the 'where' part of the statement can be built up before execution.





Mel states he/she is unsure of the question and that it will only return results where the two fields are not blank


Which just leaves my own reply. Apologies OP, I should have stated that I was looking at this just as a MSSQL query.
My post was probably a bit short, but still trying to get used to this posting and only trying to help.

So, out of the 4 replies (hardly a shocking number) I can only say that I did indeed read the post a few times to try and understand it.

I think looking at the other replies and then yours, you seem to have answered it.

Giblet

You can use the following

where title = coalesce(nullif({$title},''),title)

If you passed in null instead of an empty string, you wouldn't need the nullif