Tutorials - MySQL - Create

Now that your database and user have been created, you're almost ready to start storing and retrieving data. Data in MySQL is stored in "tables", which consist of columns (one for each piece of data to be stored) and rows (one for each entry in the database). This is the fundamental characteristic of a relational database systems such as MySQL. First, we need to connect to the database:

<?php
mysql_connect("localhost", "admin", "password") or die("Couldn't connect: " . mysql_error());
echo "Connected to MySQL<br />";
?>

Test this code on your server (or local test environment) to make sure that you can connect to MySQL - if you can't you'll get an error prefixed by "Couldn't connect" which will usually tell you what the problem is with your connection. Now that we know we're able to connect, we can try to use the database we set up in the previous tutorial:

<?php
mysql_connect("localhost", "admin", "password") or die("Couldn't connect: " . mysql_error());
echo "Connected to MySQL<br />";
mysql_select_db("test") or die("Unable to switch to database: " . mysql_error());
echo "Connected to Database<br />";
?>

Again, test this code on you server or local test environment to make sure that you can connect to MySQL and switch to the database we have set up. The last thing that we need to do is create a table for our data to be stored in. Most databases consist of a large amount of tables, we'll mostly just be working with one until we come to explain joins.

Imagine we wanted to store a data about our customers for an arbitrary company. First we need to work out the data that we need to store, and the type of data this will be:

Customer ID - Integer - Unique to each customer
Tax ID - Integer - Could also be unique to each customer, however we cannot guarantee this
Name - String
Address - String

We need to create the "customers" table to hold this data. This is how we put the MySQL command together to do this:

CREATE TABLE `customers`(

This is the start of the command, next we add the columns and their data types:

customerid INT NOT_NULL AUTO_INCREMENT,
taxid INT NOT_NULL,
name VARCHAR(255) NOT_NULL,
address TEXT NOT_NULL,

In this table, we don't want any of the columns to be null (have no data in them) so we have told MySQL to enforce this through the NOT_NULL parameter. We can also tell the database to automatically increment the customerid to ensure it is unique through the AUTO_INCREMENT parameter. Finally, we need to add a primary key - the column which the database will use to reference the relationships between tables and the unique piece of data which differentiates the rows - in the case, customerid.

PRIMARY KEY(customerid)

Next, we can put together our MySQL query:

CREATE TABLE `customers`(
  customerid INT NOT_NULL AUTO_INCREMENT,
  taxid INT NOT_NULL,
  name VARCHAR(255) NOT_NULL,
  address TEXT NOT_NULL,
  PRIMARY KEY(customerid)
)

Finally, we can add our MySQL query to a block of PHP code in order to execute it on our server or test environment:

mysql_connect("localhost", "admin", "password") or die("Couldn't connect: " . mysql_error());
echo "Connected to MySQL<br />";
mysql_select_db("test") or die("Unable to switch to database: " . mysql_error());
echo "Connected to Database<br />";

mysql_query("CREATE TABLE `customers`(
  customerid INT NOT_NULL AUTO_INCREMENT,
  taxid INT NOT_NULL,
  name VARCHAR(255) NOT_NULL,
  address TEXT NOT_NULL,
  PRIMARY KEY(customerid)
)") or die("Couldn't create table: " . mysql_error());

echo "Table created<br />";

Page Responses

Currently there have been no responses to this page...

Post Response

If you have anything to contribute to this tutorial, found a bug, or know a better way of achieving the same goal, please leave your response below.