fastsol1
09-03-2011, 02:49 AM
So I usually don't have a issue with queries but this is making me bonkers. I know I can do it with 2 queries but I am sure there is a way to do it with one.
I have a table with users and their id's are the auto increment like normal. Then another table with a column user_id and attend_date. The user_id will hold a row that relates to the id of a user in the other table, pretty normal. What I want is to get all the user info for a specific user from the `bus` table and get 5 rows from the `attendance` table at the same time where the id from table one equals the user_id in table 2.
If I use this query and a while loop I get the below array returned.
$_sql ="SELECT `bus`.`id`, `bus`.`fname`, `attendance`.`attend_date` FROM `bus`
INNER JOIN `attendance`
ON `bus`.`id` = `attendance`.`user_id`
WHERE `bus`.`id`='$_id' ORDER BY `attendance`.`attend_date` DESC LIMIT 5";
Array
(
[0] => Array
(
[id] => 189
[attend_date] => 2011-09-02 19:49:35
)
[1] => Array
(
[id] => 189
[attend_date] => 2011-09-02 17:46:12
)
[2] => Array
(
[id] => 189
[attend_date] => 2011-09-02 17:45:35
)
)
Don't worry about the fact that it's only 3 and not 5 outputted, I don't have 5 records in there at the moment and that's not the issue at all. The thing I am trying to get is a single array with the user name and the 5 attend_date rather than the user name 5 times also. Honestly the way I have it will work but I am sure there is a better way to make this happen in the query.
Here is a example of what I would hope to return in the query
[id] => 189
[attend_date] => Array(
[0] => 2011-09-02 17:45:35
[1] => 2011-09-02 17:46:12
[2] => 2011-09-02 19:49:35
)
Or something like that, you get the idea. Here is a couple links to screenshots of the db - bus table (http://amewebdesigns.com/php_help/student5/images/attend.jpg) and attendance table (http://amewebdesigns.com/php_help/student5/images/bus.jpg)
Any insight is appreciated
I have a table with users and their id's are the auto increment like normal. Then another table with a column user_id and attend_date. The user_id will hold a row that relates to the id of a user in the other table, pretty normal. What I want is to get all the user info for a specific user from the `bus` table and get 5 rows from the `attendance` table at the same time where the id from table one equals the user_id in table 2.
If I use this query and a while loop I get the below array returned.
$_sql ="SELECT `bus`.`id`, `bus`.`fname`, `attendance`.`attend_date` FROM `bus`
INNER JOIN `attendance`
ON `bus`.`id` = `attendance`.`user_id`
WHERE `bus`.`id`='$_id' ORDER BY `attendance`.`attend_date` DESC LIMIT 5";
Array
(
[0] => Array
(
[id] => 189
[attend_date] => 2011-09-02 19:49:35
)
[1] => Array
(
[id] => 189
[attend_date] => 2011-09-02 17:46:12
)
[2] => Array
(
[id] => 189
[attend_date] => 2011-09-02 17:45:35
)
)
Don't worry about the fact that it's only 3 and not 5 outputted, I don't have 5 records in there at the moment and that's not the issue at all. The thing I am trying to get is a single array with the user name and the 5 attend_date rather than the user name 5 times also. Honestly the way I have it will work but I am sure there is a better way to make this happen in the query.
Here is a example of what I would hope to return in the query
[id] => 189
[attend_date] => Array(
[0] => 2011-09-02 17:45:35
[1] => 2011-09-02 17:46:12
[2] => 2011-09-02 19:49:35
)
Or something like that, you get the idea. Here is a couple links to screenshots of the db - bus table (http://amewebdesigns.com/php_help/student5/images/attend.jpg) and attendance table (http://amewebdesigns.com/php_help/student5/images/bus.jpg)
Any insight is appreciated