[SOLVED] mysql_fetch_array + sorting through two result arrays

[Padster]

Hello all, Im new, 

 

 

This has been annoying me all day, i have two SQL queries one conatins all the possible timeslots availabel for a day the other the ones already taken.  Joins didnt work and im no trying to use conditional structures to match the times that appear in both set of results but im struggling.  Where clauses dont help and ive tried for loops to extract the results but just got the first one out.  here is the section of code

 

$time_sql="SELECT time FROM timeslot ORDER BY time";

$time=mysql_query($time_sql,$db);

$numRows1=mysql_num_rows($time);

 

$taken_sql="SELECT time, date FROM appointments where date='".$_SESSION["date"]."' and doctor_no='".$_POST["avail_drs"]."'";

$taken=mysql_query($taken_sql,$db);

?>

<form>

<select name="time">

<option value="0">Select A Time</option>

<?php

while ($time_res = mysql_fetch_array($time)){ 

echo "<option value=".$time_res["time"].">".$time_res["time"]."</option>"; 

}

 

while ($taken_res = mysql_fetch_array($taken)){

echo "<option value=1>".$taken_res["time"]." - UNAVAILABLE</option>"; 

}

 

?>

</select>

 

 

Hope you can help

[btherl]

For times occurring in both sets of results:

 

$taken_sql="SELECT time, date FROM timeslot JOIN appointments USING (time) where date='".$_SESSION["date"]."' and doctor_no='".$_POST["avail_drs"]."'";
$taken=mysql_query($taken_sql,$db) or die("Query failed: " . mysql_error());

 

If you want times NOT occurring in both sets, then that's doable too, just ask

 

If the query above fails, post the error message here.

 

BTW, it makes life much easier if you add the "or die(...)" to each mysql_query().  That makes your script stop whenever there's a query error, instead of continuing and acting strangely.

[Padster]

thanks for the reply, i had tried a join like that but i want a sort of LEFT join that will produce something like this

 

Time |patientID

09:00 3

09:15

09:30

14:00 3

16:00 4

 

a join only gives me some beacuse the nulls are eliminated by the where clause.  I want to combine the lists.  Does anyone have any idea how to do this or to do it with two queries as above

 

[btherl]

No problems   A left join keeps everything in the left side of the join.  So since you want to keep everything from time, that will be the left table.

 

$taken_sql="SELECT time, date, patientID FROM timeslot LEFT JOIN appointments USING (time) "
. " WHERE ( date='".$_SESSION["date"]."' and doctor_no='".$_POST["avail_drs"]."' )"
. " OR ( appointments.time IS NULL) ";
$taken=mysql_query($taken_sql,$db) or die("Query failed: " . mysql_error());

 

There are 3 changes there

 

1.  Add patientID (minor change)

2.  Change JOIN to LEFT JOIN

3.  Add an extra condition so that rows with no appointments are still considered valid to return.  If no rows from appointments match, then every column from appointments will be NULL in that result row.  So I check one column from appointments for nullness.

[Padster]

Mate thanks for your help, i dont think i explained myself that well which probably hasnt helped.  but ive been working away and have it nearly cracked

 

Im generating a select menu with the appointments that are free, taken appointments are ignored but i can only get the first returned row to be considered.  i know im not using a while loop and that beacause i dont wanto to generate the list loads of times just one please help.

 

$taken_slots = mysql_fetch_array($taken);

#only give the first row

 

for ($hour=9;$hour<=16;$hour++){

 

if($hour<10){

$on_hour="0$hour:00";

 

if (!in_array($on_hour, $taken_slots)) {

echo "<option value=\"$on_hour\">$on_hour</option>";}

 

for($turns=1;$turns<=3;$turns++){

    $min=$turns*15;

$hr_mins="0$hour:$min";

 

if (!in_array($hr_mins, $taken_slots)){

#check is the time generated taken already/ in the array if not print it

echo "<option value=\"$hr_mins\">$hr_mins</option>";}

   

}

 

}

else{

$on_hour="$hour:00";

 

if (!in_array($on_hour, $taken_slots)) {

echo "<option value=\"$on_hour\">$on_hour</option>";}

 

  for($turns=1;$turns<=3;$turns++){

    $min=$turns*15;

$hr_mins="$hour:$min";

 

  if (!in_array($hr_mins, $taken_slots)){

echo "<option value=\"$hr_mins\">$hr_mins</option>";}

}

 

}

}

[btherl]

You need a while loop

 

$taken_slots = array();
while ($row = mysql_fetch_array($taken)) {
  $taken_slots[] = $row['time'];
}

 

That will give you an array of times, suitable for use with in_array(), as you have in your existing code.  Give that a go

 

If it's not working, add this immediately after you generate $taken_slots array:

 

var_dump($taken_slots);

 

That will show you what's in the array.

[Padster]

thanks mate i eventually got to that solution some time last night but the two results into arrays and used a for loop to evaluate them this is what i ended up with

 

<select name="time">

<option value="0">Select A Time</option>

<?php

$all_timeslots= array();

$i=0;

while ($all_time_slots = mysql_fetch_array($time)) {

$all_timeslots[$i]=$all_time_slots["time"];

$i=$i+1;

}

 

$all_taken_slots= array();

$h=0;

while ($taken_slots = mysql_fetch_array($taken)) {

$all_taken_slots[$h]=$taken_slots["time"];

$h=$h+1;

}

$b=0;

for($a=0; $a<=$number_of_slots-1; $a++){

if ($all_timeslots[$a]==$all_taken_slots[$b]) {

echo "<option value=\"1\">".$all_timeslots[$a]." - TAKEN</option>";

$b=$b+1;

}

else{

  echo "<option value=\"".$all_timeslots[$a]."\">".$all_timeslots[$a]."</option>";

}

}

?>

</select>