Count a multi-dimensional array

[LMcGhee]

I am using this query to display available artwork.

$query = "SELECT * FROM artwork WHERE status = 'available' ORDER BY associated DESC";
$result = mysql_query($query);

In this table there is also a variable called $artistId which is not set. I would like to get a count
on all the available artworks done by specific artists. How would I go about getting these numbers for
all artists?

Thanks in advance,

Lindsay

[chrisranjana]

I'm just typing off the top of my head but you should get the idea

Do you want something like

SELECT COUNT(*) from artwork GROUP BY artistId

[LMcGhee]

Hi Chris,

How would you grab the counts for each artist?

I did this but it isn't working.

$query = "SELECT COUNT(*) from artwork GROUP BY artistId";
$result = mysql_query($query);

echo $result;

Thanks.

[omoutop]

Try this:

$query = "SELECT artistId, COUNT(artistId) as artictCount From artwork GROUP BY artistId";
$result = mysql_query($query);
$row = mysql_fetch_array($result);
echo 'Artist with id '.$row['artistId'].' has '.$row['artictCount'].' records';

[LMcGhee]

Amazing Omoutop!

Just what I needed... I put the echo in a while loop and now am grabbing the amount of piece available per artists!

$query3 = "SELECT *, COUNT(artistId) as artistCount FROM artwork WHERE status = 'available' GROUP BY artistId";
$result3 = mysql_query($query3);

while($row = mysql_fetch_array($result3)) {

echo $row['firstName'].'&nbsp;'.$row['lastName'].'#'.$row['artistCount'].'<br />';

}

Now I am trying to get each artworks # out of the total number. Can I get find this from the current query or would I have to write a new one?

Thanks!

Lindsay

[LMcGhee]

Hi! I need to write a query that gets the # of the art piece that is associated with a specific artist in a database. The query above tells me how many available pieces each artist has in the database but I need to be able to get the number of the piece in the list of artists work.

Any thoughts?