MySQL in PHP
MySQL, Setting Up, Connecting, Viewing, Inserting
MySQL
What is MySQL?
• MySQL is one of the most popular relational
database system being used today.
• MySQL database stores data into tables like other
relational database. A table is a collection of related
data, and it is divided into rows and columns.
What is MySQL?
• Each row in a table represents a data record that are
inherently connected to each other such as
information related to a particular person, whereas
each column represents a specific field such as id,
first_name, last_name, email, etc.
What is MySQL?
• SQL, the Structured Query Language, is a simple,
standardized language for communicating with relational
databases like MySQL.
• With SQL you can perform any database-related task, such
as creating databases and tables, saving, deleting, updating
data, and querying a database for specific records.
Setting Up
Setting Up MySQL in PHP
• Before connecting to a database server through PHP,
you must have at least a web server and the MySQL
database server itself for your database, and the
database itself.
• Applications like XAMPP and WAMP offers these
services once installed on your machine.
Setting Up MySQL in PHP
• You can create database and tables and view, insert,
delete or update data directly to the database
provided by XAMPP/WAMP through phpMyAdmin.
• This can be accessed through your browser and typing
http://localhost/phpmyadmin/ on the address bar.
Connecting/Closing Connection
Connecting to MySQL
• In order to store or access the data inside a MySQL
database, you first need to connect to the MySQL
database server.
• PHP offers two different ways to connect to MySQL
server: MySQLi (Improved MySQL) and PDO (PHP
Data Objects) extensions.
Connecting to MySQL
• In PHP you can easily do this using the
mysqli_connect() function. All communication
between PHP and the MySQL database server takes
place through this connection.
Connecting to MySQL
• The default username for MySQL database server is
root and there is no password.
• However to prevent your databases from intrusion
and unauthorized access you should set password for
MySQL accounts.
Closing MySQL Connection
• The connection to the
MySQL database server will
be closed automatically as
soon as the execution of the
script ends.
Closing MySQL Connection
• However, if you want to
close it earlier you can do
this by simply calling the PHP
mysqli_close() function.
Showing Database Data
The include statement
• Before we move to inserting, deleting and updating
data, we must first discuss the include() statement.
This allow you to include the code contained in a PHP
file within another PHP file.
The include statement
• You can save a lot of time and work through including files —
Just store a block of code in a separate file and include it
wherever you want using the include() statements instead of
typing the entire block of code multiple times.
• A typical example is including the header, footer, menu or
mysql connector file within all the pages of a website.
The include statement
• Instead of copy/pasting these codes to each individual pages:
mysqli_connect.php
The include statement
• …just include the file to save time in managing changes
and errors!
• Common practice is to save these ‘include’ files to a folder
separate to your main pages (ex. includes folder)
Selecting Database Data
• To retrieve data from database tables, we use the SQL
SELECT statement to select the records from database
tables. Its basic syntax is as follows:
Selecting Database Data
• Example of
showing
database data
using the SELECT
statement in SQL
through PHP:
Inserting Database Data
Inserting Database Data
• The INSERT INTO statement is used to insert new rows
in a database table.
• Let's make a SQL query using the INSERT INTO
statement with appropriate values, after that we will
execute this insert query through passing it to the PHP
mysqli_query() function to insert data in table.
Selecting Database Data
• Here's an example,
which insert a new row
to the tbl_products table
by specifying values for
the productID,
productName and stocks
fields.
