Page 1 of 1
using the results of 1 query to find the results in another
Posted: Thu Jan 29, 2004 6:27 am
by nutstretch
I have a query which gets me a set of results from another table.
I need then to go through each of the records in turn and fetch results from another tables and display these.
Table one holds the ID to a county
Table two shows that county and those that surround it using the ID from one as a foriegn key in table two. Two fields 1 CountyID(FK) and CountyNear.
Table three holds details of customers
I want to find each of those countynear results in turn and check table three to display the results of all records which match each of the countynear records.
what is the best way of doing this. I feel i want to do a nested loop but am unsure of the syntax.
Anyone that can point me in the correct direction?
Thanks in anticipation
Posted: Thu Jan 29, 2004 7:20 am
by McGruff
Is table 2 like this..
county_id | adjacent_id
..and with a row for every adjacent_id related to each county_id?
PS: ought to be able to do it one query.
Posted: Thu Jan 29, 2004 7:29 am
by nutstretch
It uses a name instead of adjacent _id. Do you think I would be better using an Adjacent_ID instead of a name?
i want to populate a variable $countynear1 with the value of the currents rows countnear data. How do i parse the value to my new variable $countynear1. It keps telling me I have an error.
I am trying
$countynear1 = .$row['CountyNear'].;
anyhelp appreciated
Posted: Thu Jan 29, 2004 9:51 am
by jaxn
Just a hunch, but if you are running two queries are you using different variable names for the result and the rows? i.e.:
Code: Select all
<?php
$result1 = mysql_query($sql1);
while ($row1 = mysql_fetch_array($result1)) {
$sql2 = "...";
$result2 = mysql_query($sql2);
while ($row2 = mysql_fetch_array($result2)) {
....
}
}
?>
If your queries are nested like that and you don't have different variable names it will produce the kind of error I think you are describing.
Though seeing some of the problem code always helps.
-Jackson
Posted: Thu Jan 29, 2004 12:00 pm
by nutstretch
I am now getting some results but only from some records when I know there should be more than those coming through.
The table i am querying for the second sql has 4 records that i know should present. The code doesn't seem to be looping properly so i feel i must have something wrong. It is not finding the county that the user has chosen just one of the other 7
$resultID = mysql_query("SELECT * FROM tblcountyname where countyID = '$county'", $linkID)or die(mysql_error());
print "<table border=0 ><tr><th> CountyNear</th>";
print "<th> RetailerName</th>" ;
print "<tr>";
while ($row = mysql_fetch_array($resultID, MYSQL_ASSOC))
$countyname1 = $row['CountyNear'];
{
$resultID1= mysql_query("SELECT * FROM tblretailer WHERE RetailerCounty LIKE '$countyname1'", $linkID) or die(mysql_error());
while ($row2 = mysql_fetch_array($resultID1, MYSQL_ASSOC))
{
print "<td>".$row2['RetailerCounty']."</td>";
print "<td>".$row2['RetailerName']."</td>";
print "<tr>";
}
}
print"</table>";
mysql_close($linkID);
response appreciated
Posted: Thu Jan 29, 2004 8:05 pm
by microthick
It looks like an HTML issue, rather than a database query issue. I think the HTML for your table isn't being generated properly.
Try this:
Code: Select all
$resultID = mysql_query("SELECT * FROM tblcountyname where countyID = '$county'", $linkID)or die(mysql_error());
echo "<table border=0>\n";
echo " <tr>\n";
echo " <th>CountyNear</th><th>RetailerName</th>\n" ;
echo " </tr>\n";
while ($row = mysql_fetch_array($resultID, MYSQL_ASSOC))
$countyname1 = $rowї'CountyNear'];
{
$resultID1= mysql_query("SELECT * FROM tblretailer WHERE RetailerCounty LIKE '$countyname1'", $linkID) or die(mysql_error());
while ($row2 = mysql_fetch_array($resultID1, MYSQL_ASSOC))
{
echo " <tr>\n";
echo " <td>".$row2ї'RetailerCounty']."</td>\n";
echo " <td>".$row2ї'RetailerName']."</td>\n";
echo " <\tr>\n";
}
}
echo "</table>\n";
mysql_close($linkID);
After, View Source to see that what you see on screen is what is actually also in the source.
Posted: Thu Jan 29, 2004 8:54 pm
by McGruff
Have you tried to figure out a single JOIN query?
Query logic saves a lot of messing about in php
http://www.oreilly.com/catalog/javadtab ... r/ch02.pdf