MySQLi – Select Data

The SELECT command allows us to select information from our database and do the following:

– send the data back to the requesting web page.
– display the data directly from the PHP script (we will look at this option).

1
2
3
4
5
6
7
8
9
10
11
12
13
 
$sql = "SELECT * FROM Messages";
$result = $conn->query($sql);
 
if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "Title: " . $row["title"]. " - Message text: " . $row["messagetext"]. " " . $row["email"]. "<br>";
        // we can access each field through the $row variable.
    }
} else {
    echo "0 results";
}

The above code selects all data from the ‘Messages’ table. However, we can set criteria on the data we want to select.

When using the echo function, you can use any HTML to style your output.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
$sql = "SELECT title, messagtext FROM Messages WHERE email = 'smith@smith.com'";
$result = $conn->query($sql);
 
if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "Title: " . $row["title"]. " - Message Text: " . $row["messagetext"]. "<br>";
        // we can access each field through the $row variable.
        // the '.' allows us to concatenate the fields.
    }
} else {
    echo "0 results";
}

MySQLi – Delete data

As the title suggests, we will learn how to delete a single or multiple rows (records) from a table within our database.

I will no longer be including the connection code in the examples. You should realise that you must connect to the database in every new PHP script.

1
2
3
4
5
6
7
8
9
 
$sql = "DELETE FROM Messages 
WHERE email = 'smith@smith.com'";
 
if ($conn->query($sql) === TRUE) {
    echo "Message has been deleted";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

The above script will delete the message stored in the table that has the email ‘smith@smith.com’ associated with it. Please note that if there is more than one record associated with the email address ‘smith@smith.com’ then they will all be deleted. Best using a unique identifier.

You can delete everything from the table by using the following code:

1
2
3
4
5
6
7
8
 
$sql = "DELETE * FROM Messages";
 
if ($conn->query($sql) === TRUE) {
    echo "All messages have been deleted";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

The ‘*’ denotes everything and can also be used to SELECT everything from the table.

MySQLi – Create Table and Insert Data

Now that we have created a database, we will attempt to create a table then populate it with data.

Below is the php code that will create a table. There are four fields, one of them being ID. Every time a record is added to the table, ID will incremented by one automatically to provide each record with a unique id.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
 
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
 
$sql = "CREATE TABLE Messages (
ID int NOT NULL AUTO_INCREMENT,
title VARCHAR(50),
messagetext VARCHAR(10),
email VARCHAR(100),
PRIMARY KEY (ID))";
 
 
if ($conn->query($sql) === TRUE) {
    echo "Table has been created";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}
 
$conn->close();
?>

The php and mysqli code below allows us to insert data into our ‘Messages’ table. The table will have been created using phpMyAdmin. If no table exists an error message will be displayed.

Insert data into a database

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
 
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
 
$sql = "INSERT INTO Messages (title, messagetext, email)
VALUES ('Intro Message', 'A message', 'smith@smith.com')";
 
if ($conn->query($sql) === TRUE) {
    echo "A new message has been posted";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}
 
$conn->close();
?>

Your task is to insert 5 new records into the ‘Messages’ table. Please refer to the following website to see how insert multiple records at the same time: Multiple Inserts

MySQLi – Connecting to a Database

In order to manipulate the contents of your database dynamically we will use PHP and SQL.

PHP – is a widely-used open source general-purpose scripting language that is especially suited for web development and can be embedded into HTML.

SQL – is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems.

Connecting to a Database – Your teacher will provide you with localhost, username and password

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
 
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
 
$conn->close();
?>

The above piece of code allows us to connect to the named database. You must always remember to close the connection to database at the end of every script.

We will now try and create a table then insert data into the database using the INSERT INTO statement.