Need help with count

[harkly]

Need help with inserting a count at the end of my text.

 

2 tables

 

Artist

artID

name

ect

 

Images

imgID

artID

etc

 

I have a list that pulls all the artists from the Artist table alphabetically. It also displays the artist image if available. I need help with adding the number of images that I have for each artist.

 

I am thinking I need to count the number of time the artid shows up in my Images table, right? And if so how do I incorporate it into the code below?

 

JPG ArtistName, Birth Date – Date of Death, # of images on file

 

 

 <?php

      echo "<div id='title'><h1>Browse by Artist Alphabetically</h1></div>";

      echo "<div id='search2'>";
      echo "
      <a href='artistHTML.php?alpha=A'>A</a>
      <a href='artistHTML.php?alpha=B'>B</a> 
      <a href='artistHTML.php?alpha=C'>C</a> 
      <a href='artistHTML.php?alpha=D'>D</a> 
      <a href='artistHTML.php?alpha=E'>E</a> 
      <a href='artistHTML.php?alpha=F'>F</a> 
      <a href='artistHTML.php?alpha=G'>G</a> 
      <a href='artistHTML.php?alpha=H'>H</a> 
      <a href='artistHTML.php?alpha=I'>I</a> 
      <a href='artistHTML.php?alpha=J'>J</a> 
      <a href='artistHTML.php?alpha=K'>K</a> 
      <a href='artistHTML.php?alpha=L'>L</a> 
      <a href='artistHTML.php?alpha=M'>M</a> 
      <a href='artistHTML.php?alpha=N'>N</a> 
      <a href='artistHTML.php?alpha=O'>O</a> 
      <a href='artistHTML.php?alpha=P'>P</a> 
      <a href='artistHTML.php?alpha=Q'>Q</a>
      <a href='artistHTML.php?alpha=R'>R</a>
      <a href='artistHTML.php?alpha=S'>S</a>
      <a href='artistHTML.php?alpha=T'>T</a>
      <a href='artistHTML.php?alpha=U'>U</a>
      <a href='artistHTML.php?alpha=V'>V</a>
      <a href='artistHTML.php?alpha=W'>W</a>
      <a href='artistHTML.php?alpha=X'>X</a>
      <a href='artistHTML.php?alpha=Y'>Y</a>
      <a href='artistHTML.php?alpha=Z'>Z</a><br><br><br>";

      echo "</div>";

$search=$_GET["alpha"];
  if ($search != "")
    {
      $result = mysql_query("SELECT * FROM artist WHERE lastname like '$search%' ORDER by lastname");
      $count = count(0);
        while ($r=mysql_fetch_array($result))
          {	
            $fullName=$r["fullName"];
            $dob=$r["dob"];
            $dod=$r["dod"];
            $nationality =$r["nationality"];
            $choosen_medium=$r["artForm"];
            $artid=$r["artid"];

      //display the row
            echo "<div id='search2'>";
              echo $count++;
              echo ". <img src='../image/$artid/$artid.jpg' width='150' height='150' border='0'><a href='formArtist.php?artid=$r[artid]'>".$fullName."</a>, ".$nationality.", ".$dob."-".$dod."</div>";
          }
}
?>

 

A version of count

 

    $query = "SELECT artid from image where artid.image=artid.artist";
      $result = mysql_query($query);
      $totimg = mysql_num_rows($result);

      echo "Images - $totimg<br>";

 

 

I think I need to use a join but not sure where to go from there?

 

 

$result = mysql_query("SELECT artID.artist, fullName.artist, dob.artist, dob.artist, artID.image FROM artist, image WHERE lastname like '$search%' ORDER by lastname");

[rhodesa]

try this:

SELECT a.*,COUNT(i.*) as num_images FROM artist a LEFT JOIN image i ON a.artid = i.artid