only first row returned - fetch array

[Padster]

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

      }

 

}

}

[haku]

You already answered the question yourself. You aren't using a while loop.

 

mysql_fetch_array != mysql_fetch_arrays.

 

Its just one array.

 

What you need to do is use some logic when getting the data out of your database. You didn't include your database query, but I'm guessing you are just getting everything out of it, then checking the data after. But what you should be doing is making your select query something like this:

 

SELECT somecolumns FROM sometable WHERE somevariable=something (AND someothervariable=somethingelse)

 

You can add the AND part if necessary. If you put your query together correctly, you will only get the information you need out of the database, and can then just output all the data, instead of having to scan everything for data you want, throwing away data you dont.

[Padster]

Hey i dont know what you mean this is the query.

 

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

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

 

This returns several rows with times like this:

 

TIME

09:00

14:00

16:00

 

How would you use a while loop here as it putting it around the for loop will run the for loop each time which wont help - im confused help!!

 

Thanks

 

 

[haku]

I'll be honest, your code is hard for me to read. What exactly are you trying to do?

[Padster]

the query returns all times that are booked out.  i want to make a select menu that contains all the times that remain between 9am and 4:45.  so im generating appointment times at 15 min intervals. 

 

I want to check if the generated time is the same as the times fetched from the database and when it is that time is omitted from the select menu

 

[trq]

Replace...

 

$taken_slots = mysql_fetch_array($taken);

 

with....

 

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

 

then place one last closing } (for the end of the while()) at the end of your code.

[haku]

I would suggest creating another column in your table called 'booked' or something, and when an appointment is booked, set it to 1. Then at the end of your database query, add one more condition to your WHERE statement that's WHERE booked < 1.

 

This way all the data that comes out of the database will be unbooked data, and you can just output it as is. Of course you will have to put all the times into the database in the first place. You actually should have at least two, and probably 3, tables for this.

 

Table 1: Doctors - 2 colums (as a base, you can add more)

column1        column2

Dr's name    ID number

 

Table 2: Times - 2 columns

column1        column2

  time            id number

 

Table 3: joining - 3 columns

column1      column2        column3

Dr_ID          time_id          booked

 

 

So you put all the doctors into table 1 and assign them an ID number. Then you put all the times into table 2, and assign them an ID number. Then you put all the possible combinations of doctor and time into table 3, and as the appointments are booked you set 'booked=1'.

[Padster]

prephaps my database design isnt helping.  i have a three table system as you describe

 

CREATE TABLE `doctor` (

  `doctor_no` int(20) NOT NULL auto_increment,

  `fname` varchar(30) collate latin1_general_ci NOT NULL,

  `lname` varchar(30) collate latin1_general_ci NOT NULL,

  `surgery_no` int(20) NOT NULL,

  PRIMARY KEY  (`doctor_no`),

  KEY `surgery_no` (`surgery_no`)

) ENGINE=InnoDB;

 

CREATE TABLE `appointments` (

  `patientID` int(20) NOT NULL,

  `doctor_no` int(20) NOT NULL,

  `date` date NOT NULL,

  `time` varchar(10) collate latin1_general_ci NOT NULL,

  UNIQUE KEY (`patientID`,`doctor_no`,`date`,`time`),

  KEY `doctor_no` (`doctor_no`)

) ENGINE=InnoDB;

 

 

CREATE TABLE `patient` (

  `patientID` int(20) NOT NULL auto_increment,

  `fName` varchar(40) collate latin1_general_ci NOT NULL,

  `lName` varchar(40) collate latin1_general_ci NOT NULL,

  `dob` date NOT NULL,

  `number` varchar(10) collate latin1_general_ci NOT NULL,

  `street` varchar(40) collate latin1_general_ci NOT NULL,

  `town` varchar(40) collate latin1_general_ci NOT NULL,

  `postcode` varchar(10) collate latin1_general_ci NOT NULL,

  `telephone` varchar(20) collate latin1_general_ci NOT NULL,

  `surgery_no` int(20) NOT NULL,

  PRIMARY KEY  (`patientID`),

  KEY `surgery_no` (`surgery_no`)

) ENGINE=InnoDB;

 

I join the appointments table with one called time slots to organise the appointments on a specific date with a specific doctor.

[haku]

The way I would do it (and by no means am I saying its the best way, its just 'a' way. Actually, Im quite sure there are better ways) would be to add a 'booked' column into 'appointments'. Then, you will have to create rows in the 'appointments' table for each time a doctor is available, leaving booked empty. Then, when an appointment is booked, changed 'booked' to 1. And as I said before, do your query on that table for WHERE < 1.

 

OR, you could do it the way thorpe said, which may be a hell of a lot easier.