Advanced Search with PHP, AJAX and MYSQL

Question

I am creating an advanced search, where a user can select(html select) a category (which filters the results with GROUP BY and HAVING) and then they can type into an input to find results using LIKE with multiple columns in the db.

I'm getting no errors, however is not returning the correct data the select(html) returns unrelated results and the input returns nothing, therefore, I'm pretty sure the issue is with my SQL query in candidate_search.php

HTML:

<form action="" class="serach_form" method="post">
    <select id="" name="cv_category" class="cv_category">
        <option value="category1">category 1 </option>
        <option value="category2">category 2</option>
    </select>
    <input type="search" class="search_input" placeholder="Search" />
</form>

AJAX:

$('.serach_form').change(function() {
    var $search_input = $('.search_input').val();
    var $cv_category = $('.cv_category').val();
    var url = "candidate_search.php";
    $.ajax({
        //create an ajax request to load_page.php
        type: "POST", 
        url: url, 
        data:{
            search_input: $search_input, cv_category: $cv_category
        },  
        dataType: "html",   //expect html to be returned                
        success: function(date) {
            $('#search_data').html(date);
        }
    })
});

PHP: (candidate_search.php)

<?php 
include 'db_connect.php';
$search_input = $_POST['search_input'];
$cv_category = $_POST['cv_category'];
if(isset($search_input) || (isset($cv_category))){
    $sql = $dbh->prepare("SELECT * FROM candidates_table WHERE full_name LIKE :search_input AND phone_number LIKE :search_input AND email_address LIKE :search_input AND id LIKE :search_input GROUP BY cv_category HAVING :cv_category");
    $sql->bindValue(':search_input', '%' . $search_input . '%', PDO::PARAM_STR);
    $sql->bindValue(':cv_category', '%' . $cv_category . '%', PDO::PARAM_STR);
    if($sql->execute()) {
        $sql->setFetchMode(PDO::FETCH_ASSOC);
    }
    while($row = $sql->fetch()) { 
        // Do something ..... //
    } // End of whileloop //
} // end of isset // ?>

Answer 1

I am assuming full_name and phone_number are text fields? Why are you using like when you can do an equal to?

SELECT * FROM candidates_table WHERE full_name = :search_input OR phone_number = :search_input OR email_address = :search_input OR id = :search_input GROUP BY cv_category HAVING :cv_category"

Also why are you using HAVING? HAVING clause is used to do a filter for aggregate queries like sum or average or similar. I don't think you want that.

If you only want to get the values when search term is exactly equal to the value in the db then I would suggest you use the = rather than like.

Looking at the javascript I can see that you want to continuously lookup every time the form changes. You would want to do partial text matches as well. In this case you should use wildcards to match the value. Use % before and after the search term. If the search string is a substring of the value in the DB, it would return a result.

Also I would replace the having query with an and operation. Pleas check the following code:

Here is the query:

I would use this query instead of the one you are using.

"SELECT * FROM candidates_table WHERE (full_name like '%:search_input%' OR phone_number = '%:search_input%' OR email_address = '%:search_input%' OR id = '%:search_input%') AND cv_category = :cv_category"

PS: I would also recommend you explore another data source other than MySQL. Like queries are slow in MySQL.

----EDIT----

<?php 
include 'db_connect.php';
$search_input = $_POST['search_input'];
$cv_category = $_POST['cv_category'];
if(isset($search_input) || (isset($cv_category))){
    $sql = $dbh->prepare("SELECT * FROM candidates_table WHERE (full_name like '%:full_name%' OR phone_number = '%:phone_number%' OR email_address = '%:email_address%' OR id = '%:id%') AND cv_category = :cv_category"
);
    $sql->bindValue(':full_name', '%' . $search_input . '%', PDO::PARAM_STR);
    $sql->bindValue(':phone_number', '%' . $search_input . '%', PDO::PARAM_STR);
    $sql->bindValue(':email_address', '%' . $search_input . '%', PDO::PARAM_STR);
    $sql->bindValue(':id', '%' . $search_input . '%', PDO::PARAM_STR);
    $sql->bindValue(':cv_category', '%' . $cv_category . '%', PDO::PARAM_STR);
    if($sql->execute()) {
        $sql->setFetchMode(PDO::FETCH_ASSOC);
    }
    while($row = $sql->fetch()) { 
        // Do something ..... //
    } // End of whileloop //
} // end of isset // ?>

Answer 2

First, you can try to debug the steps:

first debug with var_dump(name_of_variable); die();

the receive values from the browser, if its ok, your request

• Try to execute directly in MySQL to see the result
• Debug your PDO prepare and bindValue

• And your request should use OR not AND condition like

  • SELECT * FROM candidates_table WHERE full_name LIKE :search_input OR phone_number LIKE :search_input OR email_address LIKE :search_input OR id LIKE :search_input GROUP BY cv_category HAVING :cv_category

And don't forget to return JSON or XML not an array ( throw error ).

Hope it will help you !

Answer 3

The search query should be always with OR operator, like this:

$sql = $dbh->prepare("SELECT * FROM candidates_table WHERE full_name LIKE :search_input OR phone_number LIKE :search_input OR email_address LIKE :search_input OR id LIKE :search_input GROUP BY cv_category HAVING :cv_category");