php display of mysql info?

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!

dreams4000

If this isn't explained clear enough, I am more than happy to elaborate and try to clear it up. Thank you so much for any help.

AstroTeg

Looks like you're doing a 1-to-1 join. What your looking for is a 1 to many which would be a LEFT JOIN.

dreams4000

Hey Astro,

Thank you for the information. Can you give me an idea how that one is done?

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.

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?

dreams4000

I have tried the LEFT JOIN and INNER JOIN commands, but I cannot seem to get them to work correctly with this code. ANY help in getting this thing sorted out and working would be apprecited quite a lot. Thanks all!

AstroTeg

For clarity, I'm pretty sure I won't be able to do justice in explaining left and inner joins so you may want to find some docs on this. But essentially...

FROM webcal_entry, webcal_site_extras, webcal_entry_user WHERE webcal_entry.cal_id = webcal_entry_user.cal_id AND webcal_entry.cal_id = webcal_site_extras.cal_id

This is a one to one join. Notice the id = id in your where clause. This can be rewritten using an INNER JOIN:

FROM webcal_entry INNER JOIN webcal_entry_user ON webcal_entry_user.cal_id = webcal_entry.cal_id

This links up these two tables. To get the third one in as a left join, you might do this:

FROM webcal_entry INNER JOIN webcal_entry_user ON webcal_entry_user.cal_id = webcal_entry.cal_id LEFT JOIN web_entry_extra ON web_entry_extras.cal_id = web_entry.cal_id

Then you can tack on your WHERE clause after this updated FROM clause (minus all the ID linking you're doing in the WHERE clause).