Posted by Justin Koivisto on 02/01/06 17:02

G0ng wrote:
> I have the following tables:
>
> students
> id, name,....
>
> lessons
> id,description,....
>
> studentslessons
> id,studentid,lessonid,grade
>
> I want to list the lessons of student with name="John" for example.
>
> The one way to do it is :
> select id from students where name="John" and grab the id. then select *
> from studentslessons where studentid=id;
>
> the other is
> select * from students,studentslessons where students.name="john" AND
> students.id=studentslessons.studentid

IME, I find queries that actually use JOIN to be quite a bit quicker. In
your case, you should try something similar to the following:

SELECT students.name, lessons.description, studentslessons.grade
FROM students
INNER JOIN lessons
INNER JOIN studentslessons ON studentslessons.studentid = students.id
ON lessons.id = studentslessons.lessonid
WHERE students.name = 'John'

> Which is more efficient when using php4 and mysql 4.1? I'm using a
> rather old computer Pentium 800MHz.

With php, the fewer queries you issue, the more efficient the code will
likely be. That way all the work is being done on the mysql server
rather than the web server. The only thing you have to do then is
optimize the queries to be faster.

--
Justin Koivisto, ZCE - justin@koivi.com
http://koivi.com

[Back to original message]