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.

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: 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 MySQL: using data_seek to fetch row

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

The data_seek function in PHP allow us to fetch a particular row.

Syntax:

bool mysqli_result::data_seek (int $offset)

Data_seek adjusts the result pointer to an arbitrary row specified by offset in the result.

<?php
/* Open a connection */
$mysqli = new mysqli("localhost", "root", "", "allartist");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s", mysqli_connect_error());
    exit();
}

$query = "select * from artist";
if ($result = $mysqli->query( $query)) {

    /* seek to row no. 4 */
    $result->data_seek(3);

    /* fetch row */
    $row = $result->fetch_row();

    printf ("Artiste: %s Album: %s Year: %s", $row[1], $row[2], $row[3]);

    /* free result set*/
    $result->close();
}

/* close connection */
$mysqli->close();
?>

The offset here is 3 and as it starts from index 0, it points to row 4 in the table.

This function can only be used with buffered results attained from the use of the mysqli_store_result() or mysqli_query() functions.

Here is the output after running the script above.

data_seek

PHP MySQL: Select a single record using fetch_assoc

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

A single record in the table can be retrieved by using fetch_assoc().

We have this table as below and let’s say we ask ourselves when was Nirvana’s Smell Like Teen Spirit released.

IDartistealbumyear
1Rod StewartMaggie May1973
2New OrderSubstance1987
3NirvanaSmell Like Teen Spirit1992
4Rod StewartOut of Order1988
Run the SQL

We first have to make some adjustments to the query statement:

select year from artist where artiste='Nirvana' and album='Smell Like Teen Spirit'
Run the SQL
<?php
$mysqli = new mysqli("localhost", "root", "", "allartist");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s", mysqli_connect_error());
    exit();
}

$query = "select year as yr from artist where artiste='Nirvana' and album='Smell Like Teen Spirit'";

if ($result = $mysqli->query($query)) {

    /* fetch associative array */
    while ($row = $result->fetch_assoc()) {
        printf ("Smell Like Teen Spirit was released in: %d", $row["yr"]);
        echo "<br>";
    }

    /* free result set */
    $result->free();
}

/* close connection */
$mysqli->close();
?>

We have used associative array method fetch_assoc().

If the above script is ran, the output in the browser is as below:

select4