Room reservation system PHP mysql

PHP
1

Hi,
I am a noob in php and mysql, and I am trying to create a simple room reservation system for my school.
I wanted to create a table with the available and occupied hours like this:

However, I am not getting it right.
For each hour I chose to identify with a letter, like:

a->8:00-8:50; b->9:00-9:50; c->10:00-10:50; d-> 11:00-11:50

I’ll get the occupied hours of a certain date in my mysql database like this:


$con = db_connect();
$result=mysqli_query($con,"SELECT * FROM reservations WHERE data = '$mydate' ORDER BY hour");
while($row = mysqli_fetch_array($result)){
	$person = $row['person'];
	$room = $row['room'];
	$hour = $row['hour'];
        $convertedhour = converthour($hour); // convertes the letter to correspondent hour
         echo
         "<tr>
          <td>$convertedhour</td>
         <td>$person</td>
         <td>$room</td>
          </tr>";
           }

Now my question is how to put lines when that hour is not occupied?
I’ve tried this:

$con = db_connect();
$result=mysqli_query($con,"SELECT * FROM reservations WHERE data = '$mydate' ORDER BY hour");

$hours=array("a","b","c","d);
$arrlength=count($hours);

while($row = mysqli_fetch_array($result)){
      for($j=0; $j<$arrlength; $j++){
		if ( $hours[$j]; == $row['hour'];){ // if it exists in reservation table, therfore is occupied
               $person = $row['person'];
	                    $room = $row['room'];
	                    $hour = $row['hour'];
      
// How to advance to the next element of row?

                 } else{ //if doesnt match, hour is available
                $person = &#8220;<a href='add_reserva.php?hora=$hora&mydate=$mydate'>Requisitar</a></&#8221;;
                $room = &#8220;&#8221;;
                $hour = $hours[$j];
                }
          $convertedhour = converthour($hour); // convertes the letter to correspondent hour
         echo
         "<tr>
          <td>$convertedhour</td>
         <td>$person</td>
         <td>$room</td>
          </tr>";
           }
  }

But it does not work because I dont know how to advance to the next row …
Can you help me please??

2

Well the coloration of your last code block should alert you to the first reason it doesnt work. (You’ve missed a close quote)

3
4

Have you got one record per room per hour?

The variable $mydate needs to be sanitized before being allowed anywhere near the database (either by use of the mysqli_real_escape_string() string function or more preferably by making use of [URL=“http://php.net/manual/en/mysqli.quickstart.prepared-statements.php”]prepared statements) otherwise your code will be vulnerable to an [URL=“http://php.net/manual/en/security.database.sql-injection.php”]SQL Injection attack. All user submitted data no matter how it’s being submitted (GET, POST or REQUEST arrays or a cookie) must always be considered unsafe untill it has been validated and sanitized.

Are you going to use all fields returned by the query? If not, name only the fields need in the SELECT clause, with a , in-between each one as when selecting data from any given table, unless you’re selecting from a “temporary table” generated by a sub-query then the "dreaded, evil SELECT * as @r937; phrases it, is a waste of resources.

5

I think I’d try to build up an array for each element as part of the loop, then display them in a separate loop. Because you don’t know how many ‘empty hours’ there will be between each query return, it’s harder to code. So build an array of each person and room (indexed by the hour) and then run through those arrays and display either the data or the link to create the data.



$hour = array("a", "b", "c", "d");

while ($row = mysqli_fetch_array($result)) {
   $h = converthour($row['hour']);
   $person[$h] = $row['person'];
   $room[$h] = $row['room'];
   }
foreach ($hour as $key => $hourref) {
   $x = converthour($xhourref);
   if (isset($person[$x])) {
      // display the room use details
   } else {
      // display a link to book the slot
   }
}

Apologies for the code, I’m new at PHP. I think you could probably use an index array instead of numeric values, and there are probably lots of optimisations you could do.

6

Hi,
I only have records for the hours that are occupied.
Thats why I want to compare occupied rows from the database with my hours array. probably there is a better and more efficient way to do that.

I have read abou sanitization and prepared statements, however for the time being I’m trying to make this work like this, than to change it and make it more “secure”.

:wink: