query within a query will not return the correct fields

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
dpayton
Forum Newbie
Posts: 10
Joined: Fri Jun 24, 2005 1:48 pm

query within a query will not return the correct fields

Post by dpayton »

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.

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']}; ";
From this part of the query, I get the correct results. The next part is where I get the problem.

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']} (";
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.

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']}; ";
[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]

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]};
}
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:

Code: Select all

$query2 = "Select phonenumber, personid from personphonejoin where personid = {$row['personid']}";
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.


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>";

}
?>
Thanks in advance for your help,

Dawn
Last edited by dpayton on Fri Jun 24, 2005 2:36 pm, edited 1 time in total.
User avatar
dethron
Forum Contributor
Posts: 370
Joined: Sat Apr 27, 2002 11:39 am
Location: Istanbul

Post by dethron »

if you want us to help you, first help us....

Read the following first : viewtopic.php?t=21171
User avatar
dethron
Forum Contributor
Posts: 370
Joined: Sat Apr 27, 2002 11:39 am
Location: Istanbul

Post by dethron »

why dont you echo the query2 and send the result manually to db, i mean using phpmyadmin?
dpayton
Forum Newbie
Posts: 10
Joined: Fri Jun 24, 2005 1:48 pm

Post by dpayton »

I have never used phpmyadmin. However, I did print the results of the query and it isn't returning the phone number at all. Is that what you mean?

Also, did I change the post to the format that you wanted?
User avatar
dethron
Forum Contributor
Posts: 370
Joined: Sat Apr 27, 2002 11:39 am
Location: Istanbul

Post by dethron »

dpayton wrote:Also, did I change the post to the format that you wanted?
Well, you are a quickstone, learning fast ;)

Lets continue on solving your problem, you have the following line

Code: Select all

$query2 = "Select * from personphonejoin where personid = {$row['personid']}";
replace it by the following

Code: Select all

echo $query2 = "Select * from personphonejoin where personid = {$row['personid']}";
this will write the query to the screen, so you can check it manuall.
can you tell me the query?
dpayton
Forum Newbie
Posts: 10
Joined: Fri Jun 24, 2005 1:48 pm

Post by dpayton »

Here are the results:

Select * from personphonejoin where personid = 00002

An aside, I am not that quick. I could not figure out which tag the above should have been placed in.
User avatar
dethron
Forum Contributor
Posts: 370
Joined: Sat Apr 27, 2002 11:39 am
Location: Istanbul

Post by dethron »

what is the type of personid in your db?
dpayton
Forum Newbie
Posts: 10
Joined: Fri Jun 24, 2005 1:48 pm

Post by dpayton »

smallint[5] unsigned for the personphonejoin table

and

smallint[5] unsigned zerofill for people table
User avatar
dethron
Forum Contributor
Posts: 370
Joined: Sat Apr 27, 2002 11:39 am
Location: Istanbul

Post by dethron »

Instead of this

Code: Select all

Select * from personphonejoin where personid = 00002
modify the query string like that

Code: Select all

<?php
    $query2 = "SELECT * FROM personphonejoin WHERE personid = 2";
?>
i am sure you got the idea,,,,
dpayton
Forum Newbie
Posts: 10
Joined: Fri Jun 24, 2005 1:48 pm

Post by dpayton »

i changed it manually to

Code: Select all

$query2 = "Select * from personphonejoin where personid = \"2\"";
User avatar
dethron
Forum Contributor
Posts: 370
Joined: Sat Apr 27, 2002 11:39 am
Location: Istanbul

Post by dethron »

worked? or still no result?
dpayton
Forum Newbie
Posts: 10
Joined: Fri Jun 24, 2005 1:48 pm

Post by dpayton »

sorry. Half of my above message got cut off. when I changed the code manually, I still get no results for phonenumber.
dpayton
Forum Newbie
Posts: 10
Joined: Fri Jun 24, 2005 1:48 pm

Post by dpayton »

I figured it out. I omitted the following code:

Code: Select all

$row2 = mysql_fetch_array($query_result2);

Thanks!

Dawn
User avatar
dethron
Forum Contributor
Posts: 370
Joined: Sat Apr 27, 2002 11:39 am
Location: Istanbul

Post by dethron »

sorry dude, i had to go to help one of my friend, and it seems you have solved the problem, right?

then, why dont you label your subject by adding [SOLVED] :)

we are waiting to help you more in near future ;)

happy coding....
Post Reply