PHP Form & MySQL: Insert a record through variable

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

IDmovieactoryear
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.

<?php

$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)
VALUES
('$title1', 'Will Smith', 1973)";

mysqli_query($mysqli, $sql_in);

mysqli_close($mysqli);

?>

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 Form & MySQL: Enter data through form

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

IDmovieactoryear
1Independence DayWill Smith1996

We will build a form and enter the above record to the table.

We will need to go through the usual steps before executing the insert query string.

The steps are:

  • Set up the db connection
  • Create the database
  • Use the database
  • Create the table

And finally, we execute the query string in line 46.

<html>
<body>

<form action='' method='post'>
    Movie: <input type='text' name='title'><br>
    Actor: <input type='text' name='actor'><br>
    Release Date yyyy-mm-dd: <input type='text' name='release_date'><br>
    <input type='submit'>
</form>

</body>
</html>
<html>
<body>

<?php
$title = $_POST['title'];
$actor = $_POST['actor'];
$release_date = $_POST['release_date'];

echo "Title: " . $title . "<br>";
echo "Actor: " . $actor . "<br>";
echo "Release Date: " . $release_date . "<br>";


$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), rel_date datetime)";
mysqli_query($mysqli, $sql_tb);

$release_date = DateTime::createFromFormat('Y-m-d', $release_date);


if ($release_date) {
    $release_date = $release_date->format('Y-m-d');
    $sql_in = "insert into movie (title, actor, rel_date) values ('$title', '$actor', '$release_date')";
    mysqli_query($mysqli, $sql_in);
}

mysqli_close($mysqli);

?>

</body>
</html>

Using what we have learnt about php form and db insert, the above script could be developed quite easily.

The form generated by the above script is as shown below:

mysql_form1

After running the above script, and typing in the necessary inputs we can check the database using Toad.

form_insert

PHP Form & MySQL: Getting the date from a form

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

Datetime is a very common data type in database.  There are various functions available in php and mysql for manipulating the datetime variables.

Here, we have a form for us to input the release date of a movie.

<html>
<body>

<form action='' method='post'>
    Movie: <input type='text' name='title'><br>
    Actor: <input type='text' name='actor'><br>
    Release Date yyyy-mm-dd: <input type='text' name='release_date'><br>
    <input type='submit'>
</form>

</body>
</html>
<html>
<body>

<?php
$title = @$_POST['title'];
$actor = @$_POST['actor'];
$release_date = @$_POST['release_date'];

echo "Title: " . $title . "<br>";
echo "Actor: " . $actor . "<br>";
echo "Release Date: " . $release_date . "<br>";


$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), rel_date datetime)";
mysqli_query($mysqli, $sql_tb);

$release_date = DateTime::createFromFormat('Y-m-d', $release_date);


if ($release_date) {
    $release_date = $release_date->format('Y-m-d');
    $sql_in = "insert into movie (title, actor, rel_date) values ('$title', '$actor', '$release_date')";
    mysqli_query($mysqli, $sql_in);
}

mysqli_close($mysqli);

?>

</body>
</html>

dateform

In this example, we have used the Y-m-d format, which is a frequently used format.  After getting the date from the form, we need to convert it to a format that we could insert into mysql.

We have used OOP style as below.  Note that even though we have asked the user to input the date in Y-m-d format, we still need the 2 statements below.

$release_date = DateTime::createFromFormat('Y-m-d', $release_date);
$release_date = $release_date->format('Y-m-d');

qform

There is this line:

$sql_use_db = "use allmovie";

Currently we only have one database.  We could have a few datasases and this line is to let us create the table movie under the database allmovie.