CreateTables.php Source Code

1. In cPanel, create a new database, user and user password. Make sure the user has access privileges to the database.
2. Create a directory for your pages (e.g., /campus_angular/). Store all the files you create in the directory.
3. Create a file named _global.php containing the following 12 lines of code:
<?php

    $sqlHost = 'localhost';
    $sqlUser = 'xxxxxx_uuuuuuu';   // replace with database username
    $sqlPass = 'pppppppp';    // replace with user password
    $sqlDatabase = 'xxxxxx_dddddddd';    // replace with database name

    $conn = mysql_connect($sqlHost, $sqlUser, $sqlPass) or die("Couldn't connect to MySQL server on : " . mysql_error() . '.');

    $db = mysql_select_db($sqlDatabase, $conn) or die("Couldn't select database $sqlDatabase: " . mysql_error() . '.');

?>
Before saving and uploading the file, replace the username (xxxxxx_uuuuuuu), password (pppppppp) and database (xxxxxx_dddddddd) name with the names you created in Step 1.
4. Save and execute CreateTables.php to create the database. CreateTables.php consists of following 133 lines of code:
<?php 

include('_global.php');

$sqlList = <<<EOT
DROP TABLE IF EXISTS SALE;
DROP TABLE IF EXISTS AGENT;
DROP TABLE IF EXISTS OFFICE;
DROP TABLE IF EXISTS DESTINATION;
CREATE TABLE IF NOT EXISTS DESTINATION (
  DestinationID int(11) NOT NULL AUTO_INCREMENT,
  DestinationName varchar(100) NOT NULL,
  PRIMARY KEY (DestinationID)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=15 ;
INSERT INTO DESTINATION (DestinationID, DestinationName) VALUES
(1, 'Rome'),
(2, 'Paris'),
(3, 'London'),
(4, 'Dubai'),
(5, 'Delhi'),
(6, 'Moscow'),
(7, 'Kochi'),
(8, 'Bangkok'),
(9, 'Taipei'),
(10, 'Sydney'),
(11, 'Perth'),
(12, 'Auckland'),
(13, 'Mexico City'),
(14, 'Sao Paulo');
CREATE TABLE IF NOT EXISTS OFFICE (
  OfficeID int(11) NOT NULL AUTO_INCREMENT,
  OfficeLocation varchar(100) NOT NULL,
  PRIMARY KEY (OfficeID)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;
INSERT INTO OFFICE (OfficeID, OfficeLocation) VALUES
(1, 'Honolulu'),
(2, 'Manoa'),
(3, 'Waipahu'),
(4, 'Mililani'),
(5, 'Puna'),
(6, 'Hawaii Kai'),
(7, 'Kaneohe'),
(8, 'Kahului');
CREATE TABLE IF NOT EXISTS AGENT (
  AgentID int(11) NOT NULL AUTO_INCREMENT,
  AgentName varchar(100) NOT NULL DEFAULT '',
  OfficeKey int(11) NOT NULL,
  PRIMARY KEY (AgentID),
  KEY OfficeKey (OfficeKey)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=13 ;
INSERT INTO AGENT (AgentID, AgentName, OfficeKey) VALUES
(1, 'Tom Brower', 1),
(2, 'Isaac Choy', 2),
(3, 'Ty Cullen', 3),
(4, 'Ryan Yamane', 4),
(5, 'Joy San Buenaventura', 5),
(6, 'Mark Hashem', 6),
(7, 'Jarrett Keohokalole', 7),
(8, 'Linda Ichiyama', 1),
(9, 'Ken Ito', 7),
(10, 'Takashi Ohno', 1),
(11, 'Gene Ward', 6),
(12, 'Kyle Yamashita', 8);
CREATE TABLE IF NOT EXISTS SALE (
  SaleID int(11) NOT NULL AUTO_INCREMENT,
  SaleDate date DEFAULT NULL,
  AgentKey int(11) NOT NULL,
  Amount decimal(11,2) DEFAULT NULL,
  DestinationKey int(11) NOT NULL,
  PRIMARY KEY (SaleID),
  KEY AgentKey (AgentKey),
  KEY DestinationKey (DestinationKey)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=31 ;
INSERT INTO SALE (SaleID, SaleDate, AgentKey, Amount, DestinationKey) VALUES
(1, '2014-12-02', 4, '200.00', 12),
(2, '2014-02-03', 3, '1200.00', 2),
(3, '2014-06-25', 2, '400.00', 5),
(4, '2014-04-04', 12, '3000.00', 5),
(5, '2014-03-08', 1, '2500.00', 7),
(6, '2014-11-05', 5, '300.00', 3),
(7, '2014-05-12', 8, '250.00', 1),
(8, '2014-03-05', 4, '2000.00', 10),
(9, '2014-03-04', 3, '300.00', 11),
(10, '2014-05-06', 4, '4500.00', 10),
(11, '2014-06-07', 5, '3400.00', 12),
(12, '2014-07-08', 6, '20.00', 13),
(13, '2014-08-09', 7, '2000.00', 14),
(14, '2014-09-10', 8, '2300.00', 9),
(15, '2014-02-21', 9, '1200.00', 8),
(16, '2016-08-26', 11, '2300.00', 7),
(17, '2014-04-21', 10, '700.00', 6),
(18, '2014-03-15', 9, '6000.00', 5),
(19, '2014-02-24', 8, '4000.00', 4),
(20, '2014-01-31', 7, '4000.00', 3),
(21, '2014-07-26', 6, '3600.00', 2),
(22, '2014-08-12', 5, '3400.00', 1),
(23, '2014-09-13', 4, '4500.00', 2),
(24, '2014-04-05', 3, '6700.00', 3),
(25, '2014-02-03', 2, '2300.00', 4),
(26, '2014-02-08', 1, '230.00', 5),
(27, '2014-02-08', 1, '320.00', 6),
(28, '2014-02-06', 2, '3210.00', 6),
(29, '2014-01-06', 3, '3200.00', 7),
(30, '2014-09-08', 4, '1090.00', 8);
ALTER TABLE AGENT
  ADD CONSTRAINT AGENT_ibfk_1 FOREIGN KEY (OfficeKey) REFERENCES OFFICE (OfficeID);
ALTER TABLE SALE
  ADD CONSTRAINT SALE_ibfk_1 FOREIGN KEY (AgentKey) REFERENCES AGENT (AgentID),
  ADD CONSTRAINT SALE_ibfk_2 FOREIGN KEY (DestinationKey) REFERENCES DESTINATION (DestinationID);
EOT;
$sqls = explode(";",$sqlList);
foreach ($sqls as &$sql)
{
    if($sql>'')
    {
        $echo .= "$sql\n";
        $sqlExecute = mysql_query($sql, $conn) or die("Couldn't perform query $sql (".__LINE__."): " . mysql_error() . '.');
    }
}

$sqlSelect = "SHOW TABLE STATUS";

$sqlTable = mysql_query($sqlSelect, $conn) or die("Couldn't perform query $sqlSelect (".__LINE__."): " . mysql_error() . '.');


while($sqlRecord = mysql_fetch_assoc($sqlTable)) array_push($properties,$sqlRecord);

$message = "Done";

echo json_encode([ "properties" => $properties, "echo" => $echo, "message" => $message ], JSON_PRETTY_PRINT);

?>
Confirm your database has been populated using phpMyAdmin in cPanel.


Back