Please help I'm desperate

Anon

My university dissertation has to be in on monday and I'm really stuck: I want to display a table of repeat prescriptions by looping a process. Within this I want to extract the patients info - but only the last one displays. Please help I'm desperate!!

if ($no_prescriptions > 0){
print "There are $no_prescriptions prescriptions queued in the database.";

// Print table of queued prescriptions
?>
<table width="90%" border="1" cellspacing="0" cellpadding="0" align="center" bordercolor="#98BEDE">
<tr bgcolor="#98BEDE">
<td>Repeat Prescription Requests
<table cellpadding="2" cellspacing="1" width="100%" align="center">
<tr bgcolor="#DCEAF5">
<td align="center">ID No.</td>
<td align="center">Patient Details</td>
<td align="center">Request Details</td>
<td align='center'>Patient Comments</td>
<td align="center">Date of request</td>
<td align="center">Collection Place</td>
<td align="center">Collection Date</td>
<td align="center">Accept?</td>
<td align="center">Decline?</td>

  </tr>
  <?
  $sql_select = "select * from prescriptions where pending = '1' order by rp_date asc";
  if (!$sql_select) { print("Error");}
  $result = mysql_query ($sql_select);

  $bgcolour="#DCEAF5";
  while ($row = mysql_fetch_array($result))
  {
      $rp_id = $row["rp_id"];
	  $user_id = $row["user_id"];
      $comments = $row["comments"];
	  $des_1 = $row["des_1"];
      $quan_1 = $row["quan_1"];
	  $des_2 = $row["des_2"];
      $quan_2 = $row["quan_2"];
	  $des_3 = $row["des_3"];
      $quan_3 = $row["quan_3"];
	  $des_4 = $row["des_4"];
      $quan_4 = $row["quan_4"];
	  $des_5 = $row["des_5"];
      $quan_5 = $row["quan_5"];
	  $des_6 = $row["des_6"];
      $quan_6 = $row["quan_6"];
	  $rp_date_day = substr($row["rp_date"],6,2);
	  $rp_date_month = substr($row["rp_date"],4,2);
	  $rp_date_year = substr($row["rp_date"],0,4);
	  $rp_collect_day = substr($row["collect_when"],6,2);
	  $rp_collect_month = substr($row["collect_when"],4,2);
	  $rp_collect_year = substr($row["collect_when"],0,4);
	  $collect_place = $row["collect_place"];
	  $collect_date = "$rp_collect_day-$rp_collect_month-$rp_collect_year";

// THIS BIT DOESN'T WORK

$sql_select_user = "select * from patients where user_id ='$user_id'";
if (!$sql_select_user) { print("Error");}
$result_user = mysql_query ($sql_select_user);

  while ($row = mysql_fetch_array($result_user))
  {
      $first_name = $row["first_name"];
      $last_name = $row["last_name"];
      $dob_day = substr($row["dob"],6,2);
	  $dob_month = substr($row["dob"],4,2);
	  $dob_year = substr($row["dob"],0,4);
	  $sex = $row["sex"];
      $user_email = $row["user_email"];
   }

	if ($sex== 'M'){
			$sex="Male";}
	if ($sex== 'F'){
			$sex="Female";}
	if ($bgcolour =="#CADEEE"){
			$bgcolour ="#DCEAF5";}
	elseif ($bgcolour =="#DCEAF5"){
		    $bgcolour ="#CADEEE";}

  print("<tr bgcolor='$bgcolour'>");
  print("<td align='center'><font size='2'>$rp_id</font></td>");
  print("<td align='left' valign='top'><font size='2'><b>Surname:</b> $last_name<br><b>Forename:</b> $first_name<br><b>DoB:</b> $dob_day-$dob_month-$dob_year<br><b>Sex:</b> $sex<br><b>Email Address:</b> <a href='mailto:$user_email'>$user_email</a><br><b>User ID:</b> $user_id</font></td>");

  print("<td align='left' valign='top'><font size='2'><p>$des_1<br>$quan_1</p><p>$des_2<br>$quan_2</p><p>$des_3<br>$quan_3</p><p>$des_4<br>$quan_4</p><p>$des_5<br>$quan_5</p><p>$des_6<br>$quan_6</p></font></td>");
  print("<td align='left' valign='top'><font size='2'>$comments</font></td>");
  print("<td align='center'><font size='2'>$rp_date_day-$rp_date_month-$rp_date_year</font></td>");
  print("<td align='center'><font size='2'>$collect_place</font></td>");
  print("<td align='center'><font size='2'>$collect_date</font></td>");
  print("<td align='center'><font size='2'><a href='prescriptions.php?rp_id=$rp_id&app=1&email=$user_email&collect_place=$collect_place&collect_date=$collect_date'>Approve</a></font></td>");
  print("<td align='center'><font size='2'><a href='prescriptions.php?rp_id=$rp_id&dec=1&email=$user_email'>Decline</a></font></td>");
  }
  print("</font></tr></table></td></tr></table>");	

}

bboyd

Ali,

Try this:

// THIS BIT DOESN'T WORK
$sql_select_user = "select * from patients where user_id =\"$user_id\"";

Bill

Anon

Thanks but it still doesn't work, its wierd, because the users details are displayed on the bottom row of the table, but none above.

bboyd

Hmm - at this point I would insert a bunch of diagnostic print statements to see what values I'm getting inside and outside of the internal while loop.

Bill

Anon

This loop is your problem. Every time you loop you are over writing the previous loop through. There for only the last time through the loop is being kept.

I would try making these variables arrays
ex.
$first_name[] = $row["first_name"];
$last_name[] = $row["last_name"];
$dob_day[] = substr($row["dob"],6,2);
$dob_month[] = substr($row["dob"],4,2);
$dob_year[] = substr($row["dob"],0,4);
$sex[] = $row["sex"];
$user_email[] = $row["user_email"];

while ($row = mysql_fetch_array($result_user))
{
$first_name = $row["first_name"];
$last_name = $row["last_name"];
$dob_day = substr($row["dob"],6,2);
$dob_month = substr($row["dob"],4,2);
$dob_year = substr($row["dob"],0,4);
$sex = $row["sex"];
$user_email = $row["user_email"];
}

zalath

Hmm, are you trying to extract several records from the users table? it seems to me you're using an unique ID in the query's where clause (user_id or whatnot) which would effectively cause only one row of data to be fetched.

-L-

Anon

It only returns 1 users details because the user id is unique. Do you know how I can get round this?

Anon

Surname: Array
Forename: Array
DoB: Array-Array-Array
Sex: Array
Email Address: Array
User ID: 1

This is the result dave... but it does do it for every user.

Anon

How can this sql command therfore be executed each time the loop goes round to bring up other users info? Or is this not possible?

zalath

You got it all backwards. The point is, you're restricting the result set yourself with that WHERE clause. If you want to loop thru every single user, drop the WHERE clause, executing "select * from patients", and then looping. You only need to exec the SQL once.

-L-