Hey all,
I have a sql command that is stating something like, select sometable.somecolum where othertable.othercolum = onetable.onecolum
This works, but the "othertable" is inserting an event that is one row and has a single id. The "onetable" is inserting multiple items with multiple ids, but the ids match the single one. For example.
event 1 Rooms reserved 1, 2, 3
I can display for event 1 and room 1, but it wont display room 2 and 3. The multiple ids match event 1's id too..
ID Name
1 Event 1
ID Name
1 Room 1
1 Room 2
1 Room 3
Any ideas on how to display more than the first item?
Thanks!
SQL query 1 to many
Moderator: General Moderators
-
dreams4000
- Forum Newbie
- Posts: 9
- Joined: Thu Feb 05, 2004 1:51 pm
Unipus, it has everything to do with php as it is written in php, this is a php file accessing a mysql database. As for the description, I am sorry you did not understand it. I am basically doing a 1 to many relationship and have a sql call in PHP that is trying to retrieve the information to display it. ie 1 event to many rooms.
-
dreams4000
- Forum Newbie
- Posts: 9
- Joined: Thu Feb 05, 2004 1:51 pm
Here is the query so you can see more of what is going on. It is not the full function, but please dont bother with that as the rest is not needed for this issue.
What is of concern is the webcal_site_extras.cal_id and the webcal_entry.cal_id fields need to relate. webcal_entry has one single event with an id of 1. webcal_site_extras has many room reservations related to that event. Their id's match up with the id in webcal_entry...namely 1. When information about event 1 is displayed, it needs to show all of the related room reservations. So, you have a 1 (the event) to many (the rooms) relationship. Any ideas?
Code: Select all
function query_events ( $user, $want_repeated, $date_filter, $cat_id = '' ) {
global $login;
global $layers;
$result = array ();
$layers_byuser = array ();
$sql = "SELECT webcal_entry.cal_name, webcal_entry.cal_description, "
. "webcal_entry.cal_date, webcal_entry.cal_time, "
. "webcal_entry.cal_id, webcal_entry.cal_ext_for_id, "
. "webcal_entry.cal_priority, "
. "webcal_site_extras.cal_data, "
. "webcal_entry.cal_access, webcal_entry.cal_duration, "
. "webcal_entry_user.cal_status, "
. "webcal_entry_user.cal_login ";
if ( $want_repeated ) {
$sql .= ", "
. "webcal_entry_repeats.cal_type, webcal_entry_repeats.cal_end, "
. "webcal_entry_repeats.cal_frequency, webcal_entry_repeats.cal_days "
. "FROM webcal_entry, webcal_site_extras, webcal_entry_repeats, webcal_entry_user "
. "WHERE webcal_entry.cal_id = webcal_entry_repeats.cal_id AND "
. "webcal_entry.cal_id = webcal_site_extras.cal_id AND ";
} else {
$sql .= "FROM webcal_entry, webcal_site_extras, webcal_entry_user WHERE ";
}
$sql .= "webcal_entry.cal_id = webcal_entry_user.cal_id " .
"AND webcal_entry.cal_id = webcal_site_extras.cal_id " .
"AND webcal_entry_user.cal_status IN ('A','W') ";-
dreams4000
- Forum Newbie
- Posts: 9
- Joined: Thu Feb 05, 2004 1:51 pm
From what I understand, to go from a table with 1 item to matching that one to a table with many related items, it has to be done with a LEFT JOIN or INNER JOIN command. I have tried quite a few different ways of doing that on here, but just cannot seem to get it to display. Really could use some help on getting this code worked out. Thanks all.