query within a query will not return the correct fields
Posted: Fri Jun 24, 2005 2:23 pm
I am trying to run a query within a query and cannot get the results that I am looking for. For some reasons, the second query will only pull one of the two columns that I am asking for. Below is the entire code in brown. Then in blue and red, I broke down the results I get from the code and what I have already done to try to fix the problem.
From this part of the query, I get the correct results. The next part is where I get the problem.
The above query is where the problem starts. Nothing from this query displays. I have tried a few differents things to identify the problem.
First, I changed the first query results to the following.
[color = red]It returned a value for personid, so I know that the variable is defined.
Second, I just displayed the results from $query2:[/color]
This code returned the personid only, which is position [0] in the array. It is not finding the phonenumber (which is position[1] in the array) at all and is what I am trying to display.
Third, I changed the mysql statement in $query2 to:
It still isn't returning the phone number.
Finally, I have checked the database and a phonenumber column exits. I spelled it correctly and have checked all of the obvious things.
I cannot tell if the third query below is working becuase I cannot get past the second.
Thanks in advance for your help,
Dawn
Code: Select all
$query = "Select * from contactsitejoin join people on people.personid = contactsitejoin.personid where siteid = $siteid";
$query_result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($query_result);
while($row = mysql_fetch_array($query_result)) {
print "<p><strong>{$row['position']}</strong> {$row['title']}{$row['fname']} {$row['lname']}; ";
$query2 = "Select * from personphonejoin where personid = {$row['personid']}";
$query_result2 = mysql_query($query2) or die(mysql_error());
$row2 = mysql_fetch_array($query_result2);
while($row2 = mysql_fetch_array($query_result2)) {
print "Phone: {$row2['phonenumber']} (";
$query3 = "Select * from phone where phonenumber = {$row2['phonenumber']}";
$query_result3 = mysql_query($query3) or die(mysql_error());
$row3 = mysql_fetch_array($query_result);
while ($row3 = mysql_fetch_array($query_result3)) {
print "{$row3['phoneclass']} ";
}
}
print "</p>";
}
?>Code: Select all
$query = "Select * from contactsitejoin join people on people.personid = contactsitejoin.personid where siteid = $siteid";
$query_result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($query_result);
while($row = mysql_fetch_array($query_result)) {
print "<p><strong>{$row['position']}</strong> {$row['title']}{$row['fname']} {$row['lname']}; ";Code: Select all
$query2 = "Select * from personphonejoin where personid = {$row['personid']}";
$query_result2 = mysql_query($query2) or die(mysql_error());
$row2 = mysql_fetch_array($query_result2);
while($row2 = mysql_fetch_array($query_result2)) {
print "Phone: {$row2['phonenumber']} (";First, I changed the first query results to the following.
Code: Select all
$row = mysql_fetch_array($query_result);
while($row = mysql_fetch_array($query_result)) {
print "<p><strong>{$row['personid']}{$row['position']}</strong> {$row['title']}{$row['fname']} {$row['lname']}; ";Second, I just displayed the results from $query2:[/color]
Code: Select all
$query2 = "Select * from personphonejoin where personid = {$row['personid']}";
$query_result2 = mysql_query($query2) or die(mysql_error());
$row2 = mysql_fetch_array($query_result2);
if($row2) {
print "{$row2[0]} {$row2[1]};
}Third, I changed the mysql statement in $query2 to:
Code: Select all
$query2 = "Select phonenumber, personid from personphonejoin where personid = {$row['personid']}";Finally, I have checked the database and a phonenumber column exits. I spelled it correctly and have checked all of the obvious things.
I cannot tell if the third query below is working becuase I cannot get past the second.
Code: Select all
$query3 = "Select * from phone where phonenumber = {$row2['phonenumber']}";
$query_result3 = mysql_query($query3) or die(mysql_error());
$row3 = mysql_fetch_array($query_result);
while ($row3 = mysql_fetch_array($query_result3)) {
print "{$row3['phoneclass']} ";
}
}
print "</p>";
}
?>Dawn