Accessing data with PHP and MySQL
Table of Contents
Introduction
PHP is a general-purpose scripting language used for creating dynamic web pages. It can do a lot of things in web applications like the following below:
- PHP can
- Generate a dynamic web page content
- collect form data
- Manipulate data in the database
- Manage files within the web server
- Secure data using encryption
- Send and receive cookies
PHP and MySQL environment setup
PHP can’t be run without web server, so in this section, we are going to set up the PHP environment. Including the MySql server to store and manage data.
Install XAMPP web server
XAMPP is an open-source web server application that can be used in different platform. It provides a package which contains Apache HTTP Server, MySQL, and interpreters for scripts written in the PHP and Perl programming.
Download XAMPP in apachefriends.org or search it in google search engine.
Once downloaded the installer of XAMPP and run it, you will see the starting point of the setup:
Click the next button, and select all components you want, I recommend select all of it for the future use:
Then click next, and choose the path of the XAMPP server, the default path is C:\xampp:
And then click next button until you finish the installation.
Once the installation is done, you can now open the XAMPP control panel to launch the Apache server and MySQL database.
In control panel click the start button for Apache and MySQL.
Note: Always start the Apache server first before MySQL server, and make sure each server turns to green, meaning, it starts successfully:
PHP quick example
Once the XAMPP is launched, you can now explore the PHP syntax, let’s try to print a hello world message as our first example.
let’s start by creating lgeratech folder in C:\xampp\htdocs, once the folder is created, open your favorite text editor and then copy and paste the code below. And then save the file inside of lgeratech folder, make sure you save it as PHP file type or added .php extension, in order to interpret it by XAMPP server.
<!DOCTYPE html>
<html>
<body>
<?php
echo "Hello World";
?>
</body>
</html>As you can see, we defined the PHP script by adding tag which starts with <?php and ends with ?>. And also we used echo keyword to print hello world message.
The PHP scripts can be placed anywhere inside of the HTML document:
<!DOCTYPE html>
<html>
<head>
<?php
echo "<title>LGeraTech</title>";
?>
</head>
<body>
<?php
echo "<h1>Hello World</h1>";
?>
</body>
</html>And we can also use echo to define HTML elements, like we did in the code above, which we defined <title> and <h1> tags.
What you need
- It is necessary to have a bit of knowledge in HTML, CSS and JavaScript since the PHP file contains those technologies.
- And also you must have a knowledge in Query Language for data management. At least you know how to create a database and table, and select data using a simple query statement.
In this tutorial, we are going to use the previous example which in HTML, CSS, and Javascript Tutorial
Get all HTML files we created in the previous tutorial, and copy and paste it in the lgeratech folder. Once done copying the files, change all HTML files from .html to .php extension, therefore, the web server can interpret the PHP scripts.
If you missed the previous tutorial or you want to skip this setup, you can clone the repository below: https://github.com/LeoBernabe/data_access_php.git
Create User Database in MySQL
In the previous tutorial, we used browser sessions to store data, which is not a good practice and secure in terms of storing data. Therefore, in this tutorial, we are going to create a database that we can use to store our user data for our user registration page.
In XAMPP control panel, click the Admin button in MySQL section to open the phpMyAdmin client server. Or access the http://localhost/phpmyadmin/ URL. make sure that MySQL server is launched successfully:
To create new database, click the New on the left side menu of the application. Enter userdb for the Database name field, and then click the Create button.
Once the database is created, you can now create table for our user data storage. Click the database we created on the side menu, and then enter user for the table name and enter 7 for the Number of columns field. And then click the Go button:
Once you clicked the Go button, the application will navigate you in the column properties setup which you need to define the properties of each column:
If you want to skip the table setup, you can go to the console section to execute the script below:
CREATE TABLE `userdb`.`user` ( `id` INT NOT NULL AUTO_INCREMENT , `firstname` VARCHAR(50) NOT NULL , `lastname` VARCHAR(50) NOT NULL , `age` INT(3) NOT NULL , `gender` VARCHAR(10) NOT NULL , `password` VARCHAR(50) NOT NULL , `username` VARCHAR(50) NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;MySQL connection
In this section, we are going to create a PHP file that will enable a connection to the MySQL server.
Create new file connection.php and place it in same folder where the other PHP files are located. after that, copy and paste the code below:
<?php
/* The credentials below is used to access the database */
$host_name = "localhost";// xampp server host name
$db_username = "root";// MySQL default username is root
$password = "";// MySQL default password is empty
$database = "userdb";// The database we defined earlier
$connection = mysqli_connect($host_name, $db_username, $password, $database);
// Test connection, if the connection fails, the application will be terminated and prints connect error message
if($connection === false){
die(mysqli_connect_error());
}
?>The mysqli_connect() function is used to open a new connection to MySQL database.
The mysqli prefix means the improved version of MySQL functionalities in PHP.
User registration page
Before we modify the user registration page, we must create new PHP file for inserting or saving data in user table.
First, let’s create insert_user.php file and place it in lgeratech folder. If you have cloned the repository URL provided in previous section, you can skip this procedure.
Once you created the file, copy and paste the code below:
<?php
include 'connection.php';
$first_name = mysqli_real_escape_string($connection, $_REQUEST['firstname']);
$last_name = mysqli_real_escape_string($connection, $_REQUEST['lastname']);
$age = mysqli_real_escape_string($connection, $_REQUEST['age']);
$gender = mysqli_real_escape_string($connection, $_REQUEST['gender']);
$username = mysqli_real_escape_string($connection, $_REQUEST['username']);
$password = mysqli_real_escape_string($connection, $_REQUEST['password']);
$sql = "INSERT INTO user (firstname, lastname, age, gender, username, password)
VALUES ('$first_name', '$last_name', '$age', '$gender', '$username', '$password')";
if(mysqli_query($connection, $sql)){
$message = 'User has been added successfully!';
header("Location: login.php?message=". urlencode($message));// Send success message to login page
} else{
echo "ERROR: $sql. " . mysqli_error($connection);
}
mysqli_close($connection);
?>In the previous section, we defined the MySQL connection in another file. Now, as you can see above, we included that file using include 'connection.php'; to import that connection function in the code above. Therefore, we can reuse the code we described in other files.
The mysqli_real_escape_string() function escapes the special character in string. Therefore, it is used to avoid having an issue with storing string data that has special characters.
The $_REQUEST is a super global variable used to collect data once the HTML is submitted. Later you will see how is it work when we request for user registration.
The mysqli_query() function is used to execute SQL statement to manage data in database. The query we used in statement is INSERT query, therefore the function will return True when it is successful.
In the code above, when the query is true, the application will navigate you on the login page with a successful message. If false, the application will be terminated, and it prints the MySQL error message.
The final function, which is mysqli_close() terminates the connection that opens previously.
In registration.php, modify the <form> element and refer to the code below:
<form action="insert_user.php" method="post" onsubmit="return register()">Login page
For the login page, we are going to create a function that will authenticate the user account using the credentials.
Create auth.php file then place it in lgeratech folder, once done, copy and paste the code below:
<?php
include 'connection.php';
session_start();
$username = mysqli_real_escape_string($connection, $_REQUEST['username']);
$password = mysqli_real_escape_string($connection, $_REQUEST['password']);
$sql = "SELECT * FROM user WHERE username = '$username' and password = '$password' LIMIT 1";
$result = mysqli_query($connection, $sql);
if (mysqli_num_rows($result) > 0) {
$obj = mysqli_fetch_assoc($result);
$_SESSION['user'] = $obj;
$message = 'Access Granted';
header("Location: home.php?message=". urlencode($message));
} else {
$message = 'Invalid username or password!';
header("Location: login.php?message=". urlencode($message));
}
mysqli_close($connection);
?>As you can see, we use mysqli_query() function to execute a SELECT SQL statement. In this case, the function will return data, instead of boolean (true or false). To check if there is existing data, we used mysqli_num_rows() function.
If mysqli_num_rows() function returns the number of rows that greater than to zero, the program will convert the data to the PHP object using mysqli_fetch_assoc() function. And the object will store in PHP session using $_SESSION['user'], after that, the system will navigate to the home page and display a success message.
Note: In order to work the $_SESSION storage, you should declare the session_start() at the top of the PHP script.
In the previous section, we discuss displaying a success message when the registration success. To display that message in the login form, we need to add another PHP script in login.php, simply copy and paste the code below and place it on the top of <html> element:
<?php
if (isset($_GET['message'])) {
echo '<script type="text/javascript">alert("' . $_GET['message'] . '");</script>';
}
?>The $_GET is also one of the common superglobal variables in PHP, it is used to get data on the URL parameters.
Example URL: http://localhost/lgeratech/login.php?message=User+has+been+added+successfully%21
As you can see on the example URL, we used the message parameter to pass data on the login page, therefore, the $_GET will read this parameter to get its value.
Once done, modify the <form> element and refer to the code below:
<form action="auth.php" method="post" onsubmit="return validateCredentials()">Home and profile page
In this section, we are going to add the message display function on the home page for success login. And also we are going to get the session data that we stored on the login page during successful login and display the data on the profile page.
Home page
In home.php, add PHP script same as we added in login.php for the displaying of success message:
<?php
if (isset($_GET['message'])) {
echo '<script type="text/javascript">alert("' . $_GET['message'] . '");</script>';
}
?>Profile page
To display the user details in profile page, open the profile.php file and add the following script below on the top of <html> element:
<?php
session_start();
$user = isset($_SESSION['user']) ? $_SESSION['user'] : null;
?>The isset() function checks whether a variable is empty and checks if the variable is declared or set.
Test the Application
Alright! We have done applying PHP functions and modifying the pages of our application.
Now, the application is ready to perform the changes and the most important is to access the data in MySQL database.
To test the application, make sure the Apache and MySQL server are running.
Once the servers are started, access the http://localhost/lgeratech/login.php to navigate to the login page. For the other demo watch the video below:
Additionally, You can access the login page without specifying the file name by adding an index.php file. Once added, copy and paste the code below:
<?php
header("Location: login.php");
exit();
?>Now, you can access the login.php without specifying the file name in the URL: http://localhost/lgeratech/
Conclusion
Congrats! You’ve finally set up the database into your basic web application. You can now explore and practice the other SQL statements like the update, delete, and more statements.
You can refer to my other posts and tutorials if you want to learn more:
supreme hoodie
WONDERFUL Post.thanks for share..more wait .. ?