![Page 1: >> PHP: MySQL & CRUD. R ecall Database Tables Records is composed of Operations (CRUD) Create Retrieve Update Delete DBMS Access Control MySQL phpMyAdmin](https://reader036.vdocuments.us/reader036/viewer/2022082510/5a4d1aec7f8b9ab05997b5f2/html5/thumbnails/1.jpg)
>> PHP: MySQL & CRUD
![Page 2: >> PHP: MySQL & CRUD. R ecall Database Tables Records is composed of Operations (CRUD) Create Retrieve Update Delete DBMS Access Control MySQL phpMyAdmin](https://reader036.vdocuments.us/reader036/viewer/2022082510/5a4d1aec7f8b9ab05997b5f2/html5/thumbnails/2.jpg)
RecallDatabase
TablesRecords
is composed of is composed of
Operations (CRUD)CreateRetrieveUpdateDelete
DBMS
Access Control MySQL
phpMyAdmin
Web-Based Systems - Misbhauddin 2
![Page 3: >> PHP: MySQL & CRUD. R ecall Database Tables Records is composed of Operations (CRUD) Create Retrieve Update Delete DBMS Access Control MySQL phpMyAdmin](https://reader036.vdocuments.us/reader036/viewer/2022082510/5a4d1aec7f8b9ab05997b5f2/html5/thumbnails/3.jpg)
phpMyAdmin
Click Here
http://localhost/phpmyadmin/
Web-Based Systems - Misbhauddin 3
![Page 4: >> PHP: MySQL & CRUD. R ecall Database Tables Records is composed of Operations (CRUD) Create Retrieve Update Delete DBMS Access Control MySQL phpMyAdmin](https://reader036.vdocuments.us/reader036/viewer/2022082510/5a4d1aec7f8b9ab05997b5f2/html5/thumbnails/4.jpg)
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
![Page 5: >> PHP: MySQL & CRUD. R ecall Database Tables Records is composed of Operations (CRUD) Create Retrieve Update Delete DBMS Access Control MySQL phpMyAdmin](https://reader036.vdocuments.us/reader036/viewer/2022082510/5a4d1aec7f8b9ab05997b5f2/html5/thumbnails/5.jpg)
Database Operations
• Create User• Create Database• Create Table• Insert Records in the Table
DEMO
Web-Based Systems - Misbhauddin
5
![Page 6: >> PHP: MySQL & CRUD. R ecall Database Tables Records is composed of Operations (CRUD) Create Retrieve Update Delete DBMS Access Control MySQL phpMyAdmin](https://reader036.vdocuments.us/reader036/viewer/2022082510/5a4d1aec7f8b9ab05997b5f2/html5/thumbnails/6.jpg)
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
![Page 7: >> PHP: MySQL & CRUD. R ecall Database Tables Records is composed of Operations (CRUD) Create Retrieve Update Delete DBMS Access Control MySQL phpMyAdmin](https://reader036.vdocuments.us/reader036/viewer/2022082510/5a4d1aec7f8b9ab05997b5f2/html5/thumbnails/7.jpg)
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
![Page 8: >> PHP: MySQL & CRUD. R ecall Database Tables Records is composed of Operations (CRUD) Create Retrieve Update Delete DBMS Access Control MySQL phpMyAdmin](https://reader036.vdocuments.us/reader036/viewer/2022082510/5a4d1aec7f8b9ab05997b5f2/html5/thumbnails/8.jpg)
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
![Page 9: >> PHP: MySQL & CRUD. R ecall Database Tables Records is composed of Operations (CRUD) Create Retrieve Update Delete DBMS Access Control MySQL phpMyAdmin](https://reader036.vdocuments.us/reader036/viewer/2022082510/5a4d1aec7f8b9ab05997b5f2/html5/thumbnails/9.jpg)
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. ☻
![Page 10: >> PHP: MySQL & CRUD. R ecall Database Tables Records is composed of Operations (CRUD) Create Retrieve Update Delete DBMS Access Control MySQL phpMyAdmin](https://reader036.vdocuments.us/reader036/viewer/2022082510/5a4d1aec7f8b9ab05997b5f2/html5/thumbnails/10.jpg)
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
![Page 11: >> PHP: MySQL & CRUD. R ecall Database Tables Records is composed of Operations (CRUD) Create Retrieve Update Delete DBMS Access Control MySQL phpMyAdmin](https://reader036.vdocuments.us/reader036/viewer/2022082510/5a4d1aec7f8b9ab05997b5f2/html5/thumbnails/11.jpg)
Web-Based Systems - Misbhauddin 11
Next on Web-based Systems
• Next Class – Form Handling in PHP– Add Items to the Database