How do i combine into one query

MySQL database
#1

$q1 = “SELECT COUNT(thread_id)AS aa FROM table WHERE col = ‘a’”;
$q2 = “SELECT COUNT(thread_id)AS bb FROM table WHERE col = ‘b’”;

$r1 = @mysqli_query ($dbc, $q1) or trigger_error("Query: $q1\n
MySQL Error: " . mysqli_error($dbc));
$r2 = @mysqli_query ($dbc, $q2) or trigger_error("Query: $q2\n
MySQL Error: " . mysqli_error($dbc));

$row1 = mysqli_fetch_array($r1, MYSQLI_ASSOC);
$row2 = mysqli_fetch_array($r2, MYSQLI_ASSOC);

echo $row1[‘aa’]
echo $row2[‘bb’]

im guessing at some kind of case clause but unsure how to do it correctly

#2

SELECT COUNT(thread_id) AS aa FROM table WHERE col = “a”
UNION
SELECT COUNT(thread_id) AS bb FROM table WHERE col = “b”

Something like that should work…

#3

MySQL’s group by will help you here.

$q = "SELECT count(thread_id) as thread_count, `col` FROM `table` WHERE `col` in ('a', 'b') GROUP BY `col`";

$res = mysqli_query ($dbc, $q);

if ($res) {
    while ($r = mysqli_fetch_assoc($res)) {
        echo $r["col"] . " has " . $r["thread_count"];
    }
} else {
    trigger_error("Query: $q\n MySQL Error: " . mysqli_error($dbc));
}

This code will return two rows; the fields will be the value of col and the number of threads with that value for col.

I’ve moved the output code (the while loop here) into the if statement as any call to mysql_fetch_* will result in an error if the query failed.

As an aside, you should look at using PDO to interact with your database; it’s interface is object oriented and is a little nicer to use than mysqli.

#4

Thankyou for your help it has certainly made the query shorter and is working. im trying now to get to
use it how i intend to.

Do i have to select col? as do not plan to echo out this on my page but when i try to remove it gives error “Undefined index: col”

Lastly because you put the output code in a while loop it echos out all the counts in one place. in reality i want to echo these out in various place on my page so my question now is how is this done?

#5

You need to select col because now you’re using one query, you need to be able to see which count refers to which value. To get each row as a separate variable you have to be a little more fiddly in the while loop:

if ($res) {
    $count_a = 0;
    $count_b = 0;
    while ($r = mysqli_fetch_assoc($res)) {
        if ($r['col'] == "a") {
            $count_a = $r['thread_count'];
        } else {
            $count_b = $r['thread_count'];
        }
    }
} else {
    trigger_error("Query: $q\n MySQL Error: " . mysqli_error($dbc));
}

$count_a and $count_b will now be your values.

Sponsor our Newsletter | Privacy Policy | Terms of Service