CODEWITHSUNDEEP
phpsqltimeslots
tonoslfx
tonoslfx

Reputation: 3442

PHP timeslot booking

regarding of this question..

PHP Booking timeslot

I tried 'GROUP BY' id_timeslot still didnt work, as its only showing the booked timeslot not available

i tried that solution, but give me an error and not quite understand how to use 'coelence'

table timeslot  (id_timeslot integer);
table doctor (id_doctor integer);
table bookslot  (id_bookslot, id_doctor, id_timeslot integer);

insert into doctor (id_doctor)
values (1 = doc_A), (2 = doc_B), (3 = doc_C);

insert into TimeSlot (id_timeslot)
values (1 = 10:00:00), (2 = 10:15:00), (3 = 10:30:00), (4 = 10:45:00);

insert into bookslot (id_doctor,id_timeslot)
values (1,1), (1,5), (2,1), (2,4), (3,1);

Join mysql table

$q = $mysqli->query("SELECT * FROM bookslot 
  RIGHT JOIN timeslot ON bookslot.id_timeslot = timeslot.id_timeslot
  LEFT JOIN doctor ON bookslot.id_doctor = doctor.id_doctor ");

echoing result and checking if it matches todays date or else set available

while($r = $q->fetch_array(MYSQLI_ASSOC)) :

 echo '<tr>';
 echo '<td align="center">' . $r['times']  . '</td>';

 if($r['booked_date'] == date('Y-m-d') && $r['id_doctor'] == 1):
  echo '<td><a href="#available" class="booked">booked</a></td>';
 else :
  echo '<td><a href="#" class="available">available</a></td>';
 endif;

 if($r['booked_date'] == date('Y-m-d') && $r['id_doctor'] == 2):
  echo '<td><a href="#available" class="booked">booked</a></td>';
 else :
  echo '<td><a href="#" class="available">available</a></td>';
 endif;

 if($r['booked_date'] == date('Y-m-d') && $r['id_doctor'] == 3):
  echo '<td><a href="#available" class="booked">booked</a></td>';
 else :
  echo '<td><a href="#" class="available">available</a></td>';
 endif;

echo '</tr>';

endwhile;

result from webpage alt text

and i want the result look like:

id_timeslot  doc_A    doc_B    doc_C
----------------------------------------------
1            booked     booked     booked
2            available  available  available
3            available  available  available
4            available  booked     available
5            booked     available  available

Any other solution please!

Upvotes: 0

Views: 4192

Answers (3)

Inca
Inca

Reputation: 1901

It is important to realise that an sql-table is not a table with x,y-data.

So what you see in the php-output is exactly what you get from the database, a list with data that stores only one fact (time, person) per row, and repeats that for any slot / person.

You need to compact it yourself, and make columns where you get rows from the sql. (You could also do it in strict sql but I don't think that will make matters any easier, so I'll leave that.)

First of all, I'd suggest a different sql, that will give you a value for every person/slot-combination, and calculate availability in SQL.

SELECT *, isnull(bookslot.id_bookslot) as 'available' FROM timeslot 
LEFT JOIN doctor ON bookslot.id_doctor = doctor.id_doctor 
LEFT JOIN timeslot ON bookslot.id_timeslot = timeslot.id_timeslot
ORDER BY timeslot.times, doctor.name_doctor

You now don't need to calculate availability, but read the calculated field $r['available'].

The order by is important: you need it so it will list all values as you want them going from left to right. So you want [(10:00, doctor A), (10:00, doctor B), (10:15, doctor A)...]

You'll need two loops, one for the rows, but also one to create the columns. But since the query is changed, you already know you have all values.

$prev_slot = 0;
$firstrow = true;

while($r = $q->fetch_array(MYSQLI_ASSOC)) :

$slot =  $r['times'] ;
if( $slot != $prev_slot ):
    // new timeslot, so here and only here we create a new row      
    if( !$firstrow ):
        // if needed, close the old:        
        echo '</tr>';
        $firstrow = false;
    endif;
    echo '<tr>';
    echo '<td align="center">' . $r['times']  . '</td>';
endif;

// here we are just moving sideways
if($r['available'] ):
    echo '<td><a href="#" class="available">available</a></td>';        
else :
    echo '<td><a href="#available" class="booked">booked</a></td>';
endif;
endwhile;

echo '</tr>'; // close the final row.

Upvotes: 1

Srisa
Srisa

Reputation: 1025

Either change the query to return one row per slot or re-arrange the array after fetching the data from the result set.

Upvotes: 0

Phill Pafford
Phill Pafford

Reputation: 85318

Hmm, looks like you check for each time index and then print your table. try this

 while($r = $q->fetch_array(MYSQLI_ASSOC)) :
    echo '<tr>';
    echo '<td align="center">' . $r['times']  . '</td>';

    if($r['booked_date'] == date('Y-m-d') && $r['id_doctor'] == 1):
       echo '<td><a href="#available" class="booked">booked</a></td>';
    elseif($r['booked_date'] == date('Y-m-d') && $r['id_doctor'] == 2):
       echo '<td><a href="#available" class="booked">booked</a></td>';
    elseif($r['booked_date'] == date('Y-m-d') && $r['id_doctor'] == 3):
       echo '<td><a href="#available" class="booked">booked</a></td>';
    else :
       echo '<td><a href="#" class="available">available</a></td>';
    endif;

    echo '</tr>';
 endwhile;

Upvotes: 1

Related Questions