Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases

Mayur Pande
PLUS
Mayur Pande
Courses Plus Student 11,711 Points
Posted by Mayur Pande
Mayur Pande
Courses Plus Student 11,711 Points

Can someone give an explanation of this query?

Hi,

I have been modifying a tutor website for a friend, and am getting stuck on how to add an optional driver to certain lessons, so that if the lesson has a driver the student can book the driver as well as the lesson.

I asked a question here however sadly did not get a reply.

I have noticed the coder before me wrote this query in order to get all the details for a lesson including "tutoremail, studentemail, time, class, location, capacity, availability, booked, topic"

$result = mysqli_query($this->link, "select tutoremail, starttime, endtime, location, class, level, topic, capacity, 
(capacity - (select count(*) from grouptuitionbooking where grouptuitionbooking.tutoremail = grouptuition.tutoremail and grouptuitionbooking.starttime = grouptuition.starttime)) as available, 
username, name, 
(select '$email' in (select studentemail from grouptuitionbooking where grouptuitionbooking.tutoremail = grouptuition.tutoremail and grouptuitionbooking.starttime = grouptuition.starttime)) as booked 
from grouptuition 
inner join (select tutor.email, tutor.username, user.name 
from tutor 
inner join user 
on tutor.email = user.email) t 
on grouptuition.tutoremail = t.email and starttime >= NOW() 
order by starttime asc, username asc");

This is exactly what I want to achieve except with different tables i.e. "driver","studentdriverbooking"

If anyone could explain to me what is going on in this query it would be a great help.

I understand the part up to "(select '$email' in"

1 Answer

Steven Parker
Steven Parker
231,261 Points
Steven Parker
Steven Parker
231,261 Points

Until someone with a more complete understanding of this syntax answers, I have a guess:

I'm not familiar with PHP, so I didn't see your other post. But I've also never seen the SELECT ... IN syntax. I'm going to assume for the moment it's not an error and is similar to SELECT ... FROM. Perhaps it returns a concatenated string of the results instead of each one on a new row?

I also don't know what $email represents, but I'll bet it will be a string with at least one wildcard sequence. So in that case, it's applying the wildcard match to the list of studentemail items returned by the nested subquery, and then putting them in the booked result column.

I noticed this is essentially the same nested subquery that produces the aggregate count named available a few lines above.

Everything else looks like standard SQL — I'll assume you understand the rest.

So did that help?