PHP + MySQL Loop

[StuHannah]

Evening (Or Morning wherever you are)!

 

Looking for a bit of help as what I am trying to achieve I am unsure if its possible, so I am asking the brains on PHPFreaks.

 

Basically I am developing a booking system, and I have managed to get the system to store the data from the MIS system, however if there is a free session it isn't recorded. For instance, we have 5 sessions a day, but on Mondays the room only has 4 pieces of information recorded as one of them is free. So below is an example of the data within the MIS Data:

 

MIS_ID

MIS_Session

MIS_Staff

MIS_Group

1

MONA:1

ABC

GP1

2

MONA:2

DEF

GP2

3

MONA:4

GHI

GP3

 

I am able to pull all this information, however it will only pull 4 piece (as I know there is only 4 in the DB) is there anyway to maybe specify within a variable for instance $sessions = 5; then get it so loop through until the values meet: while($session <= mysql_num_rows);

 

Hoping someone can point me in the right direction as I am so close, but feel so far!

 

Thank you in advance to anyone who has any valuable input!

 

Kind Regards,

 

Stuart

[benanamen]

You are using obsolete Mysql code that has been completely removed from PHP. You need to use PDO with prepared statements. Post your current DB schema. I cannot tell if you just posted random MIS data or if that is your DB structure as well. If that is your DB it is not correct and needs to be fixed before going further.

 

https://phpdelusions.net/pdo

 

* More details on exactly which MIS would be helpful. I assume it means Managment Information System, which one? Is there an API?

Edited November 7, 2016 by benanamen

[Barand]

What does "MONA:2" mean?

[StuHannah]

Thanks for the reply. Just realised I didn't say MySQLi, was just Pseudo'ing it out off the top of my head as an idea. Below is the table structure:

 

• tbl_misdata is the data straight from the MIS system
• tbl_periods are all the periods we use i.e. MONA:1, MONA:2 (A being the week we are on and the number is the session)
• tbl_rooms the list of rooms we have to book.
• tbl_weeks list of weeks and wether they are A week, B week or a Holiday.

So my Pseudo, to help explain it:

 

In the admin side, we specify the weeks in the DB so they are A/B/H

When the page loads, it checks what week we are on by date and then queries the DB to see what type of week this is.

Once we determine week, we are able to loop through the misdata where it matches the week (so if we return an A week, we want all the data that matches MonA% in the query.

 

Hope this helps! #readytotakeabeating

CREATE TABLE `tbl_bookings` (
  `Booking_` int(11) NOT NULL,
  `Booking_Room` varchar(200) NOT NULL,
  `Booking_Period` varchar(200) NOT NULL,
  `Booking_Week` varchar(200) NOT NULL,
  `Booking_User` varchar(200) NOT NULL,
  `Booking_Class` varchar(200) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `tbl_misdata` (
  `MIS_ID` int(11) NOT NULL,
  `MIS_Room` varchar(200) NOT NULL,
  `MIS_Period` varchar(200) NOT NULL,
  `MIS_Class` varchar(200) NOT NULL,
  `MIS_Staff` varchar(200) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `tbl_periods` (
  `Period_ID` int(11) NOT NULL,
  `Period_Name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `tbl_rooms` (
  `RM_ID` int(11) NOT NULL,
  `RM_Name` varchar(100) NOT NULL,
  `RM_Desc` varchar(200) NOT NULL,
  `RM_Available` varchar(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `tbl_weeks` (
  `Week_ID` int(11) NOT NULL,
  `Week_Name` varchar(200) NOT NULL,
  `Week_Type` varchar(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

[benanamen]

As previously asked, What MIS system and is their an API available? 

 

Is that the complete DB schema? If so, you are missing tables and have other problems to fix to get it right. 

Edited November 7, 2016 by benanamen

[ginerjm]

Are there always 5 sessions for every room? If so write your loop to repeat those 5 times and in the loop do your fetch without a while verb. If the while fails, then set the values to null and show them that way.

[StuHannah]

They mean the week and session.

 

MONA:1 = Monday of Week A, session 1. MONB:1  = Monday of Week B, Session 1.

[benanamen]

Are you really going to make us drag info out of you post by post? 

 

WHAT MIS?

 

IS THERE AN API?

 

Getting this right starts with the datasource, not your attempt at how to handle it. See the XY Problem in my signature.

Edited November 7, 2016 by benanamen

[StuHannah]

The MIS is SIMS and I am unsure if there is an API, I'm simply pulling data through the command reported into a csv the have a script to import this to the MySQL mis data table.

[Barand]

Week name?

 

Do you really name your weeks?

 

I would have expected to see a DATE field in that table, and in the mistable (booking) table too.

[StuHannah]

Yeah we run a two week cycle so have two lots of data, week a data and week b all in one table, the idea is that when the user loads up the page, it will determine what week it is and query the data only from that week.

 

Hope that makes sense.

 

Stuart

[Barand]

Doesn't the week type tell you if it's week A, B or H? I still don't wee what the week name is for. Have you sample data.

[benanamen]

You know, a link to SIMS would be helpful. A search for SIMS booking gives 466,000 results. The script you use to get the data could also helpful.

[StuHannah]

This table contains all of the MIS Data from SIMS.net. The report that runs grabs the following information:
 

Room Name                    Period                                 Class name                       Staff Code
Room1                                MonA:1                              7L/IT1                                  ASm
Room1                                MonA:2                              8R/Ma1                              PKa

 

CREATE TABLE `tbl_misdata` (

  `MIS_ID` int(11) NOT NULL,

  `MIS_Room` varchar(200) NOT NULL,

  `MIS_Period` varchar(200) NOT NULL,

  `MIS_Class` varchar(200) NOT NULL,

  `MIS_Staff` varchar(200) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

This table contains all the available periods available on the MIS System. (These are static so I’ve extracted them from the SIMS report)

 

CREATE TABLE `tbl_periods` (

  `Period_Name` varchar(100) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

This table contains the list of rooms. Again these are manually added to the DB. If we wanted a new room, I would add it to the system manually, and the edit the report to include this in the data export.

 

CREATE TABLE `tbl_rooms` (

  `RM_ID` int(11) NOT NULL,

  `RM_Name` varchar(100) NOT NULL,

  `RM_Desc` varchar(200) NOT NULL,

  `RM_Available` varchar(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

This table contains the list of rooms. Again these are manually added to the DB. If we wanted a new room, I would add it to the system manually, and the edit the report to include this in the data export.

 

CREATE TABLE `tbl_weeks` (
  `Week_ID` int(11) NOT NULL,

  `week_Name` varchar(100) NOT NULL,

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

This table will eventually have the extra bookings that have been added by the end user.

 

CREATE TABLE `tbl_bookings` (

  `Booking_` int(11) NOT NULL,

  `Booking_Room` varchar(200) NOT NULL,

  `Booking_Period` varchar(200) NOT NULL,

  `Booking_Week` varchar(200) NOT NULL,

  `Booking_User` varchar(200) NOT NULL,

  `Booking_Class` varchar(200) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

Brief overview of some of the pages
 

Admin Side

SetupWeeks.php – Loops through 52 weeks ahead of the week it has been opened (So gets used once a year) and has checkboxes with A/B/H as the options. A = Week A, B = Week B and H = Holiday.

The idea with this is that when the main page loads, it will get the current week we are on; query the database to see what sort of week we are on, then pull that week’s MIS information.

NewRoom.php – Creates a new room in the tbl_rooms table, where it is given details such as number of pc’s etc.

 

End User

Index.php – Page loads, connecting to the DB. Check what week we are on, starting at Monday. Once it has determined the week, it
checks the database to see what week it is. If it is an A week, it will query filtering all the A week MIS information, if it’s a B Week, same again checks for the B week information. If the week is a H, it simply lets the user know it’s a holiday week.

Bit I am up to!

Once the MIS data has been brought to the end user, it will print the current information on the screen, however at present it will print everything, but if there is a gap i.e. there isn’t any MIS data available as the period is free, it doesn’t print that it’s free, as I know it doesn’t have the information, so it’ won’t know – There are always 5 sessions a day, so was thinking of having a variable with that states and for each result i.e. $i=5 for each monA:$i <5) print the result, if (!$result)then print to say it’s free.

 

Hope this will help, and provide you with enough information. Apologies for not providing all information earlier, burning the candle at both ends at the moment.

[benanamen]

http://sims.net/ ????

 

All that is there is a useless broken outdated site with next to no information or a contact number. Is this a third party company that you do business with or does it belong to you?

 

If it's third party I would be highly sceptical of trusting my business to them based on what I see there.

 

As long as we are at it, how about attaching a zip of those php files you listed. Some people on here don't like to download zips but I will sandbox it before I open it.

 

How locked into this "System" are you? There are much better booking systems available.

 

* Does this have anything to do with the SIM's reality simulator game?

Edited November 8, 2016 by benanamen

[StuHannah]

SIMS.net is a School Information Management System, not the game. The site is nothing to do with me.

 

I was simply looking at a way of developing my own internal booking system, taking away the need to use paper, and without costing anything but my time.

 

All I was doing was trying to learn along the way.

 

Thanks anyway and best wishes.

 

Stuart

[Barand]

The first thing you need to do is break up that compound period column - you have three items in there that should be in separate columns

• Day number (1 - 7)
• Week (YYYYWW)
• Session number

You will also need a session table containing five rows, one for each session 1 - 5. Your query needs to know what should be there if it wants to show you what is missing.

[Barand]

To illustrate the above, if you have

mysql> SELECT * FROM tbl_bookings;
+------------+------+-----+--------+------------+------+--------+
| booking_id | room | day | week   | session_no | user | class  |
+------------+------+-----+--------+------------+------+--------+
|          1 | 1    | 1   | 201640 |          1 | ABC  | CLASS1 |
|          2 | 1    | 1   | 201640 |          2 | DEF  | CLASS2 |
|          3 | 1    | 1   | 201640 |          4 | GHI  | CLASS3 |
|          4 | 1    | 1   | 201640 |          5 | JKL  | CLASS4 |
+------------+------+-----+--------+------------+------+--------+

mysql> SELECT * FROM session;
+------------+----------+----------+
| session_no | starts   | ends     |
+------------+----------+----------+
|          1 | 09:30:00 | 10:29:59 |
|          2 | 10:30:00 | 11:29:59 |
|          3 | 11:30:00 | 12:29:59 |
|          4 | 13:30:00 | 14:29:59 |
|          5 | 14:30:00 | 15:29:59 |
+------------+----------+----------+

Then you can find unfilled sessions like this

SELECT 
    s.session_no
  , s.starts
  , s.ends
  , b.room
  , b.user
  , b.class
FROM session s 
LEFT JOIN tbl_bookings b 
    ON s.session_no = b.session_no
    AND week = '201640'
    AND day = 1;

+------------+----------+----------+------+------+--------+
| session_no | starts   | ends     | room | user | class  |
+------------+----------+----------+------+------+--------+
|          1 | 09:30:00 | 10:29:59 | 1    | ABC  | CLASS1 |
|          2 | 10:30:00 | 11:29:59 | 1    | DEF  | CLASS2 |
|          3 | 11:30:00 | 12:29:59 | NULL | NULL | NULL   |  <-- unfilled session
|          4 | 13:30:00 | 14:29:59 | 1    | GHI  | CLASS3 |
|          5 | 14:30:00 | 15:29:59 | 1    | JKL  | CLASS4 |
+------------+----------+----------+------+------+--------+