SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Looking up a field's entries from another table

    Not sure if I've best summarised that, but here's what I'm looking to do.

    Basically, at the moment I have two tables :

    Employers
    Contacts

    But the eventual idea is for it to be a one-to-many set up, so any given employer can have many contacts.

    I've gotten as far as creating the two tables, and all the pages to add / display / update / delete and search each of these.

    The way it should work, is that a main record should always be created first in the Employers table.

    But what I'd like to do is have a a drop down list on the 'Add Contact' form, which displays each existing 'Employer Name' from the Employers table, which acts to link that Contact to the existing Employer.

    I presume this should be possible, but I'm not sure exactly how to go about it.

    Any pointers much appreciated.

  2. #2
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is that for the page where I'll be able to display an Employer, along with all the Contacts for that Employer?

    I was thinking of the page where I add a new Contact - so I've got all the fields to be added, and a drop down for the Employer field, pre-populated with all the existing Employers.

  3. #3
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Sorry, I misinterpreted the relationship. All you need to list the employers is a straight SELECT:

    Code:
    SELECT ID, Name FROM Employers

  4. #4
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is that in the form?

    So if it was a static list :

    <select name="select" size="1">
    <option value="Employer 1">Employer 1</option>
    <option value="Employer 2">Employer 2</option>
    <option value="Employer 3">Employer 3</option>
    </select>

    How should the syntax be in for this?

    Is this as well as, or instead of creating the selection at the top of the page?

  5. #5
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Here's an example of adding a dropdown to select an employer on the add contact page:

    PHP Code:
    <select name="select">
    <?php
    $sql 
    "SELECT ID, Name FROM Employers";
    $result mysql_query($sql);
    while (
    $row mysql_fetch_array($result)) {
      echo 
    '<option value="' $row['ID'] . '">' $row['Name'] . '</option>';
    }
    ?>
    </select>

  6. #6
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Dan - seem to have got that just about working - just off to test I'm writing it back to the table correctly...

  7. #7
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Something doesn't seem right - with that added in to the page, the page no longer seems to add the new record to the Contact table, and just sticks on the AddContact page, rather than adding the record, and going to the ContactAdded page.

    The PHP at the top of the page looks like :

    Code:
    <?php require_once('../Connections/connSearchTechUK.php'); ?>
    <?php
    // Load the common classes
    require_once('../includes/common/KT_common.php');
    
    // Load the tNG classes
    require_once('../includes/tng/tNG.inc.php');
    
    // Make a transaction dispatcher instance
    $tNGs = new tNG_dispatcher("../");
    
    // Make unified connection variable
    $conn_connSearchTechUK = new KT_connection($connSearchTechUK, $database_connSearchTechUK);
    
    // Start trigger
    $formValidation = new tNG_FormValidation();
    $formValidation->addField("FirstName", true, "text", "", "", "", "Please enter this contact's first name");
    $formValidation->addField("LastName", true, "text", "", "", "", "Please enter this contact's last name");
    $formValidation->addField("Tel", true, "text", "", "", "", "Please enter a phone number for this contact");
    $formValidation->addField("EmployerID", true, "numeric", "", "", "", "Please enter the ID number of this contact's company");
    $tNGs->prepareValidation($formValidation);
    // End trigger
    
    // Make an insert transaction instance
    $ins_EmployerContacts = new tNG_insert($conn_connSearchTechUK);
    $tNGs->addTransaction($ins_EmployerContacts);
    // Register triggers
    $ins_EmployerContacts->registerTrigger("STARTER", "Trigger_Default_Starter", 1, "POST", "KT_Insert1");
    $ins_EmployerContacts->registerTrigger("BEFORE", "Trigger_Default_FormValidation", 10, $formValidation);
    $ins_EmployerContacts->registerTrigger("END", "Trigger_Default_Redirect", 99, "contactAdded.php");
    // Add columns
    $ins_EmployerContacts->setTable("EmployerContacts");
    $ins_EmployerContacts->addColumn("Employer", "STRING_TYPE", "POST", "Employer");
    $ins_EmployerContacts->addColumn("FirstName", "STRING_TYPE", "POST", "FirstName");
    $ins_EmployerContacts->addColumn("LastName", "STRING_TYPE", "POST", "LastName");
    $ins_EmployerContacts->addColumn("JobTitle", "STRING_TYPE", "POST", "JobTitle");
    $ins_EmployerContacts->addColumn("Department", "STRING_TYPE", "POST", "Department");
    $ins_EmployerContacts->addColumn("Tel", "STRING_TYPE", "POST", "Tel");
    $ins_EmployerContacts->addColumn("Mobile", "STRING_TYPE", "POST", "Mobile");
    $ins_EmployerContacts->addColumn("Fax", "STRING_TYPE", "POST", "Fax");
    $ins_EmployerContacts->addColumn("Email", "STRING_TYPE", "POST", "Email");
    $ins_EmployerContacts->addColumn("Notes", "STRING_TYPE", "POST", "Notes");
    $ins_EmployerContacts->addColumn("EmployerID", "NUMERIC_TYPE", "POST", "EmployerID");
    $ins_EmployerContacts->setPrimaryKey("ContactID", "NUMERIC_TYPE");
    
    // Execute all the registered transactions
    $tNGs->executeTransactions();
    
    // Get the transaction recordset
    $rsEmployerContacts = $tNGs->getRecordset("EmployerContacts");
    $row_rsEmployerContacts = mysql_fetch_assoc($rsEmployerContacts);
    $totalRows_rsEmployerContacts = mysql_num_rows($rsEmployerContacts);
    ?>
    and my select code is as you posted earlier :

    Code:
    <select name="select">
    <?php
    $sql = "SELECT EmployerID, Employer FROM Employers";
    $result = mysql_query($sql);
    while ($row = mysql_fetch_array($result)) {
      echo '<option value="' . $row['EmployerID'] . '">' . $row['Employer'] . '</option>';
    }
    ?>
    </select>
    Any idea what's causing it to stop working?

  8. #8
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You are requiring a field called EmployerID, but you have not given a name to the <select> tag for choosing that.

  9. #9
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Where should that be?

    I thought all I did was change ID and Name to EmployerID and Employer, which are the field names in my table?

  10. #10
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I assume somewhere hidden into those classes you're using, they're accessing $_POST to get the actual form data. But your <select> tag has no name, so you can't get what employer was selected...

    <select name="EmployerID">

  11. #11
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Think I've spotted what was causing it to fail - I had Employer and EmployerID to be added, but only EmployerID in form.

    Taking that out seems to have fixed it.

    Thanks for your help with this - I might post again when I'm doing the page to display Employers and Contacts, but presume that's mostly taken care of with the inner join query you posted originally.

    But the idea is to be able to click on an Employer details page, that will list the main details common to the Employer, with a list of each Contact attached to that Employer below, with a click through to a details page for each Contact.


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
  •