SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Linking 2 tables together by unique referencing numbers

    Dear all,

    I am trying to submit my data on a php created form to mysql database where it is separate by 2 tables (device) and (site).

    Below is a clearer pic using table to explain what i intend to do in order for you guys to assist me.

    Code:
    Device Table
    ID   Device_num   Device_loc
    1   AD192C              2
    2   AD23O                3
    3   FT432                3
    4   GH324                1
    
    Site Table
    ID   area
    1   Single
    2   Dual
    3   Quad
    4   Octal
    I created a form where device_num is to be keyed in by user and a drop down option for Site table of 4 fields (see Site table above) to choose from.

    I managed to use the INSERT function to insert the device number into the database, however when i chose the area (say Dual), the Site_ID '2' will reflect '2' in the device table as well - cant seem to get that work out!.

    Below is my code i used :

    PHP Code:
    [Select]$val_d $_POST['Device_num'];
    $val_s $_POST['site_model'];

    $site_insert "INSERT INTO site (site_id, site_model)
                    VALUES (NULL, '
    $val_s')";
    $results mysql_query($site_insert) or die (mysql_error());

    $siteid "SELECT site_id
                  FROM site
                  WHERE site_id = device_site"
    ;
    $siteid_id mysql_query($siteid) or die (mysql_error());

    $device_insert "INSERT INTO device (device_id, device_num, device_site)
                    VALUES (NULL, '
    $val_d', '$siteid_id')";
    $results mysql_query($device_insert) or die (mysql_error()); 

    Thanks in advance for the help.
    Cheers

  2. #2
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There is an error in this query:
    PHP Code:
    $siteid "SELECT site_id FROM WHERE site_id = device_site"
    If i understand correctly device_site is a field "Site Table", so you need to "join" this table.

  3. #3
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes i would like to 'join' this site table to the device table.

    Any idea how i should write the code as?

    Im kind of new to this php and Mysql program.
    really appreciate the help.

  4. #4
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    $siteid = "SELECT * FROM site JOIN device ON site.site_id=device.device_id WHERE ...";
    $siteid_id = mysql_query($siteid) or die (mysql_error());
    guess this will work if not write back

    And also one tip: when you want to add id you can miss it in insert function so it will be auto increment
    for example not:
    INSERT INTO device (device_id, device_num, device_site)
    VALUES (NULL, '$val_d', '$siteid_id')";
    but

    INSERT INTO device (device_num, device_site)
    VALUES ('$val_d', '$siteid_id')";

  5. #5
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi madmaxxp,

    it doesnt seem to work.
    im not sure if i made my 1st post question clear since i just read it again on this query.
    I managed to use the INSERT function to insert the device number into the database, however when i chose the area (say Dual), the Site_ID '2' will reflect '2' in the device table as well - cant seem to get that work out!.
    the Site_ID '2' will reflect '2' in the device table on the field device_loc as well
    also i m wondering am i writing the code correctly for my site table as the values are alrdy insert into the 'site_area' database. should i write this code again?
    Code:
    $site_insert = "INSERT INTO site (site_id, site_model)                 
    			    VALUES (NULL, '$val_s')"; 
    $results = mysql_query($site_insert) or die (mysql_error());


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
  •