Create PHP 7 MySQL Database Tables Using MySQLi & PDO

In this tutorial we'll learn how to use MySQLi and PDO to create MySQL database tables in PHP 7.

You can use the CREATE DATABASE SQL instruction to create a database in your MySQL client so let's start by creating a database. Open a new terminal and run the following command:

$ mysql -u root -p

Enter your MySQL instance password when prompted.

Note: The official tool for working with MySQL is the mysql client which get installed when you install MySQL in your machine. The MySQL client can be used through your terminal as a CLI tool.

Next, you can run the following command to create a MySQL database:

mysql> create database mydb;

That's it! We now have a database.

Let's now see how you can create a MySQL table using PHP, MySQLi and PDO.

The mysqli extension is a relational database driver that allows you to access the functionality provided by MySQL 4.1 and above in PHP. It stands for MySQL Improved.

Creating a MySQL Table in PHP Using MySQLi

Let's start with the MySQLi extension.

Create a server.php file and add the following variables:

<?php

$server = "localhost";
$dbuser = "root";
$dbpassword = "YOUR_DATABASE_PASSWORD";
$dbname = "mydb";

Note: Make sure to change your database user and password accrodingly.

Next, create a connection to your MySQL database using the following code:

$connection = new mysqli($server, $dbuser, $dbpassword, $dbname);

if ($connection->connect_error) {
    die("Connection error: " . $connection->connect_error);
}

Next, create a SQL query to create the database table called contacts:

$sqlQuery = "CREATE TABLE contacts (
id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
firstName VARCHAR(35) NOT NULL,
lastName VARCHAR(35) NOT NULL,
email VARCHAR(55)
)";

Next, run the SQL query using the following code:

if ($connection->query($sqlQuery) === TRUE) {
    echo "Table created successfully!";
} else {
    echo "Error creating SQL table: " . $connection->error;
}

Finally, close your database connection using the following code:

$connection->close();
?>

Using PDO to Create MySQL Database Table in PHP

PDO stands for PHP Data Object. It's a set of PHP extensions that provide a core PDO class and database drivers for major database systems.

You can also use PDO for connectiong and creating a MySQL database table:

<?php

$server = "localhost";
$dbuser = "root";
$dbpassword = "YOUR_DATABASE_PASSWORD";
$dbname = "mydb";

try {
    $connection = new PDO("mysql:host=$server;dbname=$dbname", $dbuser, $dbpassword);


    $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sqlQuery = "CREATE TABLE contacts (
        id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
        firstName VARCHAR(35) NOT NULL,
        lastName VARCHAR(35) NOT NULL,
        email VARCHAR(55)
    )";

    $connection->exec($sqlQuery);

    echo "Table created successfully!";
    }
catch(PDOException $e){
    echo $sqlQuery . "<br>" . $e->getMessage();
}

$connection = null;
?>

Conclusion

In this quick post, you have seen how you can create a MySQL database table in PHP using the MySQLi extension and PDO.

Note: We also publish our tutorials on Medium and DEV.to. If you prefer reading in these platforms, you can follow us there to get our newest articles.

You can reach the author via Twitter:

About the author

Ahmed Bouchefra
is a web developer with 5+ years of experience and technical author with an engineering degree on software development. You can hire him with a click on the link above or contact him via his LinkedIn account. He authored technical content for the industry-leading websites such as SitePoint, Smashing, DigitalOcean, RealPython, freeCodeCamp, JScrambler, Pusher, and Auth0. He also co-authored various books about modern web development that you can find from Amazon or Leanpub


Get our Learn Angular 8 in 15 Easy Steps ebook in pdf, epub and mobi formats, plus a new Angular 8 tutorial every 3 days.


Online Courses (Affiliate)

If you prefer learning with videos. Check out one of the best Angular courses online
Angular 8 - The Complete Guide (2019+ Edition)

Angular Crash Course for Busy Developers

comments powered by Disqus
DMCA.com Protection Status