PHP Tutorials
- What is MySqli
- mysql query
- mysql query example
- InnoDB
- mysql column Type
- CRUD Example
- Connection Using Function
- mysql keys
- SELECT
- WHERE
- UPDATE
- Count no of Rows
- ALIAS
- AND, AND & OR
- BETWEEN
- COMPARISON OPERATOR
- DELETE
- DELETE LIMIT
- DISTINCT
- EXISTS
- FROM
- GROUP BY
- HAVING
- IN
- INTERSECT
- IS NULL & IS NOT NULL
- LIKE
- NOT
- ORDER BY
- SELECT LIMIT
- SUBQUERY
- TRUNCATE
- UNION && UNION ALL
- Concat & Group_Concat
- mysql Function
- Mysql Insert Id
- MySql Aggregate Function
- Mysql Join
- JOIN in MySql
- Trigger
- Procedure
- Transaction
- views
- Index
- SQL Injection
- Normalization
- Query Bind
- Interview Questions
Important Link
What is mysql?
MySql is a open source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL).
- MySQL is a database system that runs on a server.
- MySQL is developed, distributed, and supported by Oracle Corporation.
- MySQL database are stored in tables. A table is a collection of related data, and it consists of columns and rows.
Types MySQL table types or storage engines.
MySQL provides various storage engines for its tables as below:
- MyISAM Storage Engine
- InnoDB Storage Engine
- MERGE Storage Engine
- ARCHIVE Storage Engine
- CSV Storage Engine
- MEMORY (HEAP) Storage Engine
- FEDERATED Storage Engine
- BLACKHOLE Storage Engine
Each storage engine has its own advantages and disadvantages.
MyISAM
MyISAM extends the former ISAM storage engine. The size of MyISAM table can be up to 256TB, which is huge.The MyISAM tables are not transaction-safe.
Before MySQL version 5.5, MyISAM is the default storage engine when you create a table without specifying the storage engine explicitly. From version 5.5, MySQL uses InnoDB as the default storage engine
InnoDB
The InnoDB tables fully support ACID-compliant and transactions. They are also optimal for performance. InnoDB table supports foreign keys, commit, rollback, roll-forward operations. The size of an InnoDB table can be up to 64TB.
Like MyISAM, the InnoDB tables are portable between different platforms and operating systems. MySQL also checks and repairs InnoDB tables, if necessary, at startup.
MERGE
A MERGE table is a virtual table that combines multiple MyISAM tables that have a similar structure into one table. The MERGE storage engine is also known as the MRG_MyISAM engine. The MERGE table does not have its own indexes; it uses indexes of the component tables instead.
Using MERGE table, you can speed up performance when joining multiple tables. MySQL only allows you to perform SELECT, DELETE, UPDATE and INSERT operations on the MERGE tables. If you use DROP TABLE statement on a MERGE table, only MERGE specification is removed. The underlying tables will not be affected.
Memory
The memory tables are stored in memory and use hash indexes so that they are faster than MyISAM tables. The lifetime of the data of the memory tables depends on the uptime of the database server. The memory storage engine is formerly known as HEAP.
CSV
The CSV storage engine stores data in comma-separated values (CSV) file format. A CSV table brings a convenient way to migrate data into non-SQL applications such as spreadsheet software.
CSV table does not support NULL data type. In addition, the read operation requires a full table scan.
Database Queries :-
What is difference between InnoDB and MyISAM
PHP 5 and later can work with a MySQL database using:
Should I Use MySQLi or PDO? :-
MySQLi and PDO have their advantages:
Both MySQLi and PDO Syntax
Open a Connection to MySQL
First of all we need to create a connection to the server.
Example (MySQLi procedural):-
$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";
mysqli_close($conn);
?>
/* ********************** OR ***************** */
<?php
$host = "localhost";
$username ="root";
$password = "";
$db_name = "gmax";
$con = mysqli_connect($host,$username,$password)or die(mysqli_error());
mysqli_select_db($con,$db_name)or die(mysqli_error($con));
mysqli_close($conn);
?>
Create a db connection using define constant
Example :-
define ( 'DB_USER', 'gmax' );
define ( 'DB_PASSWORD', 'gmaxpass' );
define ( 'DB_NAME', 'gmaxdb' );
$conn = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD) or die('Could not connect to database server.');
mysqli_select_db($conn,DB_NAME) or die('Could not select database.');
...
/********************** OR *****************/
<?php
define("host", "localhost");
define("user", "root");
define("pass", "");
define("db_name", "iwebbies_apps");
$conn = mysqli_connect(host,user,pass,db_name) or die(mysqli_error());
mysqli_select_db($conn, db_name) or die(mysqli_error($conn));
if(!$conn)
{
echo "database error connection";
}
else {
echo "connection successfully created.";
}
?>
Open a Connection to MySQL
First of all we need to create a connection to the server.
Example (MySQLi Object-Oriented):-
$servername = "localhost";
$username = "username";
$password = "password";
// Create connection
$conn = new mysqli($servername, $username, $password);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>
Example (PDO):-
$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";
?>