Internet and Web Technology (AD-503 (A)) Unit 5 Notes

How unit 5 is examined

PHP talks to MySQL through the mysqli extension; the marks sit in database-handling programs (create, select, list, delete) worth 7 marks each, plus one login-page case study.

Basic commands with PHP examples

Not asked since 2022

Definition. PHP sends SQL commands to the MySQL server as strings through mysqli functions, and MySQL returns either a result set or an error.

Key points.

PHP is the server-side language and MySQL is the database, so PHP builds the SQL text and the database executes it.
The usual order of a script is connect, select the database, run the query, read the result, and close the connection.
mysqli_query($conn, $sql) runs any SQL command; it returns a result object for SELECT and true or false for INSERT, UPDATE, DELETE and CREATE.
mysqli_fetch_assoc($result) returns one row as an associative array, mysqli_fetch_row() returns a numeric array, and mysqli_num_rows($result) counts the rows.

Function	Purpose
`mysqli_connect()`	open connection to the server
`mysqli_select_db()`	choose the current database
`mysqli_query()`	execute an SQL statement
`mysqli_fetch_assoc()`	read the next row
`mysqli_error()`	text of the last error
`mysqli_close()`	close the connection

<?php
$conn = mysqli_connect("localhost","root","","college");
$r = mysqli_query($conn,"SELECT * FROM student");
while ($row = mysqli_fetch_assoc($r))
    echo $row['id']." ".$row['name']."<br>";
mysqli_close($conn);
?>

Connection to server

Definition. mysqli_connect(host, user, password, database) opens a connection to the MySQL server and returns a connection object, or false if the connection fails.

Key points.

The typical local values are host localhost, user root and an empty password, and the fourth argument (the database name) is optional.
When the connection fails, mysqli_connect_error() returns the reason, and die() stops the script after printing it.
The object-oriented form is new mysqli($host,$user,$pass,$db), and the connection is closed with mysqli_close($conn).
PDO (new PDO("mysql:host=localhost;dbname=college",$user,$pass)) is an alternative that works with many databases and supports prepared statements.

<?php
$conn = mysqli_connect("localhost","root","");
if (!$conn) {
    die("Connection failed: ".mysqli_connect_error());
}
echo "Connected successfully";
?>

Creating, selecting, listing, altering and deleting databases, tables and data

Definition. A PHP script connects with mysqli_connect(), sends each SQL statement (CREATE, USE, SHOW, INSERT, ALTER, SELECT, DELETE, DROP) through mysqli_query(), and checks the returned value for errors.

Key points.

Connect first with mysqli_connect("localhost","root","") and stop with die(mysqli_connect_error()) if the connection fails.
Create a database with the query CREATE DATABASE college and select it with mysqli_select_db($conn,"college").
List databases with SHOW DATABASES and list the tables of the selected database with SHOW TABLES, looping over the result with mysqli_fetch_row().
Create a table with CREATE TABLE student(id INT PRIMARY KEY, name VARCHAR(30)) and insert a row with INSERT INTO student VALUES(1,'Ravi').
Change the structure with ALTER TABLE student ADD age INT, and read data with the query SELECT * FROM student WHERE id=1.
Delete rows with DELETE FROM student WHERE id=1, remove a table with DROP TABLE student and remove a database with DROP DATABASE college.
Always test the value returned by mysqli_query(), print mysqli_error($conn) on failure, and finish with mysqli_close($conn).

Task	SQL statement
create database	`CREATE DATABASE college`
list databases	`SHOW DATABASES`
list tables	`SHOW TABLES`
create table	`CREATE TABLE student(...)`
insert data	`INSERT INTO student VALUES(...)`
alter table	`ALTER TABLE student ADD age INT`
delete data	`DELETE FROM student WHERE id=1`
delete table / database	`DROP TABLE student` / `DROP DATABASE college`

Example (create, select, list).

<?php
$conn = mysqli_connect("localhost","root","");
if (!$conn) die("Failed: ".mysqli_connect_error());
if (mysqli_query($conn,"CREATE DATABASE shop")) echo "Database created<br>";
else echo "Error: ".mysqli_error($conn);
mysqli_select_db($conn,"shop");
mysqli_query($conn,"CREATE TABLE item(id INT, name VARCHAR(30))");
$r = mysqli_query($conn,"SHOW TABLES");
while ($row = mysqli_fetch_row($r)) echo $row[0]."<br>";
mysqli_close($conn);
?>

Example (delete data).

<?php
$conn = mysqli_connect("localhost","root","","college");
if (!$conn) die("Failed: ".mysqli_connect_error());
$sql = "DELETE FROM student WHERE id=1";
if (mysqli_query($conn,$sql))
    echo mysqli_affected_rows($conn)." row deleted";
else
    echo "Error: ".mysqli_error($conn);
mysqli_close($conn);
?>

Answer frame. Open with "PHP uses the mysqli extension to run SQL commands on a MySQL server"; write the connection code first with error handling; for the creating, selecting and listing question then show CREATE DATABASE, mysqli_select_db() and SHOW TABLES in that order; for the delete question show the DELETE query with WHERE, the result check and mysqli_affected_rows(); close with "the connection is closed and any error is reported through mysqli_error()".

Pitfall: A DELETE without a WHERE clause removes every row of the table.

Asked: [7 marks] (Nov 2022) Write details using with PHP database creating, selecting, listing. Asked: [7 marks] (Nov 2023) Write a PHP script to delete data from an existing MySQL table.

PHP myadmin and database error handling

Definition. phpMyAdmin is a free web-based tool, written in PHP, for managing MySQL databases through a browser without typing SQL commands.

Key points.

It creates and drops databases and tables, edits and deletes rows, runs SQL queries and imports or exports data, and it is bundled with XAMPP and WAMP at localhost/phpmyadmin.
In PHP code, errors are handled by testing the return value of every mysqli call and reporting it with mysqli_error(), mysqli_connect_error() and die().
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT) makes mysqli throw exceptions, which are caught with try-catch.

<?php
$r = mysqli_query($conn,"SELECT * FROM nothere");
if (!$r) { echo "Error: ".mysqli_error($conn); }
?>

Case study: Web based application development

Definition. A login application has an HTML form that posts a username and password to a PHP script, which validates them, stores the details in MySQL and starts a session.

Key points.

The HTML form uses method="post" with a username field and a password field, so the values do not appear in the URL.
The PHP script reads the values from $_POST, validates that no field is empty, and escapes them with mysqli_real_escape_string() to stop SQL injection.
It connects to the database and stores the details with an INSERT statement, saving the password with password_hash() and never as plain text.
session_start() and $_SESSION['user'] keep the user logged in on later pages, and a failed check shows an error message.

Example.

<form method="post" action="login.php">
 Username <input name="user">
 Password <input type="password" name="pass">
 <input type="submit" value="Register"></form>
<?php
session_start();
$c = mysqli_connect("localhost","root","","app");
if (!$c) die("Failed: ".mysqli_connect_error());
$u = mysqli_real_escape_string($c,$_POST['user']);
$p = password_hash($_POST['pass'],PASSWORD_DEFAULT);
if ($u == "" || $_POST['pass'] == "") echo "Fill all fields";
elseif (mysqli_query($c,"INSERT INTO users(name,pass) VALUES('$u','$p')"))
    $_SESSION['user'] = $u;
else echo "Error: ".mysqli_error($c);
?>

Answer frame. Open with "A web application login page takes credentials from an HTML form and stores them in MySQL using PHP"; draw the form first, then the PHP receiving code, then validation, the INSERT and the session; close with "the session keeps the user logged in".

Asked: [7 marks] (Nov 2023) Write PHP code to create a login page for a web application and store details in database.

Last-minute revision

mysqli_connect(host,user,pass,db) opens the connection and mysqli_close() closes it.
mysqli_query() runs SQL and mysqli_error() gives the error text.
mysqli_connect_error() reports a failed connection.
mysqli_select_db($conn,"db") selects the database.
SHOW DATABASES and SHOW TABLES list databases and tables.
ALTER TABLE t ADD col type changes the structure of a table.
DELETE FROM t WHERE cond deletes rows and DROP TABLE or DROP DATABASE removes the object.
mysqli_fetch_assoc() reads a row as an associative array.
Login: HTML form with POST, validate, INSERT or SELECT, then session_start().
phpMyAdmin is a web GUI for MySQL.

Memory hooks

Connect, Select, Query, Fetch, Close: "CSQFC".
DELETE needs WHERE, or everything goes.
SHOW lists, DROP destroys, ALTER changes.

Coverage checklist

Basic commands with PHP examples: covered, no past questions.
Connection to server: covered, no past questions.
creating database, selecting a database, listing database, listing table names, creating a table, inserting data, altering tables, queries, deleting database, deleting data and tables: covers Nov 2022 (creating, selecting, listing) and Nov 2023 (delete data).
PHP myadmin and database error handling: covered, no past questions.
Case study: Web based application development: covers Nov 2023 (login page).

Account

Internet and Web Technology (AD-503 (A)) - Unit 5 Short Notes

How unit 5 is examined

Basic commands with PHP examples

Connection to server

Creating, selecting, listing, altering and deleting databases, tables and data

PHP myadmin and database error handling

Case study: Web based application development

Last-minute revision

Memory hooks

Coverage checklist