Deutsch English

PHP MySQL class tutorial

In this blog I will explain a PHP MySQL class which contains the most important methods of quick and easy manipulation of MySQL data.
I have created a blog table in order to show you the methods of this class.

Overview of the methods of this class:

  • __construct() is the constructor of the class and creates a connection with the MySQL database
  • createTable($table, $fields, $key) creates a MySQL table
  • res($query) sends SQL query to the MySQL servers
  • num($res) returns the number of found data sets of a query
  • row($res) returns the number of found data sets of a query as an associative array
  • insert($table, $data) inserts new data in a  MySQL table
  • update($table, $data, $where) updates data of a MySQL table
  • delete($table, $where) deletes data from a MySQL table
  • setWhere($where) creates a string-where-syntax out of an array
  • realScapeString($fields) checks whether the data is in a safe syntax before it is saved in the database

Now that we’ve taken a quick look on all the methods, let’s start with the example blog table.

First, we’ll download the file MySQL.php and save it in an index on a webserver which supports PHP and MySQL. Then we’ll open the file MySQL.php in a text editor or developer tool and insert the MySQL access data:
private $DBHOST = 'localhost'; //Database host
private $DBUSR = 'root'; // Database user
private $DBPWD = ''; // Database password
private $DBName = 'db'; // Database name

The next step is to create a file index.php in the index where MySQL.php is located. We  open index.php in a text editor and include the file MySQL.php in index.php.

<?php
    require 'MySQL.php';
    $db = new MySQL();
?>

In order to find out if the class MySQL was correctly included in index.php, and if the connection with the database has been established, we open the file index.php in a browser http://localhost/myproject/index.php.
If no error message (e.g. „can’t connect to mySQL server“ or „can’t select MySQL database“) pops up, we can proceed with the next steps of our tutorial.

Creating a table with createTable:
With the method createTable ($tableName, $fields, $primaryKey) we can create a MySQL table.

$tableName is the name of the table. In our case, the table is called „Blog“.
$fields is an array and contains the field names and SQL types.
$primaryKey defines the PRIMARY KEY of the table „Blog“.

We now create an array in index.php, which contains all fields of the blog table. Next, we call up the function $db->createTable ('Blog', $fields, 'ID'). The file index.php should now look like this:

<?php
require 'MySQL.php';
    $db = new MySQL();
    $fields = array(
        'ID' => 'int(11) NOT NULL AUTO_INCREMENT',
        'Title' => 'varchar(100) NOT NULL',
        'shortDescription' => 'mediumtext NOT NULL',
        'Content' => 'text NOT NULL',
        'Created' => 'datetime NOT NULL');
    $db->createTable('Blog', $fields, 'ID');
?>

Now, let’s save the file index.php and open it in a browser http://localhost/yorproject/index.php
Next, we log into PHPMyAdmin and check whether the table „Blog“ has been created.

Extending the table by adding fields

alterTable($tableName, $fields) enables us to extend the table by adding fields.

$tableName is the name of the table. In our case, the table is called „Blog“.

$fields is an array and contains the field names and SQL types.

 <?php

require 'MySQL.php';
    $db = new MySQL();
    $fields = array(
    'newField1' => 'varchar(100)',
    'newField2' => 'varchar(100)');
$tableName = 'Blog';
$db->alterTable($tableName, $fields);
?>

Inserting data into the blog table
With the method insert ($tableName, $data) we can insert entries into the blog table.
The method insert also return the most recently added ID.
$tableName is the name of the table.
$data is an array and defines, which fields we want to insert into the blog table and the field values.

 

<?php
require 'MySQL.php';
    $db = new MySQL();
    $data = array(
        'Title' => 'Hello World',
        'shortDescription' => 'This is a short description of my first Blog',
        'Content' => 'This is content of my first Blog',
        'Created' => date('Y-m-d H:i:s')
    );
    $lastID = $db->insert('Blog', $data);
?>

Updating the blog table data
The method update ($table, $date, $where) enables us to update certain data of a table.
$tableName is the name of the table.
$data is an array and defines, which fields we want to insert into the blog table and the field values.
$where is an array and defines identifier and identifier value. We can also send $where to the function update as a string (z.b. $where = 'ID = 1')

 

<?php
require 'MySQL.php';
    $db = new MySQL();
    $tableName = 'Blog';
    $data = array(
        'Title' => 'Updated blog Title',
        'shortDescription' => 'Updated blog short description ',
        'Content' => 'Updated blog content');
    $where = array('ID' => 2);
    $db->update($tableName, $data, $where);
?>

Deleting data from a blog table
With delete($tableName, $where) we can delete a certain row in a table.

<?php
require 'MySQL.php';
    $db = new MySQL();
    $where = array('ID' => 2);
    $tableName = 'Blog';
    $db->delete($tableName, $where);
?>

Extracting data from the blog table
In order to extract data from the blog table, we use the following methods in index.php

<?php
require 'MySQL.php';
    $db = new MySQL();
    $query = 'select*from Blog order by Created ASC';
    $res = $db->res($query);
    $num = $db->num($res);

    if ($num >= 1) {
        echo '<h1>' . $num . ' Einträge</h1>';
        echo '<ul>';
        while ($row = $db->row($res)) {
            echo '<li>' . $row['Title'] . '</li>';
        }
        echo '</ul>';
    } else {
        echo '<h1>No items were found</h1>';
    }
?>

We can empty a table with truncateTable($tableName). The method dropTable($tableName) can delete a table from the database.
This tutorial is now finished. If you have any questions, please leave a comment below!