Create a MySQL Database called “CSC306Class”. Create three (3) tables in the database called “studentInf”, “IntructorInf”,...

Question

Question

Create a MySQL Database called “CSC306Class”. Create three (3) tables in the database called “studentInf”, “IntructorInf”,...

Create a MySQL Database called “CSC306Class”. Create three (3) tables in the database called “studentInf”, “IntructorInf”, and “LikedAndDisliked”. Each table should consist of five columns that are relatable to the table name. Insert at least five row of data in each table. The data can be any information as long as it is related to the table name. Upload the codes to complete the above for grading. Extra Credit: If you were able to download and setup an php server on computer. Try to create the database, tables, and rows. Then upload a screenshot and code for extra credits

This is for a PHP class

engineering Computer-Science

Add a comment Improve this question Transcribed image text

Answer 1

Answer #1

I'm using Ubuntu for setting up PHP apache and php-mysql server.
Installed them by following commands:-

$> sudo apt install apache2
$> sudo apt install mysql-server

Then create root user on localhost with password 12345.

$> mysql -u root -p -h localhost

Then create a new user with host and password.
i have created 'demo' user with password '12345'.

$mysql> CREATE USER 'demo'@'localhost' IDENTIFIED BY '12345';

Then create database CSC306Class

$mysql> CREATE DATABASE CSC306Class;

Grant all privileges for 'demo' user for this database

$mysql> GRANT ALL PRIVILEGES ON CSC306Class.* TO 'demo'@'localhost';

PHP file for creating Tables.

<!DOCTYPE html>
<html>
<head>
<title>PHP Class</title>
</head>
<body>

<?php

$servername = "localhost";
$username = "demo";
$password = "12345";
$dbname = "CSC306Class";

// create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// check connection is created
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

// StudentInf table
$StudentInfTable = "CREATE TABLE StudentInf (
rollNo INT(4) PRIMARY KEY,
firstName VARCHAR(50) NOT NULL,
lastName VARCHAR(50) NOT NULL,
sex VARCHAR(10),
course VARCHAR(50)
)";

// InstructorInf table
$InstructorInfTable = "CREATE TABLE InstructorInf (
id INT(4) PRIMARY KEY,
firstName VARCHAR(50) NOT NULL,
lastName VARCHAR(50) NOT NULL,
title VARCHAR(50),
phoneNo VARCHAR(20)
)";

// LikedAndDislikedTable
$LikedAndDislikedTable = "CREATE TABLE LikedAndDisliked (
id INT(4) PRIMARY KEY,
liked INT(4),
disliked INT(4),
comment VARCHAR(50),
rating INT(5)
)";

// drop StudentInf table if already exists
if ($conn->query("DROP TABLE IF EXISTS StudentInf") === TRUE) {
// create StudentInf table
if ($conn->query($StudentInfTable) === TRUE) {
 echo "Table StudentInf created successfully ";
} else {
 echo "Error creating table: " . $conn->error;
}
} else {
 echo "Error dropping table: " . $conn->error;
}

// drop InstructorInf table if already exists and create new
if ($conn->query("DROP TABLE IF EXISTS InstructorInf") === TRUE) {
if ($conn->query($InstructorInfTable) === TRUE) {
 echo "Table InstructorInf created successfully ";
} else {
 echo "Error creating table: " . $conn->error;
}
} else {
 echo "Error dropping table: " . $conn->error;
}

// drop LikedAndDisliked table if already exists and create new
if ($conn->query("DROP TABLE IF EXISTS LikedAndDisliked") === TRUE) {
if ($conn->query($LikedAndDislikedTable) === TRUE) {
 echo "Table LikedAndDisliked created successfully ";
} else {
 echo "Error creating table: " . $conn->error;
}
} else {
 echo "Error dropping table: " . $conn->error;
}

// array of StudentInf table data
$studentData = array("INSERT INTO StudentInf VALUES (101, 'AAAAA', 'ZZZZZ', 'Male', 'PHP')",
                     "INSERT INTO StudentInf VALUES (102, 'BBBBB', 'YYYYY', 'Female', 'C++')",
                     "INSERT INTO StudentInf VALUES (103, 'CCCCC', 'XXXXX', 'Female', 'Java')",
                     "INSERT INTO StudentInf VALUES (104, 'DDDDD', 'WWWWW', 'Male', 'PHP')",
                     "INSERT INTO StudentInf VALUES (105, 'EEEEE', 'VVVVV', 'Male', 'C#')");

// array of InstructorInf table data
$instructorData = array("INSERT INTO InstructorInf VALUES (10, 'AAAAA', 'ZZZZZ', 'PHP Expert', '263463432425')",
                     "INSERT INTO InstructorInf VALUES (11, 'BBBBB', 'YYYYY', 'C++ Expert', '2622334234')",
                     "INSERT INTO InstructorInf VALUES (12, 'CCCCC', 'XXXXX', 'Java Expert', '91824712784')",
                     "INSERT INTO InstructorInf VALUES (13, 'DDDDD', 'WWWWW', 'PHP Expert', '32785783713')",
                     "INSERT INTO InstructorInf VALUES (14, 'EEEEE', 'VVVVV', 'C# Experts', '783478471274')");

// array of LikedAndDisliked table data
$likedAndDislikedData = array("INSERT INTO LikedAndDisliked VALUES (10, 0, 5, 'Good', 10)",
                              "INSERT INTO LikedAndDisliked VALUES (11, 3, 3, 'Best', 6)",
                              "INSERT INTO LikedAndDisliked VALUES (12, 9, 1, 'Average', 5)",
                              "INSERT INTO LikedAndDisliked VALUES (13, 4, 0, 'Worst', 4)",
                              "INSERT INTO LikedAndDisliked VALUES (14, 5, 2, 'Not so good', 8)");

// insert each student data into table
for ($i = 0; $i < 5; $i++) {
if ($conn->query($studentData[$i]) != TRUE) {
echo "Error: " . $studentData[$i] . " " . $conn->error;
}
}

// insert each instructor data into table
for ($i = 0; $i < 5; $i++) {
if ($conn->query($instructorData[$i]) != TRUE) {
echo "Error: " . $instructorData[$i] . " " . $conn->error;
}
}

// insert each likedAndDisliked data into table
for ($i = 0; $i < 5; $i++) {
if ($conn->query($likedAndDislikedData[$i]) != TRUE) {
echo "Error: " . $likedAndDislikedData[$i] . " " . $conn->error;
}
}

// display all tables data
$result = $conn->query("SELECT * FROM StudentInf");

if ($result->num_rows > 0) {
 echo " StudentInf ";
 while($row = $result->fetch_assoc()) {
 echo "RollNo: " . $row["rollNo"]. "  FirstName: "
 . $row["firstName"]. "  LastName: "
 . $row["lastName"]."  Sex: "
 . $row["sex"]. "  Course: " . $row["course"]." ";
 }
} else {
 echo "0 results";
}

$result = $conn->query("SELECT * FROM InstructorInf");

if ($result->num_rows > 0) {
 echo " InstructorInf ";
 while($row = $result->fetch_assoc()) {
 echo "ID: " . $row["id"]. "  FirstName: "
 . $row["firstName"]. "  LastName: "
 . $row["lastName"]."  Title: "
 . $row["title"]. "  PhoneNo: " . $row["phoneNo"]." ";
 }
} else {
 echo "0 results";
}

$result = $conn->query("SELECT * FROM LikedAndDisliked");

if ($result->num_rows > 0) {
 echo " LikedAndDisliked ";
 while($row = $result->fetch_assoc()) {
 echo "ID: " . $row["id"]. "  Liked: "
 . $row["liked"]. "  Disliked: "
 . $row["disliked"]."  Comment: "
 . $row["comment"]. "  Rating: " . $row["rating"]." ";
 }
} else {
 echo "0 results";
}

$conn->close();

?>
</body>
</html>

Add a comment

Answer 2

Create a MySQL Database called “CSC306Class”. Create three (3) tables in the database called “studentInf”, “IntructorInf”,...

Homework Answers

Add Answer to:
Create a MySQL Database called “CSC306Class”. Create three (3) tables in the database called “studentInf”, “IntructorInf”,...

Post as a guest

Earn Coins

Create a database called COMPANY using those generated SQL scripts in MySQL database. Show the screenshot...

Using the MySQL Workbench create a new database using your design specifications Add at least 10...

Hello, I'm working on inserting data into a table using MySQL and Python on my Windows...

Create a new MySQL database called "players". Create a user for your database named "Jane". Jane...

Using PHP and MYSQL create a form that: user Full name user Comment (as many lines...

Write code for mySQL 1) create a new one database ; to create your table 2)...

Create the script for a table in the clinic database you made called Medicine and include...

MySQL 8 Create a PHP program that will read in each table of the database you...

PHP : I need to make a table in database that holds three fields. A unique...

Through the remaining assignments due in this course, you will be creating a simple database for...

Create a MySQL Database called “CSC306Class”. Create three (3) tables in the database called “studentInf”, “IntructorInf”,...

Homework Answers

Add Answer to: Create a MySQL Database called “CSC306Class”. Create three (3) tables in the database called “studentInf”, “IntructorInf”,...

Post as a guest

Earn Coins

Add Answer to:
Create a MySQL Database called “CSC306Class”. Create three (3) tables in the database called “studentInf”, “IntructorInf”,...