I have a book search that is searching for books i am looking to have it search for books that share authors categories or publishers. I have it set up and it works for the OR clause e.g books that have a category CHILDRENS or HISTORY related to them this works correctly but when i search for books that belong to 2 categories (AND) e.g CHILDRENS AND MAGIC (Harry Potter) it does not show these book even though they are linked in the database.
Above is the search i have done using OR, When i do a search for books that belong to Childrens AND HISTORY
Above you can see i get no results for books that share Childrens and Magic when Harrypotter books do belong to both of these. Above is the link in the database that gives every book their category, Magic is Category 1 and Childrens is Category 2 and you can see they both share these.
Below is the PHP code for the Query's
<?php
include 'header.php';
include 'searchscriptTEST.php';
$sql = "SELECT DISTINCT bk.title AS Title, bk.bookid AS BookID, bk.year AS Year, bk.publisher AS Publisher, aut.authorname AS Author
FROM book bk
JOIN book_category bk_cat
ON bk_cat.book_id = bk.bookid
JOIN categories cat
ON cat.id = bk_cat.category_id
JOIN books_authors bk_aut
ON bk_aut.book_id = bk.bookid
JOIN authors aut
ON aut.id = bk_aut.author_id";
if(isset($_GET['searchInput'])){
$input = $_GET['searchInput'];
$input = preg_replace('/[^A-Za-z0-9]/', '', $input);
}
if (isset($input)){
$getters = array();
$queries = array();
foreach ($_GET as $key => $value) {
$temp = is_array($value) ? $value : trim($value);
if (!empty($temp)){
if (!in_array($key, $getters)){
$getters[$key] = $value;
}
}
}
if (!empty($getters)) {
foreach($getters as $key => $value){
//${$key} = $value;
switch ($key) {
case 'searchInput':
array_push($queries,"(bk.title LIKE '%{$getters['searchInput']}%'
|| bk.description LIKE '%{$getters['searchInput']}%' || bk.isbn LIKE '%{$getters['searchInput']}%'
|| bk.keywords LIKE '%{$getters['searchInput']}%' || aut.authorname LIKE '%{$getters['searchInput']}%')");
break;
case 'srch_publisher':
array_push($queries, "(bk.publisher = '{$getters["srch_publisher"]}')");
break;
case 'Year':
if(isset($getters['Year1']) ==""){
array_push($queries, "(bk.year = '{$getters['Year']}')");
} else {
array_push($queries, "(bk.year BETWEEN '$value' AND '{$getters['Year1']}')");
}
break;
case 'srch_author':
if(isset($getters['authorOperator']) ==""){
array_push($queries, "(bk_aut.author_id = '{$getters["srch_author"]}')");
} else {
$operator = $getters['authorOperator'];
array_push($queries, "(bk_aut.author_id = '$value' $operator bk_aut.author_id = '{$getters['srch_author1']}')");
}
break;
case 'srch_category':
if(isset($getters['catOperator']) ==""){
array_push($queries, "(bk_cat.category_id = '{$getters["srch_category"]}')");
} else {
$operator1 = $getters['catOperator'];
array_push($queries, "(bk_cat.category_id = '$value' $operator1 bk_cat.category_id = '{$getters['srch_category1']}')");
}
break;
}
}
}
if(!empty($queries)){
$sql .= " WHERE ";
$i = 1;
foreach ($queries as $query) {
if($i < count($queries)){
$sql .= $query." AND ";
} else {
$sql .= $query;
}
$i++;
}
}
$sql .= " GROUP BY bk.title ORDER BY bk.title ASC";
var_dump($sql);
}else{
$sql .= " GROUP BY bk.title ORDER BY bk.title ASC";
}
$rs = mysql_query($sql) or die(mysql_error());
$rows = mysql_fetch_assoc($rs);
$tot_rows = mysql_num_rows($rs);
?>
This is the SQL Dump that gets sent to the database,
SELECT
DISTINCT bk.title AS Title,
bk.bookid AS BookID,
bk.year AS Year,
bk.publisher AS Publisher,
aut.authorname AS Author
FROM book bk
JOIN book_category bk_cat ON bk_cat.book_id = bk.bookid
JOIN categories cat ON cat.id = bk_cat.category_id
JOIN books_authors bk_aut ON bk_aut.book_id = bk.bookid
JOIN authors aut ON aut.id = bk_aut.author_id
WHERE
(bk_cat.category_id = '2' AND bk_cat.category_id = '1')
GROUP BY bk.title
ORDER BY bk.title ASC
