PHP Tutorials

Create tables and database in PHP/MySQL
A database is composed of one or more tables.

Create a database
We use the CREATE DATABASE statement in MySQL to create a database.

Syntax:
CREATE DATABASE database_name

NOTE: We must use the mysql_query() function so that PHP will execute the given command like CREATE DATABASE database_name. This function is used to send a command or query to a MySQL connection.

In this example, we will be making a database named newdatabase:

<?php
$connect = mysql_connect(“localhost”,”bryan”,”777”);
if(!$connect)
{
die(“failed to connect: “ . mysql_error());
}

$makedatabase = mysql_query(“CREATE DATABASE newdatabase”,$connect);

if(!$makedatabase)
{
echo “Failed to create database: “ . mysql_error();
}
else
{
echo “Database created”;
}

mysql_close($connect);
?>

Creating a table in MySQL
We need to use the CREATE TABLE statement when making a table in MySQL.

Syntax:
CREATE TABLE mytable
{
column_name1 data_type,
column_name2 data_type,
…………..
}

Example:
This example will create an employee table and the 3 columns are emp_num, emp_name, and emp_age.

<?php
$connect = mysql_connect(“localhost”,”bryan”,”777”);
if (!$connect)
{
die(“Failed to connect: “ . mysql_error());
}

$makedatabase = mysql_query(“CREATE DATABASE newdatabase”,$connect);

if(!$makedatabase)
{
echo “Failed to create database: “ . mysql_error();
}
else
{
echo “Database created”;
}

mysql_select_db(“newdatabase”, $connect);

$create_table = “CREATE TABLE employee
(
emp_num int,
emp_name varchar(50),
emp_age int
)”;

mysql_query($create_table,$connect);
mysql_close($connect);
?>

NOTE: Before creating a table, you must first make sure that you have selected a database by using the function mysql_select_db().

Primary Keys

Every table should have a primary key. Primary keys are used to uniquely identify the rows in a table. There should be no duplicates in the values of the primary key. They value of primary can’t contain a null.

In the example below, we made the emp_id as the primary key. Primary key is often a number and used with AUTO_INCREMENT. AUTO_INCREMENT increases by 1 each time a new record is added. We should also use NOT NULL to make sure that the primary key will not have null values.

$create_table = “CREATE TABLE employee
(
emp_num int NOT NULL AUTO_INCREMENT,
PRIMARY KEY(emp_num),
emp_name varchar(50),
emp_age int
)”;

mysql_query($create_table,$connect);

mysql_close($connect);

Connect PHP to a MySQL Database
MySQL is the most common database used with PHP.

Connecting PHP to a MySQL Database
You must first connect to a database so you can access and work with data in a database.

Syntax (Connect PHP to MySQL):
mysql_connect(servername, username, password);

Example (Connect PHP to MySQL):
In this example, I stored the connection in the $connect variable so we will be using it later and if it failed to connect then the die statement will be executed.

<?php
$connect = mysql_connect(“localhost”,”bryan”,”777”);
if(!$connect)
{
die(“Failed to connect: “ . mysql_error());
}
?>

Closing a connection
As soon as the script ends, the connection will be automatically closed but you can close it by using the mysql_close() function.

Here is an example:

<?php
$connect = mysql_connect(“localhost”,”bryan”,”777”);
if(!$connect)
{
die(“Failed to connect: “ . mysql_error());
}
mysql_close($connect);
?>