Jump to content

six track reservation system (PHP & MYSQL)

eightball

By eightball
in PHP Coding Help

 Share

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
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

 

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
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.