I have a problem with my search functionality.
When we hit search first we search by post title, post author and post tags in posts table. When we don't have any result then I want to search by category name in category table.
When there is no result in first search query then we get from category table category_id and try to find by this category_id posts from post table in 3rd query.
Unfortunately when it hit 2nd query I get back string: "No Result of Search. Try again later.".
Additionally I get this 2nd query string for debugging purposes and it is:
SELECT cat_id FROM categories WHERE cat_title LIKE '%HTML%'
When I run this query in phpmyadmin it return 1 row as it suposed to but in my php code it doesn't work. Don't know why. Maybe something is wrong with this prepare statements for example with close methods.
I am using in my code 3 stmt queries. Here is my code:
if(isset($_POST['search']) && !empty($_POST['search'])) {
$search = escape($_POST['search']);
$search = "%{$search}%";
//FIRST WE ARE LOOKING BY TITLE, TAGS AND AUTHOR.
$stmt = mysqli_prepare($connection, "SELECT post_id, post_title, post_author, post_date, post_image, post_content, post_tags FROM " . DB_PREFIX . "posts WHERE post_tags LIKE ? OR post_title LIKE ? OR post_author LIKE ?");
mysqli_stmt_bind_param($stmt, "sss", $search, $search, $search);
mysqli_stmt_execute($stmt);
confirmQuery($stmt);
mysqli_stmt_bind_result($stmt, $post_id, $post_title, $post_author, $post_date, $post_image, $post_content, $post_tags);
mysqli_stmt_store_result($stmt);
if(mysqli_stmt_num_rows($stmt) === 0) {
mysqli_stmt_close($stmt);
//NOW WE ARE LOOKING BY CATEGORY
$search = escape($_POST['search']);
$search = "'%{$search}%'";
$stmt1 = mysqli_prepare($connection, "SELECT cat_id FROM " . DB_PREFIX . "categories WHERE cat_title LIKE ?");
mysqli_stmt_bind_param($stmt1, "s", $search);
mysqli_stmt_execute($stmt1);
confirmQuery($stmt1);
mysqli_stmt_bind_result($stmt1, $cat_id);
mysqli_stmt_fetch($stmt1);
mysqli_stmt_store_result($stmt1);
if(mysqli_stmt_num_rows($stmt1) === 0) {
//NO RESULT IN FIRST SEARCH AND SECOND SEARCH QUERY
mysqli_stmt_close($stmt1);
echo "<h1 class='search-no-result'>No Result of Search. Try again later.</h1>";
//DELETE THIS LATER.
echo "<h1 class='search-no-result'>SELECT cat_id FROM " . DB_PREFIX . "categories WHERE cat_title LIKE {$search}</h1>";
//--------------------
$no_result = true;
//IF THERE IS CATEGORY LIKE SEARCHED
} else {
//WE ARE LOOKING FOR POST FROM THIS CATEGORY
$stmt2 = mysqli_prepare($connection, "SELECT post_id, post_title, post_author, post_date, post_image, post_content, post_tags FROM " . DB_PREFIX . "posts WHERE post_category_id = ?");
mysqli_stmt_bind_param($stmt2, "i", $cat_id);
mysqli_stmt_execute($stmt2);
confirmQuery($stmt2);
mysqli_stmt_bind_result($stmt2, $post_id, $post_title, $post_author, $post_date, $post_image, $post_content, $post_tags);
}
} else {
$no_result = false;
}
} else {
redirect("/Udemy/cms/index");
}
Additionally I can say that search works well with Title, Author, Tags search. Only with Category fails.
So what is the problem?
Something wrong with stmt queries or something else ?
//EDIT
I changed scond query to :
$sql = "SELECT COUNT(*) AS cnt FROM " . DB_PREFIX . "categories WHERE cat_title LIKE ?";
The result is :
No Result of Search. Try again later. SELECT COUNT(*) AS cnt FROM categories WHERE cat_title LIKE '%html%' | cnt is 0
So apparently something is wrong with sql query but when I type this query in phpmyadmin I get this result:
cnt 1
I think the error is because I make second prepare statement after first but I thought $stmt->close() would make it good. Maybe this is the case but I am not sure.
Do you have any ideas how to solve that ?