>> php: mysql & crud. r ecall database tables records is composed of operations (crud)...
Post on 08-Jan-2018
223 Views
Preview:
DESCRIPTION
TRANSCRIPT
>> 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
top related