SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Sep 2013
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Red face Room reservation system PHP mysql

    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:

    Capturar2.JPG

    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:

    Code PHP:
    $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:

    PHP Code:
    $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 = “<a href='add_reserva.php?hora=$hora&mydate=$mydate'>Requisitar</a></”;
                    
    $room = “”;
                    
    $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. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    Well the coloration of your last code block should alert you to the first reason it doesnt work. (You've missed a close quote)
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  3. #3
    SitePoint Member
    Join Date
    Sep 2013
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Sorry my mistake, but doesnt work anyway

    Quote Originally Posted by m1984 View Post
    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:

    Capturar2.JPG

    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:

    Code PHP:
    $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:

    PHP Code:
    $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 <a href='add_reserva.php?hora=$hora&mydate=$mydate'>Requisitar</a></;
                    
    $room ;
                    
    $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??

  4. #4
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,072
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    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 prepared statements) otherwise your code will be vulnerable to an 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.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  5. #5
    SitePoint Evangelist
    Join Date
    Aug 2006
    Location
    Nantwich, Cheshire
    Posts
    451
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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.

    Code:
    $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.
    http://www.firenza.net - my homage to a car from the 1970s

  6. #6
    SitePoint Member
    Join Date
    Sep 2013
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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".



    Quote Originally Posted by SpacePhoenix View Post
    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 prepared statements) otherwise your code will be vulnerable to an 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.


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •