dynamically naming a variable?

Question

I have a table with various auctions, where each record has both a username and a category. More than likely, there will be multiple records with the same username and category. There are four different types of categories.

I am wondering if it would be possible, given the prepared query below, to have the second bound parameter be the result of looping through an array containing the 4 categories, and if I could dynamically assign the result to an array?

$countAuctionsQuery = "select COUNT(USERNAME, SUBCAT) from AUCTIONS where username = ? AND SUBCAT = ?";

    if ($getRecords = $con->prepare($countAuctionsQuery)) 
        {
        $getRecords->bind_param("ss", $username, $subcat);
        $getRecords->execute();        
        $getRecords->bind_result($numRecords);
         }

edit:

An example of data

Auctions


username    itemnumber cost category
------------------------------------------------
fredx    222  $33 fake
fredx    123  $43 fake
timo     765  $54 fake
fredx    987  $99 sold
bobk     233  $77 fake
wenx     11  $12 ok
fredx    23  $31 ok
fredx    723  $73 fake
wenx     44  $88 ok

So, for username fredx and category fake, 3 should be returned.

For username fredx and category sold, 1 should be returned

For username timo and category fake, 1 should be returned

For username wenx and category ok, 2 should be returned.

I want to be able to print out like so:

Fake items: $numfake items or category['fake']
OK items: $numok items or category['ok']
Sold items: $numsold items or category['sold']

Answer 1

Use this:

SELECT  subcat, COUNT(*)
FROM    AUCTIONS
WHERE   username = ?
GROUP BY
        subcat

and fetch the results in a loop:

$countAuctionsQuery = $query;

if ($getRecords = $con->prepare($countAuctionsQuery)) {
    $getRecords->bind_param("s", $username);
    $getRecords->execute();        
    $getRecords->bind_result($subcat, $numRecords);
    while ($getRecords->fetch()) {
        print "$subcat items: $numRecords items of category['$subcat']";
    }
}

Answer 2

Try this:

function getCategoryCount($user, $conn) {
    $result = array();
    $query = "SELECT category, COUNT(id) AS count FROM Auctions
              WHERE username=? GROUP BY category";
    $stmt = $conn->prepare($query);
    $stmt->bind_param('s',$user);
    $stmt->execute();
    $stmt->bind_result($cat, $count);
    while ($stmt->fetch()) {
     $result[$cat] = $count;
    }
    return $result;
}

You can use the function like this:

$count = getCategoryCount('fred', $con);
print $count['fake']; // prints '3'

Conversely, you can also do this:

$query = "SELECT category, COUNT(id) AS count FROM Auctions
          WHERE username=? GROUP BY category";
$stmt = $con->prepare($query);
$stmt->bind_param('s',$user);
$user = 'fred';
$stmt->execute();
$stmt->bind_result($cat, $count);
while ($stmt->fetch()) {
    ${'num'.$cat} = $count;
}

print $numfake;
print $numok;
print $numsold;