This question already has an answer here:
I have anime table with anime-id,title,genres and genres table with genres-id,genres
and lookup table with anime-id,genres-id every anime have at least two genres from (action,comedy,drama)
html
<ul class="genres">
<li><input type='checkbox' name='genres[]' value='1'> action</li>
<li><input type='checkbox' name='genres[]' value='2'> comedy</li>
<li><input type='checkbox' name='genres[]' value='3'> drama</li>
</ul>
php
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
$genres = @$_POST['genres']or die('<p class="list" style="width: 100%;">Select at least one genres</p>');
$statement = '';
foreach ($genres as $genres_id) {
$statement .= " AND genres_id='$genres_id'";
}
$sql = "SELECT DISTINCT table FROM table
WHERE 1 $statement";
$result = mysqli_query($conn, $sql)or die('cant getd');
if(mysqli_num_rows($result) > 0) {
while($row = mysqli_fetch_assoc($result)) {
$anime_id = $row['id'];
$sql = "SELECT id,title FROM table
WHERE id='$table'";
$r = mysqli_query($conn, $sql)or die('cant table from table');
if(mysqli_num_rows($r) > 0) {
while($table= mysqli_fetch_assoc($r)) {
$id = $table['id'];
$title = $table['title'];
echo "<p class='list'><a href='http://$_SERVER[SERVER_NAME]/watch.php?id=$id'>$title</a></p>";
}
}else {echo '<p class="animelist" style="width: 100%;">No Resault found, Try a different genres.</p>';}
}
}else {echo '<p class="table" style="width: 100%;">No Resault found, Try a different combination of genres.</p>';}
}?>
the query work fine if I select only one genres but on multi genres select the query fail please, any help?
ps: all animes in database have exactly the same genre
</div>
Think about what you're doing:
WHERE 1 AND foo=bar AND foo=baz AND foo=qux
exactly how is that one single field ever going to SIMULTANEOUSLY AND EXACTLY match multiple different values?
You're basically saying "This car MUST be a banana AND a kitten AND a submarine". No. A car can only ever be a car (ignoring Oscar Meier Weinermobiles...)
Perhaps you should use OR instead...
If you want to get all that have any of the ids you have selected, you need to use OR. Using AND would only get those that has all IDs at the same time
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
$genres = @$_POST['genres']or die('<p class="animelist" style="width: 100%;">Select at least one genres</p>');
$statement = '';
foreach ($genres as $genres_id) {
$statement .= " OR genres_id='$genres_id'";
}
Maybe you want to get all the records for supplied id? In this case, you should use OR or IN operators, e.g.
WHERE genres_id='a' OR genres_id = 'b' OR ..
OR
WHERE genres_id in ('a', 'b', ..)
I suspect that each record only has a single genres_id, so no record is going to have both genres_id=1 AND genres_id=2. It is going to match one or the other. In this case you would want your query to be an OR query (genres_id=1 OR genres_id=2), or better would be to use an IN clause - genres_id IN (1,2).
You might also want to look into sanitizing your inputs to avoid sql injection attack vectors, etc. It appears you are appending raw inputs to a sql query rather than using parameterized queries, which can be a real security risk, and allow users to gain full access to your database or wipe all your data.
---UPDATE BASED ON COMMENT --- So it seems like you don't understand how the record selection works - you are querying on records - if you join tables you are still filtering each row of that join. So if you have one column for "genre_id" with a value, that value can't be 1 and 2 for the same row.
AnimeID=1, Genre_id=Comedy is one record AnimeID=1, Genre_id=Action is another record.
Any Genre_id=Comedy AND Genre_ID=Action query won't match either row since neither row meets both criteria.
You could do this with subqueries however - WHERE AnimeID IN (SELECT AnimeID FROM Genres WHERE Genre_ID=Comedy) AND AnimeID IN (SELECT AnimeID FROM Genres WHERE Genre_ID=Action). That will get you records where Action and Comedy genres are specified.
Not the most efficient of queries, but you need to get correct results before optimizing, right?