dynamically naming a variable?

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']

From stackoverflow

• 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']";
      }
  }
  

  Joshxtothe4 : I need to be able to print out like fake items: $numfakeitems sold items: $numsolditems oktiems: $numokitems etc, which is why I thought it made sense to use an array. At the moment I don't think I can do that with just one variable, unless I have misunderstood?

  Quassnoi : Please post some sample data and the resultset you want to get.

  Joshxtothe4 : adding to question

  Quassnoi : My code does exactly what you want. See update (I only changed the printed string).

  Schnalle : it think, quassnois query does exactly what you want, josh - for a specific username. if you need the same without the username restriction you have to add username to the GROUP BY and SELECT part. select username, category, count(*) from auctions group by username, category;

• 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;