Searching for MYSQLI record across multiple tables with AJAX

Question

I am adding search functionality to my website, currently the user can search for a item by model name, for example '100D' however they cannot search for brand, in this case it would be 'Canon'. I would like to allow the user to search for 'canon 100d' and the result shows up, where as currently the user can only search '100d'

I have 2 tables, one being 'brands' and one being 'models', this allows me to store the brand information separately from the models because there is one 'brand' to-many 'models'. Because of this I only store the brand 'id' within the models table.

Here is my 'fetch.php' file, this is called in via AJAX from the search form, here is the query where I search for the model based of the input in the search form:

$model_search_query = $cameras_pdo->prepare("SELECT * FROM models WHERE model LIKE :search");
    $model_search_query->execute(array(':search' => "%" . $_POST['search'] . "%"));

    while ($model_search = $model_search_query->fetch(PDO::FETCH_ASSOC)) {
        $brand_search_query = "SELECT brand FROM brands WHERE id =" . $model_search['brand']; 
        $brand_search = mysqli_fetch_assoc(mysqli_query($cameras, $brand_search_query));

        // OUTPUT SEARCH RESULTS HERE 

    }

?>

This above code allows for the user to search '100d' and then the result 'Canon 100D' shows because I have another query in the while loop that finds the brand name based of the input.

I want the user to be able to search the string 'canon 100d' and even though the brand name is stored in a different table to the model, it still returns the result.

I have attempted to do this by creating another query that searches the the brands based of the input however and then returns the ID of the brand in the brands table, then then can be used in the model search query... this does not work, if anyone knows a way to solve my issue, it would be greatly appreciated. Thankyou

You seem to confuse PDO with MySQLi. It looks like you are using both these APIs interchangeably, it doesn't work that way - you need to stick with one. Which one is up to you - [this article](http://php.net/manual/en/mysqlinfo.api.choosing.php) can help you decide which. The important thing is that you use placeholders and bind your variables with `prepare()`, and not inject variables directly into the querystring. — Qirel, Jun 04 '17 at 13:05

score 0 · Answer 1 · answered Jun 04 '17 at 11:59

I have not tested this but it seems you need a JOIN, try this query

$search_query   = explode(" ", $_POST['search']);
$model_search_query = $cameras_pdo->prepare("SELECT m.id,m.model,b.brand FROM models m INNER JOIN brands b ON b.id = m.brand WHERE (m.model LIKE :model AND b.brand LIKE :brand) OR m.model LIKE :model");
$model_search_query->execute(array(':model' => "%" . $search_query[1] . "%", ':brand'=>"%" . $search_query[0] . "%"));

while ($model_search = $model_search_query->fetch(PDO::FETCH_ASSOC)) {

    // OUTPUT SEARCH RESULTS HERE 

}

Hi, thanks for that, I have just tested it and this allows me to search for the brand however not the model. When I search for the brand, It outputs the results along with this error : Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in — Bradley, Jun 04 '17 at 12:02

Searching for MYSQLI record across multiple tables with AJAX

1 Answers1