[RESOLVED] Looking up a field's entries from another table

[turtleton09]

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.

[Krik]

Yea that is fairly simple.

I assume you know how to query the database. Make it a real basic query and then use a while of foreach loop to display each record.

But you need each record displayed in a <select> element.

So put the opening select tag outside the while of foreach loop. And inside the loop have the <option> show the emplorers name and have its value set to the the records Id

<selcect name="employers">
<?php>
//query table

while ($employer_row = mysql_fetch_assoc($employer_res)) {

echo "<option value='" . $employer_row['Employer_Id'] . "'>" . $employer_row['Employer_Name'];

}
?>
</select>

I assuming that there is some kind of unique number wether auto incementing or assigned for each employer and then the form will retreiving that number to be stored with the each contact record.

[turtleton09]

Thanks Krik -

I added in the query, and changed the field names to EmployerID and Employer to match the fields in my table Employers :

<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>

Which is working in that the drop down is being populated by the Employer field from the Employers table, but now when I click on the form button, the record isn't being added, and the page moving to the confirmation page.

The PHP at the top of the page looks like :

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

Any ideas why that's stopped working?

[Krik]

Well I have a suspition but I am really taking a stab in the dark as those 3 require_once's at the top I am guessing are were the classes that are called later are being created. And hence I do not know how they are processing the data. And of coucre it is impractial for you to try an post them at this point.

The thing I noticed was this. The name you gave the select field was "select". But in your code it looks like you call it like with an name of "EmployerID". I am guessing that is playing a part in the problem.

[turtleton09]

Hi Krik,

I was looking at it again this afternoon, and spotted I didn't have a matching list of fields to insert into - I'd added EmployerID and Employer, but only had one in the form.

Thanks for taking the time to look at it.