Question

Assignment 7 This is a two-part assignment. Students will create and submit 3 scripts.

Here are the instructions: Before You need to Import the books_sc database via phpMyAdmin Script

1 Create a php file to connect to the books_sc database. You can name this file connect.php. This separate file will be ‘included’ in the other scripts, script 2 and script 2 Script 2 (25 points – to receive these points, you need to have the connect.php file created and submitted)

1. Connect to the books_sc database by including the connect.php 2. Create a query to select all books from the books table. Perform the necessary steps to obtain all records from the record set 3. Displayed the retrieved records using a foreach loop. Display the data in an organized manner. Make use of the main.css file we’ve been using so far, or have your own. Remember, we are working towards a term project Script 3 (25 points – to receive these points, you need to have the connect.php file created and submitted)

1. Connect to the books_sc database by including the connect.php 2. Create a query to select books from the books table with a price less than 30 dollars. Perform the necessary steps to obtain all records from the record set 3. Display the data in an organized manner. Make use of the main.css file we’ve been using so far, or have your own. Remember, we are working towards a term project

books_sc-1.sql

Answer 1

connect.php file

You need to change the servername, username, password and dbname as per the actual ones.

<?php

// replace these values with your values
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "books_sc";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection, exit if error
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

?>

This file can be inclused in subsequent scripts to get connect to the database.

script2.php

Assuming the structure of the books table to be

books (isbn, author, title, price, catID)

<?php include 'connect.php';

// sql statement to select all books
$sql = "SELECT * FROM books";

// execute query
$result = $conn->query($sql);

// constructing a table
echo "<table border=1><thead><th>ISBN</th><th>Author</th><th>Title</th><th>Price</th><th>Cat Id</th></thead>";

// create table rows for each row
foreach ($result as $row) {
        echo "<tr><td>" . $row["isbn"] . "</td>";
        echo "<td>" . $row["author"] . "</td>";
        echo "<td>" . $row["title"] . "</td>";
        echo "<td>" . $row["price"] . "</td>";
        echo "<td>" . $row["catID"] . "</td></tr>";
}

// close the table tag
echo "</table>";

// close database connection
$conn->close();
?>

Find the sample output below.

Script3.php

<?php include 'connect.php';

// sql statement to select books priced less than 30$
$sql = "SELECT * FROM books WHERE price < 30";

// execute query
$result = $conn->query($sql);

// constructing a table
echo "<table border=1><thead><th>ISBN</th><th>Author</th><th>Title</th><th>Price</th><th>Cat Id</th></thead>";

// create table rows for each row
foreach ($result as $row) {
        echo "<tr><td>" . $row["isbn"] . "</td>";
        echo "<td>" . $row["author"] . "</td>";
        echo "<td>" . $row["title"] . "</td>";
        echo "<td>" . $row["price"] . "</td>";
        echo "<td>" . $row["catID"] . "</td></tr>";
}

// close the table tag
echo "</table>";

// close database connection
$conn->close();
?>

Find the sample output below.

Thanks & Regards.