creating new recordset based on retrieved data

chieffan

I am pulling in real estate listings from a particular database table. Along with listing information, this table includes an agent's name.

This page is created dynamically based off the MLS number for the property as seen here http://www.burnettrealty.net/listings/details.php?MLN=62142192.

I also have another database table that has all the agent information like agent name, email, phone, etc.

What I want to do is find a way to attach that agent name from the listings, to the information in the agents table.

My current sql query does this SELECT * FROM real_estate WHERE MLN = %s
the agents field is broken into ListingAgentFirstName and ListingAgentLastName

In the second query I have "SELECT agent_name, email, FROM agents;

The end result is I want a user to be able to click on an email link for the agent to email them.

Thanks in advance.

Roger_Ramjet

You do this in 1 query with a JOIN. You need to learn about joins and how to use them or you will not be able to use half of the functionality built into relational databases

chieffan

Thanks, Roger.

I have been working on this and initially the agent name in one table is broken out into first and last name, so I created variables to bring this together.

<?php $first= $row_rs_searchResults['ListingAgentFirstName']; ?> <?php $last= $row_rs_searchResults['ListingAgentLastName']; ?> <?php $fullname=$first. " ".$last;?>

I looked at the joins and this looks like a simple select from the mySQL example, so I was trying

$colname_rs_agents = "$fullname";
if (isset($SERVER['fullname'])) {
$colname_rs_agents = (get_magic_quotes_gpc()) ? $SERVER['fullname'] : addslashes($_SERVER['fullname']);
}
mysql_select_db($database_listings, $listings);
$query_rs_agents = sprintf("SELECT agent_name, email FROM Burnettagents WHERE agent_name = '%s'", $colname_rs_agents);
$rs_agents = mysql_query($query_rs_agents, $listings) or die(mysql_error());
$row_rs_agents = mysql_fetch_assoc($rs_agents);
$totalRows_rs_agents = mysql_num_rows($rs_agents);
?>

Am I close here? I know I need to get the names concatanated together before I can do any match. Now the name has been returned it should just be matching the full name variable with the full name from the recordset.

Thank you.

Roger_Ramjet

You should know about the string functions in MySQL . You can do this name manipulation within the query

$sql = "SELECT r.*, b.agent_name, b.email, CONCAT_WS(' ', r.ListingAgentFirstName, r.ListingAgentLastName) AS f
            FROM Burnettagents AS b INNER JOIN  real_estate AS r
            ON b.agent_name = f
            WHERE r.MLN= $var";

May have to name all columns to select instead of using the *, I forget in mysql.

chieffan

Thanks, I will try this.