PHP MySQL: Create Table

This entry is part 5 of 17 in the series PHP & MySQL

In this sqlfiddle, we have created a table and inserted some data into the table.

CREATE TABLE artist
	(
 id int auto_increment primary key, 
 artiste varchar(30), 
 album varchar(50),
 year int(10)
 
 );

INSERT INTO artist
(artiste, album, year)
VALUES
('Rod Stewart', 'Maggie May', 1973),
('New Order', 'Substance', 1987),
('Nirvana', 'Smell Like Teen Spirit', 1992);
Run the SQL

Let’s create this similar table using PHP.  Using PHP, a table is created by mysqli_query.

There are 2 parameters in mysqli_query.  The database connection string $mysqli and the create table string $sql.

<?php

$mysqli = new mysqli("localhost", "root", "", "allartist");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL";
}

$sql = "CREATE TABLE artist (id int auto_increment primary key, artiste varchar(30), album varchar(50), year int(10))";

if (mysqli_query($mysqli, $sql)) {
    echo "Table artist created successfully";
} else {
    echo "Error creating table with error no: " . $mysqli->errno;
}

?>

If the WAMP server in your PC is started, you can try to run the above script.  If an error number is generated, you can google the relevant error message.

You can now login to phpMyAdmin and see the table artist similar to the screenshot below.

artist_table

MySQL Clients: PHPMyAdmin

This entry is part 2 of 8 in the series MySQL Clients

First, start the WAMP server and make sure that it appears in the far right of the task bar in your Windows Desktop.

Click on it and look for PHPMyAdmin.  Start PHPMyAdmin and you will be brought to localhost/phpmyadmin in the web browser.

phpmyadmin

If this is the first time you have started using PHPMyAdmin, login as root without a password.  You will be brought to this page.

phpmyadmin1

Create Database

Click on Databases tab.

database

We will now create a database called allartist.  Type in allartist in the create database textbox and click on create.  A database allartist will be successfully created.

Create A Table

Using the code below, we will now create a table.

CREATE TABLE artist
    (
 id int auto_increment primary key, 
 artiste varchar(30), 
 album varchar(50),
 price decimal(3, 2) 
 );
 
INSERT INTO artist
(artiste, album, price)
VALUES
('Rod Stewart', 'Maggie May', 8.90 ),
('New Order', 'Substance', 7.50),
('Nirvana', 'Smell Like Teen Spirit', 9.80);

First click on allartist on the left column of the page.  Click on SQL and paste the code to the SQL query box.  Click on Go.

createtable

Go back to SQL tab and use the following query to display the table.  

select * from artist

A page will be shown as below.

query1

SQL: NULL value

This entry is part 18 of 20 in the series SQL Tutorial

create table artist
	(
 id int auto_increment primary key, 
 artiste varchar(30) not null, 
 album varchar(50),
 year int(10)
 );

When creating a table, we can impose a not null value constraint to a column.

The attribute not null specifies that the column cannot contain the null value. NULL is a special value indicating “no value”.

As shown in the below screenshot, a error message column cannot null value is generated.

null