Hi all.
Back again,
I have 2 tables.
TABLE 1:
chandos_agents
FIELDS
agent_id (primary key)
agent_contact
agent_name
agent_location_id
and so on
TABLE 2:
chandos_agents_location
FIELDS
agent_id (primary key)
agent_location
I have the following SQL statement:
<?php require_once('../Connections/Chandos.php'); ?>
<?php
$maxRows_agents_results = 1;
$pageNum_agents_results = 0;
if (isset($_GET['pageNum_agents_results'])) {
$pageNum_agents_results = $_GET['pageNum_agents_results'];
}
$startRow_agents_results = $pageNum_agents_results * $maxRows_agents_results;
$colname_agents_results = "1";
if (isset($_GET['agent_location_id'])) {
$colname_agents_results = (get_magic_quotes_gpc()) ? $_GET['agent_location_id'] : addslashes($_GET['agent_location_id']);
}
mysql_select_db($database_Chandos, $Chandos);
$query_agents_results = sprintf("SELECT chandos_agents.*, chandos_agents_location.agent_location FROM chandos_agents JOIN chandos_agents_location WHERE chandos_agents.agent_location_id = '%s'", $colname_agents_results);
$query_limit_agents_results = sprintf("%s LIMIT %d, %d", $query_agents_results, $startRow_agents_results, $maxRows_agents_results);
$agents_results = mysql_query($query_limit_agents_results, $Chandos) or die(mysql_error());
$row_agents_results = mysql_fetch_assoc($agents_results);
if (isset($_GET['totalRows_agents_results'])) {
$totalRows_agents_results = $_GET['totalRows_agents_results'];
} else {
$all_agents_results = mysql_query($query_agents_results);
$totalRows_agents_results = mysql_num_rows($all_agents_results);
}
$totalPages_agents_results = ceil($totalRows_agents_results/$maxRows_agents_results)-1;
?>
What I am trying to do is:
show the results with all the fields from "chandos_agents" and only "agent_location" from "chandos_agents_location".
The are records in the "chandos_agents" table that have the same "agent_location_id" because the there are more than one agent in each country location, one-to-many.
The problem is, the results show the correct record for the "chandos_agent" but shows the same "agent_location from the chandos_agents_location table.
PLUS, if there are 2 or more agents that have the same "agent_location_id" only one record is shown in the results.
I know this post is long winded, but I do neew help with this.
If you can it would be great.
Kind regards, keep safe and well.
Dereck
