Jump to content

six track reservation system (PHP & MYSQL)

eightball

By eightball
in PHP Coding Help

Recommended Posts

eightball Newbie

eightball

Posted
Posted

I'm a beginner with php and mysql, but i wanted to try if i'm able to complete this maybe quite tricky task. My starting point was to

change this code http://mureakuha.com/koodikirjasto/596 of a week reservation calendar the way it would be one day calendar with 6 tracks u could reserve.  The original code is in finnish so i tried translate all to english. Not sure if they're properly translated, but let's hope u'll get the idea ;) Here u can see the original code in action: http://www.zuronet.org/test/varauskalenteri/

 

I think there's lot to before the script works the way it should, but now i have a problem i'm not able to solve myself. When i tried to make a new reservation event, the script gives me the same error message all the time "Error! You can't set a reservation event in the past!"  That means it's the line  139:  if ($_POST['year'] < 2007) die("Error! You can't set a reservation event in the past!");  , so why does the year is less than 2007 in this variable?  U can see my script in action here: http://varaukset.freehostia.com/index_eng.php  The times are in 24H format.

So is there someone out there who could help me with this problem?

 

I also have another question: do u guys think that the script could work this way? i mean are those mysql table fetches reasonable?

 

Thanks a lot

 

 

Aleksi Peltonen

 

In the original script there's only one MYSQL table, but i have 3 tables now:

 

CREATE TABLE `clients` (
  `id` int(10) NOT NULL auto_increment,
  `company` varchar(60) NOT NULL default '',
  `firstname` varchar(20) NOT NULL default '',
  `familyname` varchar(30) NOT NULL default '',
  `address` varchar(70) NOT NULL default '',
  `phone` varchar(15) NOT NULL default '',
  `email` varchar(50) NOT NULL default '',
  `information` varchar(70) NOT NULL default '',
  `notice` varchar(40) NOT NULL default '',
  `remove` int(1) default NULL,
  PRIMARY KEY  (`id`)
)

CREATE TABLE `trackz` (
  `id` int(10) NOT NULL auto_increment,
  `track1` int(1) default NULL,
  `track2` int(1) default NULL,
  `track3` int(1) default NULL,
  `track4` int(1) default NULL,
  `track5` int(1) default NULL,
  `track6` int(1) default NULL,
  PRIMARY KEY  (`id`)
)

CREATE TABLE `reservations` (
  `startt` int(20) default NULL,
  `endt` int(20) default NULL,
  `id` int(10) NOT NULL auto_increment,
  `track_id` int(10) default NULL,
  `client_id` int(10) default NULL,
  PRIMARY KEY  (`id`)
)

 

 

<?php
$start = 800; //  The start time
$stop = 2230; // The stop time

$number_of_tracks = 6;  

$server = "SERVER_NAME"; 
$database = "DATABASE_NAME";    
$table = array("reservations","trackz","clients");  // Tables

$pass = "PASSSWORD";            
$user = "USERNAME";            

// Let's set some variables
$http = $_SERVER['PHP_SELF'];
$tracks = array("Track 1","Track 2","Track 3","Track 4","Track 5","Track 6");
$result = array();
$cycle = array();
$reserved = array(NULL,NULL,NULL,NULL,NULL,NULL);

Function search_between($val, $arr, $trk)
{
  $rtn = false;
  $trk --;
  $tr = array("track1","track2","track3","track4","track4","track5","track6");
  foreach($arr as $array)
  {
    if($array['reservations.startt'] <= $val && $array['reservations.endt'] > $val && $array["'trackz.".$tr[$trk]."'"] == 1) // If the time is reserved, the reservation table will be returned
      { $rtn = $array;
        break; }
  }
  return $rtn;
}

Function get_yesterday($day,$month,$year)
{
  $timestamp = mktime(0,0,0,$month,$day,$year);
  $timestamp -= 60*60*24;

  return $timestamp;
}


//Checks if the track is reserved 
Function track_check($trc)
{
  $rtn = true;
  
  foreach($trc as $array)
  {
    for ($i=1;$i<=6;$i++){
      
      if($array["'trackz.track".$i."'"] == 1)
      { $rtn = false;
        break; }
    } 
  }
  
  return $rtn;
}

//Fetches the previos id of the table
Function previous_id($table, $id_column) {
  if ($table && $id_column) {
    $result = mysql_query("SELECT " . $id_column . " FROM " . $table . "
    ORDER BY " . $id_column . " DESC
    LIMIT 1");
    $stuff = mysql_fetch_assoc($result);
    return $stuff[$id_column];
  } else {
    return false;
  }
}

// Variables...
$day = $_GET['day'];
$month = $_GET['month'];
$year = $_GET['year'];
if(empty($_GET['day'])) $day = date('d');
if(empty($_GET['month'])) $month = date('m');
if(empty($_GET['year'])) $year = date('Y');

// Forward and back button values
$yesterday = get_yesterday($day,$month,$year);
$forward['day'] = date("d", ($yesterday+(2*24*3600)));
$forward['month'] = date("m", ($yesterday+(2*24*3600)));
$forward['year'] = date("Y", ($yesterday+(2*24*3600)));
$back['day'] = date("d", $yesterday);
$back['month'] = date("m", $yesterday);
$back['year'] = date("Y", $yesterday);


if(isset($_POST['confi'])) // If 'jump to day' button has been pressed 
{
  // Error notifications
  if ($_POST['day'] > 31) die("Error! There can't be that many days in a month");
  if ($_POST['month'] > 12) die("Error! There can't be that many months in a day");
  if ($_POST['year'] < 2007) die("Error! You can't set a reservation event in the past");
}

for ($n=$_POST['track'];$n<=$_POST['track2'];$n++) 
{

// Yhdistetään
$yhteys = mysql_connect($server, $user, $pass) or die("Error occured when tried to connect to the database!");
mysql_select_db($database,$yhteys) or die("Error occured when tried to select the database!");

if(!empty($_GET['id']) && $_GET['act'] == "poista" && $_GET['track'] == $n) // The reservation of the track removing process
{
  
  // removing the reservation event
  
  $query = "UPDATE trackz.track".$n." SET trackz.track".$n." = NULL WHERE reservations.id = '".intval($_GET['id'])." AND reservations.track_id = trackz_id";
  mysql_query($query, $yhteys) or die('Error when running command<br>'.$query);
  mysql_close($yhteys);
    
  // The fetch of the track information to the $result array
  $query = "SELECT * FROM trackz WHERE reservations.id = '".intval($_GET['id'])." AND reservations.track_id = trackz_id";
  $haku = mysql_query($query, $yhteys) or die('Error when running command<br>'.$query);
  while($res = @mysql_fetch_array($haku, MYSQL_ASSOC)) $result[] = $res;
  mysql_close($yhteys);
  
  if (track_check($result) != false) // If there's no reserved tracks connected to the reservation event, the whole reservation event will be removed
  {
  $query = "DELETE FROM ".$table[0]." WHERE reservations.id = '".intval($_GET['id'])."'";
  mysql_query($query, $yhteys) or die('Error when running command<br>'.$query);
  mysql_close($yhteys);
  }
  // Refresh the page
  header("Location: ".$http);
}

elseif(isset($_POST['confirm'])) // the reservation process is added, also is checked if the button has been pressed
{
  // Error notifications
  if (empty($_POST['firstname']) && empty($_POST['familyname']) && empty($_POST['company'])) die("Error! Fill one of these fields: firstname, familyname, company");
  if ($_POST['day'] > 31) die("Error! There can't be that many days in a month!");
  if ($_POST['month'] > 12) die("Error! There can't be that many months in a year!");
  if ($_POST['year'] < 2007) die("Error! You can't set a reservation event in the past!");
  if ($_POST['hour'] > 24) die("Error! There can't be that many hours in a day!");
  if ($_POST['minute'] > 60) die("Error! There can't be that many minutes in an hour!");
  if ($_POST['hour2'] > 24) die("Error! There can't be that many hours in a day!");
  if ($_POST['minute2'] > 60) die("Error! There can't be that many minutes in an hour!");
  if ($_POST['hour'] > $_POST['hour2']) die("Error! the starting hour should not be greater than the ending hour!");
  

  
  // information
  $start_date = mktime($_POST['hour'], $_POST['minute'], 0, $_POST['month'], $_POST['day'], $_POST['year']);
  $end_date = mktime($_POST['hour2'], $_POST['minute2'], 0, $_POST['month'], $_POST['day'], $_POST['year']);
  $information = mysql_escape_string(htmlspecialchars($_POST['information']));
  $fname = mysql_escape_string(htmlspecialchars($_POST['firstname']));
  $sname = mysql_escape_string(htmlspecialchars($_POST['familyname']));
  $company = mysql_escape_string(htmlspecialchars($_POST['company']));
  $email = mysql_escape_string(htmlspecialchars($_POST['email']));
  $address = mysql_escape_string(htmlspecialchars($_POST['address']));
  $phone = mysql_escape_string(htmlspecialchars($_POST['phone']));
  $notice = mysql_escape_string(htmlspecialchars($_POST['notice']));
  

  
  // is checked if the time has already reserved
  $query = "SELECT * FROM ".$table[0]." WHERE startt >= ".mktime(0, 0, 0, $_POST['month'], $_POST['day'], $_POST['year'])." AND endt <= ".mktime(23, 59, 59, $_POST['month'], $_POST['day'], $_POST['year']);
  $haku = mysql_query($query, $yhteys) or die('Error when running command<br>'.$query);
  $num = mysql_num_rows($haku);
  while($res = mysql_fetch_array($haku, MYSQL_ASSOC)) $result[] = $res;

  // is checked if the start of the reservation event is inside of any other events
  $gofor = ($end_date-$start_date)/60/30;
  for($r=0; $r < $gofor; $r++)
    if(search_between(($start_date+$r*1800), $result, $n) != false) die("Error! This time is already reserved!");
// is checked if the end of the reservation event is inside of any other events
  if (search_between(($end_date-1), $result, $n) != false) die("Error! This time is already reserved!");

  $reserved[$n-1]=1;
}
}
  if(isset($_POST['confirm']))
  {
   $query = "INSERT INTO trackz (track1, track2, track3, track4, track5, track6) VALUES ( ".$reserved[0].", ".$reserved[1].", ".$reserved[2].", ".$reserved[3].", ".$reserved[4].", ".$reserved[5].")";
  mysql_query($query, $yhteys) or die('Error when running command<br>'.$query);
  $last_track_id = previous_id($table[1], "id");
  mysql_close($yhteys);
  
  $query = "INSERT INTO clients (company, firstname, familyname, address, phone, email, information, notice) VALUES ( '".$company."', '".$fname."', '".$sname."', '".$address."', ".$phone.", '".$email."', '".$information."', '".$notice."')";
  $last_client_id = previous_id($table[2], "id");
  mysql_query($query, $yhteys) or die('Error when running command<br>'.$query);
  
  $query = "INSERT INTO reservations (startt, endt, client_id, track_id ) VALUES ( ".$start_date.", ".$end_date.", ".$last_client_id.", ".$last_track_id.")";
  mysql_query($query, $yhteys) or die('Error when running command<br>'.$query);
  mysql_close($yhteys);

   // Refresh the page
  header("Location: ".$http);

  }
  
  

// the reservation event information is fetched to the $result array
$query = "SELECT * FROM reservations, trackz, clients WHERE reservations.startt >= ".$day." AND reservations.endt <= ".($day+(60*60*24))." AND reservations.track_id = trackz.id AND reservations.client_id = clients.id";
$haku = mysql_query($query, $yhteys) or die('Error when running command<br>'.$query);
while($res = @mysql_fetch_array($haku, MYSQL_ASSOC)) $result[] = $res;
mysql_close($yhteys);




// The creating of a reservation table process

$contents .= '
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
  <head>
    <title>Kalenteri v0.1</title>
    <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
    <style type="text/css">

    <!--
body {
      background-color: black;
      color: #386592;
      font-family: Arial;
      font-size: 10pt;
      scrollbar-face-color: #6FC1FF;
      scrollbar-shadow-color: #5596C8;
      scrollbar-highlight-color: #9ED5FF;
      scrollbar-3dlight-color: #292829;
      scrollbar-darkshadow-color: #000000;
      scrollbar-track-color: #000000;
      scrollbar-arrow-color: #9EF4FF;
}

a {
      color: white;
      text-decoration: none;
      background-image: url("navlbg.gif");
      background-repeat: no-repeat;
      background-position: left center;
      font-family: Arial;
      font-size: 10pt;
}

a:link {
      color: white;
      text-decoration: none;
      background-image: url("navlbg.gif");
      background-repeat: no-repeat;
      background-position: left center;
      font-family: Arial;
      font-size: 10pt;
}

a:active {
      color: white;
      text-decoration: none;
      background-image: url("navlbg.gif");
      background-repeat: no-repeat;
      background-position: left center;
      font-family: Arial;
      font-size: 10pt;
}

a:visited {
      color: white;
      text-decoration: none;
      background-image: url("navlbg.gif");
      background-repeat: no-repeat;
      background-position: left center;
      font-family: Arial;
      font-size: 10pt;
}

a:hover {
      border-color: #0084D6;
      border-top: 1px;
      border-bottom: 1px;
      border-right:3px;
      color: #BEFFFF;
      background-image: url("navlbg2.gif");
      background-repeat: no-repeat;
      background-position: left center;
      font-family: Arial;
      font-size: 10pt;
}

.cornercell {
      text-align: center;
      border: 1px solid black;
      height: 20px;
      background-color: #35586C;
      color: white;
}
.daycell {
      text-align: center;
      width: 12%;
      height: 20px;
      border-bottom: 1px solid black;
      border-right: 1px solid black;
      border-top: 1px solid black;
      background-color: #35586C;
      color: white;
}
.timecell {
      text-align: center;
      border-bottom: 1px solid black;
      border-right: 1px solid black;
      border-left: 1px solid black;
      background-color: #35586C;
      width: 11%;
      height: 20px;
      color: white;
}
.concell {
      border-right: 1px solid black;
      border-bottom: 1px solid black;
      background-color: #417591;
      text-align: center;
}
.setcell {
      border-right: 1px solid black;
      border-bottom: 1px solid black;
      background-color: #417591;
      text-align: center;
      color: black;
}
   
    input.selection {
      background-color: #35586C;
      color: white;
      border-color: #5C9CC1;
      border-right-size:1px;
      border-left-size:1px;
      border-top-size:1px;
      border-bottom-size:1px;
}
   
    select.selection {
      background-color: #35586C;
      color: white;
      border-color: #5C9CC1;
      border-right-size:1px;
      border-left-size:1px;
      border-top-size:1px;
      border-bottom-size:1px;
}
    --> 
    </style>
  </head>
  <body>
    <table cellpadding="6" border="0" width="100%">
    <tr>
      <td align="left">
        The current day: <b>'.$day.'.'.$month.'.'.$year.'</b><br />
        <a href="'.$http.'?day='.$back['day'].'&month='.$back['month'].'&year='.$back['year'].'">[Previous Day]</a>
        <a href="'.$http.'?day='.date('d').'&month='.date('m').'&year='.date('Y').'">[This day]</a>
        <a href="'.$http.'?day='.$forward['day'].'&month='.$forward['month'].'&year='.$forward['year'].'">[Next Day]</a><br>
      </td>
      <td align="right">
        <form method="get" action="'.$http.'" style="margin: 0px;">
        Jump to the day:
        <input type="text" size="2" value="'.$day.'" maxlength="2" name="day"> <b>.</b>
        <input type="text" size="2" value="'.$month.'" maxlength="2" name="month"> <b>.</b>
        <input type="text" size="4" value="'.$year.'" maxlength="4" name="year">
        <input type="submit" name="confi" value=">>">
        </form>
      </td>
    </tr>
    </table>
    <table cellspacing="0" cellpadding="1" width="100%">
    <tr>
      <td class="cornercell"> Time </td>
  ';

for($f=0;$f<6;$f++)
{
  $contents .= "    <td class=\"daycell\"> ".$tracks[$f]." </td>\n";
  $set[$g] = 0;
}

$contents .= "    </tr>\n";

for($i=$start; $i<$stop; $i=$i+50) // all the hours are gone through
{
  $contents .= "    <tr>\n";

  // the next piece of code will show the time in the column on the left
  $first_num = substr($i, 0, -2);
  if(substr($i, -2) == 50) $last_num = '30'; else $last_num = '00';
  if($last_num == 30) { $sec_last_num = '00'; $sec_first_num = $first_num+1; } else { $sec_last_num = '30'; $sec_first_num = $first_num; }
  $contents .= "      <td class=\"timecell\">".$first_num.":".$last_num."-".$sec_first_num.":".$sec_last_num."</td>\n";

  // the tracks are gone through, 6 tracks...
  for($g=1; $g <= ($number_of_tracks); $g++)
  {
    $seek_time = 24*60*60+$yesterday+($first_num*60*60+$last_num*60);
    
    $s_result = search_between($seek_time, $result, $g);  // the reservation of the hour is checked for every track
    
    if(!$s_result) // If the reservation event was empty, the cell will be empty
    {
      $information = " ";
      $not_found = false;
    }
    else // The reservation event found, the reservation info is putted into the array
    {
      $not_found = true;
       
      if (empty($s_result['company']))
      {
          if (empty($s_result['familyname'])) { $reservation = stripslashes($s_result['firstname']);}
          else { $reservation = stripslashes($s_result['familyname']);}
      }
      else { $reservation = stripslashes($s_result['company']);}
      
      
      
      // if there's no more reservation on the $g track and the reservation evet will be one hour or longer, in the $cycle variable let's set it to 'go through'
      if($cycle[$g] == 0 && (($s_result['endt'] - $s_result['startt'])/60/30) >= 1)
      {
        $cycle[$g] = ($s_result['endt'] - $s_result['startt'])/60/30; // How many hours the reservation event will be last is calculated. Hours = rowspan, is putted in to the $cycle variable in the $g track column
        $begin = 1; // the new cell must be started is set 
      }
    }
   
    if($cycle[$g] == 0) // the empty cell is printed, if there's no info to put in
    {
      $contents .= "      <td class=\"concell\">".$reservation." </td>\n"; // the drawing of the old cell is continued or the new empty cell is created
    }
    elseif($begin == 1) // the new cell is started and some info is written in.
    {
      $contents .= "      <td class=\"setcell\" rowspan=\"".$cycle[$g]."\">"; // the new cell is created, rowspan (hours)
      if($not_found)
        $contents .= '<a href="'.$http.'?act=poista&track='.$g.'&id='.$s_result['id'].'&day='.$day.'&month='.$month.'&year='.$year.'">[D]</a> ';
      $contents .= $information."</td>\n";
      $begin = 0;
    }
    if($cycle[$g] > 0) // If the info has been printed into the cell, the cell won't be finish until the end of the reservation event
    {
      $cycle[$g]--;
    }
  }
  $contents .= "    </tr>\n";
}

$contents .= '
    </table><br>
   
    <form action="'.$http.'?day='.$day.'&month='.$month.'&year='.$year.'" method="post" name="form">
    <table border="0" cellpadding="3" cellspacing="0" width="98%">
    <tr>
      <td valign="middle" align="left">The time to reserve</td>
      <td valign="middle" align="left">
        '.$day.'<b>.</b>
        '.$month.'<b>.</b>
        '.$year.'
        Time: <select name="hour" size="1">
  ';
         
  for($r=substr($start, 0, -2); $r<=substr($stop, 0, -2); $r++)
    $contents .= '        <option value="'.$r.'">'.$r."</option>\n";
   
  $contents .= '
        </select> <b>:</b>
        <select name="minute" size="1">
           <option value="00">00</option>
           <option value="30">30</option>
        </select> -
        <select name="hour2" size="1">
  ';
       
  for($r=substr($start, 0, -2); $r<=substr($stop, 0, -2); $r++)
    $contents .= '        <option value="'.$r.'">'.$r."</option>\n";
   
  $contents .= '
        </select> <b>:</b>
        <select name="minute2" size="1">
           <option value="00">00</option>
           <option value="30">30</option>
        </select>
      </td>
    </tr>
    <tr>
      <td valign="middle" align="left">The tracks to reserve:</td>
      <td valign="middle" align="left">
      <select name="track" size="1">
   ';
   
   for ($i=1;$i<=$number_of_tracks;$i++)
    $contents .= '        <option value="'.$i.'">'.$i."</option>\n";
    
   $contents .= ' 
    </select> -
        
      <select name="track2" size="1">
   ';
   
   for ($i=1;$i<=$number_of_tracks;$i++)
    $contents .= '        <option value="'.$i.'">'.$i."</option>\n";
    
   $contents .= ' 
    </select>
    </td>
    </tr>
    <tr>
      <td valign="middle" align="left">first name     </td>
      <td valign="middle" align="left"><input type="text" size="20" maxlength="20" name="firstname">
         <input type="checkbox" name="remove" value="remove" />Also remove the client information after the reservation event has been removed</td>
    </tr>
    <tr>
    <td valign="middle" align="left">family name    </td>
    <td valign="middle" align="left"><input type="text" size="30" maxlength="30" name="familyname"></td>
    </tr>
    <tr>
      <td valign="middle" align="left">company     </td>
      <td valign="middle" align="left"><input type="text" size="30" maxlength="60" name="company"></td>
    </tr>
    <tr>
      <td valign="middle" align="left">address     </td>
      <td valign="middle" align="left"><input type="text" size="30" maxlength="70" name="address"></td>
    </tr>
    <tr>
      <td valign="middle" align="left">Email     </td>
      <td valign="middle" align="left"><input type="text" size="30" maxlength="50" name="email"></td>
    </tr>
    <tr>
      <td valign="middle" align="left">phone     </td>
      <td valign="middle" align="left"><input type="text" size="15" maxlength="15" name="phone"></td>
    </tr>
    <tr>
      <td valign="middle" align="left">info about the client     </td>
      <td valign="middle" align="left"><input type="text" size="30" maxlength="70" name="information"></td>
    </tr>
    <tr>
      <td valign="middle" align="left">notice     </td>
      <td valign="middle" align="left"><input type="text" size="30" maxlength="40" name="notice"></td>
    </tr>
    <tr>
      <td valign="middle" align="left">Add</td>
      <td valign="middle" align="left"><input type="submit" name="confirm" value="Add"></td>
    </tr>
    </table>
    </form>
  </body>
</html>';
print $contents;
?>

Link to comment
https://forums.phpfreaks.com/topic/58398-six-track-reservation-system-php-mysql/
Share on other sites
  • 8 months later...
laffin Newbie

laffin

Posted
Posted

use datetime/timestamp structures in yer MySQL

 

it's far more easiear to deal with in php

 

CREATE TABLE `reservations` (
  `startt` TIMESTAMP default NULL,
  `endt` TIMESTAMP default NULL,
  `id` int(10) NOT NULL auto_increment,
  `track_id` int(10) default NULL,
  `client_id` int(10) default NULL,
  PRIMARY KEY  (`id`)
)

 

to use the timestamp in php, use date to format as needed.

 

conversion of user input into a timestamp use strtotime

 

than testing it against a prior time is a matter of checking the result against time

 

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.