CODEWITHSUNDEEP
php
ayumou
ayumou

Reputation: 13

Php filter contents

Table 1: book (book_id as primary key)

book_id|title
1      |book 1
2      |book 2
3      |book 3

table 2: genre (genre_id as primary key)

genre_id|name
1       |genre 1
2       |genre 2

Table 3:list (id as primary book_id and genre_id as foreign key) //to put more genre in 1 book

id|book_id|genre_id
1 |   1   |  1
2 |   3   |  2
3 |   2   |  1

I want to filter them by genre 1 only so here is my code

<?php
include "mysql-connect.php";

$sql = "SELECT title
FROM book
INNER JOIN list ON book.book_id = list.book_id
WHERE list.genre_id = 1
ORDER BY title ASC";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
    echo "". $row["title"] ."<br>";
}
}
$conn->close();
?>

But every time I want to filter them I need to change my where statment value, How can I do it dynamically?

Upvotes: 0

Views: 102

Answers (3)

ScaisEdge
ScaisEdge

Reputation: 133400

assuming you can set the value for filter in a var name $myActID because you obtain this form a POST value enter by user 

    $myActID = $_POST['my_id'];

you could use a prepared statements and bind the value for filter 

    $stmt = $conn->prepare("SELECT title
                    FROM book
                    INNER JOIN list ON book.book_id = list.book_id
                    WHERE list.genre_id = ?
                    ORDER BY title AS");
    $stmt->execute([$myActID]);

you can accessing the result iterating over eg: using fetch 

    while ($row = $stmt->fetch()) {
       print_r($row);
    }

Upvotes: 1

Osama
Osama

Reputation: 3030

As I understand you want to make the genere_id of where as a variable so here is the sql statement 

$id=1;
$sql = "SELECT title
FROM book
INNER JOIN list ON book.book_id = list.book_id
WHERE list.genre_id =".$id."
ORDER BY title ASC";

Upvotes: 0

clearshot66
clearshot66

Reputation: 2302

Try this instead, use a prepared statement then set $id to the id you want instead of modifying the query each time

<?php
include "mysql-connect.php";
$id = 1;
$sql = "SELECT title
FROM book
INNER JOIN list ON book.book_id = list.book_id
WHERE list.genre_id = ?
ORDER BY title ASC";

$stmt = $conn->prepare($query)
if($stmt){
   $stmt->bind_param("i",$id);
   $stmt->execute();
   $stmt->bind_result($title);
   $stmt->store_result;
   $rows = $stmt->num_rows;
   if($rows > 0){
      while($stmt->fetch()){ 
        echo $title; 
      }
   }
   $stmt->close();
}

$conn->close();

Upvotes: 1

Related Questions