This article explains how you can access and manipulate MySQL database using MySQLi Object-oriented interface in PHP.
This article covers the following MySQL statements with code examples. The code examples are pasted in the textarea so you can easily copy then edit as per your requirements.
- CREATE TABLE
- RENAME TABLE
- INSERT INTO
- SELECT
We will use PHP and Mysqli object oriented extension in this tutorial to perform different types of tasks to retrieve , create and
update records on a MySQL database.
Note :-
The codes used here are just for educational purposes and to teach you how to implement a db connection for read and right access. You can copy and paste these codes on your server for testing and learning.
Setting up a secure connection to MySQL database
To connect to MySQL database with our code examples, you must have the following things ready :
- Your MySQL host name
- Your MySQL username
- Your MySQL database password
- Database name
You will get these informations on your hosting Cpanel. Create a new database and copy the db informations from there.
For testing and learning purposes, create a new directory in your document root and name it as db_project .
Remember that the db_project directory is for learning purposes only and it must be reachable by visiting yoursite.com/db_project/foobar .
Now inside that directory , create a new file named
connection.php with the contents shown below.
connection.php
<?php
//Setting up a secure connection to database
$db_host="localhost";
$db_user="foobar";
$db_pass="123456";
$db_name="my_db";
$conn = new mysqli($db_host,$db_user,$db_pass,$db_name);
// Check the connection
if ($conn -> connect_errno) {
echo "Could not connect to MySQL: " . $mysqli -> connect_error;
exit();
}
else {echo "Connected to MySQL database!";}
?>
The connection.php sets up a secure connection with your MySQL database so that you can access the database . You can use this single file to execute multiple Mysqli queries on different pages by including this file.
Add your server informations in this file and test it on your server. If everything is ok you will get a text output “Connected” on your page.
Okay cool, now that you have successfully established the connection with your MySQL server , it’s time to create a table. See the next step below to create a table .
Creating a table on MySQL database
A table on MySQL database can hold multiple columns and rows. In this article , we are going to create a table named MyUsers with 5 unique columns :
- ID
- username
- password
- email
- reg_date
We will use this table to store user informations.
Create a new file named create_table.php in your db_project directory with the following contents :
create_table.php
<?php
//include the connection file
include "connection.php";
// sql code to create table
//Creating table MyGursts with 5 fields
//----------------------
// ID
//Username
//Password
//Email
//reg_date
$sql = "CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(30) NOT NULL,
password VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";
//if the query is successful
if ($conn->query($sql) === TRUE) {
echo "Table MyGuests created successfully";
}
//If something goes wrong
else {
echo "Error creating table: " . $conn->error;
}
$conn->close();
?>
The code above creates 5 column in the “MyUsers” table.
The ID column is AUTO_INCREMENT which means that the rows of this column will get incremented by +1 each time a new record is added.
username , password and email has varchar data types with the maximum allowed text and finally the reg_date column is a current time which means the row value of this column will get updated automatically with server date time.
Edit this file if you need to change anything or add more columns and run it on your server . This will create a new table in your MySQL database.
Renaming MySQL table
If you created a new table on MySQL and you want
to change the table name , you can use MySQL RENAME
statement to change the table name.
The code below changes table name from MyUsers to MyNewUsers .
You will only need to use this code if you are not happy with the existing table name and you
want to change it something else.
For learning and testing purpose , create a new file named rename_table.php and put the following contents in that file :
<?php
//include the connection file
include "connection.php";
// sql code to rename table
$sql = "RENAME TABLE MyUsers TO MyNewUsers";
//if the query is successful
if ($conn->query($sql) === TRUE) {
echo "Table MyUsers renamed successfully";
}
//If something goes wrong
else {
echo "Error renaming table: " . $conn->error;
}
$conn->close();
?>
The syntex to change or rename database table is simple and easier to understand.
RENAME TABLE OldName TO NewName
Where OldName is name of your existing table and NewName is the name you want to replace your old table name with.
Adding records to MySQL database with PHP
To add a record to MYSQL db we use the INSERT INTO statement. With this statement we can add a record (row) to db.
add-record.php
<?php
// Include the connection file
include "connection.php";
//Add records to database now
//Adding new records to 3 columns
$user_name="Neha";
$pass="123456";
$email="[email protected]";
$sql = "INSERT INTO MyUsers (username, password, email)
VALUES ('$user_name', '$pass', '$email')";
//If the query is successful
if ($conn->query($sql) === TRUE) {
echo "New record added successfully";
//If something goes wrong
} else {
echo "Error: " . $sql . "
" . $conn->error;
}
$conn->close();
?>
Select data from MySQL database
To select data from the “MyUsers” table we will use the following code.
Create a new file named select.php in your MySQL test directory and put the following file contents in that file.
select.php
<?php
// Include the db connection file
include "connection.php";
//Select specific columns from MyUsers table
$sql = "SELECT id, username, password, email, reg_date FROM MyUsers";
$result = $conn->query($sql);
// If result rows are more then 0
if ($result->num_rows > 0) {
// output data of each row in html table
echo "<table>";
echo "<tr>";
echo "<th>ID</th><th>Username</th>
<th>Password</th>
<th>Email</th>
<th>Registered on</th>
</tr>";
while($row = $result->fetch_assoc()) {
echo "<tr>";
echo '<td>'.$row["id"];
echo '<td>'.$row["username"];
echo '<td>'.$row["password"];
echo '<td>'.$row["email"];
echo '<td>'.$row["reg_date"];
echo "</tr>";
}
echo "";
}
// Else print an error
else {
echo "No results found";
}
$conn->close();
?>
The code above will select all the specific columns from the database.
We have requested to select all the columns from the table. If you just need to get a single column ,ie : username you can then modify the select statement like so :
$sql = "SELECT username FROM MyUsers";
This will now return all the rows for a specific column.
Display database records on a webpage
There are many ways to display MySQL records on a webpage. You can either show the data in tabular format using html table or use any block level elements to show the record. The following is a basic example of showing MySQL records on an HTML webpage. It shows the retrieved data in HTML table element.
<!DOCTYPE html>
<html>
<head>
<title>My db records</title>
</head>
<body>
<!--php code that retrieves records from database-->
<?php
// Include the db connection file
include "connection.php";
//Select specific columns from MyUsers table
$sql = "SELECT id, username, password, email, reg_date FROM MyUsers";
$result = $conn->query($sql);
// If result rows are more then 0
if ($result->num_rows > 0) {
// output data of each row in html table
echo "<table>";
echo "<tr>";
echo "<th>ID</th><th>Username</th>
<th>Password</th>
<th>Email</th>
<th>Registered on</th>
</tr>";
while($row = $result->fetch_assoc()) {
echo "<tr>";
echo '<td>'.$row["id"];
echo '<td>'.$row["username"];
echo '<td>'.$row["password"];
echo '<td>'.$row["email"];
echo '<td>'.$row["reg_date"];
echo "</tr>";
}
echo "";
}
// Else print an error
else {
echo "No results found";
}
$conn->close();
?>
</body>
</html>
English
Afrikaans
Albanian
Amharic
Arabic
Armenian
Azerbaijani
Basque
Belarusian
Bengali
Bosnian
Bulgarian
Catalan
Cebuano
Chichewa
Chinese (Simplified)
Chinese (Traditional)
Corsican
Croatian
Czech
Danish
Dutch
Esperanto
Estonian
Filipino
Finnish
French
Frisian
Galician
Georgian
German
Greek
Gujarati
Haitian Creole
Hausa
Hawaiian
Hebrew
Hindi
Hmong
Hungarian
Icelandic
Igbo
Indonesian
Irish
Italian
Japanese
Javanese
Kannada
Kazakh
Khmer
Korean
Kurdish (Kurmanji)
Kyrgyz
Lao
Latin
Latvian
Lithuanian
Luxembourgish
Macedonian
Malagasy
Malay
Malayalam
Maltese
Maori
Marathi
Mongolian
Myanmar (Burmese)
Nepali
Norwegian
Pashto
Persian
Polish
Portuguese
Punjabi
Romanian
Russian
Samoan
Scottish Gaelic
Serbian
Sesotho
Shona
Sindhi
Sinhala
Slovak
Slovenian
Somali
Spanish
Sudanese
Swahili
Swedish
Tajik
Tamil
Telugu
Thai
Turkish
Ukrainian
Urdu
Uzbek
Vietnamese
Welsh
Xhosa
Yiddish
Yoruba
Zulu