How to write a query for PHP and MySQL?

Php mysql how to display the number of albums an artist has?

• $dbQuery="select id, name from artists where name like '$keyword%' order by name asc limit 15"; $dbResult=mysql_query($dbQuery); echo mysql_num_rows($dbResult)."\n"; while ($dbRow=mysql_fetch_array($dbResult)) { // for each artist, return the number of albums belonging to that artist // use a separate query here to obtain the number // return the number after the artist's name in the echo statement, separated with a '^' (1b) echo $dbRow["id"]."^".$dbRow["name"]."\n"; Have this code and as you can see i have to write another dbquery that will display the number of albums an artist has. I also have to modify this piece of code function showArtists() { if (ajaxObject.readyState==4) { var artistsArray=ajaxObject.responseText.spl… var numArtists=artistsArray[0]; var htmlStr=""; for (var i=1; i<=numArtists; i++) { var artistDetails=artistsArray[i].split("^")… htmlStr+="<div onmouseover='javascript:suggestOver(this… "; htmlStr+="onmouseout='javascript:sugge… "; htmlStr+="onclick='javascript:setArtis… "; // add the number of albums in brackets after the artist's name in the next line (1b) htmlStr+="class='suggest_link'>" + artistDetails[1] + "</div> "; so that it will be displayed on the webpage. Anyone know how i do this, im new to php and mysql.

• Answer:

  you'll probably have to us a join with another table. but as you don't describe wihch tables you have, i can only guess... SELECT count(*) FROM artists ar JOIN albums al ON ar.id = al.artist_id; or something like that. however it would be way more efficient to include the album count in the first query. making a query, and then for each result issunig a new query is usually very bad design...