setdata.php | searchcode

/setdata.php

https://bitbucket.org/dmdonnelly/developer-test
PHP | 207 lines | 176 code | 11 blank | 20 comment | 25 complexity | ab72e22c67a16c34a3bd888d56811498 MD5 | raw file

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Databee Test - Set Test Data</title>
    <!-- Bootstrap -->
    <link href="css/bootstrap-4.0.0.css" rel="stylesheet">
	<!-- All SQL connection code contained to a seperate file -->
	<? include("connect.php"); ?>
	</head>
  <body>
    <div class="container mt-2">
      <div class="row">
          <div class="text-center col-md-3 col-12"></div>
		  <div class="text-center col-md-5 col-12">
			  <div class="col-12 card bg-light mx-auto">
              <h3 class="text-center card-header">Set Test Data</h3>
              <form class="card-body" action="<?=$PHP_SELF?>" method="post">
                <div class="form-group row">
                  <label for="numVenue" class="col-form-label">Number of Venues to Add</label>
                  <div class="input-group">
                    <input type="number" min="0" class="form-control" id="numVenue" name="numVenue" >
                  </div>
                </div>
				<div class="form-group row">
                  <label for="numBookings" class="col-form-label">Number of 'Existing Bookings' to Add*</label>
                  <div class="input-group">
                    <input type="number" min="0" class="form-control" id="numBookings" name="numBookings" >
                  </div>
				  <sub style="line-height: 1; text-align: left;">* as data is generated randomly, there may be less entries than selected due to duplicate entries.</sub>
                </div>
				<div class="form-group row">
				  <div class="input-group">
					<label for="doBEachV" class="col-form-label">
						<input type="checkbox" id="doBEachV" name="doBEachV" value="1" > Apply additional bookings to each venue?
					</label>
                  </div>
				</div><div class="form-group row">
				  <div class="input-group">
					<label for="doReset" class="col-form-label">
						<input type="checkbox" id="doReset" name="doReset" value="1" > Reset dataset before repopulating?
					</label>
                  </div>
				</div>
				  <input type="submit" name="submit" value="Submit">
              </form>
            </div>
		  </div>
		  <div class="text-center col-md-4 col-12"></div>
      </div>
    </div>
	<? if(isset($_POST['submit'])): ?>
	<hr>
	<div class="container mt-2">
      <div class="row">
          <div class="text-center col-md-3 col-12"></div>
		  <div class="text-center col-md-5 col-12">
			  <div class="col-12 card bg-light mx-auto">
              	<h3 class="text-center card-header">Results</h3>
	<?
	  // On a reset, delete both tables if they exist, then recreate them
	  // This option can also be used to create the tables for the first time on a new setup
	  if(isset($_POST['doReset'])) {
		  
		  ///////////////////////////////////////////////////////////////////////////////////////////////////////////
		  // The tables below have key values allowing up to 9,999,999,999 entries.                                //
		  // However, in a real-world setting, booking_id would need to have a higher maximum value than venue_id, //
		  // as each venue can have between 264 and 279 bookings, depending on the number of days in the 3 months. //
		  // I decided that it wasn't important enough to change for this test, as in order to exceed the maximum  //
		  // value of booking_id, there would need to be in excess of 26,384,225 venues.                           //
		  // Additionally, the server I used to test this functionality on only allowed 75,000 queries per hour,   //
		  // so I wasn't able to stress-test this to anywhere near those limits                                    //
		  ///////////////////////////////////////////////////////////////////////////////////////////////////////////
		  
		  $result1 = mysql_query("DROP TABLE IF EXISTS `venue`");
		  if (!$result1) {
			echo("<p>Error performing query: " . mysql_error() . "</p>");
			exit();
		  }
		  
		  $result2 = mysql_query(
			"CREATE TABLE IF NOT EXISTS `venue` (
			  `venue_id` int(10) NOT NULL AUTO_INCREMENT,
			  `venue_name` varchar(30) NOT NULL,
			  PRIMARY KEY (`venue_id`)
			)");
		  if (!$result2) {
			echo("<p>Error performing query: " . mysql_error() . "</p>");
			exit();
		  }
		  
		  $result3 = mysql_query("DROP TABLE IF EXISTS `venue_booking`");
		  if (!$result3) {
			echo("<p>Error performing query: " . mysql_error() . "</p>");
			exit();
		  }
		  
		  $result4 = mysql_query(
		  	"CREATE TABLE IF NOT EXISTS `venue_booking` (
			  `booking_id` int(10) unsigned NOT NULL,
			  `venue_id` int(10) unsigned NOT NULL,
			  `date` date NOT NULL,
			  `period` tinyint(1) unsigned NOT NULL,
			  PRIMARY KEY (`booking_id`,`venue_id`,`date`,`period`)
			)");
		  if (!$result4) {
			echo("<p>Error performing query: " . mysql_error() . "</p>");
			exit();
		  }
	  
		  echo "Successfully reset the dataset.<br>";	// If no errors have occurred to halt operations, report success
		  $booking_id = 1; 								// Reset the starting booking ID to 1
	  } else {
		  // If the dataset is not being reset, the existing values from it are required before adding more
		  // This gets the number of existing venues, to ensure the unique key of the table is maintained
		  $sql_query = "SELECT * FROM " . $db_venue;
		  $result = mysql_query($sql_query);
		  $num_venue = mysql_num_rows($result);
		  
		  // This gets the highest existing booking ID, so as to start adding entries one higher
		  $sql_query = "SELECT MAX(`booking_id`) FROM " . $db_venue_book;
		  $result = mysql_query($sql_query);
		  $row = mysql_fetch_array($result);
		  $booking_id = $row[0] + 1;
	  }
	  
	  // The 'Add Venue' code is only run if the submitted data calls for additional venues to be added
	  if ($_POST['numVenue'] > 0) {
		  if(isset($_POST['doReset'])) {
			  $venue_id = 1; 					// Set the initial venue ID to 1 on a full reset
		  } else {
			  $venue_id = $num_venue + 1; 		// Increment the existing maximum venue ID on standard entry
		  }
		  for ($i=1; $i <= $_POST['numVenue']; $i++) {
			  // Rather than generating a random name, venues are just assigned a number matching their ID
			  $data_string = "INSERT INTO `venue` VALUES (" . $venue_id . ", 'Venue " . $venue_id . "')";
			  $result = mysql_query($data_string);
			  if (!$result) {
				echo("<p>Error performing query: " . mysql_error() . "</p>");
				exit();
			  }
			  $venue_id++;
		  }
		  echo $_POST['numVenue'] . " venues have been inserted.<br>";	// If no errors have occurred to halt operations, report success
	  } else {
		  // If a reset has occurred, or there are just no venues in the system, further operations are cancelled
		  if ($num_venue == 0 || isset($_POST['doReset'])) {
			  echo "The dataset has no venues.<br>";
			  exit();
		  }
	  }
	  
	  $startdate = strtotime("now");		// Random dates are selected starting from 'today'
	  $enddate = strtotime("+3 month");		// Random dates are selected up to any point three months from 'today'
	  $n = 0; 								// Counter for how many insertions are carried out, for outputted results
	  
	  // If a reset has occurred, the number of venues is the number inserted. If not, any inserted are then added to the existing number
	  if(isset($_POST['doReset'])) {
		  $num_venue = $_POST['numVenue'];
	  } else {
		  $num_venue += $_POST['numVenue'];
	  }
	  
	  // If the option to assign additional bookings to each venue is selected, then for each venue, the chosen number of random bookings are generated and added
	  // If thet option if not selected, then as part of generating the random bookings, a random venue is selected
	  if(isset($_POST['doBEachV'])) {
		  for ($i=1; $i <= $num_venue; $i++) {
			  for ($j=1; $j <= $_POST['numBookings']; $j++) {
				  $date = rand($startdate,$enddate);
				  $period = rand(1,3);
				  $data_string = "INSERT IGNORE INTO `venue_booking` VALUES (" . $booking_id . ", " . $i . ", '". date("Y-m-d",$date) ."', ". $period .")";
				  $result = mysql_query($data_string);
				  if (!$result) {
					echo("<p>Error performing query: " . mysql_error() . "</p>");
					exit();
				  }
				  $booking_id++;
				  $n++;
			  }
		  }
	  } else {
		  for ($j=1; $j <= $_POST['numBookings']; $j++) {
			  $date = rand($startdate,$enddate);
			  $period = rand(1,3);
			  $venue = rand(1,$num_venue);
			  $data_string = "INSERT IGNORE INTO `venue_booking` VALUES (" . $booking_id . ", " . $venue . ", '". date("Y-m-d",$date) ."', ". $period .")";
			  $result = mysql_query($data_string);
			  if (!$result) {
				echo("<p>Error performing query: " . mysql_error() . "</p>");
				exit();
			  }
			  $booking_id++;
			  $n++;
		  }
	  }
	  echo $n . " existing bookings have been inserted.<br>"	// If no errors have occurred to halt operations, report success
	?>
			  </div>
		  </div>
		  <div class="text-center col-md-4 col-12"></div>
      </div>
    </div>
	<? endif; ?>  
  </body>
</html>