How to access MySQL database in PHP

In this tutorial, we are going to access MySQL database from PHP. Accessing MySQL from PHP is very simple and easy to implement. PHP contains MySQLi class functions to connect and access the database. Some of those are used in this article.

• prepare() – To prepare query statement object
• fetch_row() – To read row data as an associative array. There are many MySQL fetch functions in PHP to read table rows.
• execute() – executes queries with the reference of the statement object created.

This article contains a short example of accessing databases using PHP and MySQL. The upcoming sections explain the example code to guide beginners.

Example files and database script

This example uses the database table named employees. It performs the create, read, update and delete (CRUD) functionalities with the database.

File Structure

This is the example code file structure. It contains the files in the root to perform read-write operations. The landing page contains a navigation link to go to the list page.

Database script

Import this database script into the development environment. It contains the CREATE statement of the target table on which the CRUD is performed.

Then configure the database details at the top of each file in the example code.

CREATE TABLE `employees` (
  `id` int(11) NOT NULL,
  `employee_name` varchar(255) NOT NULL,
  `employee_email` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `employees`
--
ALTER TABLE `employees`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `employees`
--
ALTER TABLE `employees`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

Create and Configure Database

Open PHPMyAdmin or any other database client to create the database. Select the newly created database and create the table on which the CRUD is going to be performed.

After creating the database and tables connect this from PHP by setting the configuration. The following code is used to connect MySQL from PHP. It requires hostname, database name, database username, database password.

This example code uses the procedural way of connecting the MySQL database.

<?php
// to be replaced with your own information
$conn = mysqli_connect("localhost","root","password","database"); 
?>

Create New Record

The code shows the INSERT query to add a new row to the database table employees by mapping values to its columns employee_name and employee_email.

Since the column values are string data, those are enclosed with single quotes. The prepare() function creates the SQL statement object to bind the params. Then, the code executes the query with the reference of this object.

<?php
// Configure your database details here
$conn = mysqli_connect("localhost", "root", "", "database");

$employeeName  = 'Emma';
$employeeEmail = 'emma@domainname.com';
$sqlStatement = $conn->prepare("INSERT INTO employees(employee_name, employee_email) VALUES (?, ?)");
$sqlStatement->bind_param("ss", $employeeName, $employeeEmail); 
if($sqlStatement->execute()) {
    $message = "Record Added Successfully.";
} else {
    $message = "Problem in Adding New Record.";
}
$sqlStatement->close();
$conn->close();
print $message;
?>

Read Data from Table

The SELECT query is used to read database table rows. The following code shows how to read all rows from the “employees” table. I used the fetch_assoc() function to get the associative array column fields and values for each row.

The code will return a list of employee names and it’s email in an associative array.

After MySQL fetch, it iterates the result using PHP foreach() and prints the data into the browser.

<?php
// Configure your database details here
$conn = mysqli_connect("localhost", "root", "", "database");
$sqlStatement = "SELECT * FROM employees";
$result = $conn->query($sqlStatement);
$conn->close();	
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
    $tblEntries[] = $row;
    }
}

foreach($tblEntries as $record) {
?>
Employee Name : <?php echo $record["employee_name"]; ?><br/>
Employee Email: <?php echo $record["employee_email"]; ?><br/>
<br/>
<?php 
}
?>

This code will display the result in the browser as like as below.
Employee Name : Emma
Employee Email : emma@domainname.com

Employee Name : Olivia
Employee Email : oilivia@domainname.com

We can read filtered data by using the WHERE clause. The following code executes the SELECT query with a prepared statement. It reads the employees in ‘emma@domainname.com’ email from the database.

<?php
$sqlStatement = "SELECT * FROM employees WHERE employee_email=?";
$sqlStatement->bind_param("s", 'emma@domainname.com');
$result = $conn->query($sqlStatement);
$conn->close();
?>

Conclusion

Thus we have learned how to Access MySQL from PHP. TThis article contains a short example of accessing databases using PHP and MySQL.

I hope this example helped to create your own code to Access MySQL from PHP .