SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist tangledman's Avatar
    Join Date
    Sep 2005
    Location
    Puerto de Mazarron, Murcia, Spain
    Posts
    426
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Updating a link table

    Hello everyone;

    I have a three tables;

    item
    item_id
    item_name

    person
    person_id
    person_name


    item_person (LINK TABLE)
    item_id
    person_id


    When adding to the database I use a check box form to list all the items. This then builds an array.
    PHP Code:
    $result=mssql_query($query);
    while(
    $array mssql_fetch_assoc($result)){
        
    $item_id $array['item_id'];
        
    $item_name $array['item_name'];

    echo 
    "$item_name<input type=\"checkbox\" name=\"items[]\" value=\"item_id\"><br />";


    I can then insert that data in the link table;

    PHP Code:
    foreach ($items as $item_id)
        {
        
        
        
    $query="INSERT into item_person (item_id, person_id) values ('$item_id','$person_id')";
        
    $result=mysql_query($query);
        } 
    My question is how can I create an update form containing all the items in the ITEM table and have those items preselected where there is a record in the link table.

    Anyone who helps with this can take the rest of the day off.

    Many thanks.

    Will

  2. #2
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Use a LEFT JOIN (or RIGHT)

    Code:
    SELECT i.*, ip.person AS check
    FROM item i LEFT JOIN item_person ip ON i.item_id = ip.item_id AND ip.person = 1
    Would return all the items, and the check colum would be NULL if person doesn't have the item, or person.id otherwise.

  3. #3
    SitePoint Evangelist tangledman's Avatar
    Join Date
    Sep 2005
    Location
    Puerto de Mazarron, Murcia, Spain
    Posts
    426
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks

    Ok I can see where this is going...but I am not quite there.

    I obviously need to read up abit more on left joins.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •