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.

<?php

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

$sql = "INSERT INTO artist
(artiste, album, year)
VALUES
('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.

mysql_insert

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

select * from artist

inserted data

PHP MySQL: Delete

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

Before issuing delete command using PHP, let’s check that you have the proper environment and that there are records in the table artist using Toad.

inserted data

The query statement we use to delete is

delete from artist where id = 1

We pass the above query to a variable $sql using

$sql = "delete from artist where id = 1"

The full PHP script is as below.  As with the earlier insert example, we pass the connection string and the above delete string to mysqli_query.

<?php

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

$sql = "delete from artist where id = 1";

if (mysqli_query($mysqli, $sql)) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting data from table with error no: " . $mysqli->errno;
}

?>

If deletion is successful, we will see the browser display as below.

mysql_delete

We can also verify that the record is successfully deleted using Toad.

toad_delete

PHP MySQL: Update, non OOP style

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

We have used mostly OOP way of making connection to the DB and executing the query string.  Since we are using PHP 5, it is recommended to use OOP method.

By now, you should be able to see how easy it is to execute a query string using PHP.  For this mysql update, we will set up the connection using non OOP way.

Instead of using new mysqli we use mysqli_connect().

<?php
$link = mysqli_connect("localhost", "root", "", "allartist");

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

$sql = "update artist set year = 1990 where id = 2";

if (mysqli_query($link, $sql) === TRUE) {
    echo "Record updated successfully<br>";
} else {
    echo "Error updating record into table with error no: " . $mysqli->errno;
}
mysqli_close($link);

?>

After updating, you can use Toad to check if the record has been updated correctly.

toad_update

PHP MySQL: Select count

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

When it comes to querying a database, we always like to use select count to get the number of records in the table.

select count(*) from artist

However, let’s look at the code below.

To get the select count, we first pass the query string into $mysqli->query().

<?php
$mysqli = new mysqli("localhost", "root", "", "allartist");

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

if ($result = $mysqli->query("SELECT * FROM artist")) {

    /* determine number of rows result set */
    $row_cnt = $result->num_rows;

    printf("Result set has %d rows.", $row_cnt);

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

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

If connection to the DB is successful, we should get the following screenshot displayed on the browser.

select count

We are using

select * from artist

and yet we are able to get the number of records in the table.  This is because we are using OOP way of programming.

$result = $mysqli->query("SELECT * FROM artist")

In OOP, a variable is an object.  After executing the above statement, the relevant methods and properties associated with the object could be used.

In this case we have used

$row_cnt = $result->num_rows;

to retrieve the number of records in the table artist.

PHP MySQL: Select Count Using fetch_assoc()

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

If we are to use

select count(*) from artist

in the query statement, we can use the fetch_assoc() method provided by PHP.

The syntax is:

array mysqli_result::fetch_assoc ( void )

select count(*) from artist

will only return a row and a column.  For associative array, we would need to have a column name.

We can assign a column name cnt as

select count(*) as cnt from artist

The PHP code would be as below:

<?php
$mysqli = new mysqli("localhost", "root", "", "allartist");

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

$query = "select count(*) as cnt from artist ";

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

    /* fetch associative array */

    $row = $result->fetch_assoc();
    printf("Number of records:  %s", $row["cnt"]);


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

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

Here the screenshot after running the script above:

select2