SHARE
TWEET

Untitled

a guest Jan 28th, 2016 98 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE Customers
  2. (
  3. CusID int NOT NULL AUTO_INCREMENT,
  4. LastName varchar(255) NOT NULL,
  5. FirstName varchar(255),
  6. PRIMARY KEY (CusID)
  7. );
  8.  
  9. CREATE TABLE Categories
  10. (
  11. CatID int NOT NULL AUTO_INCREMENT,
  12. CatName varchar(255) NOT NULL,
  13. Description varchar(255) NOT NULL,
  14. PRIMARY KEY (CatID)
  15. );
  16.  
  17. CREATE TABLE Media
  18. (
  19. MedID int NOT NULL AUTO_INCREMENT,
  20. MedName varchar(255) NOT NULL,
  21. MedDesc varchar(255) NOT NULL,
  22. MedType varchar(255) NOT NULL,
  23. PRIMARY KEY (MedID)
  24. );
  25.  
  26. CREATE TABLE Rentals
  27. (
  28. RentID int NOT NULL AUTO_INCREMENT,
  29. CusID varchar(255) NOT NULL,
  30. MedID varchar(255) NOT NULL,
  31. PRIMARY KEY (RentID)
  32. );
  33.  
  34. INSERT INTO Customers (LastName, FirstName) VALUES ('Dole','Bob');
  35. INSERT INTO Customers (LastName, FirstName) VALUES ('Doe','John');
  36. INSERT INTO Customers (LastName, FirstName) VALUES ('Smith','Ryan');
  37. INSERT INTO Customers (LastName, FirstName) VALUES ('Adams','Jack');
  38. INSERT INTO Customers (LastName, FirstName) VALUES ('Brood','Caladan');
  39. INSERT INTO Customers (LastName, FirstName) VALUES ('Rake','Anomander');
  40. INSERT INTO Customers (LastName, FirstName) VALUES ('Ruin','Silchas');
  41. INSERT INTO Customers (LastName, FirstName) VALUES ('Orlong','Karsa');
  42. INSERT INTO Customers (LastName, FirstName) VALUES ('Paran','Ganoes');
  43. INSERT INTO Customers (LastName, FirstName) VALUES ('Sengar','Trull');
  44.  
  45.  
  46. INSERT INTO Categories (CatName, Description) VALUES ('DVD','a type of compact disc able to store large amounts of data');
  47. INSERT INTO Categories (CatName, Description) VALUES ('BluRay','a digital optical disc data storage format');
  48. INSERT INTO Categories (CatName, Description) VALUES ('Game','a game played by electronically manipulating images produced by a computer program on a television screen or other display screen');
  49.  
  50. INSERT INTO Media (MedName, MedType, MedDesc) VALUES ('Hostage','DVD','A failed police negotiator turned small town cop must save the lives of a family held hostage.');
  51. INSERT INTO Media (MedName, MedType, MedDesc) VALUES ('Inception','DVD','A thief who steals corporate secrets through use of the dream-sharing technology is given the inverse task of planting an idea into the mind of a CEO.');
  52. INSERT INTO Media (MedName, MedType, MedDesc) VALUES ('Goodfellas','DVD','Henry Hill and his friends work their way up through the mob hierarchy.');
  53. INSERT INTO Media (MedName, MedType, MedDesc) VALUES ('Se7en','DVD','Two detectives hunt a serial killer who uses the seven deadly sins as his modus operandi.');
  54. INSERT INTO Media (MedName, MedType, MedDesc) VALUES ('Interstellar','DVD','A team of explorers travel through a wormhole in space in an attempt to ensure humanitys survival.');
  55. INSERT INTO Media (MedName, MedType, MedDesc) VALUES ('Casablanca','DVD','An American expatriate meets a former lover with unforeseen complications.');
  56. INSERT INTO Media (MedName, MedType, MedDesc) VALUES ('Psycho','DVD','A Phoenix secretary checks into a remote motel run by a young man under the domination of his mother.');
  57. INSERT INTO Media (MedName, MedType, MedDesc) VALUES ('Whiplash','DVD','A promising young drummer enrolls at a cut-throat music conservatory.');
  58. INSERT INTO Media (MedName, MedType, MedDesc) VALUES ('Memento','DVD','A man creates a strange system to help him remember things.');
  59. INSERT INTO Media (MedName, MedType, MedDesc) VALUES ('Gladiator','DVD','A Roman general comes to Rome as a gladiator to seek revenge.');
  60. INSERT INTO Media (MedName, MedType, MedDesc) VALUES ('Alien','DVD','The commercial vessel Nostromo receives a distress call from an unexplored planet.');
  61. INSERT INTO Media (MedName, MedType, MedDesc) VALUES ('Aliens','DVD','The planet from Alien (1979) has been colonized, but contact is lost.');
  62. INSERT INTO Media (MedName, MedType, MedDesc) VALUES ('Oldboy','BluRay','After being kidnapped and imprisoned for 15 years, Oh Dae-Su is released, only to find that he must find his captor in 5 days.');
  63. INSERT INTO Media (MedName, MedType, MedDesc) VALUES ('Vertigo','BluRay','A San Francisco detective suffering from acrophobia investigates the strange activities of an old friend''s wife, all the while becoming dangerously obsessed with her.');
  64. INSERT INTO Media (MedName, MedType, MedDesc) VALUES ('M','BluRay','When the police in a German city are unable to catch a child-murderer, other criminals join in the manhunt.');
  65. INSERT INTO Media (MedName, MedType, MedDesc) VALUES ('The Shawshank Redemption','BluRay','Two imprisoned men bond over a number of years, finding solace and eventual redemption through acts of common decency.');
  66. INSERT INTO Media (MedName, MedType, MedDesc) VALUES ('The Godfather','BluRay','The aging patriarch of an organized crime dynasty transfers control of his clandestine empire to his reluctant son.');
  67. INSERT INTO Media (MedName, MedType, MedDesc) VALUES ('The Dark Knight','BluRay','When the menace known as the Joker wreaks havoc and chaos on the people of Gotham, the caped crusader must come to terms with one of the greatest psychological tests of his ability to fight injustice.');
  68. INSERT INTO Media (MedName, MedType, MedDesc) VALUES ('Pulp Fiction','BluRay','The lives of two mob hit men, a boxer, a gangster''s wife, and a pair of diner bandits intertwine in four tales of violence and redemption.');
  69. INSERT INTO Media (MedName, MedType, MedDesc) VALUES ('Fight Club','BluRay','An insomniac office worker, looking for a way to change his life, crosses paths with a devil-may-care soap maker, forming an underground fight club that evolves into something much, much more...');
  70. INSERT INTO Media (MedName, MedType, MedDesc) VALUES ('Forrest Gump','BluRay','Forrest Gump, while not intelligent, has accidentally been present at many historic moments, but his true love, Jenny Curran, eludes him.');
  71. INSERT INTO Media (MedName, MedType, MedDesc) VALUES ('The Matrix','BluRay','A computer hacker learns from mysterious rebels about the true nature of his reality and his role in the war against its controllers.');
  72. INSERT INTO Media (MedName, MedType, MedDesc) VALUES ('City of God','BluRay','Two boys growing up in a violent neighborhood of Rio de Janeiro take different paths: one becomes a photographer, the other a drug dealer.');
  73. INSERT INTO Media (MedName, MedType, MedDesc) VALUES ('The Usual Suspects','BluRay','A sole survivor tells of the twisty events leading up to a horrific gun battle on a boat, which begin when five criminals meet at a seemingly random police lineup.');
  74. INSERT INTO Media (MedName, MedType, MedDesc) VALUES ('Life is Beautiful','BluRay','When an open-minded Jewish librarian and his son become victims of the Holocaust, he uses a perfect mixture of will, humor and imagination to protect his son from the dangers around their camp.');
  75. INSERT INTO Media (MedName, MedType, MedDesc) VALUES ('Halo','Game','Halo is a military science fiction first-person shooter video game created by Bungie.');
  76. INSERT INTO Media (MedName, MedType, MedDesc) VALUES ('Black Ops','Game','Black Ops is a 2010 first-person shooter video game in the Call of Duty franchise.');
  77. INSERT INTO Media (MedName, MedType, MedDesc) VALUES ('Diablo III','Game','Diablo III is an action role-playing video game developed and published by Blizzard Entertainment.');
  78. INSERT INTO Media (MedName, MedType, MedDesc) VALUES ('Skyrim','Game','Skyrim is an action role-playing open world video game developed by Bethesda Game Studios and published by Bethesda Softworks.');
  79. INSERT INTO Media (MedName, MedType, MedDesc) VALUES ('Fallout','Game','Fallout is a series of post-apocalyptic role-playing video games.');
  80.  
  81. INSERT INTO Rentals (CusID, MedID) VALUES ('1','14');
  82. INSERT INTO Rentals (CusID, MedID) VALUES ('1','11');
  83. INSERT INTO Rentals (CusID, MedID) VALUES ('2','4');
  84. INSERT INTO Rentals (CusID, MedID) VALUES ('4','7');
  85. INSERT INTO Rentals (CusID, MedID) VALUES ('5','5');
  86. INSERT INTO Rentals (CusID, MedID) VALUES ('6','5');
  87. INSERT INTO Rentals (CusID, MedID) VALUES ('7','1');
  88. INSERT INTO Rentals (CusID, MedID) VALUES ('4','3');
  89. INSERT INTO Rentals (CusID, MedID) VALUES ('8','6');
  90. INSERT INTO Rentals (CusID, MedID) VALUES ('9','10');
  91. INSERT INTO Rentals (CusID, MedID) VALUES ('2','6');
  92. INSERT INTO Rentals (CusID, MedID) VALUES ('3','12');
  93. INSERT INTO Rentals (CusID, MedID) VALUES ('4','30');
  94. INSERT INTO Rentals (CusID, MedID) VALUES ('5','25');
  95. INSERT INTO Rentals (CusID, MedID) VALUES ('6','26');
  96. INSERT INTO Rentals (CusID, MedID) VALUES ('7','22');
  97. INSERT INTO Rentals (CusID, MedID) VALUES ('8','19');
  98. INSERT INTO Rentals (CusID, MedID) VALUES ('9','2');
  99. INSERT INTO Rentals (CusID, MedID) VALUES ('10','17');
  100. INSERT INTO Rentals (CusID, MedID) VALUES ('4','23');
  101.  
  102.  
  103.  
  104. <!DOCTYPE html>
  105. <html>
  106. <head>
  107. <style>
  108. table {
  109.   border: 1px solid #000;
  110. }
  111. </style>
  112. </head>
  113. <body>
  114.  
  115. <?php
  116. $servername = "localhost";
  117. $username = "root";
  118. $password = "";
  119.  
  120. // Create connection
  121. $conn = new mysqli($servername, $username, $password);
  122.  
  123. // Check connection
  124. if ($conn->connect_error) {
  125.     die("Connection failed: " . $conn->connect_error);
  126. }
  127.  
  128. // We're looking to see what customers are renting
  129.  
  130. $sql = "SELECT * FROM school.Rentals";
  131. $result = $conn->query($sql);
  132.  
  133. if ($result->num_rows > 0) {
  134.     // output data of each row
  135.     echo "<p>This is a list of 20 rental entries.  With more advanced SQL, it could be used to pull the name of the customer, as well as the title and type of media they've rented</p><br />";
  136.     echo "<table><tr><td><b>Rental ID</b></td><td><b>Customer ID</b></td><td><b>Media ID</b></td></tr>";
  137.     while($row = $result->fetch_assoc()) {
  138.         echo "<tr><td>" . $row["RentID"]. " </td><td>" . $row["CusID"]. "</td><td>" . $row["MedID"]. "</td></tr>";
  139.     }
  140.     echo "</table><br /><br />";
  141. } else {
  142.     echo "0 results";
  143. }
  144.  
  145.  
  146. $sql = "SELECT * FROM school.Customers";
  147. $result = $conn->query($sql);
  148.  
  149. if ($result->num_rows > 0) {
  150.     // output data of each row
  151.     echo "<p>This is a list of all the customers in the database</p><br />";
  152.     echo "<table><tr><td><b>Customer ID</b></td><td><b>Last Name</b></td><td><b>First Name</b></td></tr>";
  153.     while($row = $result->fetch_assoc()) {
  154.         echo "<tr><td>" . $row["CusID"]. " </td><td>" . $row["LastName"]. "</td><td>" . $row["FirstName"]. "</td></tr>";
  155.     }
  156.     echo "</table><br /><br />";
  157. } else {
  158.     echo "0 results";
  159. }
  160.  
  161. $sql = "SELECT * FROM school.Categories";
  162. $result = $conn->query($sql);
  163.  
  164. if ($result->num_rows > 0) {
  165.     // output data of each row
  166.     echo "<p>This is a list of all the categories in the database</p><br />";
  167.     echo "<table><tr><td><b>Category ID</b></td><td><b>Category Name</b></td><td><b>Category Description</b></td></tr>";
  168.     while($row = $result->fetch_assoc()) {
  169.         echo "<tr><td>" . $row["CatID"]. " </td><td>" . $row["CatName"]. "</td><td>" . $row["Description"]. "</td></tr>";
  170.     }
  171.     echo "</table><br /><br />";
  172. } else {
  173.     echo "0 results";
  174. }
  175.  
  176. $sql = "SELECT * FROM school.Media";
  177. $result = $conn->query($sql);
  178.  
  179. if ($result->num_rows > 0) {
  180.     // output data of each row
  181.     echo "<p>This is a list of all the movies and games in the database</p><br />";
  182.     echo "<table><tr><td><b>Media ID</td><td><b>Media Type</b></td><td><b>Media Name</b></td><td><b>Media Description</b></td></tr>";
  183.     while($row = $result->fetch_assoc()) {
  184.         echo "<tr><td>" . $row["MedID"]. " </td><td>" . $row["MedType"]. "</td><td>" . $row["MedName"]. "</td><td>" . $row["MedDesc"]. "</td></tr>";
  185.     }
  186.     echo "</table><br />";
  187. } else {
  188.     echo "0 results";
  189. }
  190.  
  191. $conn->close();
  192. ?>
  193. </body>
  194. </html>
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top