In: Computer Science
PHP/SQL Lab 1 Consider the banking example we used in the SQL in-class exercises:
branch (branch-name, branch-city, assets)
customer (customer-name, customer-street, customer-city)
account (account-number, branch-name, balance)
loan (loan-number, branch-name, amount)
depositor (customer-name, account-number)
borrower (customer-name, loan-number)
Write PHP code for the following problems:
1. Create a php file to display the names and cities of customers who have an account at Downtown branch from the web.
2. Create the corresponding html and php files so that a user can insert the following customer into the database from a web browser. After the insertion, display all the customers from the web. “Jane Doe”, “401 Rosemont Ave”, “Frederick” Submission:
Submit a softcopy of the html and php files and a document containing screenshots of web outputs with URL included for both problems
1). ANSWER :
GIVENTHAT :
Answer 1:
<?php
// you have to give username,password and databasename of your SQL
Server bellow
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = new mysqli($servername, $username, $password,
$dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Retrive name and cities of customers who have an acount at
Downtown branch
$sql = "select customer-name, customer-city from customer where
customer-name in (select distinct customer-name from depositor,
account where depositor.account-number=account.account-number and
account.branch-name='Downtown')";
$result = $conn->query($sql);
if ($result->num_rows > 0)
{
// output result in each row
while($row = $result->fetch_assoc())
{
echo "Name: ". $row['customer-name']. " " . $row["customer-city"] .
"<br>";
}
}
else
{
echo "0 results";
}
$conn->close();
?>
Answer 2:
HTML file
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;
charset=utf-8" />
<title>Untitled Document</title>
</head>
<body>
<form name="form1" method="post"
action="insert_ac.php">
<table width="366" border="5">
<tr>
<td> <strong>Enter Customer
Name:</strong></td>
<td> <input type="text" name="name"> </td>
</tr>
<tr>
<td><strong> Enter Customer Street:
</strong></td>
<td> <input type="text" name="street"></td>
</tr>
<tr>
<td><strong> Enter Customer City:
</strong></td>
<td> <input type="text" name="city"> </td>
</tr>
<tr>
<td> </td> </tr>
<tr> </tr>
<tr>
<td> <input type="submit"
value="Submit"></td>
<td> </td>
</tr>
</table>
</form>
</body>
</html>
</body>
</html>
PHP file ie PHP code to insert customer data into database File name : insert_ac.php
<?php
$host="localhost"; // Host name
$username=""; // Mysql username
$password=""; // Mysql password
$db_name="test"; // Database name
// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot
connect");
mysql_select_db("$db_name")or die("cannot select DB");
// Get values from form
$name=$_POST['name'];
$street=$_POST['street'];
$city=$_POST['city'];
// Insert data into mysql
$sql="INSERT INTO customer(customer-name, customer-street,
customer-city)VALUES('$name', '$street', '$city')";
$result=mysql_query($sql);
// if successfully insert data into database, displays message
"Successful".
if($result){
echo "Successful";
echo "<BR>";
echo "<a href='insert.php'>Back to main
page</a>";
}
else {
echo "ERROR";
}
?>
<?php
// close connection
mysql_close();
?>
PHP code to display all customers from the database file name :displaycustomer.php
<?php
// you have to give username,password and databasename of your
SQL Server bellow
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = mysqli_connect($servername, $username, $password,
$dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// Retrieve all the customer information
$sql = "SELECT * FROM customer";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0)
{
// output data of each row
while($row = mysqli_fetch_assoc($result))
{
$customername=$row['customer-name']
;
$customerstreet=$row['customer-street'] ;
$customercity=$row['customer-city']
;
echo
$customername.",".$customerstreet.",".$customercity;
}
}
else
{
echo "0 results";
}
mysqli_close($conn);
?>