How to CREATE a database and a table using PHP.
TAGS: SQL, CREATE, PHP, MySQL


I assume that you have downloaded and installed the LAMPstack (or WAMP or MAMP), as explained in the first blogposting. Now follow these steps to setup a new database using PHP code.

The code we will use for creating the database is this:

CREATE DATABASE IF NOT EXISTS dbname;

And the code for creating the table is like this:
CREATE TABLE IF NOT EXISTS contacts
(
AutoID int NOT NULL AUTO_INCREMENT ,
Lastame varchar(255),
Firstname varchar(255),
EmailAddr varchar(255),
WebAddr varchar(255), 
primary key(AutoID)
);
The first line will create the database for us. You can change the name of your database to what you like. With the second block we create the table, in the DB, for our contacts. In this table we will create some fields for holding info of our contacts, with firstname, lastname, emailaddress, and webaddress. These fields are set to be able to hold upto 255 characters. Also I make a field named “AutoID” that is giving each entry a number for reference. These numbers will be incrementing by 1 (one) for every new table row.

So our PHP document will look like this:

<?php 

$SQL_string = "CREATE DATABASE IF NOT EXISTS dbname"; 

$con = mysqli_connect('127.0.0.1', 'root', 'Your_Password', ''); 
if (mysqli_connect_errno()) { 
  echo 'Error connecting to DB'; 
} 

$dostuff = mysqli_query($con, $SQL_string); 

if(! $dostuff ) { 
echo 'Error in executing query'; 
} 
mysql_close($con); 
echo 'Success! Your database have been created!'; 
?>

If you get an error connecting to the DB then it is most likely the password. Notice that the last place, where you see '' is kept empty, that is where the name of your database will be, when using this connection-string later, calling an existing database.

And now I will create the table, with five fields. Like so;

<?php 
$con = mysqli_connect('127.0.0.1', 'root', 'Your_Password', 'dbname'); 
if (mysqli_connect_errno()) { 
  echo 'Error connecting to DB'; 
  exit();
} 

$SQL_string = "CREATE TABLE IF NOT EXISTS contacts 
(AutoID int NOT NULL AUTO_INCREMENT, 
Lastname varchar(255), 
Firstname varchar(255), 
EmailAddr varchar(255), 
WebAddr varchar(255), 
primary key(AutoID))"; 

$dostuff = mysqli_query($con, $SQL_string); 

if(! $dostuff ) { 
echo 'Error in executing query'; 
exit(); 
} 

mysql_close($con); 

echo 'Success! Your table have been created!'; 
?>

Notice, that this time, the name of the database is existing, and therefor written in the connection-string.
Okay. Now you have a database with a table, ready to fill with all your contacts. In the next blog I will continue with an example showing how to write information to our new database.