Maintaining Connection State with PHP
Maintaining Connection State
HTTP is stateless.
- Each HTTP request is unrelated to any other (from the point of web server)
To maintain connection state, there are 3 common ways:
- Encode state information in the next URL to be used by the client
- RUL:
localhost/web/wage.html?state='tom'
for example - Not good to send sensitive data
- RUL:
- Use Cookie
- Use Session
PHP Cookie
What is a Cookie?
- A cookie is often used to identify a user.
- A cookie is a small file that the server embeds on the user’s computer.
- Each time the same computer requests a page with a browser, it will send the cookie too.
We can use PHP Cookie to:
- Create/Retrieve a Cookie
setcookie($cookie_name, $value, time()+(60*60*24)); // 1 day
- Check if a Cookie is set
isset($_COOKIE[$cookie_name])
- Modify a Cookie value
- use
setcookie()
again
- use
- Delete a Cookie
- use
setcookie()
with an expiration date setcookie($cookie_name, $value, time()-3600)
- use
Example Code
login.html
1 | <body> |
login.php
1 | <body> |
delete-cookie.php
1 |
|
PHP Session
What is a Session
- A session is a way to store information (in variables) to be used across multiple pages.
- Unlike a cookie, the information is not stored on the user’s computer.
- Session variables are set with the PHP global variable
$_SESSION
.
Using PHP Session, we can:
- Start a Session
session_start()
- Check if a Session is set
isset($_SESSION[$session_name])
- Get/Set Session variable values
$_SESSION["score"] = "100"; //Get/Set session vairables
for example
- Destroy a PHP Session
- We need to rremove all session variables before destroying the session
session_unset()
- remove all session variablessession_destroy()
- destroy the session
Code Example
math-quiz.php
1 |
|
math-quit.php
1 |
|
Connecting to Database
A relational database allows us to store data in one or more related tables.
A Relational Database Management System (RDBMS) provides management tools , e.g.,creating, removing, modifying, searching databases, tables, records, …
- Unlike text files, databases and tables in an RDBMS are not directly accessible.
- Requests must be submitted using the Structured Query Language (SQL) that the RDBMS provides.
- The RDBMS processes each request (called a query) and returns a result.
MySQL
MySQL is a freeware RDBMS and query language, widely used for Web-based applications.
- A version of MySQL is installed with your Web server XAMPP.
- To start MySQL server: just start both Apache and MySQL in the XAMPP Control Panel.
3 Ways to Work with MySQL
- From CLI (Command Line)
- Click “Shell” in the XAMPP Control Panel
- Through a GUI (graphical interface) which is phpMyAdmin
- From a custom application developed in a programming language such as PHP.
- use PHP functions designed to submit and process MySQL queries.
To Create a Database
mysql-setup.html
1 |
|
mysql-setup.php
1 |
|
Working with PHP and MySQL
In order to work with MySQL we must:
- Open a connection to a MySQL server
mysqli_connect($URL, $userID, $userPW, $dbName);
- Connect to a MySQL database
$connect = mysqli_connect($URL, $userID, $userPW, $dbName);
- Submit queries as needed and receive and handle the results of these queries
$result = mysqli_query($connect, $userQuery)
will receive the result of the querymysqli_fetch_assoc($result)
continue to extract records from the result set until no more records are found
- Close the connection
mysqli_close()
Open/Close a connection
We use PHP function mysqli_connect()
to open a connection to a MySQL server.
4 Arguments:
- The URL of the MySQL RDBMS
- A user ID that has been registered with the system
- The user password
- The name of the specific database we wish to work with
Example:
mysql-connect.php
1 |
|
yourfile.php
1 |
|
mysqli_connect()
returns a bool value to let the programmer to see wthether it is connected.die()
causes the application to terminate with any error message that you provide.mysqli_connect_error()
returns the standard MySQL error message.mysqli_close()
closes a connection.
Submit a query
1 | //Example: |
$result = mysqli_query($connect, $userQuery)
- first argument references the database connection
- second argument is the query to be submitted
$result
will receive the result of the query
- If there is an error and the query cannot be processed (e.g., connection error, MySQL syntax error), then
mysqli_query()
returns false. - If successful,
mysqli_query()
returns a result set of the 0 or more records that satisfy the query. - Before processing the result set, we need to:
- Test
$result
to see if there was an error. - Use the
mysqli_num_rows()
function to test if the query found NO records.
- Test
Processing the Result Set
1 | $userQuery = "SELECT firstName, lastName FROM personnel"; |
Use mysqli_fetch_assoc()
function continues to extract records from the result set until no more records are found (then it returns false).
- Each extracted record is an associative array.
If there is more than a single record, use while ( $row = mysqli_fetch_assoc($result) )
to extact records.
- Each time the loop repeats,
$row
is assigned the next record in the result set as an associative array. $row['key']
is your record value. E.g.$row['firstName']
contain the first name of that row.
Example 1: Display the results as an HTML table.
1 | $server = "localhost"; |
Example 2: Use input from an HTML Form to construct a query
1 | $searchName = $_POST['searchName’]; |
Example 3: Perform aggregations on MySQL queries
- MySQL provides a number of aggregation functions, for example COUNT, SUM, AVG, MIN, MAX,
- To find the average hourly wage of all employees:
$userQuery = "SELECT AVG(hourlyWage) FROM personnel";
- The average is stored in the associative array with the key name
AVG(hourlyWage)
1 | $userQuery = "SELECT AVG(hourlyWage) FROM personnel"; |
Example 4: Perform JOIN operations on multiple tables
1 | $userQuery= "SELECT firstName, lastName, hourlyWage, hoursWorked |