What is MySQL?
MySQL is a database system used on the web
MySQL is a database system that runs on a server
MySQL is ideal for both small and large applications
MySQL is very fast, reliable, and easy to use
MySQL uses standard SQL
MySQL compiles on a number of platforms
MySQL is free to download and use
MySQL is developed, distributed, and supported by Oracle Corporation
PHP Connect to MySQL
<?php
$servername = "localhost";
$username = "username";
$password = "password";
// Create connection
$conn = mysqli_connect($servername, $username, $password);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>
Close the Connection
The connection will be closed automatically when the script ends. To close the connection
before, use the following
mysqli_close($conn);
Create a MySQL Database Using MySQLi and PHP
The CREATE DATABASE statement is used to create a database in MySQL.
<?php
$servername = "localhost";
$username = "username";
$password = "password";
// Create connection
$conn = mysqli_connect($servername, $username, $password);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// Create database
$sql = "CREATE DATABASE myDB";
if (mysqli_query($conn, $sql)) {
echo "Database created successfully";
} else {
echo "Error creating database: " . mysqli_error($conn);
}
mysqli_close($conn);
?>
PHP MySQL Create Table
A database table has its own unique name and consists of columns and rows.
Create a MySQL Table Using MySQLi and PDO
The CREATE TABLE statement is used to create a table in MySQL.
We will create a table named "MyGuests", with five columns: "id", "firstname", "lastname",
"email" and "reg_date":
CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP
)
Example (MySQLi Procedural)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// sql to create table
$sql = "CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP
)";
if (mysqli_query($conn, $sql)) {
echo "Table MyGuests created successfully";
} else {
echo "Error creating table: " . mysqli_error($conn);
}
mysqli_close($conn);
?>
PHP MySQL Insert Data
fter a database and a table have been created, we can start adding data in them.
Here are some syntax rules to follow:
The SQL query must be quoted in PHP
String values inside the SQL query must be quoted
Numeric values must not be quoted
The word NULL must not be quoted
The INSERT INTO statement is used to add new records to a MySQL table:
INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)
Example
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";
if (mysqli_query($conn, $sql)) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
mysqli_close($conn);
?>
Get ID of The Last Inserted Record
If we perform an INSERT or UPDATE on a table with an AUTO_INCREMENT field, we can
get the ID of the last inserted/updated record immediately.
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";
if (mysqli_query($conn, $sql)) {
$last_id = mysqli_insert_id($conn);
echo "New record created successfully. Last inserted ID is: " . $last_id;
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
mysqli_close($conn);
?>
Select Data From a MySQL Database
The SELECT statement is used to select data from one or more tables:
SELECT column_name(s) FROM table_name
or we can use the * character to select ALL columns from a table:
SELECT * FROM table_name
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
// output data of each row
while($row = mysqli_fetch_assoc($result)) {
echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
}
} else {
echo "0 results";
}
mysqli_close($conn);
?>
Select and Filter Data From a MySQL Database
The WHERE clause is used to filter records.
The WHERE clause is used to extract only those records that fulfill a specified condition.
SELECT column_name(s) FROM table_name WHERE column_name operator value
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$sql = "SELECT id, firstname, lastname FROM MyGuests WHERE lastname='Doe'";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
// output data of each row
while($row = mysqli_fetch_assoc($result)) {
echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
}
} else {
echo "0 results";
}
mysqli_close($conn); use loop
?>
Select and Order Data From a MySQL Database
The ORDER BY clause is used to sort the result-set in ascending or descending order.
The ORDER BY clause sorts the records in ascending order by default. To sort the records in
descending order, use the DESC keyword.
SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$sql = "SELECT id, firstname, lastname FROM MyGuests ORDER BY lastname";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
// output data of each row
while($row = mysqli_fetch_assoc($result)) {
echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
}
} else {
echo "0 results";
}
mysqli_close($conn);
?>
Delete Data From a MySQL Table Using MySQLi
SIMPLE
The DELETE statement is used to delete records from a table:
DELETE FROM table_name
WHERE some_column = some_value
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// sql to delete a record
$sql = "DELETE FROM MyGuests WHERE id=3";
if (mysqli_query($conn, $sql)) {
echo "Record deleted successfully";
} else {
echo "Error deleting record: " . mysqli_error($conn);
}
mysqli_close($conn);
?>
Update Data In a MySQL Table Using MySQLi
The UPDATE statement is used to update existing records in a table:
UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
Simple
die("Connection failed: " . mysqli_connect_error());
}
$sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";
if (mysqli_query($conn, $sql)) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . mysqli_error($conn);
}
mysqli_close($conn);
?>
Limit Data Selections From a MySQL Database
MySQL provides a LIMIT clause that is used to specify the number of records to return.
The LIMIT clause makes it easy to code multi page results or pagination with SQL, and is very
useful on large tables. Returning a large number of records can impact on performance.
Assume we wish to select all records from 1 - 30 (inclusive) from a table called "Orders". The
SQL query would then look like this:
$sql = "SELECT * FROM Orders LIMIT 30";
PHP MySQLi Functions
Function Description
affected_rows() Returns the number of affected rows in the previous MySQL operation
autocommit() Turns on or off auto-committing database modifications
begin_transaction() Starts a transaction
change_user() Changes the user of the specified database connection
character_set_name() Returns the default character set for the database connection
close() Closes a previously opened database connection
commit() Commits the current transaction
connect() Opens a new connection to the MySQL server
connect_errno() Returns the error code from the last connection error
connect_error() Returns the error description from the last connection error
data_seek() Adjusts the result pointer to an arbitrary row in the result-set
debug() Performs debugging operations
dump_debug_info() Dumps debugging info into the log
errno() Returns the last error code for the most recent function call
error() Returns the last error description for the most recent function call
error_list() Returns a list of errors for the most recent function call
fetch_all() Fetches all result rows as an associative array, a numeric array, or both
fetch_array() Fetches a result row as an associative, a numeric array, or both
fetch_assoc() Fetches a result row as an associative array
fetch_field() Returns the next field in the result-set, as an object
fetch_field_direct() Returns meta-data for a single field in the result-set, as an object
fetch_fields() Returns an array of objects that represent the fields in a result-set
fetch_lengths() Returns the lengths of the columns of the current row in the result-set
fetch_object() Returns the current row of a result-set, as an object
fetch_row() Fetches one row from a result-set and returns it as an enumerated array
field_count() Returns the number of columns for the most recent query
field_seek() Sets the field cursor to the given field offset
get_charset() Returns a character set object
get_client_info() Returns the MySQL client library version
get_client_stats() Returns statistics about client per-process
get_client_version() Returns the MySQL client library version as an integer
get_connection_stats() Returns statistics about the client connection
get_host_info() Returns the MySQL server hostname and the connection type
get_proto_info() Returns the MySQL protocol version
get_server_info() Returns the MySQL server version
get_server_version() Returns the MySQL server version as an integer
info() Returns information about the last executed query
init() Initializes MySQLi and returns a resource for use with real_connect()
insert_id() Returns the auto-generated id from the last query
kill() Asks the server to kill a MySQL thread
more_results() Checks if there are more results from a multi query
multi_query() Performs one or more queries on the database
next_result() Prepares the next result-set from multi_query()
options() Sets extra connect options and affect behavior for a connection
ping() Pings a server connection, or tries to reconnect if the connection has gone
down
poll() Polls connections
prepare() Prepares an SQL statement for execution
query() Performs a query against a database
real_connect() Opens a new connection to the MySQL server
real_escape_string() Escapes special characters in a string for use in an SQL statement
real_query() Executes a single SQL query
reap_async_query() Returns result from an async SQL query
refresh() Refreshes/flushes tables or caches, or resets the replication server information
rollback() Rolls back the current transaction for the database
select_db() Select the default database for database queries
set_charset() Sets the default client character set
set_local_infile_default() Unsets user defined handler for load local infile command
set_local_infile_handler() Set callback function for LOAD DATA LOCAL INFILE command
sqlstate() Returns the SQLSTATE error code for the error
ssl_set() Used to establish secure connections using SSL
stat() Returns the current system status
stmt_init() Initializes a statement and returns an object for use with stmt_prepare()
store_result() Transfers a result-set from the last query
thread_id() Returns the thread ID for the current connection
thread_safe() Returns whether the client library is compiled as thread-safe
use_result() Initiates the retrieval of a result-set from the last query executed
warning_count() Returns the number of warnings from the last query in the connection
