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

PHP MySQL: Display records using fetch_row()

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

We have used associative array method to display the table records.  If you preferred, we can also use non-associative array method using fetch_row().

Syntax: mixed mysqli_result::fetch_row (void)

I would recommend that you use the earlier fetch_assoc() as it offers a cleaner coding style.

<?php
$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)) {

    /* fetch object array */
    while ($row = $result->fetch_row()) {
        printf ("%s %s %s %s", $row[0], $row[1], $row[2], $row[3]);
        echo "<br>";
    }

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

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

We would usually combine fetch_row with a while loop to retrieve the records in DB.

If we ran the above code, we get the same result as shown in the screenshot below.

fetch_row

PHP MySQL: Select to display table using fetch_assoc

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

Let us revisit this 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),
('Rod Stewart', 'Out of Order', 1988);
Run the SQL
IDartistealbumyear
1Rod StewartMaggie May1973
2New OrderSubstance1987
3NirvanaSmell Like Teen Spirit1992
4Rod StewartOut of Order1988
<?php
$mysqli = new mysqli("localhost", "root", "", "allartist");

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

$query = "select id, artiste, album, year from artist";

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

    /* fetch associative array */
    while ($row = $result->fetch_assoc()) {
        printf ("%d %s %s %s", $row["id"], $row["artiste"], $row["album"], $row["year"] );
        echo "<br>";
    }

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

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

select3

fetch_assoc() returns an associative array that corresponds to the fetched row or null if there are no more rows.  Thus when there are no more rows, the while loop will not be ran.

The good thing about using fetch_assoc() is that the code is almost self explanatory.

We can use the column name, such as $row[“artiste”], in the array and print out each row in the table.