PDO multiple queries understanding issue

Question

Okay guys I am having a hard time understanding how to do a certain query.

This is what I am trying to accomplish:

I am trying to output DISTINCT brands from a query result. So when a user searches for instance "SHOE" I can output all of the shoes in my database, plus have a choice on the side where the user can select a certain brand to filter the results. I already can output all of the data I need from the MATCH AGAINST query, I just can't figure out how to do A query that can query my first query with PDO.

Here is what I have:

<?php
require_once 'login.php';
$db = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
$usersearch=strip_tags($_POST['searchquery']);
$query=$db->prepare("SELECT * FROM products WHERE MATCH(description) AGAINST (? IN BOOLEAN MODE) OR MATCH(brand) AGAINST(? IN BOOLEAN MODE);SELECT DISTINCT brand FROM products");
$query->execute(array('+%'.$usersearch.'%','+%'.$usersearch.'%'));
$result=$query->fetchALL(PDO::FETCH_ASSOC);
?>

My problem is with the "SELECT DISTINCT brand FROM products".

Is this the correct way to perform multiple queries? Will this select each brand FROM THE FIRST QUERY to the left of the semicolon or just perform the query on each row in the DB?

Any help would be appreciated.

I'm kind of at a stand still.

This (this link: https://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd/55169977#55169977) solution help you to run multiple query together, If an incorrect a query occurs, it does not execute any other query. — Sajad Mirzaei, Mar 16 '19 at 07:27

sectus · Answer 1 · 2013-05-15T08:03:56.593

1

No, you cannot prepare and execute multi queries in one string with PDO.

You can use mysqli::multi_query, but without preparing. But it useless for multi select.

You could use union, but count of selected fields must be the same. For example

SELECT brand FROM products 
    WHERE 
        MATCH(description) AGAINST (? IN BOOLEAN MODE) 
        OR MATCH(brand) AGAINST(? IN BOOLEAN MODE) 
UNION SELECT DISTINCT brand FROM products

P.S. or just use two separated queries :^ )

edited May 15 '13 at 08:03

answered May 15 '13 at 07:52

sectus

15,605
5
55
97

Thank you sectus for your reply. I figured it out looping through an array and not echoing to screen any duplicates. – Korupshen May 15 '13 at 18:00

score -1 · Answer 2 · answered May 15 '13 at 18:26

-1

I am posting this answer in case anyone else runs into this problem. I used an empty array to store each brand in and if any of the values matched, continue is used to pass it by.

<?php
    $emptyarray=array();
    foreach($result as $row) {
        $individualbrand=$row['brand'];
        if(in_array($individualbrand,$emptyarray))
            continue;
        $emptyarray[]=$individualbrand;
        for($i=0;$i<10;++$i)
            echo '<a href="#">'.$emptyarray[$i].'</a><br />';}
            ?>

This will output UNIQUE brand names from the database results. The empty array is created, populated while being checked for duplicates. Then a for loop iterates through each array value. (I used 10 because I don't think I'll have more than 10 brand names in all resulting from a search.) Hopefully this can help someone in the future.

answered May 15 '13 at 18:26

Korupshen

21
1
5

Actually the last closing bracket needs to be right after the line:$emptyarray[]=$individualbrand; Well sectus, maybe I shouldn't have asked it thinking I needed multiple queries to select distinct brands. And one more thing, "Oh, God....This code works flawlessly with each database query that I send." What's wrong with creating an array with unique values and echoing with a for loop? I see nothing wrong, and again I will iterate for you: It worked for me. – Korupshen May 16 '13 at 05:15
1. You can use pure sql for it. – sectus May 16 '13 at 05:44
2. Your code has lack of logic (yes, brace was not in right place). 3. 'I used 10 because I don't think I'll have more than 10 brand names in all resulting from a search' - it's dirty way with notices. – sectus May 16 '13 at 05:51
'It worked for me' -- it's normal. But try to produce nice code when you doing it for other. – sectus May 16 '13 at 05:55

PDO multiple queries understanding issue

2 Answers2