PHP Form & MySQL: Insert a record through variable

This entry is part 1 of 3 in the series PHP Form & MySQL

1Independence DayWill Smith1996
2Ender's GameAsa Butterfield2013
3LincolnDaniel Day Lewis2012
4InceptionLeonardo DiCaprio2010

Let’s say we want to create the above database table by inserting a record through a variable.

To set up the insert query string in line 19, I have used the following 4 steps:

  • create database, line 8
  • use database, line 11
  • create table, line 14
  • insert into table, line 19

And finally to execute the query string on line 24.


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

$sql_db = "create database if not exists allmovie";
mysqli_query($mysqli, $sql_db);

$sql_use_db = "use allmovie";
mysqli_query($mysqli, $sql_use_db);

$sql_tb = "create table if not exists movie (id int auto_increment primary key, title varchar(30), actor varchar(50), year int(10))";
mysqli_query($mysqli, $sql_tb);

$title1 = 'Independence Day';

$sql_in = "INSERT INTO movie
(title, actor, year)
('$title1', 'Will Smith', 1973)";

mysqli_query($mysqli, $sql_in);



In line 11, I have used this statement:

$sql_use_db = "use allmovie";

The use db_name statement tells MySQL to use the db_name database as the default (current) database for subsequent statements.

Notice that I have used the variable $title1 and put that into the $sql_in query string in line 19.

Note also that $title1 is enclosed in a single quote ” string to differentiate it with $sql_in.


PHP MySQL: Insert Record

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

We use similar function mysqli_query() to insert records to a database table.  Here we insert 3 records into the table allartist.


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

$sql = "INSERT INTO artist
(artiste, album, year)
('Rod Stewart', 'Maggie May', 1973),
('New Order', 'Substance', 1987),
('Nirvana', 'Smell Like Teen Spirit', 1992)";

if (mysqli_query($mysqli, $sql)) {
    echo "Data inserted successfully";
} else {
    echo "Error inserting data into table with error no: " . $mysqli->errno;


After running the script and if it is successful, we should see a message as shown in the screenshot below.


Using Toad, we can display the inserted data using the query

select * from artist

inserted data


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

The acronym CRUD (create, read, update, delete) refers to the major functions that are implemented in relational database.  Once you have mastered these 4 functions, you will be quite proficient in manipulating the database, just like manipulating the DOM in web programming.


In the earlier examples, we have already used insert into and select statements.

In the upcoming posts, we will delve further into the select statements.