>> PHP: MySQL & CRUD

RecallDatabase

TablesRecords

is composed of is composed of

Operations (CRUD)CreateRetrieveUpdateDelete

DBMS

Access Control MySQL

phpMyAdmin

Web-Based Systems - Misbhauddin 2

phpMyAdmin

Click Here

http://localhost/phpmyadmin/

Web-Based Systems - Misbhauddin 3

Collation

character set is a set of symbols and encodings

• Collation is a set of rules for comparing characters in a character set• In latin9, there are letters as e é è ê f• If sorted by their binary

representation, it will go "e f é ê è"

• MySQL can do these things for you:• Store strings using a variety of

character sets• Compare strings using a variety of

collations• Mix strings with different character

sets or collations in the same server, the same database, or even the same table

• Enable specification of character set and collation at any level

Web-Based Systems - Misbhauddin 4

Database Operations

• Create User• Create Database• Create Table• Insert Records in the Table

DEMO

Web-Based Systems - Misbhauddin

5

Database Connection from PHP

• Pre-requisite– Create a new file in your project– Name it connection-db.php

• WHY? – To avoid typing the database connection commands in PHP each time we connect • Remember

– HTTP is a stateless protocol

• Procedure to connect to the database• Step 1: Create and initialize the required connection variables• Host-Name ($host = ‘localhost’;)• User Name ($user = ‘your-username’;)• Password ($pwd = ‘your-password’;)• Database Name ($db = ‘store’;)

Web-Based Systems - Misbhauddin 6

Database Connection from PHP

• Step 2: Create the connection variable• $con = mysqli_connect($host,$user,$pwd,$db);

• Step 3: Check for success & failure (optional)• if(mysqli_connect_errno($con)) { echo mysqli_connect_error(); }

• Step 4: Include this connection file inside any php file that needs connection to the database

Web-Based Systems - Misbhauddin 7

Running a Query

• Process to Construct & Run a MySQL Query• Step 1: Construct the query, preferably in a variable• $query = ‘SELECT * FROM stuff’;

• Step 2: Run the Query• $result = mysqli_query($con, $query);

• Step 3: Fetch the Results• (In a regular array): mysqli_fetch_array($result)• (In an associative array): mysqli_fetch_assoc($result)

Web-Based Systems - Misbhauddin 8

Issues with result processing

• Step 3: Fetch the Results• (In a regular array): mysqli_fetch_array($result)• (In an associative array): mysqli_fetch_assoc($result)

Result is returned row-by-row

AND

foreach loop statement in PHP requires the complete array at a time

Solution?

Web-Based Systems - Misbhauddin 9

Hint: Is for each necessary inside the while loop?Maybe not. ☻

Issues with result processing

• Step 3: Fetch the Results• (In a regular array): mysqli_fetch_array($result)• (In an associative array): mysqli_fetch_assoc($result) Result is returned row-by-row

ANDforeach loop statement in PHP requires the complete array at a time

Solution

while ($row = mysqli_fetch_assoc($result)) { $stuff[$row[‘ID’]] = array(

"name" => $row[‘Name’], "img" => $row[‘Image’], "price" => $row[‘Price’]

);}

10Web-Based Systems - Misbhauddin

Web-Based Systems - Misbhauddin 11

Next on Web-based Systems

• Next Class – Form Handling in PHP– Add Items to the Database