Internet and Web Technology (CS-504 (A)) Unit 5 Notes

How unit 5 is examined

This unit covers connecting PHP to MySQL and running database commands from PHP; the marks sit in the connection program, phpMyAdmin with database errors, and query execution.

Basic commands with PHP examples

<span style="display:inline-block;padding:.16em .6em;border:1.5px solid currentColor;border-radius:999px;font-size:.68em;font-weight:700;letter-spacing:.06em;text-transform:uppercase;opacity:.75">Low weight</span>

Definition. The basic commands are the PHP statements that talk to MySQL: connect, run an SQL string, read the result and close. A PHP application such as a shopping cart uses them together with sessions.

Key points.

PHP sends every SQL command as a string through mysqli_query(), so the same function creates, inserts, updates and deletes.
A shopping cart needs two tables: products(id, name, price) and cart(id, session_id, product_id, qty).
session_start() at the top of each page gives every visitor a session id that ties cart rows to that visitor.
The flow is: list products, add to cart, view cart, remove item.

Example.

<?php // cart.php
session_start(); $c = mysqli_connect("localhost","root","","shop");
$sid = session_id();
if (isset($_GET['add']))    // add to cart
  mysqli_query($c, "INSERT INTO cart(session_id,product_id,qty) VALUES('$sid',".(int)$_GET['add'].",1)");
if (isset($_GET['del']))    // remove from cart
  mysqli_query($c, "DELETE FROM cart WHERE id=".(int)$_GET['del']." AND session_id='$sid'");
$r = mysqli_query($c, "SELECT cart.id,name,price FROM cart JOIN products ON products.id=product_id WHERE session_id='$sid'");
while ($row = mysqli_fetch_assoc($r))   // view cart
  echo $row['name']." - ".$row['price']." <a href='?del=".$row['id']."'>Remove</a><br>";

Asked: [7 marks] (Nov 2023) Create a simple shopping cart application using PHP and MySQL.

Connection to server

Definition. PHP-MySQL connectivity means opening a link from a PHP script to the MySQL server with mysqli_connect(host, username, password, dbname), which returns a connection object used by every later call. ==The connectivity string is $conn = mysqli_connect("localhost", "root", "", "college"); and it must be followed by a check for failure.==

Diagram. <figure class="ds-fig" style="margin:1.4rem 0;overflow-x:auto"><svg xmlns="http://www.w3.org/2000/svg" id="dsfig-u5-01" viewBox="0 0 467 80" width="467" height="80" role="img" aria-label="PHP script to MySQL: Con = connection object from mysqli_connect, SQL = MySQL server, DB = database"><style>#dsfig-u5-01 .e{stroke:#454C5A;stroke-width:1.4;fill:none}#dsfig-u5-01 .e.hi{stroke:#2340B8;stroke-width:2.6}#dsfig-u5-01 .n{fill:#FFFFFF;stroke:#16181D;stroke-width:1.4}#dsfig-u5-01 .n.hi{fill:#E3E9FC;stroke:#2340B8;stroke-width:2.2}#dsfig-u5-01 .n.rb-b{fill:#16181D;stroke:#16181D}#dsfig-u5-01 .n.rb-r{fill:#BD3227;stroke:#BD3227}#dsfig-u5-01 text{font-family:"JetBrains Mono",ui-monospace,Menlo,Consolas,monospace;font-size:13px}#dsfig-u5-01 .t{fill:#16181D;font-weight:500}#dsfig-u5-01 .t.inv{fill:#FFFFFF;font-weight:700}#dsfig-u5-01 .kd{stroke:#16181D;stroke-width:1.2}#dsfig-u5-01 .dot{fill:#16181D}#dsfig-u5-01 .ann{fill:#2340B8;font-size:11px;font-weight:700}#dsfig-u5-01 .lbl{fill:#6F7787;font-family:system-ui,-apple-system,sans-serif;font-size:12px;font-weight:700}#dsfig-u5-01 .ptr{fill:#2340B8;font-size:12px;font-weight:700}#dsfig-u5-01 .ah{fill:#454C5A}#dsfig-u5-01 .ah.hi{fill:#2340B8}#dsfig-u5-01 .wl rect{fill:#FFFFFF;stroke:#DCE0E7}#dsfig-u5-01 .wl .t{font-size:12px;font-weight:700}#dsfig-u5-01 .wl.hi rect{fill:#2340B8;stroke:#2340B8}#dsfig-u5-01 .wl.hi .t{fill:#FFFFFF}html.dark #dsfig-u5-01 .e{stroke:#B1B7C3}html.dark #dsfig-u5-01 .e.hi{stroke:#8FA3FF}html.dark #dsfig-u5-01 .n{fill:#161920;stroke:#E6E8ED}html.dark #dsfig-u5-01 .n.hi{fill:#1E2748;stroke:#8FA3FF}html.dark #dsfig-u5-01 .n.rb-b{fill:#E6E8ED;stroke:#E6E8ED}html.dark #dsfig-u5-01 .n.rb-r{fill:#FF7E71;stroke:#FF7E71}html.dark #dsfig-u5-01 .t{fill:#E6E8ED}html.dark #dsfig-u5-01 .t.inv{fill:#0F1115}html.dark #dsfig-u5-01 .kd{stroke:#E6E8ED}html.dark #dsfig-u5-01 .dot{fill:#E6E8ED}html.dark #dsfig-u5-01 .ann{fill:#8FA3FF}html.dark #dsfig-u5-01 .lbl{fill:#858D9C}html.dark #dsfig-u5-01 .ptr{fill:#8FA3FF}html.dark #dsfig-u5-01 .ah{fill:#B1B7C3}html.dark #dsfig-u5-01 .ah.hi{fill:#8FA3FF}html.dark #dsfig-u5-01 .wl rect{fill:#161920;stroke:#2A2E37}html.dark #dsfig-u5-01 .wl.hi rect{fill:#8FA3FF;stroke:#8FA3FF}html.dark #dsfig-u5-01 .wl.hi .t{fill:#0F1115}</style><defs><marker id="ah8" viewBox="0 0 10 10" refX="9" refY="5" markerWidth="7" markerHeight="7" orient="auto-start-reverse"><path class="ah" d="M0,1 L9,5 L0,9 z"/></marker><marker id="ahh8" viewBox="0 0 10 10" refX="9" refY="5" markerWidth="7" markerHeight="7" orient="auto-start-reverse"><path class="ah hi" d="M0,1 L9,5 L0,9 z"/></marker></defs><path class="e" d="M59,40 L148,40" marker-end="url(#ah8)"/><path class="e" d="M188,40 L277,40" marker-end="url(#ah8)"/><path class="e" d="M317,40 L406,40" marker-end="url(#ah8)"/><path class="e" d="M408,40 L61,40" marker-end="url(#ah8)"/><g class="wl"><rect x="73.8" y="31" width="61.5" height="18" rx="9"/><text class="t" x="104.5" y="40" dy=".35em" text-anchor="middle">connect</text></g><g class="wl"><rect x="210" y="31" width="47.1" height="18" rx="9"/><text class="t" x="233.5" y="40" dy=".35em" text-anchor="middle">query</text></g><g class="wl"><rect x="321.4" y="31" width="82.2" height="18" rx="9"/><text class="t" x="362.5" y="40" dy=".35em" text-anchor="middle">read/write</text></g><g class="wl"><rect x="206.4" y="31" width="54.3" height="18" rx="9"/><text class="t" x="233.5" y="40" dy=".35em" text-anchor="middle">result</text></g><circle class="n" cx="40" cy="40" r="18"/><text class="t" x="40" y="40" dy=".35em" text-anchor="middle">PHP</text><circle class="n" cx="169" cy="40" r="18"/><text class="t" x="169" y="40" dy=".35em" text-anchor="middle">Con</text><circle class="n" cx="298" cy="40" r="18"/><text class="t" x="298" y="40" dy=".35em" text-anchor="middle">SQL</text><circle class="n" cx="427" cy="40" r="18"/><text class="t" x="427" y="40" dy=".35em" text-anchor="middle">DB</text></svg><figcaption style="font-size:.82em;opacity:.72;margin-top:.45rem">PHP script to MySQL: Con = connection object from mysqli_connect, SQL = MySQL server, DB = database</figcaption></figure>

Key points.

host is the server address, normally localhost when PHP and MySQL run on the same machine.
username and password are the MySQL account credentials; XAMPP's default is user root with an empty password.
dbname is the database to select at connect time; it is optional, and mysqli_select_db() can choose it later.
PHP offers two APIs, mysqli (procedural or object style, MySQL only) and PDO (many databases, uses a DSN string).
Connection errors are detected by testing the return value: mysqli_connect() gives false on failure and mysqli_connect_error() returns the reason.
After the work is done the link is closed with mysqli_close($conn).
The database operations insert, select, update and delete are all run over this one connection with mysqli_query().

Example.

<?php
$conn = mysqli_connect("localhost", "root", "", "college");
if (!$conn) { die("Connection failed: " . mysqli_connect_error()); }
echo "Connected successfully";                  // output: Connected successfully
mysqli_query($conn, "INSERT INTO student(name) VALUES('Ravi')");
$r = mysqli_query($conn, "SELECT * FROM student");
while ($row = mysqli_fetch_assoc($r)) { echo $row['name']; }
mysqli_close($conn);

PDO form:

<?php
try { $pdo = new PDO("mysql:host=localhost;dbname=college", "root", "");
      $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); }
catch (PDOException $e) { die("Failed: " . $e->getMessage()); }

Answer frame. Open with the definition and the connectivity string; draw the flow figure; then develop the four parameters, error check, one query on the connection (insert, select, update, delete), and close; close with the complete program and its output "Connected successfully". For the "operations" question add a one-line example each of INSERT, SELECT, UPDATE and DELETE.

Pitfall: Writing mysql_connect (removed in PHP 7) instead of mysqli_connect, or omitting the failure check.

Asked: [7 marks] (Jun 2020, Nov 2022, Dec 2024, Jun 2025) Write the connectivity string in PHP with MySQL database; write a MySQL connectivity program using PHP; list the statements used to connect PHP with MySQL with an example; how to establish a connection to a MySQL database using PHP. Asked: [7 marks] (Dec 2025) Explain PHP-MySQL connectivity and database operations.

Creating database

Definition. Creating a database means running the SQL command CREATE DATABASE name from PHP.

Key points.

The connection is made without a dbname, because the database does not exist yet.
The command is sent with mysqli_query($conn, "CREATE DATABASE college"), which returns true on success.
On failure, mysqli_error($conn) gives the reason, for example that the database already exists.

Selecting a database

Definition. Selecting a database makes one database the current one for all later queries, using mysqli_select_db($conn, "college").

Key points.

It returns true or false, so it is tested like a connection.
Passing dbname as the fourth argument of mysqli_connect() does the same in one step.
The SQL equivalent is USE college.

Listing database

Definition. Listing databases shows every database on the server, using the query SHOW DATABASES.

Key points.

Run $r = mysqli_query($conn, "SHOW DATABASES"); and read the rows in a loop.
Each row is fetched with mysqli_fetch_row($r) and the name is in $row[0].
The account only sees the databases it has privileges on.

Listing table names

Definition. Listing table names shows all tables of the selected database, using SHOW TABLES.

Key points.

A database must be selected first, or the query fails.
mysqli_query($conn, "SHOW TABLES") returns one row per table with the name in $row[0].
SHOW TABLES is the SQL form; DESCRIBE tablename then lists the columns of one table.

Creating a table

Definition. Creating a table defines its columns and types with CREATE TABLE, sent through mysqli_query().

Key points.

Example: CREATE TABLE student(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), marks INT).
Each column has a name, a data type and optional constraints such as PRIMARY KEY and NOT NULL.
The database must be selected first, and the call returns true when the table is made.

Inserting data

Definition. Inserting data adds a row to a table with INSERT INTO table(columns) VALUES(values).

Key points.

Example: mysqli_query($conn, "INSERT INTO student(name,marks) VALUES('Ravi',80)").
String values sit inside single quotes and numbers do not.
mysqli_affected_rows($conn) returns 1 after a successful insert, and mysqli_insert_id($conn) gives the new AUTO_INCREMENT id.
User input should go through a prepared statement to prevent SQL injection.

Altering tables

Definition. Altering a table changes its structure with ALTER TABLE, run through mysqli_query().

Key points.

Add a column: ALTER TABLE student ADD email VARCHAR(50).
Remove a column: ALTER TABLE student DROP COLUMN email.
Change a type: ALTER TABLE student MODIFY marks FLOAT.
It changes the structure only, while UPDATE changes the data in rows.

Queries

Definition. Querying from PHP means sending an SQL statement to MySQL with mysqli_query() and then reading its result. <mark>A query is executed in five steps: connect, select the database, execute the query, fetch the result, and close the connection.</mark>

Steps.

Step 1: $conn = mysqli_connect("localhost","root","","college");   // connect and select DB
Step 2: if (!$conn) die(mysqli_connect_error());                   // check the connection
Step 3: $r = mysqli_query($conn, "SELECT * FROM student");         // execute the query
Step 4: while ($row = mysqli_fetch_assoc($r)) echo $row['name'];   // fetch row by row
Step 5: mysqli_close($conn);                                       // close

Key points.

mysqli_query() returns a result object for SELECT and true or false for INSERT, UPDATE and DELETE.
Rows are read with mysqli_fetch_assoc() (column names), mysqli_fetch_row() (numbers) or mysqli_fetch_array() (both).
mysqli_num_rows($r) counts the rows returned by a SELECT, and mysqli_affected_rows($conn) counts rows changed by INSERT, UPDATE or DELETE.
Example statements: INSERT INTO student(name,marks) VALUES('Ravi',80), UPDATE student SET marks=90 WHERE name='Ravi', DELETE FROM student WHERE name='Ravi'.
mysqli_prepare() with bind_param() (or PDO prepare()) runs the query with placeholders and is the safe method for user input.
Every call is followed by a check, if (!$r) echo mysqli_error($conn);, to handle errors.

Answer frame. Open with the definition of a query; write the five steps; show one SELECT program; then one line each for INSERT, UPDATE and DELETE with mysqli_affected_rows; close with the error check and mysqli_close.

Asked: [7 marks] (Nov 2023, Jun 2025) Explain the steps in the PHP code for querying a database with suitable examples; explain how to execute SQL queries in PHP to retrieve, insert, update and delete data from a MySQL database.

Deleting database

Definition. Deleting a database removes it and all its tables permanently with DROP DATABASE name.

Key points.

It is run as mysqli_query($conn, "DROP DATABASE college").
The action cannot be undone, so a backup comes first.
DROP DATABASE IF EXISTS college avoids an error when the database is absent.

Deleting data and tables

Definition. DELETE FROM table WHERE condition removes rows, and DROP TABLE name removes the whole table with its structure.

Key points.

Without a WHERE clause DELETE removes every row, though the table stays.
DROP TABLE student deletes both the data and the table definition.
mysqli_affected_rows($conn) tells how many rows were deleted.

PHPMyAdmin and database bugs

Definition. phpMyAdmin is a free, web-based tool written in PHP for administering MySQL through a browser, with no need to type SQL commands at a prompt. Database bugs are errors that occur when PHP talks to MySQL, such as connection failures, wrong SQL and constraint violations. <mark>phpMyAdmin manages MySQL through a browser; database bugs are handled by checking every call and using try-catch.</mark>

Key points (phpMyAdmin).

phpMyAdmin comes bundled with XAMPP and WAMP and opens at http://localhost/phpmyadmin.
It creates, alters and drops databases, tables and columns through forms.
It inserts, edits, browses and deletes rows, and runs any SQL in its SQL tab.
It imports and exports data (SQL, CSV) for backup and transfer.
It manages users and privileges, and shows table structure, indexes and relations.

Key points (database bugs).

Connection errors come from a wrong host, username, password or a stopped server; mysqli_connect_error() reports them.
Query errors come from bad SQL syntax, a missing table or column, or a duplicate key; mysqli_error($conn) reports them.
SQL injection is a security bug where unchecked user input changes the query, and prepared statements prevent it.
Exceptions: mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT) or PDO's ERRMODE_EXCEPTION throws errors, and a try-catch block handles them.
Errors should be logged with error_log() and shown to the user as a plain message, never the raw SQL error.

Example.

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
try { $c = mysqli_connect("localhost","root","","college");
      mysqli_query($c, "INSERT INTO student(id) VALUES(1)"); }
catch (mysqli_sql_exception $e) { error_log($e->getMessage()); echo "Something went wrong."; }

Answer frame. For "elucidate phpMyAdmin and bugs": define phpMyAdmin, list features 1-5, define bugs, explain the four bug types, and close with the fact that checking every call prevents most of them. For "handle errors and exceptions": open with the three error kinds, show mysqli_error, PDOException and the try-catch code, and close with logging.

Asked: [14 marks] (Nov 2022, Nov 2023, Dec 2025) Elucidate phpMyAdmin and briefly explain database bugs; short note on any two: a) phpMyAdmin and database bugs; explain the role of phpMyAdmin and handling database bugs. Asked: [7 marks] (Jun 2025) How do you handle database errors and exceptions in PHP?

Last-minute revision

Connectivity string: mysqli_connect("localhost","root","","dbname"), arguments host, user, password, database.
Failure check: mysqli_connect_error() for connection, mysqli_error($conn) for queries.
mysqli_query() runs any SQL; SELECT returns a result set, other statements return true or false.
Fetch functions: mysqli_fetch_assoc, mysqli_fetch_row, mysqli_fetch_array; counts: mysqli_num_rows, mysqli_affected_rows.
Five query steps: connect, select DB, execute, fetch, close.
SQL commands: CREATE DATABASE, USE, SHOW DATABASES, SHOW TABLES, CREATE TABLE, INSERT, ALTER TABLE, DROP DATABASE, DELETE, DROP TABLE.
DELETE removes rows only; DROP TABLE removes the table itself.
PDO uses a DSN mysql:host=...;dbname=... and PDOException.
phpMyAdmin is a browser-based MySQL admin tool bundled with XAMPP.
Shopping cart: products and cart tables plus session_start().

Memory hooks

Connect argument order: "HUPD" (Host, User, Password, Database).
Query steps: "C-S-E-F-C" (Connect, Select, Execute, Fetch, Close).
DELETE deletes rows, DROP drops the object.
Two error functions: connect_error for the door, mysqli_error for the room.
phpMyAdmin is the "GUI face" of MySQL.

Coverage checklist

Basic commandswith PHP examples: shopping cart application (Nov 2023).
Connection to server: connectivity string and program (Jun 2020, Nov 2022, Dec 2024, Jun 2025), connectivity and operations (Dec 2025).
creating database: CREATE DATABASE from PHP, no past question.
selecting a database: mysqli_select_db, no past question.
listing database: SHOW DATABASES, no past question.
listing table names: SHOW TABLES, no past question.
creating a table: CREATE TABLE, no past question.
inserting data: INSERT INTO, no past question.
altering tables: ALTER TABLE, no past question.
queries: steps of querying and SELECT, INSERT, UPDATE, DELETE (Nov 2023, Jun 2025).
deleting database: DROP DATABASE, no past question.
deleting data and tables: DELETE and DROP TABLE, no past question.
PHP myadmin and databasebugs: phpMyAdmin and bugs (Nov 2022, Nov 2023, Dec 2025), errors and exceptions (Jun 2025).

Account

Internet and Web Technology (CS-504 (A)) - Unit 5 Short Notes

How unit 5 is examined

Basic commands with PHP examples

Connection to server

Creating database

Selecting a database

Listing database

Listing table names

Creating a table

Inserting data

Altering tables

Queries

Deleting database

Deleting data and tables

PHPMyAdmin and database bugs

Last-minute revision

Memory hooks

Coverage checklist