Describe a table
In relational databases and flat file databases, a table is a set of data elements that use a model of vertical columns and horizontal rows. A cell is the unit where a row and a column meet. A table has a set number of columns, but the number of rows can be anything.
Using MySQLi and PDO to make a MySQL table
In this article, we have already learned how to use PHP to make databases in MySQL. Creating a table is done in the same way as making a database. The only difference is that instead of making a new database, we will connect to one that already exists and add a table to it. When we connect to MySQL, we can add an extra variable called \”database name\” to connect to an existing database.
In MySQL, you use the CREATE TABLE statement to make a table.
In this article, we\’ll make a table called \”Student\” that has four columns called \”id,\” \”firstname,\” \”lastname,\” and \”email.\”
These are the types of data that will be used:
VARCHAR: Holds a string of letters, numbers, and special characters that can be any length. In parentheses, the maximum size is written.
INT: The INTEGER data type can work with numbers that have a scale of zero. It can hold any whole number from -2147483648 to 2147483647.
In this article, attributes are used with data types. They are:
NOT NULL: Each row must have a value for that column; null values are not allowed.
PRIMARY KEY: A key that is used to identify each row in a table by itself. Most of the time, the ID number is in the column with the PRIMARY KEY setting.
The data type says what kind of information the column can store. Go to our Data Types reference for a full list of all the data types that can be used.
After the data type, you can choose other attributes for each column, which are not required:
NOT NULL: Each row must have a value for that column; null values are not allowed.
DEFAULT value: Set a default value that is used when no other value is passed.
UNSIGNED: This is used for number types and stores only positive numbers and 0.
AUTO INCREMENT: When a new record is added, MySQL automatically adds 1 to the value of the field.
PRIMARY KEY: A key that is used to identify each row in a table by itself. AUTO INCREMENT is often used with the PRIMARY KEY setting, which is usually an ID number.
Each table should have a column for the primary key (in this case: the \”id\” column). Each record in the table must have a different value for this field.
Three different ways to make tables are explained below:
Creating a table using MySQLi Object-oriented Procedure
Syntax :
<?php
$servername = \”localhost\”;
$username = \”username\”;
$password = \”password\”;
$dbname = \”school\”;// checking connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die(\”Connection failed: \” . $conn->connect_error);
}// sql code to create table
$sql = \”CREATE TABLE student(
id INT(3) PRIMARY KEY,
firstname VARCHAR(40) NOT NULL,
lastname VARCHAR(40) NOT NULL,
email VARCHAR(70)
)\”;if ($conn->query($sql) === TRUE) {
echo \”Table student created successfully\”;
} else {
echo \”Error creating table: \” . $conn->error;
}$conn->close();
?>
Creating a table using MySQLi Procedural procedure
Syntax :
<?php
$servername = \”localhost\”;
$username = \”username\”;
$password = \”password\”;
$dbname = \”school\”;// Checking connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die(\”Connection failed: \” . mysqli_connect_error());
}// sql code to create table
$sql = \”CREATE TABLE student (
id INT(3) PRIMARY KEY,
firstname VARCHAR(40) NOT NULL,
lastname VARCHAR(40) NOT NULL,
email VARCHAR(70)
)\”;if (mysqli_query($conn, $sql)) {
echo \”Table student created successfully\”;
} else {
echo \”Error creating table: \” . mysqli_error($conn);
}
mysqli_close($conn);
?>
Creating table using PDO procedure
Syntax :
<?php
$servername = \”localhost\”;
$username = \”username\”;
$password = \”password\”;
$dbname = \”school\”;try {
$conn = new PDO(\”mysql:host=$servername;dbname=$dbname\”,
$username, $password);// setting the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);// sql code to create table
$sql = \”CREATE TABLE student (
id INT(3) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(40) NOT NULL,
lastname VARCHAR(40) NOT NULL,
email VARCHAR(70)
)\”;// using exec() because no results are returned
$conn->exec($sql);
echo \”Table of student created successfully\”;
}
catch(PDOException $e)
{
echo $sql . \”
\” . $e->getMessage();
}$conn = null;
?>