Help with logic layout of fields and tables please

[jonnypixel]

Hi There,

 

Im making a very basic realestate property listings script,

 

I would like to create a listing and then attach leads to a listing. How would i go about removing a lead from a listing without deleting it?

 

My field name that connects the 2 tables so far is just lead_listingID - but im wondering if i need to add something else?

 

Im currently just showing the page by checking if a Listing has the same ID as the lead_listingID and then displaying that listing with the leads that are connected.

 

I just dont know the logic i need to write on how to remove a lead from the listing without actually trashing the lead?

 

I have the following tables so far:

 

• listing ( listingID, listingName, listingDesc )
   
  
• lead    ( leadID, lead_listingID, leadName, leadDesc )
  

 

The first field in both my listing table and my lead table are set to auto increment.

 

If i havent provided enough info please let me know and i will post more of the data as i complete the forms that will post the values to the fields in a MYSQL database.

 

Here is the php page code so far...

 

<?php
if ($servicecall != true) die('An error has occured. Please ensure you are logged in and try again.');
$urlListing = $_GET['listingURLidentifier'];



$query = "SELECT * FROM listing WHERE listingID=".$urlListing; 


$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) {
$listingName = $row['listingName'];
$listingDesc = $row['listingDesc'];

	print ('<h2>Listing details</h2>');
print('<b>Listing Name:</b> '.$listingName.'<br><br><b>Listing Description:</b> '.$listingDesc.'');


}	

print('<br><br><br><br><br><br>');

$query = "SELECT * FROM lead WHERE lead_listingID=".$urlListing; 
$result = mysql_query($query);
print ('<h2>Leads Attached to this listing</h2>');

if ( mysql_num_rows($result) > 0 ) {


while ($row = mysql_fetch_array($result)) {
$leadName = $row['leadName'];
$leadDesc = $row['leadDesc'];


print('<b>Lead Name:</b> '.$leadName.'<br><br><b>Lead Description:</b> '.$leadDesc.'<br>-------------------------------------------------------------------------------------------------------------------------<br>');
}
}


?>

 

Please help a begginer...

 

 

[Psycho]

Just create another field for the listing table to identify if the lead is active or not. Change the status from true to false to remove the active lead.

 

Then change your query as follows:

 

$query = "SELECT * FROM lead WHERE lead_listingID={$urlListing} and active=1";

[Catfish]

I would add an additional field in the lead table, something like "active" which can be either on or off, true or false, 0 or 1. whatever you like.

 

Then a lead needs to: 1. match the listingID and 2. be "active" to be displayed. To remove a lead from a listing, simply set active value to off or false. That way you are not removing the lead from the table, just changing the value of 1 field.

 

^^^ What he said (posted while typing)

[jonnypixel]

Thankyou guys!

 

I just came back here to find that you two cool people have given me some advice.

 

Really appreciate that.