php mysql


MySQL is one of the most popular database system used with PHP. The data in a MySQL database are stored in tables. A table is a collection of related data and it consists of columns and rows. Databases are useful for storing information categorically.

Database Queries

Structured Query Language (SQL) is a standard language that allows us to access and manipulate databases. SQL can execute queries, retrieve data, insert records, update records, delete records, create new databases, create new tables, etc.

Creating Database

You can manually create a new database in your db management tool with the file menu options available. Create database tables according to your need. Here, we're gonna discuss about how to manually insert, display, update and delete data from such database tables first.

Click on the new option on the left side panel or database menu on the top to get this window.

Give a tablename and collation to your database and click on create. Now you can click on the dbname that appears on the left panel and get inside the db. Create data tables according to your need. Rest will be taken care below.

While creating a data table you need to keep in mind a few things, that is listed below.

You'll need an id for each contents in a table and that id needs to be auto incremented with each data inserted. For this, you need to define an id for the table then the type should be selected as integer (INT) and the auto increment option A_I should be checked. This makes the table id auto increment along with the number of data inserted into that table. The id will be helpful while sorting out the latest data from that table and display in the frontend. You should define the length of the data too in characters in the table and any data that exceeds that value will be auto rejected. For id it's enough if you give it a length of 10 or 11. The standard length value is 11 though.

Not just for the id, you need to define the character type for each data column while creating a table. Generally, all data will be defined as varchar in types if the data length is not much long. To insert huge images or huge texts you can use long text with length of 99999999 as the maximum length value for varchar would be 255 only.

Database Tables

A database contains one or more tables that contain data records organized in rows. Each table is identified by a unique name. Here is an example of a database table named 'books'.

bookid name price
1 Harry Potter and the Cursed Child, Parts 1 & 2 J.K. Rowling $320
2 When Breath Becomes Air Paul Kalanithi $170
3 The Whistler John Grisham $175

The table above contains 3 record rows (one for each book) and 4 columns namely bookid, name, author and price.

SQL Statements

Most of the operations on a database are done via SQL statements. There are a number of specific statements to execute specific tasks in SQL. Some of the most important statements are listed below.

SELECT - extracts data from a database

UPDATE - updates data in a database

DELETE - deletes data from a database

INSERT - inserts new data into a database

CREATE - creates a new database or a new table or an index (search key)

ALTER - modifies a database or a table

DROP - deletes a database or a table or an index

SELECT

SELECT statement is used to select data from a database. Any data returned is stored in a table called result-set. SELECT is the most used statement and can be used along with multiple clauses.

SELECT ALL DATA

SELECT * FROM table_name;	

Selects all the data stored in the defined table.

SELECT * FROM BOOKS	

Selects all records from books table.

SELECT DEFINED DATA

SELECT columnName1,columnName2 FROM table_name;	

Selects all data from defined columns of the table.

Example

SELECT name,author from books;	

Selects all values from name and author column present in books table.

WHERE

Where clause is used to filter records. It only selects those data that meet the stated condition.

SELECT column1, column2 FROM table_name WHERE condition;	

Example

SELECT name FROM books WHERE author = 'John Grisham'; 
SELECT * FROM books WHERE bookid = 1;	

First statement selects only those name values from books table where author is John Grisham while the second one selects all data from the row where bookid is 1.

Operators that can be used with WHERE clause are listed below with their description.

Operator Description
= Equal
<> Not equal. Note: In some versions of SQL this operator may be written as !=
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN To specify multiple possible values for a column

AND, OR, NOT

AND / OR operators can be combined with WHERE caluse to filter records based on multiple conditions. AND is used to display records when all stated conditions are true while OR is used to display records when one of the stated conditions are true. Not operator displays all records when stated condition is false. We can combine these operators in a single statement as well.

AND

SELECT * FROM table_name WHERE condition1 AND condition2;

Example

SELECT * FROM books WHERE author = 'John Grisham' AND price = '$175';		

Selects all records from books table where author is John Grisham and price is $175.

OR

SELECT * FROM table_name WHERE condition1 OR condition2;

Example

SELECT * FROM books WHERE author = 'John Grisham' OR price = '$175';		

Selects all records from books table where author is John Grisham or price is $175.

NOT

SELECT * FROM table_name WHERE NOT condition;

Example

SELECT * FROM books WHERE NOT author = 'John Grisham';		

Selects all records from books table where author isn't John Grisham.

ORDER BY

ORDER BY is used to sort the result-set in ascending or descending order. Ascending order is default. We need to use DESC to sort the records in descending order.

SELECT * FROM table_name ORDER BY columnName ASC/DESC;

Example

SELECT * FROM books ORDER BY 'price' DESC;		

Selects all records from books table and displays them in descending order by price.

INSERT

INSERT is used to insert new records in a table.

INSERT INTO tblName(columnName,columnName,columnName) VALUES ('columnValue','columnValue','columnValue');	

Example

INSERT INTO books (name,author,price) VALUES ('Calumet K','Merwit Webster','$230');

Inserts corresponding records in books table.

UPDATE

Update is used to modify the existing record-set.

UPDATE table_name SET columnName1 = columnValue, columnName2 = columnValue2 
WHERE condition;	

Example

UPDATE books SET name = 'The Road Builders', price = '$130' 
WHERE bookid = 4;	

Replaces existing name and price with the new one where bookid is 4.

DELETE

DELETE is used to delete existing records from a table.

DELETE FROM table_name WHERE condition;	

Example

DELETE FROM books WHERE id = 1;	

Deletes the record of row with bookid 1 from books table.

DELETE ALL RECORDS FROM A TABLE

DELETE * FROM table_name;	

LIMIT

LIMIT allows us to limit the number of records being returned.

SELECT * FROM table_name LIMIT 5;	

Example

SELECT * FROM books LIMIT 3;		

Selects and returns only the first 3 records from books table.

LIKE

LIKE is used to search for a specified character pattern in a table.

SELECT * FROM table_name WHERE columnName LIKE pattern;	

Some of the LIKE patterns are listed below.

Pattern Description
'a%' Searches for values that start with "a"
'%a' Searches for values that end with "a"
'%a%' Searches for values that have "a" in any position
'a%o' Searches for values that start with "a" and end with "o"

Example

SELECT * FROM books WHERE name LIKE '%harry%';	

Selects all records from books table where "harry" is in the name column.

IN

IN allows us to add multiple values in a WHERE statement.

SELECT * FROM table_name WHERE columnName IN (columnValue1, columnValue2);	

Example

SELECT * FROM books WHERE bookid IN (1,2,3);	

Selects all records from books table where bookid is 1,2 and 3. It can be used to delete multiple record sets at once as well.

BETWEEN

BETWEEN allows us to select values within a defined range.

SELECT * FROM table_name WHERE columnName BETWEEN value1 AND value2;	

Example

SELECT * FROM books WHERE price BETWEEN $100 AND $200;		

Selects all records from books table whose value is between $100 and $200.

AS

AS can be used to give a temporary name to any column or a table to make it more reasonable, shorthand and readable. It is mostly used with JOIN when column name of two tables are identical.

SELECT column_name AS alias_name FROM table_name AS alias_name;	

Example

SELECT name AS book_name FROM books AS tbl_books;	

JOIN

JOIN is used to retrieve record-set from two tables with a single query when the tables have relationship established using reference.

SELECT tblName1.columnName1,tblName1.columnName2,tblName2.columnName1 FROM tblName1 JOIN tblName2 ON tblName1.columnName1 = tblName2.columnName2

Let's create a table name customers that has a relation to the table named books.

customerid name orderid
1 Jonjo Shelvey 2
2 Danny Drinkwater 3
3 Ashley Barnes 2

Here, orderid of table named customers is bookid of table named books. Now, we can use the JOIN statement to join both tables with reference to the bookid and orderid and retrieve required data.

SELECT books.name AS books_name, customers.name AS customers_name FROM books JOIN customers ON books.bookid = customers.customerid;	

Result

books_name customers_name
When Breath Becomes Air Jonjo Shelvey
The Whistler Danny Drinkwater
When Breath Becomes Air Ashley Barnes

Here, all the matched records are displayed. If you need to display unmatched data too, instead of JOIN, you need to use one of the following statements.

LEFT JOIN Returns all records from the left table and the matched ones from the right.

RIGHT JOIN Returns all records from the right table and the matched ones from the left.

FULL JOIN Returns all records when there is a match in either left or right table

CREATE DATABASE

CREATE DATABASE creates new database.

CREATE DATABASE databasename;	

DROP DATABASE

DROP DATABASE deletes a database.

DROP DATABASE databasename	

CREATE TABLE

CREATE TABLE creates a new table.

CREATE TABLE table_name (column1 datatype, column2 datatype, column3 datatype);	

Datatype defines the type of data such as varchar, integer, date, etc. as well as he string length it can hold. Constraints are used behing datatypes to specify rules for data in each columns.

Example

CREATE TABLE books (
    bookid int unsigned NOT NULL AUTO_INCREMENT,
    name varchar(255),
    author varchar(255),
    price varchar(255),
);	

A table named books will be created after this statement is executed where bookid needs to be an integer that has a positive value (unsigned), column can't be empty and the id value will increase along with the record count. Columns named name, author and price can hold variable characters where the maximum character count can't exceed 255.

DROP TABLE

DROP TABLE deletes an existing table.

DROP TABLE table_name;	

ALTER TABLE

ALTER TABLE can be used to add, delete or modify columns or add and drop various constraints in an existing table.

ADD COLUMN

ALTER TABLE table_name ADD column_name datatype;	

DROP COLUMN

ALTER TABLE table_name DROP COLUMN column_name;

SQL CONSTRAINTS

Constraints are used to limit the type of data that can be inserted into a table to ensure accuracy and reliability of the data. Violation of the constraint rules aborts the related database operations. Common constraints used in SQL are listed below.

NOT NULL - column cannot have a NULL value

UNSIGNED - only positive value

UNIQUE - all values in a column are unique

PRIMARY KEY - uniquely identifies each record. can't have more than one primary key

FOREIGN KEY - Uniquely identifies a record in another table

INDEX - Used to create and retrieve data from the database very quickly

AUTO_INCREMENT - increments primary key with each new record

FOREIGN KEY

CREATE TABLE customers (
    customerid int NOT NULL UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name varchar(255) INDEX,
    bookid int FOREIGN KEY REFERENCES books(bookid)
);