mysql query and output

[Gotenks]

Hi this is kind of hard to explain but I am going to try anyway lol. I have these 3 tables below in a database.

 

This table is called postaride

 

This table is called waypoints

 

The final table is called days

 

The id in the postaride table is an auto_increment and the other two tables get their ids from that table which is why you see in each of the id columnts 12 and 13. What I am trying to do is make a query which will search these 3 tables. I then wanted it to group the information together to be displayed.

 

for example (i have a login system that uses $session->username to find the current users username) I wanted it to find a person username and then the ID and display that information.

 

so like if it found the username tention66626 it would display something like this on my web page:

 

      username        startpoint    waypoints                                        destination     monday    tuesday ... etc

1  tention66626     Arkesden     Arkesden -> ardleigh -> althorne          1                08:00        --:--

 

      username        startpoint    waypoints                                        destination     monday    tuesday ... etc

2  tention66626     Abridge     Barkingside-> Barking-> Aveley              1                --:--            10:00

 

So it would display like this or if the username john was logged in it would do the same getting the relevant information from the tables. This is the sort of thing I am aiming for if it is even possible I have been trying all night but just could not figure it out so thanks in advance for any help or tips anyone can give and I look forward to seeing your replies

 

- Gotenks

 

[Psycho]

I'm not really sure what you are doing with that data, but I have a feeling the structure is all wrong - you definitely shouldn't need to store the username in all those tables. But, this *should* get you what you asked for.

 

FYI: Your example result had the wrong days associated with the other data.

SELECT p.username, p.startpoint, p.destination,
        GROUP_CONCAT(w.waypoint SEPARATOR ' -> ') AS waypoints,
        d.monday, d.tuesday, d.wednesday, d.thursday, d.friday, d.friday
FROM postaride AS p
JOIN waypoints AS w ON p.id = w.waypointid
JOIN days AS d ON p.id = daysid

WHERE p.username = '$session->username'

GROUP BY p.id

Edited June 29, 2013 by Psycho

[Gotenks]

It is part of a project I am working on at college where I had to make a website that allows people to login and fill out a form which is where the above data comes from. The username wasn't initially in all of the tables but as I was playing around trying different things to get it work I decided to add it to see what I could do. I was doing good up to now but trying to get my head around these table joining wasn't going too well for me at all lol. Oh and sorry about the example having the wrong data it was 2am and I was tired and confused from trying to get my head around it all night lol.

 

I would like to thank you for your quick reply because it was early in the morning for me so I posted it and went to bed expecting to be waiting a while for a reply which is why I was so surprised I got a email 20 minutes later saying there was a reply lol. Also the above query works perfect and does exactly what I need it to do so again thank you very much for that