Nesting queries inside a "While Loop" (only returns 1 result)

Korizon67 · Jun 13, 2008

I have a query I am attempting that allows a manager to hover on an icon and see who the last assigned user to a patient was, I am nesting a loop inside a loop, it works but only on the first result... I have done this in the past and had it work fine but cant seem to see my mistake here.

the '102023' is just a known patient ID number, normall it would have the variable pulled from $r["firstquerycolumn"]

Code:

<table cellpadding="0" cellspacing="0">
<tr>
<td class="header" style="border:solid black 1px;border-right:none;"><b>Patient</b></td>
<td style="border:solid black 1px;border-left:none;"><b>&nbsp;</b></td>
<td class="header" style="border:solid black 1px"><b>Chart#</b></td>
<td class="header" style="border:solid black 1px"><b>Alt. Chart#</b></td>
<td class="header" style="border:solid black 1px"><b>Sx Date</b></td>
<td class="header" style="border:solid black 1px"><b>Concern Date</b></td>
<td class="header" style="border:solid black 1px"><b>Concern Time</b></td>
<td class="header" style="border:solid black 1px"><b>PSA</b></td>
<td class="header" style="border:solid black 1px"><b>Assign To</b></td>
<td style="border:solid black 1px">&nbsp;</td>
</tr>
<?
include '../dbcon/pcc_config.php';
include '../dbcon/pcc_opendb.php';
$color22 = "#B7DBEC"; 
$color11 = "#FFFFFF";
$row_count = 0;
// Start Main Query
$result = mysql_query("SELECT pcc_concerns.*, pcc_patient.*, pcc_patient_sx.* FROM pcc_concerns JOIN pcc_patient ON pcc_patient.pcc_patient_contact_id = pcc_concerns.pcc_concerns_contact_id JOIN pcc_patient_sx on pcc_patient_sx.pcc_patient_sx_id = pcc_concerns.pcc_concerns_sx_id WHERE pcc_concerns_assigned_status = '0'");
while($r=mysql_fetch_array($result))
{

variables here....
$row_color = ($row_count % 2) ? $color11 : $color22; 

echo "<form name=\"" . $loader . "\" id=\"" . $loader . "\" action=\"\" method=\"post\" target=\"_self\">\n". 
     "<tr bgcolor=\"$row_color\">\n".
     "<td style=\"border:solid black 1px; border-right:none\"> " . $pcc_patient_lname . ", " . $pcc_patient_fname . "&nbsp;</td>\n".
     "<td style=\"border:solid black 1px; border-left:none\"><img src=\"../images/info.gif\" onMouseOver=\"showTip('ttip" . $loader. "ttip');\" onMouseOut=\"hideTip('ttip" . $loader. "ttip');\" onMouseMove=\"positionElement('ttip" . $loader. "ttip');\"></td>\n".
     "<div id=\"ttip" . $loader. "ttip\" class=\"tooltip\"><font color=\"#000000\" size=\"2\">Latest PA Info: <br /><br />";
// Start secondary query	  
$inner_result = mysql_query("SELECT pcc_concerns_date, pcc_concerns_prac_int FROM pcc_concerns WHERE pcc_concerns_contact_id = '102023' ORDER BY pcc_concerns_date desc");
while($ir=mysql_fetch_array($inner_result))
{
echo $ir["pcc_concerns_date"] . " " . $ir["pcc_concerns_prac_int"]  . "<br />"; 
}
// End secondary query	

// Continue Main Query
echo "</font></div>\n".
     "<td style=\"border:solid black 1px\"><div align=\"center\"> " . $pcc_patient_sx_chart_id . "&nbsp;</div></td>\n" .
     "<td style=\"border:solid black 1px\"><div align=\"center\"> " . $pcc_patient_sx_alt_chart_id . "&nbsp;</div></td>\n" .
     "<td style=\"border:solid black 1px\"><div align=\"center\"> " . $pcc_patient_sx_sx_date_echo . "&nbsp;</div></td>\n" .
     "<td style=\"border:solid black 1px\"><div align=\"center\"> " . $pcc_concerns_date_echo . "&nbsp;</div></td>\n" .
     "<td style=\"border:solid black 1px\"><div align=\"center\"> " . $pcc_concerns_time . "&nbsp; </div></td>\n" .
     "<td style=\"border:solid black 1px\"><div align=\"center\"> " . $pcc_patient_manager . "&nbsp;</div></td>\n" .
     "<td style=\"border:solid black 1px\"><select name=\"pcc_concerns_prac_int\">\n<option value=\"\">Select</option>\n"; 
 include 'grab_pa.php';
echo "</select></td>\n".
     "<td style=\"border:solid black 1px\"><input type=\"button\" onClick=\"document.$pcc_patient_fname$pcc_patient_care_id$pcc_patient_contact_id$pcc_concerns_id$pcc_patient_fname.action='insert_assign_pa.php'; document.getElementById('" . $loader . "').submit();\" style=\"background-color:$row_color;\" value=\"Assign\"></td>\n".
      "</tr><input type=\"hidden\" name=\"search_id\" value=\"$pcc_concerns_id\"></form>\n";
$row_count++;
}
include '../dbcon/pcc_closedb.php';
?>
</table>

I am not sure what I am missing, maybe this isnt possible but it seems it should be. The result is the first row has the information is should.. the rest of the rows only show the hover div with the preset text in them.

I am baffled, I am sure its something stupidly easy, but I am not seeing it.

Thanks,

Mike

Stretchwickster · Jun 13, 2008

Just a few observations, and I'm not sure that they will necessarily solve your problem:
1) Use <?php rather than <? when opening a PHP section.
2) The various variables used in the main loop (such as $pcc_patient_lname), I would incorporate the $r prefix (e.g. $r['pcc_patient_lname']).
3) Get rid of the deprecated <font> tags and replace with CSS.
4) You may find your code easier to read if you output blocks of HTML outside of your PHP sections and insert PHP sections (<?php ... ?>) where you need to reference a PHP variable. Just a suggestion, this can be a matter of personal preference, but I find code easier to read without all the extra backslashes.

Clive

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"To err is human, but to really foul things up you need a computer." (Paul Ehrlich)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To get the best answers from this forum see: faq102-5096

Korizon67 · Jun 13, 2008

My code works fine other than the loop... I will clean it up when it works, the question was why the second query only has a result for the first row... it doesn't grab the info on the next loop through. From what i can gather the $r["pcc_concerns_contact_id"] is empty on the second loop. Will it not carry into the second loop?

Code:

$result = mysql_query("SELECT pcc_concerns.*, pcc_patient.*, pcc_patient_sx.* FROM pcc_concerns JOIN pcc_patient ON pcc_patient.pcc_patient_contact_id = pcc_concerns.pcc_concerns_contact_id JOIN pcc_patient_sx on pcc_patient_sx.pcc_patient_sx_id = pcc_concerns.pcc_concerns_sx_id WHERE pcc_concerns_assigned_status = '0'");
while($r=mysql_fetch_array($result))
{  

*start first loop html*

// Start second loop <<< This query only runs on the first loop

$inner_result = mysql_query("SELECT pcc_concerns_date, pcc_concerns_prac_int FROM pcc_concerns WHERE pcc_concerns_contact_id = '$r["pcc_concerns_contact_id"]' ORDER BY pcc_concerns_date desc");
while($ir=mysql_fetch_array($inner_result))
{
echo $ir["pcc_concerns_date"] . " " . $ir["pcc_concerns_prac_int"]  . "<br />"; 
}

// End of second loop

*continue first loop html*
}

Korizon67 · Jun 13, 2008

the second query actually is like this...

Code:

$inner_result = mysql_query("SELECT pcc_concerns_date, pcc_concerns_prac_int FROM pcc_concerns WHERE pcc_concerns_contact_id = '". $r["pcc_concerns_contact_id"] . "' ORDER BY pcc_concerns_date desc");
while($ir=mysql_fetch_array($inner_result))

Stretchwickster · Jun 13, 2008

Try changing the $inner_result query to this:

Code:

$inner_result = mysql_query('SELECT pcc_concerns_date, pcc_concerns_prac_int FROM pcc_concerns WHERE pcc_concerns_contact_id = ' . $r['pcc_concerns_contact_id'] . ' ORDER BY pcc_concerns_date desc');

Does this make any difference?

Clive

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"To err is human, but to really foul things up you need a computer." (Paul Ehrlich)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To get the best answers from this forum see: faq102-5096

jpadie · Jun 13, 2008

looks fine.

have you checked the queries in a browser (like phpmyadmin) to see what results you get from the raw queries? it may be that you are only getting a single result because that is all there are. you might try outputting the row count as well.

Korizon67 · Jun 13, 2008

jpadie

I thought of that, thats why i put that known multiple patient concern ID in ... from my first messy post lol... the 102023 id... it has 6 results which return in the first row only... its weird. the first row of the first query works perfect.

Clive, hard coding the 102023 id in should force it to put that data in all rows, i am baffled. maybe this nested doesnt work with multiple results in the first query?

Stretchwickster · Jun 13, 2008

Try commenting out the following lines and replace $ir with $r as follows, and let me know if that works (correct me if I'm wrong but it seems like you've already incorporated the second query into the first):

Code:

// Start second loop 
//$inner_result = mysql_query("SELECT pcc_concerns_date, pcc_concerns_prac_int FROM pcc_concerns WHERE pcc_concerns_contact_id = '$r["pcc_concerns_contact_id"]' ORDER BY pcc_concerns_date desc");
//while($ir=mysql_fetch_array($inner_result))
//{
echo [COLOR=red]$r[/color]["pcc_concerns_date"] . " " . [COLOR=red]$r[/color]["pcc_concerns_prac_int"]  . "<br />";
//}

// End of second loop

Clive

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"To err is human, but to really foul things up you need a computer." (Paul Ehrlich)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To get the best answers from this forum see: faq102-5096

Korizon67 · Jun 13, 2008

this is a model of the data i get...

[Name ][SX Date ][Blah ]
[Doe, John][2008-01-01][Other Stuff] <main query>
[Concern Date][Patient Manager] <second query>
[2008-01-11 ][someuser ]
[2008-01-15 ][someuser2 ]
[2008-01-11 ][someuser2 ]
[2008-01-12 ][someuser ]
[2008-01-17 ][someuser ]
[Doe, Jane][2008-02-02][Other Stuff] <main query>
[Concern Date][Patient Manager] <second query>
nothing shows here

Korizon67 · Jun 13, 2008

Clive,

The second query "requeries" the table and lists all previous concerns and dumps them into a <div></div> that allows the Patient Advocate to hover on an image and uses js/css to pop up the div and show all previous calls and who made them... its an assigning tank and the PA wants to make sure they assign to the same user w/o having to scroll to see who the last user was (lazy babies)... its a bell and whistle lol and its driving me nuts!... can i attach an image here so you guys can see the finished product?

Stretchwickster · Jun 13, 2008

Mike,

You can upload images to a site like

http://imageshack.us/

and then include a link to the uploaded image in your Tek-Tips post.

I understand a bit more now after your 11:02 post.

This doesn't explain why you're hard-coded version doesn't re-run on subsequent main loops, but try this variation:

Code:

$inner_result = mysql_query("SELECT pcc_concerns_date, pcc_concerns_prac_int FROM pcc_concerns WHERE pcc_concerns_contact_id = '" . $r['pcc_patient.pcc_patient_contact_id'] . "' ORDER BY pcc_concerns_date desc");

Clive

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"To err is human, but to really foul things up you need a computer." (Paul Ehrlich)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To get the best answers from this forum see: faq102-5096

Korizon67 · Jun 13, 2008

Clive,

no luck... I thought you were on to something

It wouldnt even pull the first row with that... which is weird... I changed it dropping the pcc_patient. and it brought the first row back.

Is this something that should be doable? I could swear I have done this before.

Mike

Korizon67 · Jun 15, 2008

Resolved!

I discovered why this was happening...

This line of code...

Code:

 include 'grab_pa.php';

Had a connection string to another database for dynamically creating a user drop down from the login database, It was killing the connection for the query as the loop ended. You guys would've never seen the problem because you didn't see the connection string... my apologies.

I fixed it by simply sticking the database selection string in the first while statement.

Code:

...

while($ir=mysql_fetch_array($inner_result))
{
include '../dbcon/pcc_opendb.php';
echo $ir["pcc_concerns_date"] . " " . $ir["pcc_concerns_prac_int"]  . "<br />"; 

...

Thanks for trying to help out with this, it had me completely baffled and I never even thought about that user list.

Mike

Stretchwickster · Jun 16, 2008

Glad you got it sorted. It's very easy to focus in on what you think is the root of a problem, only to find the culprit is something you dismissed or overlooked - I've certainly done it many a time.

Clive

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"To err is human, but to really foul things up you need a computer." (Paul Ehrlich)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To get the best answers from this forum see: faq102-5096

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Nesting queries inside a "While Loop" (only returns 1 result)

Korizon67

Programmer

Stretchwickster

Programmer

Korizon67

Programmer

Korizon67

Programmer

Stretchwickster

Programmer

jpadie

Technical User

Korizon67

Programmer

Stretchwickster

Programmer

Korizon67

Programmer

Korizon67

Programmer

Stretchwickster

Programmer

Korizon67

Programmer

Korizon67

Programmer

Stretchwickster

Programmer

Similar threads

Part and Inventory Search

Sponsor

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Nesting queries inside a &quot;While Loop&quot; (only returns 1 result)

Programmer

Programmer

Programmer

Programmer

Programmer

Technical User

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor

Nesting queries inside a "While Loop" (only returns 1 result)