Simple Table creation with php/mysql

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

 

Doing a query within a query is not particularly simple.

I would add a field to the first table for the category and then do 30 seperate queries to select the jobs in each category with a count function that increases by one for each loop to get the number of matching jobs.

$query = "select * from job_post where category like \"builder\"";
$result = mysql_query($query) or die();
$count=0;  
while ($row= mysql_fetch_array($result)) {
$count=$count+1;
}

PHP:

 

can you elaborate slightly as im not entirly sure about what you mean.

Thanks

 

Can you list the fields in each table?

 

I don't know the field names, but this should be about what you are looking for.

Also, by put in a table, did you mean a table in the database, or an html table for display?

<?PHP

$sql = "SELECT id, job_cat FROM job_jobcategories";
$sql_res = mysql_query($sql);

while ($sql_array = mysql_fetch_array($sql_res)){
$jobcatid = $sql_array['id'];
$jobcat = $sql_array['jobcategory'];

$get_num = "SELECT id FROM job_post WHERE jobcategory = $jobcatid";
$get_nem_res = mysql_query($get_num);
$get_rows = mysql_num_rows($get_nem_res);

$display .= "$jobcat ($get_rows)<br>";

}
echo $display;
?>

PHP:

 

i meant display in html,

the field names are:

The table job_jobcategories has 2 fields

jobcategoryid
jobcategory

and the table job_post has many fields but the field of interest is

JobCategory

 

Assuming that the job_jobcategories jobcategoryid corresponds to the job_post jobcategory field.
Something like this should work:

<?PHP

$display .= "<table border=\"0\" cellspacing=\"0\" cellpadding=\"5\">";

$sql = "SELECT jobcategoryid, jobcategory FROM job_jobcategories";
$sql_res = mysql_query($sql);

while ($sql_array = mysql_fetch_array($sql_res)){
$jobcatid = $sql_array['jobcategoryid'];
$jobcat = $sql_array['jobcategory'];

$get_num = "SELECT * FROM job_post WHERE jobcategory = $jobcatid";
$get_nem_res = mysql_query($get_num);
$get_rows = mysql_num_rows($get_nem_res);

$display .= "
<tr>
<td>$jobcat</td>
<td>($get_rows)</td>
<br>
</tr>
";

}
$display .= "</table>";

echo $display;
?>

PHP:

 

Your SQL query should be something like this.

select c.*, count(jobs.id) as nOfJobs 
from categories as c, jobs 
where c.id = jobs.categoryid 
group by c.id

Code (markup):

Your resulting record set will have the two fields from categories and a field called "nOfJobs" wich holds the number of jobs in that category.

 

ok, so that is pulling the categories and displaying them how i want, however it isnt performing the count, it is just displaying like

Advertising()

thanks for the help so far

 

Sorry Perrow, but i dont understand that, can you explain it for me

Mike

one thing i fear may be incorrect

the matching fields in both tables are jobcategory and JobCategory

is mysql case sensitive?

 

Try this instead. I may have had your table fields mixed up.

<?PHP

$display .= "<table border=\"0\" cellspacing=\"0\" cellpadding=\"5\">";

$sql = "SELECT jobcategoryid, jobcategory 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>
<br>
</tr>
";

}
$display .= "</table>";

echo $display;
?>

PHP:

 

If `jobcategory` of `job_post` is integer, try this:

SELECT a.*, count(b.id) posts 
	FROM job_jobcategories a 
	LEFT JOIN job_post b 
	ON b.jobcategory = a.jobcategoryid 
	GROUP BY a.jobcategoryid, a.jobcategeory 
	ORDER BY a.jobcategory

Code (markup):

(replace b.id with b.the_name_of_job_post_id_field)

The count name is posts. So, you can use it like this: $rs['posts'];