using the results of 1 query to find the results in another

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
nutstretch
Forum Contributor
Posts: 104
Joined: Sun Jan 11, 2004 11:46 am
Location: Leicester

using the results of 1 query to find the results in another

Post 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
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post 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.
nutstretch
Forum Contributor
Posts: 104
Joined: Sun Jan 11, 2004 11:46 am
Location: Leicester

Post 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
jaxn
Forum Commoner
Posts: 55
Joined: Fri Jan 16, 2004 1:50 pm
Location: Nashville, TN

Post 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
nutstretch
Forum Contributor
Posts: 104
Joined: Sun Jan 11, 2004 11:46 am
Location: Leicester

Post 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
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post 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&#1111;'CountyNear']; 

&#123; 
$resultID1= mysql_query("SELECT * FROM tblretailer WHERE RetailerCounty LIKE '$countyname1'", $linkID) or die(mysql_error()); 
while ($row2 = mysql_fetch_array($resultID1, MYSQL_ASSOC)) 
&#123; 
echo "   <tr>\n";
echo "      <td>".$row2&#1111;'RetailerCounty']."</td>\n"; 
echo "      <td>".$row2&#1111;'RetailerName']."</td>\n"; 
echo "   <\tr>\n"; 
&#125; 
&#125; 
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.
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post 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
Post Reply