sending different table id's into user table using a form

[Jonathan_Fortis]

hello, this is a lead up to another discussion that has not been resolved. i am getting pretty desperate now. i am sure it cant be that hard and really need help i am using MySQL php

i have created a input form that when submitted sends the data to 3 different tables

i have a tenant table. guarantor table and landlord table all in the database. i also have a property table that is already populated by another form on the website. on the input form though the property table is a dynamic drop down menu that when selected populates fields on the top of the input form

i have the understanding that because the guarantorID, landlordID and propertyID are all inputting new data these will be input commands, however the property is already in the table so this will be an update command?

what i need to do is when the form is submitted the guarantorID, landlordID and propertyID all are sent to columns in the tenant table. this is called join i believe?

i have provided the unformatted code below that all works, but what i need to know how to do is the above. Link them all togeather and send all the id from the  guarantorID, landlordID and propertyID to the tenant table so they can all be reference when i create new pages

can this be done?

<?php require_once('Connections/nameprop.php'); ?>

<?php

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

  if (PHP_VERSION < 6) {

    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {

    case "text":

      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

      break;   

    case "long":

    case "int":

      $theValue = ($theValue != "") ? intval($theValue) : "NULL";

      break;

    case "double":

      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";

      break;

    case "date":

      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

      break;

    case "defined":

      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

      break;

  }

  return $theValue;

}

}

$editFormAction = $_SERVER['PHP_SELF'];

if (isset($_SERVER['QUERY_STRING'])) {

  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);

}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {

  $insertSQL = sprintf("INSERT INTO name_tenant (Title, `First Names`, Surname, DOB, DOBMonth, DOBYear, Nationality, Marital_Status, DayNumber, EvenNumber, MobNumber, Smoker, CurrentAdd, Status, PreviousAdd, Student, FinancialCCj, FinancialArrears, FinancialBankR, FinancialNone, BankName, AccountNum, SortCode, BankDuration, DebitCCCard, Electoral, `next of kin`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",

                       GetSQLValueString($_POST['Title'], "text"),

                       GetSQLValueString($_POST['First_Names'], "text"),

                       GetSQLValueString($_POST['Surname'], "text"),

                       GetSQLValueString($_POST['DOB'], "text"),

                       GetSQLValueString($_POST['DOBMonth'], "text"),

                       GetSQLValueString($_POST['DOBYear'], "text"),

                       GetSQLValueString($_POST['Nationality'], "text"),

                       GetSQLValueString($_POST['Marital_Status'], "text"),

                       GetSQLValueString($_POST['DayNumber'], "double"),

                       GetSQLValueString($_POST['EvenNumber'], "double"),

                       GetSQLValueString($_POST['MobNumber'], "double"),

                       GetSQLValueString($_POST['Smoker'], "text"),

                       GetSQLValueString($_POST['CurrentAdd'], "text"),

                       GetSQLValueString($_POST['Status'], "text"),

                       GetSQLValueString($_POST['PreviousAdd'], "text"),

                       GetSQLValueString($_POST['Student'], "text"),

                       GetSQLValueString($_POST['FinancialCCj'], "text"),

                       GetSQLValueString($_POST['FinancialArrears'], "text"),

                       GetSQLValueString($_POST['FinancialBankR'], "text"),

                       GetSQLValueString($_POST['FinancialNone'], "text"),

                       GetSQLValueString($_POST['BankName'], "text"),

                       GetSQLValueString($_POST['AccountNum'], "double"),

                       GetSQLValueString($_POST['SortCode'], "double"),

                       GetSQLValueString($_POST['BankDuration'], "text"),

                       GetSQLValueString($_POST['DebitCCCard'], "text"),

                       GetSQLValueString($_POST['Electoral'], "text"),

                       GetSQLValueString($_POST['next_of_kin'], "text"));

  mysql_select_db($database_nameprop, $nameprop);

  $Result1 = mysql_query($insertSQL, $nameprop) or die(mysql_error());

}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {

  $insertSQL = sprintf("INSERT INTO name_guarantor (FullName, Title, DOBday, DOBMonth, DOBYear, Sex, MaritalStatus, DaytimeNumber, EveningNumber, MobileNumber, Email, CurrentAddress, PreviousAddress, BankName, AccountNum, SortCode, BankDuration, CCDebitCard, ElectoralRoll, Profession, AnnualWages, PayrollNum, EmpName, EmpAddress, EmpContName, EmpContNumber, EmpEmail, EmpFinaHist, EmpDate, EmpDisclaimer) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",

                       GetSQLValueString($_POST['FullName'], "text"),

                       GetSQLValueString($_POST['Title'], "text"),

                       GetSQLValueString($_POST['DOBday'], "double"),

                       GetSQLValueString($_POST['DOBMonth'], "text"),

                       GetSQLValueString($_POST['DOBYear'], "text"),

                       GetSQLValueString($_POST['Sex'], "text"),

                       GetSQLValueString($_POST['MaritalStatus'], "text"),

                       GetSQLValueString($_POST['DaytimeNumber'], "double"),

                       GetSQLValueString($_POST['EveningNumber'], "double"),

                       GetSQLValueString($_POST['MobileNumber'], "double"),

                       GetSQLValueString($_POST['Email'], "text"),

                       GetSQLValueString($_POST['CurrentAddress'], "text"),

                       GetSQLValueString($_POST['PreviousAddress'], "text"),

                       GetSQLValueString($_POST['BankName'], "text"),

                       GetSQLValueString($_POST['AccountNum'], "double"),

                       GetSQLValueString($_POST['SortCode'], "double"),

                       GetSQLValueString($_POST['BankDuration'], "text"),

                       GetSQLValueString($_POST['CCDebitCard'], "text"),

                       GetSQLValueString($_POST['ElectoralRoll'], "text"),

                       GetSQLValueString($_POST['Profession'], "text"),

                       GetSQLValueString($_POST['AnnualWages'], "text"),

                       GetSQLValueString($_POST['PayrollNum'], "double"),

                       GetSQLValueString($_POST['EmpName'], "text"),

                       GetSQLValueString($_POST['EmpAddress'], "text"),

                       GetSQLValueString($_POST['EmpContName'], "text"),

                       GetSQLValueString($_POST['EmpContNumber'], "double"),

                       GetSQLValueString($_POST['EmpEmail'], "text"),

                       GetSQLValueString($_POST['EmpFinaHist'], "text"),

                       GetSQLValueString($_POST['EmpDate'], "double"),

                       GetSQLValueString($_POST['EmpDisclaimer'], "text"));

  mysql_select_db($database_nameprop, $nameprop);

  $Result1 = mysql_query($insertSQL, $nameprop) or die(mysql_error());

  header(sprintf("Location: %s", $insertGoTo));

}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {

  $insertSQL = sprintf("INSERT INTO name_landlord (name, `telephone number`, mobileNumber, email, address, dateday, datemonth, dateyear, terms) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)",

                       GetSQLValueString($_POST['name'], "text"),

                       GetSQLValueString($_POST['telephone_number'], "text"),

                       GetSQLValueString($_POST['mobileNumber'], "text"),

                       GetSQLValueString($_POST['email'], "text"),

                       GetSQLValueString($_POST['address'], "text"),

                       GetSQLValueString($_POST['dateday'], "text"),

                       GetSQLValueString($_POST['datemonth'], "text"),

                       GetSQLValueString($_POST['dateyear'], "text"),

                       GetSQLValueString($_POST['terms'], "text"));

  mysql_select_db($database_nameprop, $nameprop);

  $Result1 = mysql_query($insertSQL, $nameprop) or die(mysql_error());

  $insertGoTo = "pay-online.php";

  if (isset($_SERVER['QUERY_STRING'])) {

    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";

    $insertGoTo .= $_SERVER['QUERY_STRING'];

  }

  header(sprintf("Location: %s", $insertGoTo));

}

mysql_select_db($database_nameprop, $nameprop);

$query_rsApplyNow = "SELECT * FROM name_editprop";

$rsApplyNow = mysql_query($query_rsApplyNow, $nameprop) or die(mysql_error());

$row_rsApplyNow = mysql_fetch_assoc($rsApplyNow);

$totalRows_rsApplyNow = mysql_num_rows($rsApplyNow);

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<title>Untitled Document</title>

<script type="text/javascript">

function loadFields(theValue) {

         var props = theValue.split("|");

          var prop_id = props[0];

          var propAdd = props[1] ;

          var propPrice = props[2];

                      var town = props[3]

                      var postcode = props[4]

          document.getElementById('prop_id').value=prop_id;

          document.getElementById('Add1').value=propAdd;

                      document.getElementById('town').value=town;

                      document.getElementById('postcode').value=postcode;

          document.getElementById('RentalPrice').value='£' + propPrice +'/ per month';

}

</script>

</head>

<body>

<form action="<?php echo $editFormAction; ?>" method="post" name="form1" id="form1">

  <input type="hidden" name="MM_insert" value="form1" />

<!-- Place the following code where you want to show the result of the property selection --><table width="100%" border="0" cellpadding="0">

  <tr>

    <td width="23%"><!-- the following code is just copied from your code -->

      <select name="Select Property" id="Select Property" onchange="loadFields(this.value)">

        <option value="select your property">select your property</option>

        <?php

do {

?>

        <option value="<?php echo $row_rsApplyNow['prop_id'] . '|' . $row_rsApplyNow['prop_add1'] . '|' . $row_rsApplyNow['rental_price'] . '|' . $row_rsApplyNow['town'] . '|' . $row_rsApplyNow['postcode'];?>"><?php echo $row_rsApplyNow['prop_id'] . " , " . $row_rsApplyNow['prop_add1']; ?></option>

        <?php

} while ($row_rsApplyNow = mysql_fetch_assoc($rsApplyNow));

  $rows = mysql_num_rows($rsApplyNow);

  if($rows > 0) {

      mysql_data_seek($rsApplyNow, 0);

            $row_rsApplyNow = mysql_fetch_assoc($rsApplyNow);

  }

?>

      </select></td>

    <td width="77%"> </td>

  </tr>

  <tr>

    <td>Property ID:</td>

    <td><input type="text" name="prop_id" id="prop_id" readonly="readonly" /></td>

  </tr>

  <tr>

    <td>Address</td>

    <td><input type="text" name="Add1" id="Add1" readonly="readonly" /></td>

  </tr>

  <tr>

    <td>Town</td>

    <td><input type="text" name="town" id="town" readonly="readonly" /></td>

  </tr>

  <tr>

    <td>Postcode</td>

    <td><label for="postcode"></label>

      <input type="text" name="postcode" id="postcode" readonly="readonly" /></td>

  </tr>

  <tr>

    <td>Rent</td>

    <td><input type="text" name="RentalPrice" id="RentalPrice" readonly="readonly" /></td>

  </tr>

  <tr>

    <td> </td>

    <td> </td>

  </tr>

  <tr>

    <td colspan="2"><table width="100%" border="0" cellpadding="0">

      <tr>

        <td>tenant</td>

        <td>gaurantor</td>

        <td>landlord</td>

      </tr>

      <tr>

        <td><table align="center">

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">Title:</td>

            <td><input type="text" name="Title" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">First Names:</td>

            <td><input type="text" name="First_Names" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">Surname:</td>

            <td><input type="text" name="Surname" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">DOB:</td>

            <td><input type="text" name="DOB" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">DOBMonth:</td>

            <td><input type="text" name="DOBMonth" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">DOBYear:</td>

            <td><input type="text" name="DOBYear" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">Nationality:</td>

            <td><input type="text" name="Nationality" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">Marital_Status:</td>

            <td><input type="text" name="Marital_Status" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">DayNumber:</td>

            <td><input type="text" name="DayNumber" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">EvenNumber:</td>

            <td><input type="text" name="EvenNumber" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">MobNumber:</td>

            <td><input type="text" name="MobNumber" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">Smoker:</td>

            <td><input type="text" name="Smoker" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">CurrentAdd:</td>

            <td><input type="text" name="CurrentAdd" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">Status:</td>

            <td><input type="text" name="Status" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">PreviousAdd:</td>

            <td><input type="text" name="PreviousAdd" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">Student:</td>

            <td><input type="text" name="Student" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">FinancialCCj:</td>

            <td><input type="text" name="FinancialCCj" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">FinancialArrears:</td>

            <td><input type="text" name="FinancialArrears" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">FinancialBankR:</td>

            <td><input type="text" name="FinancialBankR" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">FinancialNone:</td>

            <td><input type="text" name="FinancialNone" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">BankName:</td>

            <td><input type="text" name="BankName" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">AccountNum:</td>

            <td><input type="text" name="AccountNum" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">SortCode:</td>

            <td><input type="text" name="SortCode" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">BankDuration:</td>

            <td><input type="text" name="BankDuration" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">DebitCCCard:</td>

            <td><input type="text" name="DebitCCCard" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">Electoral:</td>

            <td><input type="text" name="Electoral" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">Next of kin:</td>

            <td><input type="text" name="next_of_kin" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right"> </td>

            <td> </td>

          </tr>

        </table></td>

        <td><table align="center">

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">FullName:</td>

            <td><input type="text" name="FullName" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">Title:</td>

            <td><input type="text" name="Title2" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">DOBday:</td>

            <td><input type="text" name="DOBday" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">DOBMonth:</td>

            <td><input type="text" name="DOBMonth2" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">DOBYear:</td>

            <td><input type="text" name="DOBYear2" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">Sex:</td>

            <td><input type="text" name="Sex" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">MaritalStatus:</td>

            <td><input type="text" name="MaritalStatus" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">DaytimeNumber:</td>

            <td><input type="text" name="DaytimeNumber" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">EveningNumber:</td>

            <td><input type="text" name="EveningNumber" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">MobileNumber:</td>

            <td><input type="text" name="MobileNumber" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">Email:</td>

            <td><input type="text" name="Email" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">CurrentAddress:</td>

            <td><input type="text" name="CurrentAddress" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">PreviousAddress:</td>

            <td><input type="text" name="PreviousAddress" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">BankName:</td>

            <td><input type="text" name="BankName2" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">AccountNum:</td>

            <td><input type="text" name="AccountNum2" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">SortCode:</td>

            <td><input type="text" name="SortCode2" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">BankDuration:</td>

            <td><input type="text" name="BankDuration2" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">CCDebitCard:</td>

            <td><input type="text" name="CCDebitCard" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">ElectoralRoll:</td>

            <td><input type="text" name="ElectoralRoll" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">Profession:</td>

            <td><input type="text" name="Profession" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">AnnualWages:</td>

            <td><input type="text" name="AnnualWages" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">PayrollNum:</td>

            <td><input type="text" name="PayrollNum" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">EmpName:</td>

            <td><input type="text" name="EmpName" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">EmpAddress:</td>

            <td><input type="text" name="EmpAddress" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">EmpContName:</td>

            <td><input type="text" name="EmpContName" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">EmpContNumber:</td>

            <td><input type="text" name="EmpContNumber" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">EmpEmail:</td>

            <td><input type="text" name="EmpEmail" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">EmpFinaHist:</td>

            <td><input type="text" name="EmpFinaHist" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">EmpDate:</td>

            <td><input type="text" name="EmpDate" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">EmpDisclaimer:</td>

            <td><input type="text" name="EmpDisclaimer" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right"> </td>

            <td> </td>

          </tr>

        </table></td>

        <td><table align="center">

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">Name:</td>

            <td><input type="text" name="name" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">Telephone number:</td>

            <td><input type="text" name="telephone_number" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">MobileNumber:</td>

            <td><input type="text" name="mobileNumber" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">Email:</td>

            <td><input type="text" name="email" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">Address:</td>

            <td><input type="text" name="address" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">Dateday:</td>

            <td><input type="text" name="dateday" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">Datemonth:</td>

            <td><input type="text" name="datemonth" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">Dateyear:</td>

            <td><input type="text" name="dateyear" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">Terms:</td>

            <td><input type="text" name="terms" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right"> </td>

            <td><input type="submit" value="Insert record" /></td>

          </tr>

        </table></td>

      </tr>

    </table></td>

    </tr>

</table>

</form>

<p> </p>

<p> </p>

</form>

<p> </p>

</body>

</html>

<?php

mysql_free_result($rsApplyNow);

?>

[bregent]

Jon, I also just replied to your other thread.

>i have the understanding that because the guarantorID, landlordID and propertyID

>are all inputting new data these will be input commands, however the property is

As I asked in the other thread, do you really want to input landlord and guarantor data everytime you input a tenant? Don't you want to be able to reuse any of this data? Or would you NEVER deal with a given landlord more than once?

The purpose of splitting data into multiple tables is to eliminate redundant data. For example, if one landlord is related to 10 tenants, you wouldn't want to enter all of the same landlord data 10 times, right? But that's what you are doing in your current code.

If for some reason you really do need to enter all of that data each time, then just put it all in one table.

>however the property is already in the table so this will be an update command?

Why do you need to update the property? You would update the property only when an attribute of the property has changed. Same goes for the other tables. You update them when something has changed.

>what i need to do is when the form is submitted the guarantorID, landlordID

>and propertyID  all are sent to columns in the tenant table.

Again, you need to first generate the unique id's for guarantor and landord before you can insert them into the tenant table.

>this is called join i believe?

No, this is not a JOIN. This is just a logical relation.

It would be really helpful to understand what type of application you are building. What is the purpose, and who is entering the various data? Without knowing that, I'm just guessing but I would not build the app this way. I would create a separate maintenance page for each entity - tenant, landlord, guarantor and property. On each page you can INSERT or UPDATE data. The tenant page would consists of 3 dropdown's as well as the other tenant text fields. You would select the guarantor, property and landlord from the dropdowns and these would populate the data for the tenant.

[Jonathan_Fortis]

ok, let me try and explain the form that i am building. I have a page that i insert the properties on. this then goes into the database. the property table has information like, property id, address, postcode and a few others.

the the tenant who wants to have / stay in one of these properties applies to stay in one. they goes to an application form where they first select the property they want to stay in (this is the drop down menu) they then fill out the tenant part of the form / the guarantor part of the form and the landlord part of the form. So the tenant fill all them parts out.

Once the form has been filled out this needs to be emailed to me. this also needs to be sent to the database. I then review the information and as soon as i agree the information on the  form i go to an application page ( this consists of all the data the tenant submitted by email and to the database. I then click a box, the activates the yes/no on the tenant page then they are allowed to access their own unique page. I have done this part of it.

so each part of data, the tenant, landlord (these are old landlords of the tenant) and guarantor are unque to the tenant so do need to be entered everytime

[bregent]

>so each part of data, the tenant, landlord (these are old landlords of the tenant)

>and guarantor are unque to the tenant so do need to be entered everytime

OK, this is very helpful. As mentioned in another post, separating the data into multiple tables is used to eliminate redundant data and resolve other data normalization issues. But in this case, there is no point and only complicates things for you as all of the data has a 1 to 1 relationship. So what I would suggest is to combine all of the fields from tenant, landlord and guarantor into a single table. To that table also add a column for propertyID. Now, in the form that the user fills out, you would include all of these fill-in fields and also include the dropdown where they select the property.  When the form is submitted, all of the data they've filled in, plus the propertyID will get passed to the INSERT statement.  Now I know you also want to display the property details when they select the property.  You can still do that the way you were doing before using JavaScript - just don't insert this additional data into the table.

If for some reason you really want to split the data into multiple tables, then it will be more complicated. You would need to first insert the data into the tenant table, retrieve the unique key value, and then pass that value to the other INSERT statements for landlord and guarantor.

Hope this helps.

[Jonathan_Fortis]

>  You can still do that the way you were doing before using JavaScript - just don't insert this additional data into the table.

when you say additional data are you meaning the property data? just insert the propertyID?

so i create an insert statement

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {

  $insertSQL = sprintf("insert INTO tenant (prop_id) VALUES ( %s)",

                       GetSQLValueString($_POST['prop_id'], "text"),

(meaning insert the feild named prop_id content into the tenant table?

if this is correct this is what i tried at the beginning ant it was throwing up an error in dreamweaver

Also does it matter if the prop_id feild is read onlt does this matter?

[bregent]

>when you say additional data are you meaning the property data?

>just insert the propertyID?

Correct, don't insert the other property details.

>so i create an insert statement

That insert statement only has the the prop_id.  You also need to insert the other data from the form - the tenant, guarantor, and landlord details - that the user entered. With a single table, you can do this all with a single form and a single INSERT statement.

Something like this:

$insertSQL = sprintf("INSERT INTO name_tenant (prop_id, Title, `First Names`, Surname, DOB, DOBMonth, DOBYear, Nationality, Marital_Status, DayNumber, EvenNumber, MobNumber, Smoker, CurrentAdd, Status, PreviousAdd, Student, FinancialCCj, FinancialArrears, FinancialBankR, FinancialNone, BankName, AccountNum, SortCode, BankDuration, DebitCCCard, Electoral, `next of kin`) VALUES (%s,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",

                        GetSQLValueString($_POST['prop_id'], "text"),

                       GetSQLValueString($_POST['Title'], "text"),

                       GetSQLValueString($_POST['First_Names'], "text"),

                       GetSQLValueString($_POST['Surname'], "text"),

                       GetSQLValueString($_POST['DOB'], "text"),

                       GetSQLValueString($_POST['DOBMonth'], "text"),

                       GetSQLValueString($_POST['DOBYear'], "text"),

                       GetSQLValueString($_POST['Nationality'], "text"),

                       GetSQLValueString($_POST['Marital_Status'], "text"),

                       GetSQLValueString($_POST['DayNumber'], "double"),

                       GetSQLValueString($_POST['EvenNumber'], "double"),

                       GetSQLValueString($_POST['MobNumber'], "double"),

                       GetSQLValueString($_POST['Smoker'], "text"),

                       GetSQLValueString($_POST['CurrentAdd'], "text"),

                       GetSQLValueString($_POST['Status'], "text"),

                       GetSQLValueString($_POST['PreviousAdd'], "text"),

                       GetSQLValueString($_POST['Student'], "text"),

                       GetSQLValueString($_POST['FinancialCCj'], "text"),

                       GetSQLValueString($_POST['FinancialArrears'], "text"),

                       GetSQLValueString($_POST['FinancialBankR'], "text"),

                       GetSQLValueString($_POST['FinancialNone'], "text"),

                       GetSQLValueString($_POST['BankName'], "text"),

                       GetSQLValueString($_POST['AccountNum'], "double"),

                       GetSQLValueString($_POST['SortCode'], "double"),

                       GetSQLValueString($_POST['BankDuration'], "text"),

                       GetSQLValueString($_POST['DebitCCCard'], "text"),

                       GetSQLValueString($_POST['Electoral'], "text"),

                       GetSQLValueString($_POST['next_of_kin'], "text"));

....plus the other fields from the landlord and guarantor table that you should add to this table

Note that you also need to clean up your naming conventions. In some cases you are using ProperCase, in others camelCase. Sometimes you are using underscores with either lower case or caps, and other times using spaces in the names. First of all, be consistent with your column naming. Second, never use spaces in table/column names - it leads to problems.

>Also does it matter if the prop_id feild is read onlt does this matter?

No I don't think so. As long as it is in the form the selected value should pass to the script.

[Jonathan_Fortis]

ok thanks i will try that. i will make a test of just the tenant input and property

[Jonathan_Fortis]

i have done that but it still isnt working. i think it might be something to do with the fact that all the form feilds for the tenant DONT have id's but for the property they do. also the Javascript to populate the feilds for the property use the same prop_id

let me show you

<script type="text/javascript">

function loadFields(theValue) {

         var props = theValue.split("|");

          var prop_id = props[0];

          var propAdd = props[1] ;

          var propPrice = props[2];

                      var town = props[3]

                      var postcode = props[4]

          document.getElementById('prop_id').value=prop_id;

          document.getElementById('Add1').value=propAdd;

                      document.getElementById('town').value=town;

                      document.getElementById('postcode').value=postcode;

          document.getElementById('RentalPrice').value='£' + propPrice +'/ per month';

}

</script>

a few of the table elements look like this ( here is the menu part of the form)

      <select name="Select Property" id="Select Property" onchange="loadFields(this.value)">

        <option value="select your property">select your property</option>

        <?php

do {

?>

        <option value="<?php echo $row_rsApplyNow['prop_id'] . '|' . $row_rsApplyNow['prop_add1'] . '|' . $row_rsApplyNow['rental_price'] . '|' . $row_rsApplyNow['town'] . '|' . $row_rsApplyNow['postcode'];?>"><?php echo $row_rsApplyNow['prop_id'] . " , " . $row_rsApplyNow['prop_add1']; ?></option>

        <?php

} while ($row_rsApplyNow = mysql_fetch_assoc($rsApplyNow));

  $rows = mysql_num_rows($rsApplyNow);

  if($rows > 0) {

      mysql_data_seek($rsApplyNow, 0);

            $row_rsApplyNow = mysql_fetch_assoc($rsApplyNow);

  }

?>

      </select></td>

    <td width="77%"> </td>

  </tr>

  <tr>

    <td>Property ID:</td>

    <td><input type="text" name="prop_id" id="prop_id" value="" size="32" /></td>

    <td> </td>

  </tr>

  <tr>

    <td>Address</td>

    <td><input type="text" name="Add1" id="Add1" readonly="readonly" /></td>

  </tr>

  <tr>

    <td>Town</td>

    <td><input type="text" name="town" id="town" readonly="readonly" /></td>

  </tr>

  <tr>

    <td>Postcode</td>

    <td><label for="postcode"></label>

      <input type="text" name="postcode" id="postcode" readonly="readonly" /></td>

  </tr>

  <tr>

    <td>Rent</td>

    <td><input type="text" name="RentalPrice" id="RentalPrice" readonly="readonly" /></td>

  </tr>

and here are two feilds of the tenant part of the form

<td nowrap="nowrap" align="right">Title:</td>

            <td><input type="text" name="Title" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">First Names:</td>

            <td><input type="text" name="First_Names" value="" size="32" /></td>

so the way the form is set up for the tenant is slightly different to the prop_id.

this is the only reason why it wouldn't be working?

[bregent]

>i have done that but it still isnt working.

What are the symptoms now?

>i think it might be something to do with the fact that all the

>form feilds for the tenant DONT have id's but for the property they do.

I don't know what the symtoms are, but I doubt the problem is related to field id attribute. PHP will get the value from the name attribute, not id.

I'd really need to see the entire page and know the symptoms to troubleshoot the current problem and make code suggestions.

[Jonathan_Fortis]

basically it isnt coming up with an error code just not sending the information across and not redirecting to the next page

here is the full code for the temp for i am working on.

<?php require_once('Connections/nameprop.php'); ?>

<?php

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

  if (PHP_VERSION < 6) {

    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {

    case "text":

      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

      break;   

    case "long":

    case "int":

      $theValue = ($theValue != "") ? intval($theValue) : "NULL";

      break;

    case "double":

      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";

      break;

    case "date":

      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

      break;

    case "defined":

      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

      break;

  }

  return $theValue;

}

}

$editFormAction = $_SERVER['PHP_SELF'];

if (isset($_SERVER['QUERY_STRING'])) {

  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);

}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {

  $insertSQL = sprintf("INSERT INTO name_tenant (Title, `First Names`, Surname, DOB, DOBMonth, DOBYear, Nationality, Marital_Status, DayNumber, EvenNumber, MobNumber, Smoker, CurrentAdd, Status, PreviousAdd, Student, FinancialCCj, FinancialArrears, FinancialBankR, FinancialNone, BankName, AccountNum, SortCode, BankDuration, DebitCCCard, Electoral, `next of kin`, prop_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",

                       GetSQLValueString($_POST['Title'], "text"),

                       GetSQLValueString($_POST['First_Names'], "text"),

                       GetSQLValueString($_POST['Surname'], "text"),

                       GetSQLValueString($_POST['DOB'], "text"),

                       GetSQLValueString($_POST['DOBMonth'], "text"),

                       GetSQLValueString($_POST['DOBYear'], "text"),

                       GetSQLValueString($_POST['Nationality'], "text"),

                       GetSQLValueString($_POST['Marital_Status'], "text"),

                       GetSQLValueString($_POST['DayNumber'], "double"),

                       GetSQLValueString($_POST['EvenNumber'], "double"),

                       GetSQLValueString($_POST['MobNumber'], "double"),

                       GetSQLValueString($_POST['Smoker'], "text"),

                       GetSQLValueString($_POST['CurrentAdd'], "text"),

                       GetSQLValueString($_POST['Status'], "text"),

                       GetSQLValueString($_POST['PreviousAdd'], "text"),

                       GetSQLValueString($_POST['Student'], "text"),

                       GetSQLValueString($_POST['FinancialCCj'], "text"),

                       GetSQLValueString($_POST['FinancialArrears'], "text"),

                       GetSQLValueString($_POST['FinancialBankR'], "text"),

                       GetSQLValueString($_POST['FinancialNone'], "text"),

                       GetSQLValueString($_POST['BankName'], "text"),

                       GetSQLValueString($_POST['AccountNum'], "double"),

                       GetSQLValueString($_POST['SortCode'], "double"),

                       GetSQLValueString($_POST['BankDuration'], "text"),

                       GetSQLValueString($_POST['DebitCCCard'], "text"),

                       GetSQLValueString($_POST['Electoral'], "text"),

                       GetSQLValueString($_POST['next_of_kin'], "text"),

                       GetSQLValueString($_POST['prop_id'], "text"));

  mysql_select_db($database_nameprop, $nameprop);

  $Result1 = mysql_query($insertSQL, $nameprop) or die(mysql_error());

}

mysql_select_db($database_nameprop, $nameprop);

$query_rsApplyNow = "SELECT * FROM name_editprop";

$rsApplyNow = mysql_query($query_rsApplyNow, $nameprop) or die(mysql_error());

$row_rsApplyNow = mysql_fetch_assoc($rsApplyNow);

$totalRows_rsApplyNow = mysql_num_rows($rsApplyNow);

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<title>Untitled Document</title>

<script type="text/javascript">

function loadFields(theValue) {

         var props = theValue.split("|");

          var prop_id = props[0];

          var propAdd = props[1] ;

          var propPrice = props[2];

                      var town = props[3]

                      var postcode = props[4]

          document.getElementById('prop_id').value=prop_id;

          document.getElementById('Add1').value=propAdd;

                      document.getElementById('town').value=town;

                      document.getElementById('postcode').value=postcode;

          document.getElementById('RentalPrice').value='£' + propPrice +'/ per month';

}

</script>

</head>

<body>

<form action="<?php echo $editFormAction; ?>" method="post" name="form1" id="form1">

<!-- Place the following code where you want to show the result of the property selection --><table width="100%" border="0" cellpadding="0">

  <tr>

    <td width="23%"><!-- the following code is just copied from your code -->

      <select name="Select Property" id="Select Property" onchange="loadFields(this.value)">

        <option value="select your property">select your property</option>

        <?php

do {

?>

        <option value="<?php echo $row_rsApplyNow['prop_id'] . '|' . $row_rsApplyNow['prop_add1'] . '|' . $row_rsApplyNow['rental_price'] . '|' . $row_rsApplyNow['town'] . '|' . $row_rsApplyNow['postcode'];?>"><?php echo $row_rsApplyNow['prop_id'] . " , " . $row_rsApplyNow['prop_add1']; ?></option>

        <?php

} while ($row_rsApplyNow = mysql_fetch_assoc($rsApplyNow));

  $rows = mysql_num_rows($rsApplyNow);

  if($rows > 0) {

      mysql_data_seek($rsApplyNow, 0);

            $row_rsApplyNow = mysql_fetch_assoc($rsApplyNow);

  }

?>

      </select></td>

    <td width="77%"> </td>

  </tr>

  <tr>

    <td>Property ID:</td>

    <td><input type="text" name="prop_id" id="prop_id" readonly="readonly" /></td>

  </tr>

  <tr>

    <td>Address</td>

    <td><input type="text" name="Add1" id="Add1" readonly="readonly" /></td>

  </tr>

  <tr>

    <td>Town</td>

    <td><input type="text" name="town" id="town" readonly="readonly" /></td>

  </tr>

  <tr>

    <td>Postcode</td>

    <td><label for="postcode"></label>

      <input type="text" name="postcode" id="postcode" readonly="readonly" /></td>

  </tr>

  <tr>

    <td>Rent</td>

    <td><input type="text" name="RentalPrice" id="RentalPrice" readonly="readonly" /></td>

  </tr>

  <tr>

    <td> </td>

    <td> </td>

  </tr>

  <tr>

    <td colspan="2"><table width="100%" border="0" cellpadding="0">

      <tr>

        <td width="29%"><table align="center">

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">Title:</td>

            <td><input type="text" name="Title" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">First Names:</td>

            <td><input type="text" name="First_Names" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">Surname:</td>

            <td><input type="text" name="Surname" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">DOB:</td>

            <td><input type="text" name="DOB" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">DOBMonth:</td>

            <td><input type="text" name="DOBMonth" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">DOBYear:</td>

            <td><input type="text" name="DOBYear" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">Nationality:</td>

            <td><input type="text" name="Nationality" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">Marital_Status:</td>

            <td><input type="text" name="Marital_Status" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">DayNumber:</td>

            <td><input type="text" name="DayNumber" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">EvenNumber:</td>

            <td><input type="text" name="EvenNumber" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">MobNumber:</td>

            <td><input type="text" name="MobNumber" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">Smoker:</td>

            <td><input type="text" name="Smoker" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">CurrentAdd:</td>

            <td><input type="text" name="CurrentAdd" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">Status:</td>

            <td><input type="text" name="Status" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">PreviousAdd:</td>

            <td><input type="text" name="PreviousAdd" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">Student:</td>

            <td><input type="text" name="Student" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">FinancialCCj:</td>

            <td><input type="text" name="FinancialCCj" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">FinancialArrears:</td>

            <td><input type="text" name="FinancialArrears" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">FinancialBankR:</td>

            <td><input type="text" name="FinancialBankR" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">FinancialNone:</td>

            <td><input type="text" name="FinancialNone" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">BankName:</td>

            <td><input type="text" name="BankName" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">AccountNum:</td>

            <td><input type="text" name="AccountNum" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">SortCode:</td>

            <td><input type="text" name="SortCode" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">BankDuration:</td>

            <td><input type="text" name="BankDuration" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">DebitCCCard:</td>

            <td><input type="text" name="DebitCCCard" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">Electoral:</td>

            <td><input type="text" name="Electoral" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right">Next of kin:</td>

            <td><input type="text" name="next_of_kin" value="" size="32" /></td>

          </tr>

          <tr valign="baseline">

          </tr>

          <tr valign="baseline">

            <td nowrap="nowrap" align="right"> </td>

            <td><input type="submit" value="Insert record" /></td>

          </tr>

        </table></td>

        <td width="68%"> </td>

        <td width="3%"> </td>

      </tr>

      <tr>

        <td> </td>

        <td> </td>

        <td> </td>

      </tr>

    </table></td>

    </tr>

</table>

</form>

<input type="hidden" name="MM_insert" value="form1" />

</form>

<p> </p>

</body>

</html>

<?php

mysql_free_result($rsApplyNow);

?>

[Jonathan_Fortis]

as you suggested i am going to rewrite the tables so they are all uniform. i will then upload this, it will make it clearer

[Jonathan_Fortis]

ok, right got it working., i remade the whole database so now it works and all the information including the property ID appears in the tenant table. the property ID has its own colums. so the next thing i needed to achieve was to be able to get all the property information form that propertyID that is in the tenant table

is this the join part??

[bregent]

Great news!

>is this the join part??

Yes. When you want to SELECT the data from mulitple tables, you use a JOIN. Here's an example of pulling one column from each table using ANSI 89 syntax (I'm not sure of your table/column names):

SELECT First_Names, Town FROM name_tenant, property

WHERE name_tenant.prop_id = property.prop_id

[Jonathan_Fortis]

OK i have just lost my new data as the hard drive has gone down before i had time to back it up!!. so i will try this when i have rebuilt the form

where is this code created? can this be done using dreamweaver? or is it done in the database? of so where on the page is this interted into the document?

thanks so much for your help so far

[bregent]

>the hard drive has gone down before i had time to back it up!!

Ouch!

>where is this code created? can this be done using dreamweaver?

>or is it done in the database? of so where on the page is this interted

>into the document?

Where do you need to retrieve the data?  If you are displaying the data on the screen, then yes, you can use DW's advanced recordset to create a SELECT statement that joins multiple tables and output the results to the browser.

>thanks so much for your help so far

You're welcome.

[Jonathan_Fortis]

Ouch!

yeah i know. 2 terabites...

Where do you need to retrieve the data? it needs to be displayed on screen on a new page.

another quick one. the form needs to be sent to email aswell. is this done via a post to an email address?

thanks

[Jonathan_Fortis]

(I'm not sure of your table/column names)

>SELECT First_Names, Town FROM name_tenant, property

>WHERE name_tenant.prop_id = property.prop_id

ok my tables have the following names.

like i said i have got the prop_id to go into the table named - tenant_signup so in that table it has all the tenant information, the landlord information and the guarantor information aswell as a column named prop_id which displays the property ID from the table named name_editprop

so they are the names

so what would the statment look like if i wanted ...lets say the Title, name,email and password from the tenant_signup, and the prop_id, address, town,postcode, rental_price, RoomCode from the name_editprop table?

and where would i put that statement? could i open up the binding advanced recordet up and place it in there?

[bregent]

>so what would the statment look like if i wanted ...lets say the Title, name,

>email and password from the tenant_signup, and the prop_id, address,

>town,postcode, rental_price, RoomCode from the name_editprop table?

SELECT Title, name, email, password, prop_id, address, town, postcode, rental_price, RoomCode

FROM tenant_signup, name_editprop

WHERE tenant_signup.prop_id = name_editprop.prop_id

Note that if you have columns in both tables with the same names, then you need to qualify the column in the select list with the table, otherwise you don't. Example if both tables have a column named address, then you would qualify it like:

SELECT tenant_signup.address.....

Again, I would urge you to change the column names and stick with a consistent naming convention.

>and where would i put that statement? could i open

>up the binding advanced recordet up and place it in there?

You can use the advanced recordset to build the SQL. DW will add the code when you insert the recordset onto the page.

[Jonathan_Fortis]

i am going to change the property table then i will be the same format as the tenant table. but as it stands all column names in both tables are different.

i have added the above code to the SQL in the advanced recordset and the record set gives this error

MySQL Error#: 1052

Column 'prop_id' in field list is ambiguous

[Jonathan_Fortis]

SELECT Title, name, email, password, name_editprop.prop_id, name_editprop.prop_add1,  name_editprop.town,  name_editprop.postcode,  name_editprop.rental_price,  name_editprop.RoomCode

FROM tenant_signup, name_editprop

WHERE tenant_signup.prop_id = name_editprop.prop_id

i change it to this, so i specified the  name_editprop.for each feild coming for the property table

does this look correct?

[Jonathan_Fortis]

Thats work brilliant, thanks for you help. just another quick question.

I had already create a tenant unique page where this information would sit. To access the tenant page i have a tenant list page ( this shows a list of tenants) when you click on a tenant information (show button) you want to view it brings up their unique tenant page

the link i was using before from the tenant list was

tenant-info.php?recordID=<?php echo $row_Recordset1['userid']; ?>

and the recordset on the tenant-info.php was

SELECT *

FROM plus_signup

WHERE userid = colname

now i have the following SQL

SELECT Title, name, email, password, name_editprop.prop_id, name_editprop.prop_add1,  name_editprop.town,  name_editprop.postcode,  name_editprop.rental_price,  name_editprop.RoomCode

FROM tenant_signup, name_editprop

WHERE tenant_signup.prop_id = name_editprop.prop_id

the above link doesnt work. When you click the link it justs show the first item in the database with a property ID

i think i need to add to the Link but dont know what?

can you help?

[Jonathan_Fortis]

i have just added the userid as this was missing on the

SELECT Title, name, email, password, name_editprop.prop_id, name_editprop.prop_add1,  name_editprop.town,  name_editprop.postcode,  name_editprop.rental_price,  name_editprop.RoomCode

FROM tenant_signup, name_editprop

WHERE tenant_signup.prop_id = name_editprop.prop_id

and the FROM should read tenant NOT plus

[Jonathan_Fortis]

the other thing i have noticed is the old SAL statement has a variable

WHERE userid = colname

that isnt in the new SQL statement. does this need to go in ?

[bregent]

>the above link doesnt work. When you click the link it justs

>show the first item in the database with a property ID

Correct, the SQL would also have to include the php variable that you want to filter on in the WHERE clause.

>the other thing i have noticed is the old SAL statement has a variable

>

>WHERE userid = colname

>

>that isnt in the new SQL statement. does this need to go in ?

Yup.

[bregent]

>SELECT Title, name, email, password, name_editprop.prop_id, name_editprop.prop_add1,  name_editprop.town,  >name_editprop.postcode,  name_editprop.rental_price,  name_editprop.RoomCode

>FROM tenant_signup, name_editprop

>WHERE tenant_signup.prop_id = name_editprop.prop_id

>

>

>i change it to this, so i specified the  name_editprop.for each feild coming for the property table

>

>does this look correct?

Sort of correct, and it would work. But as I mentioned, you only need to qualify the column in the SELECT list with the table name if the column names are ambiguous. That is, if select list has column names that appear in both tables. However, many folks will qualify all columns in the select list regardless. Either way is fine. However, in your statement you only qualified the columns from the name_editprop table, and not the tenant_signup table. ( the prop_id is the only column that appears in both tables). So again, be consistent. Either qualify all columns, or only the ambiguous columns.