displaying data from a third table
Cheers Jason!
I now have the foreign key issue sorted after your post correcting my school boy error!
I am now using the primary key in the "enquiries table" which is "enquiry_id" as the foreign key in the "property_enquiries" table
I now need to take the project to the next stage by introducing a "Further Correspondence Form" that will sit at the bottom of the page that displays data from the 'enquiries' table and the 'property_enquiries' table.
this page will be used when there is further correspondence to the original enquiry where the company rep will select the enquiry from the drop down list at the top. This list displays each 'Estate name' associated with each enquiry. I am using the following code for this drop down.
mysql_select_db($database_RTMFadmin, $RTMFadmin);
$query_rsEstate_name = "SELECT property_enquiries.estate_name, property_enquiries.enquiry_id, enquiries.enquiry_id FROM property_enquiries, enquiries WHERE property_enquiries.enquiry_id = enquiries.enquiry_id";
$rsEstate_name = mysql_query($query_rsEstate_name, $RTMFadmin) or die(mysql_error());
$row_rsEstate_name = mysql_fetch_assoc($rsEstate_name);
$totalRows_rsEstate_name = mysql_num_rows($rsEstate_name);
The drop down looks like this -
<ul class="dropdown-menu">
<?php
// RepeatSelectionCounter_1 Begin Loop
$RepeatSelectionCounter_1_IterationsRemaining = $RepeatSelectionCounter_1_Iterations;
while($RepeatSelectionCounter_1_IterationsRemaining--){
if($RepeatSelectionCounterBasedLooping_1 || $row_rsEstate_name){
?>
<li><a href="enquiries.php?enquiry_id=<?php echo $row_rsEstate_name['enquiry_id']; ?>"><?php echo $row_rsEstate_name['estate_name']; ?></a></li>
<?php
} // RepeatSelectionCounter_1 Begin Alternate Content
else{
?>
<?php } // RepeatSelectionCounter_1 End Alternate Content
if(!$RepeatSelectionCounterBasedLooping_1 && $RepeatSelectionCounter_1_IterationsRemaining != 0){
if(!$row_rsEstate_name && $RepeatSelectionCounter_1_Iterations == -1){$RepeatSelectionCounter_1_IterationsRemaining = 0;}
$row_rsEstate_name = mysql_fetch_assoc($rsEstate_name);
}
$RepeatSelectionCounter_1++;
} // RepeatSelectionCounter_1 End Loop
?>
</ul>
By selecting an estate name will display the associated enquiry.
To display the correct data I am using the following query.
$varEnquiryID_rsEnquiry = "-1";
if (isset($_GET['enquiry_id'])) {
$varEnquiryID_rsEnquiry = $_GET['enquiry_id'];
}
mysql_select_db($database_RTMFadmin, $RTMFadmin);
$query_rsEnquiry = sprintf("SELECT * FROM enquiries, property_enquiries WHERE enquiries.enquiry_id = property_enquiries.enquiry_id AND enquiries.enquiry_id = %s", GetSQLValueString($varEnquiryID_rsEnquiry, "int"));
$rsEnquiry = mysql_query($query_rsEnquiry, $RTMFadmin) or die(mysql_error());
$row_rsEnquiry = mysql_fetch_assoc($rsEnquiry);
$totalRows_rsEnquiry = mysql_num_rows($rsEnquiry);
As you can see I am using a variable to retrieve the correct data from the specific drop down 'enquiry_id.
Some of this code might not be totally correct but it seems to work.
The problem I now have is trying to link up the further correspondence with the page.
When the 'further correspondence' form is submitted it will be sent to a THIRD table "enq_correspondence" in the Database. So then when a selection is made from the drop down list the correspondence is displayed at the bottom of the original enquiry.
I believe this might need to use an INNER JOIN in the tables but I cannot seem to get it to work. I assume I need to do this in the above samples but this is beyond my comfort zone/ ability.
Its worth mentioning that I have also used the primary key in the "enquiries" table as a foreign key in the "enq_correspondence" table
I have attached images of the enquiry form and the data display page with 'further correspondence' form.
If this requires paid support I am happy to do so!
Cheers!
