Page 1 of 1

SQL query 1 to many

Posted: Thu Feb 05, 2004 1:51 pm
by dreams4000
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!

Posted: Thu Feb 05, 2004 3:08 pm
by Unipus
I think you need to start over in describing your problem. You also need to post your query. You also should have posted this in the handy "databases" topic, since it has nothing to do with PHP.

Posted: Thu Feb 05, 2004 3:23 pm
by dreams4000
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.

Posted: Thu Feb 05, 2004 3:35 pm
by dreams4000
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.

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') ";
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?

Posted: Thu Feb 05, 2004 8:23 pm
by dreams4000
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.