[SOLVED] Very quick one

[coolphpdude]

Hey there,

 

This will probs be a nice easy question to all you php boffins out there.

 

I have got a students table with around 100 students in it. Each student has a preferred subject out of a choice of 30 subjects (maths, english, french, etc...) what i want to do is create a list of how many students prefer each subject. So for example

 

maths (70)

english (77)

french (40)

etc (33)

...

 

what i originally started doing was a select statement for each subject, i.e...

 

select * FROM students WHERE preferred_subject = 'maths'

select * FROM students WHERE preferred_subject = 'english'

select * FROM students WHERE preferred_subject = 'french'

 

Then doing a $maths_rows=@mysql_num_rows($maths_result); to count the number of rows returned.

 

This can be quite a tedious job when you have quite alot of subjects so what i want to know is, is there a quicker way of coming up with this info? could i do an overall statement such as 'select * FROM students' then do something where i could extract how any maths was in that query?

 

Cheers

 

[.josh]

group by

[coolphpdude]

can you explain please??

[wildteen88]

CV meant use the GROUP BY statement in your query, eg

$sql = 'SELECT preferred_subject, count(preferred_subject) as subject_total FROM students GROUP BY preferred_subject';
$result = mysql_query($sql);

while($row = mysql_fetch_assoc($result))
{
    echo '<pre>' . print_r($row, true) . '</pre>';
}

[compguru910]

group by is a mySQL function. I cant really explain it in detail because I never use it as I preffer to do it your way, but if you google mySQL group by, it will bring up a tutorial

[coolphpdude]

cheers, i think it sounds like what i need. i'll give it a go

 

 

[coolphpdude]

that works a treat. Got 1 or 2 questions about her to modify it to my needs.

 

my output is as follows...

 

Array
(
    [prefered_subject] => Maths
    [subject_total] => 1
)
Array
(
    [prefered_subject] => English
    [subject_total] => 3
)
Array
(
    [prefered_subject] => French
    [subject_total] => 1
)

 

How do i get it to display it in this format...

 

maths (1)

english (3)

french (1)

 

Also i was toying with the idea of listing all subjects and then putting a 0 if no students preferred them such as Physics (0) but im guessing that would not be possible as no students would have physics in the preferred subject field in order to display that info, is that right??

 

Thanks for your help!!

[wildteen88]

How do i get it to display it in this format...

Just change code within the while loop, eg

$sql = 'SELECT preferred_subject, count(preferred_subject) as subject_total FROM students GROUP BY preferred_subject';
$result = mysql_query($sql);

while($row = mysql_fetch_assoc($result))
{
    echo $row['preferred_subject'] . '(' . $row['subject_total'] . ')<br />';
}

 

Also i was toying with the idea of listing all subjects and then putting a 0 if no students preferred them such as Physics (0) but im guessing that would not be possible as no students would have physics in the preferred subject field in order to display that info, is that right??

 

That would be possible if you stored your subjects in a separate table, then you'd use a JOIN to query the two tables at once. Example table structure

 

[pre] subjects

----------               students

id -------------+     ----------

subject            |     ... your current fields ...

                      +--  preferred_subject[/pre]

 

Now if your tables where setup like that, you'd store the id of subject in the preferred_subject field (instead of the actual subject name). Then using a simple JOIN:

SELECT s.subject as pref_subject, COUNT(s.subject) as total_subject FROM subjects s, students st WHERE s.id = st.prefered_subject GROUP BY s.subject

 

Should produce a list like

 

maths (1)

english (3)

french (1)

physics (0)

... etc ...

 

[coolphpdude]

Hey,

 

I previously marked this topic as solved but after trying it today i'm a bit stuck so i'm back for a bit more guidance!

 

I now have my tables set up so that i have a table of subjects (ID, subject) and i have a students table with a field called preferred_subject. preferred_subject contains the ID relevant to a particular subject from the subjects table. *** As the post above ***

 

Im using the query off the previous post...

 

$sql = 'SELECT s.subject as preferred_subject, COUNT(s.subject) as total_subject FROM subjects s, students st WHERE s.id = st.prefered_subject GROUP BY s.subject';
$result = mysql_query($sql);

while($row = mysql_fetch_assoc($result))
{
   echo $row['preferred_subject'] . '(' . $row['subject_total'] . ')<br />';
}

 

but i think i must be writing the select query wrong because it is only counting the the subjects the students have selected. I need it to list all subjects from the subjects table and include the number of students that have chosen that subject as their preferred choice, i.e...

 

maths (3)

english (1)

french (0)

physics (0)

history (0)

PE (9)

 

Can any1 tell me what is wrong with my select statement and advise me what i can do so that ALL subjects are listed regardless of whether a student has chosen it as their preferred subject?

 

Thanks

[wildteen88]

Umm, I thought the query would of produced that. I did try a different tyoe of JOIN but it wasn't quite right. I'll have a word with fenway - our MySQL guru about this for you.

[fenway]

Close... you'll need to use LEFT JOIN to pull in non-matching rows:

 

SELECT 
s.subject as preferred_subject
,COUNT(s.subject) as total_subject 
FROM subjects AS s
LEFT JOIN students AS st ON ( s.id = st.prefered_subject ) 
GROUP BY s.subject

[coolphpdude]

It's getting there... really appreciate this guys!! would never have figured this out on my own. Theres only 1 last problem with this, where there is a subject that no students have selected as their preferred subject it is displaying (1) next to it instead of (0)... any ideas?

 

It should be outputting the following

 

maths (3)

english (1)

french (0)

physics (0)

history (0)

PE (9)

 

but its actually displaying

 

maths (3)

english (1)

french (1)

physics (1)

history (1)

PE (9)

 

Thanks

 

[coolphpdude]

bump - just need to know why its counting 0 records as 1?

[fenway]

bump - just need to know why its counting 0 records as 1?

Try using COUNT(st.prefered_subject) -- that is, on the non-matching table.

[coolphpdude]

You are an absolute legend!!!!!!!!!!!!! Thanks, i really appreciate this that works a treat!!!