0

User input room type, start time and end time to check the room availability. I have 2 tables in my database which is Room and Booking. How to select the room based on the type of room and the availability? Note that $sport is the room type because I try to create system for sport facility rental system.

Room

  • type_sport
  • Fee
  • roomCode

Booking

  • roomCode
  • time_end
  • time_start
  • date

I think the error might be at the $result because when I run the code it display 'No result found.'(even though I don't click the submit button) which is should display when there is no room available.

$sport=$_POST['sport'];
$date = $_POST['date'];
$start =$_POST['time_start'];
$end = $_POST['time_end'];

$result = mysql_query("SELECT r.roomCode FROM Room r WHERE r.roomCode NOT IN 
(SELECT b.roomCode FROM Booking b WHERE(b.time_end >= '$start' AND b.time_start <= '$end')
OR (b.time_end <= '$start' AND b.time_start >= '$end'))AND (r.type_sport = '%$sport%')");


                    if (!$result)
                    {die(mysql_error());}

                    if(mysql_num_rows($result) > 0)
                    { 
                        while($row= mysql_fetch_array($result))
                        {
                        echo $row['type_sport'];
                        echo $row['roomCode'];
                        echo $row['fee'] ;
                        }
                    }
                    else {echo "No result found.";}

It should display the room that available on that date and time based on type of sport user choose but what my code display is 'No result found.'

Insyirahmohd
  • 1
  • 3
  • 2
    Why are you using the long-deprecated `mysql_` code library? It was discontinued many years ago and removed entirely in PHP7. No new code should be written using this library. It leaves you vulnerable to SQL injection attacks (due to the lack of parameterised query support) and potentially other unpatched vulnerabilities. Switch to using `mysqli` or `PDO` as soon as possible, and then learn how to write parameterised queries to protect your data from malicious input. See http://bobby-tables.com for a simple explanation of the risks and some sample PHP code to write queries safely. – ADyson May 13 '19 at 09:18
  • Anyway, regarding your question...since we have no idea what input values you used, what data is in your table, or what result you expected from that combination of input values and raw data, it's impossible to say whether you wrote your query correctly or not. We will need some sample data before we can help. – ADyson May 13 '19 at 09:19
  • P.S. If it displays the message even when you didn't click submit, it's probably because you haven't placed this code within an `if` statement to check whether the form has been submitted or not... clearly you don't want the query to run when before the user has completed the form. Place all of the above code within a block like this `if ($_SERVER['REQUEST_METHOD'] === 'POST') { //your code... }`. This will check whether the request is a normal load, or a postback caused by form submission. See https://stackoverflow.com/questions/359047/detecting-request-type-in-php-get-post-put-or-delete – ADyson May 13 '19 at 09:20
  • And `r.type_sport = '%$sport%'` makes no sense...you only need wildcard (`%`) characters if you're using `LIKE` instead of `=` to find a value within the field (not an exact match to it). It's not clear here which functionality you actually want. – ADyson May 13 '19 at 09:31
  • @ADyson thank you for helping me out. For type of sport, user can search the drop down list then click the sport type he choose. So, the $sport is the value of the list clicked by the user. I used r.type_sport LIKE '%$sport%' but it will display all the sport type that available. What I am trying to do is to display only the room that user clicked but I don't know how. – Insyirahmohd May 13 '19 at 09:50
  • If the user is selecting a specific sport from a list then `r.type_sport = '$sport'` would make more sense. But beyond that I can't help you at the moment - see my 2nd comment above. Once you have included the necessary information in your question please send me another comment. Thanks. – ADyson May 13 '19 at 09:55

0 Answers0