Jump to content


Photo

Help with a query


  • Please log in to reply
5 replies to this topic

#1 radar

radar
  • Members
  • PipPipPip
  • Advanced Member
  • 645 posts
  • LocationSLC

Posted 28 August 2006 - 05:25 PM

Okay first thing I should let you know is I am using the Smarty Template engine...  Now for this site I am working on  I've got 2 tables.. one is advisors and one is recommendations.  They both have quite a bit of the same information...  Here is my query I have right now

<?php
$data = $turbo->get_all("SELECT advisors.fname, advisors.lname, advisors.advisor_id, count(recommendations.rec_id) as tot_rec, avg(recommendations.total_score) as avg_score, recommendations.total_score FROM advisors INNER JOIN recommendations ON advisors.advisor_id = recommendations.advisor_id GROUP BY advisor_id ORDER BY advisors.lname");
?>

Now this works for the most part -- but right now there is 21 entries in the advisors table and this is only picking up 14 of them..  Also something new I have to figure out is...

In the Advisors table there is a list of advisors at different institutions..
So say there is a John Smith at University of Marylyn and a John Smith at University of Southern California..  In the output I need to have it like

Smith, John University of Marylyn
Smith, John University of Southern California

So that the recommendations are not combined for the advisor. 

Any help on this would be grateful..  Thanks in advance.

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 28 August 2006 - 07:15 PM

Try a LEFT JOIN which will list advisors even if they have no recommendations

SELECT a.fname, a.lname, a.advisor_id, 
count(*) as tot_rec, avg(r.total_score) as avg_score, SUM(r.total_score) as total_score 
FROM advisors a LEFT JOIN recommendations r 
ON a.advisor_id = r.advisor_id 
GROUP BY a.advisor_id 
ORDER BY a.lname

Note that it's OK to put newlines in a query so you don't need a 60 inch monitor to view it ;)

PS as your group by includes id there should be no problem with 2 with same name
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 radar

radar
  • Members
  • PipPipPip
  • Advanced Member
  • 645 posts
  • LocationSLC

Posted 28 August 2006 - 09:33 PM

I kinda figured this out...  It was running on a INNER JOIN before and when I put a RIGHT JOIN in it seemed to work... so thats what i'm sticking with for now...

Ya know im glad this site is around for me while I am at work (I am a developer by trade)...

#4 radar

radar
  • Members
  • PipPipPip
  • Advanced Member
  • 645 posts
  • LocationSLC

Posted 29 August 2006 - 02:10 AM

Now the only thing I have to figure out is how to seperate them.. so that the advisors are seperated by institution in the display...

note: you can check this out by going to http://www.recommendmyadvisor.com -- click on the button that says browse by advisor

#5 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 29 August 2006 - 07:31 PM

To do that you need to include the institution in your SELECT then ORDER BY a.institution, a.lname.

When you process the results, check for when the institution value changes and output the new value
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#6 radar

radar
  • Members
  • PipPipPip
  • Advanced Member
  • 645 posts
  • LocationSLC

Posted 30 August 2006 - 03:27 AM

Tomorrow when I go to work I'll have to open this scripting up and take a look and see if I can figure out how to do the second part -- see the problem is I am not the original programmer on this and I'm not understanding more than half of the code -- and using smarty isnt helping much either as ive never used it before...  so if i cant im sure i'll be making more posts here..  thanks for the help thus far...




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users