How to create a database in MySQL and PHP

By David |

PHP |

30th Mar, 2019

Click here to share

In this lesson, we will learn how to create a database in MySQL database using a PHP script.

Before we start to make sure that you have phpMyAdmin, PHP, PHP server installed on your local machine or if you are working online on a live server you are good to go.

If you are on a local machine you can easily install xampp following this tutorial. Xampp will install all the things you needed for this to work.

CONNECTING TO DATABASE

The first to do is to write the script that will connect us to the database server in our case its localhost.

<?php

//connection variables.

// These are the variables that we will use to initialize connection to the datrabase

$host = 'localhost'; // the host of our database

$user = 'root'; // the user name of our database

$password = ‘hash(pasword)’; // the paseword we uae to login to database

?>

After you have written the above script

Now write the one to initiate the connection using the variables above

<?php

//create mysql connection

$mysqli = new mysqli($host,$user,$password);

if ($mysqli->connect_errno) {

    printf("Connection failed: %s\n", $mysqli->connect_error);

    die();

}

 

?>

The above script will initialize a connection to the database and if there be any error it will throw an error message, in this case, it will be MySQL error.

 

<?php

//create the database

if ( !$mysqli->query('CREATE DATABASE WORLDTOK') ) {

    printf("Errormessage: %s\n", $mysqli->error);

}

?>

The above script will now create the database. In this case, the name of the database will be Worldtok.

Remember: MySQL is case insensitive.  So CREATE IS the same as create.

<?php

//create users table with all the fields

$mysqli->query('

CREATE TABLE `WORLDTOK`.`USERS`

(

    `id` INT NOT NULL AUTO_INCREMENT,

    `first_name` VARCHAR(50) NOT NULL,

     `last_name` VARCHAR(50) NOT NULL,

    `email` VARCHAR(100) NOT NULL,

    `password` VARCHAR(100) NOT NULL,

    `hash` VARCHAR(32) NOT NULL,

    `active` BOOL NOT NULL DEFAULT 0,

PRIMARY KEY (`id`)

);') or die($mysqli->error);

 

?>

 

The last section of this we created a table called users in the database of Worldtok. And that ends this lesson.

I know that you have now learnt how you can create database and tables without going to manually go into phpMyAdmin to do that.

POPULATING TABLES WITH PHP VALUES

After you have successfully created a database and table the next thing that you have to do is to populate the database without manually going to be inputting the data to the table because it will be stressfully and confusing.

To do that take a look at the script below.

<?php

INSERT INTO `users` (`first_name`, `last_name`, `email`, `password`, `hash`, `active`) VALUES

( 'Worldtok 3', 'Emmanuel', 'info@worldtok.com', 'password', ‘hash(pasword)’, ‘1’),

( 'Worldtok 1', ' Emmanuel ', 'info@worldtok.com', ' password', ‘hash(pasword)’ , ‘1’),

( 'Worldtok 2', ' Emmanuel ', 'info@worldtok.com', ' password', ’hash(pasword)’ , ‘1’),

( 'Worldtok 3', ' Emmanuel ', 'info@worldtok.com', ' password’, ‘hash(pasword)’ , ‘1’),

( 'Worldtok 5', 'Emmanuel', 'info@worldtok.com', ' password', ’hash(pasword)’ , ‘1’),

( 'Worldtok 6', ' Emmanuel ', 'info@worldtok.com', ' password', ‘hash(pasword)’ , ‘1’),

( 'Worldtok 7', ' Emmanuel ', 'info@worldtok.com', '25', ‘hash(pasword)’ , ‘1’);

?>

The above will insert the values into the users' table

The above code can be downloaded from GitHub or you can download the zip file from here.

Procedure to run the script.

Download the script

Extract into the server direction on your local machine. Eg for xampp its htdocs.

Open the file in your favourite editor and change the database connection details and after that run, the code in your browser and the database and tables will be created for you in phpMyAdmin with the above values inserted into it perfectly for.

If you look well in the above values you will observe that the id is omitted this is because we have used auto increment on the id.

 

Auto increment will make the table to automatically generate a value for each record inserted into the table.

Leave a Reply

RELATED POSTS


Total of 0 Comment