· 9 years ago · Jul 03, 2017, 02:08 PM
1<?php
2 /* contains a function that connects to server selects the DB, defines an error msg & returns a value */
3 include ("connect.php");
4
5 $count = 0; // count for creation & populating of tables
6 $errMsg = ""; // $errMsg defined in connectDB() & returned via reference
7 $db_connect = connectDB(&$errMsg);
8
9 //************ create the friends table if it doesn't exist ************//
10 $SqlQuery = "CREATE TABLE IF NOT EXISTS friends (friend_id INTEGER NOT NULL AUTO_INCREMENT
11 PRIMARY KEY, friend_email VARCHAR(50) NOT NULL, password VARCHAR(20) NOT NULL,
12 profile_name VARCHAR(30) NOT NULL, date_started DATE NOT NULL,
13 num_of_friends INTEGER UNSIGNED)";
14 $SqlResult = @mysqli_query($db_connect, $SqlQuery)
15 or die("<p>Unable to create the friends table" . $errMsg . "</p>");
16
17 //***************** check if the friends table exists *****************//
18 $SqlQuery = "SELECT * FROM friends";
19 $SqlResult = @mysqli_query($db_connect, $SqlQuery)
20 or die("<p>Unable to find the friends table" . $errMsg . "</p>");
21
22 //************** check if records exist in friends table ************//
23 $numRows = @mysqli_num_rows($SqlResult);
24 if ($numRows == 0){
25 //************** Create 10 records into friends table *************//
26 $SqlQuery = "INSERT INTO friends(friend_id, friend_email, password,
27 profile_name, date_started, num_of_friends)
28 VALUES(0001, 'bs@gmail.com', 'xtc99', 'BSamuels', '2013-05-05', 3),
29 (NULL, 'starter77@yahoo.com.au', 'z1699', 'JAnt', '2013-05-05', 3),
30 (NULL, 'dwnandout@gmail.com', 'gfd70', 'TomTom', '2013-05-05', 2),
31 (NULL, 'lminelle@hotmail.com', 'eftg9', 'LizaM', '2013-05-05', 2),
32 (NULL, 'sbeach@gmail.com', 'sb123', 'SandyB', '2013-05-05', 2),
33 (NULL, 'newbie@gmail.com', 'neu73', 'NeuB', '2013-05-05', 2),
34 (NULL, 'VerukaS@gmail.com', 'VS4me', 'VSalt', '2013-05-05', 2),
35 (NULL, 'Maas@hotmail.com', 'M445', 'Maas', '2013-05-05', 2),
36 (NULL, 'Ojay@gmail.com', '0j4y', 'Ojay', '2013-05-05', 2),
37 (NULL, 'jb@yahoo.com.au', 'JB1977', 'Joadsters', '2013-05-05', 2)";
38
39 $SqlResult = @mysqli_query($db_connect, $SqlQuery)
40 or die("<p>Unable to add records to friends table" . $errMsg . "</p>");
41 $count++;
42 }
43
44 //*********** create the myfriends table if it doesn't exist ***********//
45 $SqlQuery = "CREATE TABLE IF NOT EXISTS myfriends (friend_id1 INTEGER NOT NULL,
46 friend_id2 INTEGER NOT NULL)";
47 $SqlResult = @mysqli_query($db_connect, $SqlQuery)
48 or die("<p>Unable to create the myfriends table" . $errMsg . "</p>");
49
50 //***************** check if the myfriends table exists *****************//
51 $SqlQuery = "SELECT * FROM myfriends";
52 $SqlResult = @mysqli_query($db_connect, $SqlQuery)
53 or die("<p>Unable to find the myfriends table" . $errMsg . "</p>");
54
55 //************** check if records exist in friends table ***************//
56 $numRows = @mysqli_num_rows($SqlResult);
57 if ($numRows == 0){
58 //*************** Create 20 records into myfriends table **************//
59 $SqlQuery = "INSERT INTO myfriends(friend_id1, friend_id2)
60 VALUES(1, 2), (1, 3), (1, 6), (2, 1), (2, 3), (2, 6), (3, 1), (3, 2),
61 (4, 5), (4, 7), (5, 4), (5, 7), (6, 1), (6, 2), (7, 4), (7, 5), (8, 9),
62 (8, 10), (9, 8), (9, 10), (10, 8), (10, 9)";
63 $SqlResult = @mysqli_query($db_connect, $SqlQuery)
64 or die("<p>Unable to add records to the myfriends table" . $errMsg . "</p>");
65 $count++;
66 }
67 @mysqli_close($db_connect);
68
69 if ($count > 0){
70 echo "<p><span class='content_text'>Tables successfully created and populated.</span></p>";
71 } else {
72 echo "<p><span class='content_text'>Tables and records already exist.</span></p>";
73 }
74?>