Website Filter using PHP and MySQL Query

Ok, so on my site we have a filter section where a user can select to filter through games by either the game's genre or the model that the game will play on.

I'm trying开发者_如何学Python to write a query that will query the database and filter it by genre if genre is the only set item, filter it by model if model is the only set item, or filter both if they're both set. I want the code to be efficient as well so if you know a good method on doing this that would be great to hear.

---

assuming variables $genre and $model already escaped. demo code only, no security for clarity purposes.

$sqlQuery = 'select * from games where 1=1';
if ( !empty($genre) ) $sqlQuery .= " and genre=$genre";
if ( !empty($model) ) $sqlQuery .= " and model=$model";

---

The best approach is to structure your database as such first:

Table 'game':
| id | title | description |
Table 'models':
| id | name | description |
Table 'genres':
| id | name | description |
Table 'game2model':
| game_id (FK to game.id) | model_id (FK to models.id) |
Table 'game2genre':
| game_id (FK to game.id) | genre_id (FK to genres.id) |

Once your DB is normalized in that way, doing your SQL filtering is extremely easy. This pseudo SQL code should get you on the right track:

[We are assuming your genres IDs are 1,2,3 and your models IDs are 10, 11 and 12].

SELECT g.id, g.name, g.description
FROM games g, games2genre gg, games2model gm
WHERE gg.game_id = g.id
AND gm.game_id = g.id
AND gg.genre_id IN (1,2,3)
AND gm.model_id IN (10,11,12)
ORDER BY g.name ASC

---

Note, the answer below assumes the form fields contain the value that is to be queried for. Don't forget error checking, and that you'll need to validate the $_GET/$_POST variables to prevent SQL injection type hacks.

// Generally you would have a function to construct your query here:
function select($where,$orderBy){
...
}

// Then when you when you are ready to build your query

$where = "";

if($model !== 'all'){                  
  $where .= "model ='".$model."'";
  $orderBy = "model ASC";
}

if($genre !== 'all'){                  
  if(isset($model)&&($model !== 'all')){
    $where .= " AND ";
  }
  $where .= "genre <='".$genre."'";
  $orderBy = "genre ASC";
}

// Adding additional filters will look like this

if($additionalFilter !== 'all'){
  if(isset($genre)&&($genre !== 'all')||isset($model)&&($model !== 'all')){
    $where .= " AND ";
  }                  
  $where .= "additionalFilter <='".$additionalFilter."'";
  $orderBy = "additionalFilter ASC";
}

// And finally to put it all together:

select($where, $orderBy);