=\ I need help with a Query

[l3rodey]

Hi, go easy on me please, I am not a pro and I am more than likely doing this the incorrect way. 

$date = date('Y-m-d 07:00:00', strtotime('-24 hours'));
$getJobs = mysql_query("SELECT * FROM jobs WHERE status='1' AND dateofenquiry>='$date'");

while ($row = mysql_fetch_array($getJobs)){
	$clientName = $row['name'];
	$jobID = $row['jobID'];

	$getNotes = mysql_query("SELECT * FROM notes WHERE jobID='$jobID'");

	if (mysql_num_rows($getNotes) == '0'){
		$getJobsa = mysql_query("SELECT * FROM jobs WHERE status='1' AND dateofenquiry>='$date'");
		$TotalNew = mysql_num_rows($getJobsa);
	}

So this is a snippet of my code, I have a query $getJobs and it runs a fetch array I get the number of notes from another table and then If the job has 0 notes then send me an email, Where it is going wrong is $getJobsa and count rows, My table within the last 24 hours has 6 jobs. But only 1 should be showing as only 1 has 0 notes. But my email says 6 when I echo $TotalNew it shows all of them, I know it's within my query but I don't know how to count the notes and put it in a query. 

 

My question is how do I count the number of rows with the jobID as notes and add it to the 1 query?

[jazzman1]

Describe both notes and jobs tables. You need to use only one query to accomplish this task.

[l3rodey]

noteID     |      jobID     |      note      
1             |          1       |     Testing note

2             |          1       |     This is another note

 

jobID       |     name

1             |    John Brown

 

 

This is my tables, Well what matters anyways, I need to show how many comments in this case 2 for john brown and show the number 2. 2 different tables one which hold all the jobs and one that holds the notes...

[jazzman1]

SELECT j.name,COUNT(n.note) as comments FROM jobs j

INNER JOIN notes n USING(jobID)

ORDER BY n.noteID DESC

 

!. Comments should contain 2 rows. Whenever, if you use count(*) the mysql will count all rows in the result set even the comment is NULL.

Edited January 22, 2014 by jazzman1

[Barand]

you need to GROUP BY otherwise you'll get a single row with total of all notes

SELECT j.jobID, j.name, COUNT(n.noteID) as totnotes
FROM jobs j
    LEFT JOIN notes n USING (jobID)
WHERE status='1' AND dateofenquiry>='$date'
GROUP BY j.jobID

You didn't tell us which tables contain "status" and "dateofenquiry". If either is in the notes table then the query will need amendment.