creating a table with counts from mysql

billybrag

Hello all,

I wondered if i could pick your brains for a second..

i have a table called job_jobcategories which contains 30 different jobcategories.

I have a second table called job_post which has all of a jobs information in it. One of the fields in this table is jobcategory, which is linked to the first table.

what i am hoping to do is to make a table that pulls each of the job categories from the first table and then counts how many jobs there are for that category and displays this in brackets.

so it will look something like....

Advertising (2)
Automotive (5)
Bank(4)
..
..
etc

can anyone help with this, in php as I have no idea,

I think it must be relativly simple,

thanks alot for any help!!

Mike

Roger_Ramjet

In sql it is very easy


$sql = "SELECT c.jobcategory, COUNT(p.cat_id) FROM jobcategories c LEFT JOIN jobposts p USING(cat_id)";

That is going to return every job category and the count of posts that have that category. The left join is neccessay to ensure that categories with no posts are still listed.

You use that query in an INSERT ... SELECT query to build your table contents.

billybrag

so far i have this - but it wont pull the category counts?

 
     <?PHP

  $display .= "<table>";
  $sql = "SELECT * FROM job_jobcategories";
  $sql_res = mysql_query($sql);

  while ($sql_array = mysql_fetch_array($sql_res)){
      $jobcat = $sql_array['jobcategory'];
      $get_num = "SELECT * FROM 'job_post' WHERE JobCategory = $jobcat";
      $get_nem_res = mysql_query($get_num);
      $get_rows = mysql_num_rows($get_nem_res);

$display .= "
      <tr>
       <td>$jobcat</td>
       <td>($get_rows)</td>
        </tr>
        ";
      }
      $display .= "</table>";
      echo $display;
      ?>

Roger_Ramjet


<?PHP

  $display .= "<table>";
  $sql = "SELECT c.JobCategory, COUNT(p.JobCategory) AS count 
      FROM job_jobcategories c LEFT JOIN job_post p USING(JobCategory)";
  $sql_res = mysql_query($sql);

  while ($sql_array = mysql_fetch_array($sql_res)) {
      $jobcat = $sql_array['jobcategory'];
      $jobcount = $sql_array['count'];
      // trap NULL counts (no entry in job_post) and replace with zero for display
      if (is_null($jobcount)) { $jobcount=0 }
$display .= "
      <tr>
       <td>$jobcat</td>
       <td>$jobcount</td>
        </tr>
        ";
      }
      $display .= "</table>";
      echo $display;
      ?>