Getting Started with Data Access using PHP & MYSQL

This is going to be a very basic and simple data access tutorial. It teaches you how to get started making your web application talk to a database.

In this tutorial, I am going to be using PHP and MySQL for this purpose. In addition, we will be using jQuery library as well as DataTables plugin. So without wasting time, let us get started with the following steps:

Step 1
In the document root (i.e: www or httdocs directory) on your web server, create a folder name dataaccess or any other name of your choice.

Step 2
Now open the folder you just created and create the following files:

index.php
config.php

Also, create the a folder named js and another folder named lib in the js sub-folder.
Now download jquery.min.js into your js folder from here.
Also download jquery.datatables.min.js into the lib sub-folder from here.
After doing the above your project structure should be like this:

getting_started_with_php_data_access_project_structure

Step 3
Run the following script against your database

create database if not exists DataAccess;
use DataAccess;


create table todos (


id int auto_increment,
task varchar(20) not null,
date_created datetime default current_timestamp,
constraint pk_todos primary key (id asc),
constraint uq_todos unique (task)


);


insert into todos (task)
values('Learn PHP'), ('Learn MySQL'), ('Build a web app'), ('Make money');

Step 4
Modify your config.php like this one:

<?php

function connect() {
return new PDO(‘mysql:host=localhost;dbname=DataAccess’, ‘root’, ‘your_password’);
}

Replace your index.php file with the following:


<!DOCTYPE html>

<html>

<head>

<title>Getting Started with DataAccess using PHP & MySQL</title>

<link href=”js/lib/datatables/jquery.dataTables.min.css” rel=”stylesheet”/>

</head>

<body>

<?php

require_once ‘config.php’;

$pdo = connect();

$result = $pdo->query(‘SELECT id, task, date_created FROM todos’);

if ($result) {

echo ‘<h1>My Todos</h1>’;

echo ‘<table id=”todos” class=” dataTable”>

<thead>

<th>ID</th>

<th>Task</th>

<th>Date Created</th>

</thead>

<tbody>’;

while($row = $result->fetch(PDO::FETCH_ASSOC)){

echo ‘<tr>

<td>’ . $row[‘id’] . ‘</td>

<td>’ . $row[‘task’] . ‘</td>

<td>’ . $row[‘date_created’] . ‘</td>

</tr>’;

}

echo ‘    </tbody>

</table>’;

} else {

echo $result->errorInfo();

}

?>

<script src=”js/jquery.min.js”></script>

<script src=”js/lib/datatables/jquery.dataTables.min.js”></script>

<script>

$(function() {

$(‘#todos’).dataTable();

});

</script>

</body>

</html>

 

If you open your application from the browser, you should have this:

getting_started_with_php_data_access_output

 

In the next tutorial, I will show you how you can add more styles to the output as well as performing other database operations.

Please leave your comment or questions below.

 

Thanks for ready & happy programming!

One thought on “Getting Started with Data Access using PHP & MYSQL

Leave a Reply

Your email address will not be published. Required fields are marked *