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