PHP
  1. PHP intro
  2. PHP basics
  3. PHP variables
  4. PHP functions
  5. PHP conditions
  6. PHP loops
  7. PHP arrays
  8. PHP classes & objects
  9. PHP strings
  10. PHP forms
  11. PHP entities
  12. PHP files
  13. PHP include files
  14. PHP date & time
  15. PHP cookies
  16. PHP databases
  17. PHP sessions
  18. PHP summary

PHP databases

One of the greatest things about PHP is its ability to communicate with databases.

This tutorial focuses on:

SQL

Before beginning a discussion of how to use PHP to communicate with databases, it is imperative to first know a little bit about the language used for database access - that language is SQL.

SQL stands for Structured Query Language, it is the standardized language used to request information from a database, and it is the language we will be using for database access. The original version of SQL was SEQUEL (Structured English Query Language), and it was designed by IBM in 1974 and 1975. In 1979, Oracle introduced SQL as a commercial database system.

MySQL

The database system we will be working with is MySQL. The MySQL database is an open source Rational Database Management System that relies on SQL for data processing.

Known for its speed and reliability, MySQL has gained much popularity over proprietary database systems. MySQL is commonly used for web and embedded applications, and it can run on several operating systems including UNIX, Windows, and Mac OS. MySQL was developed by the MySQL AB company.

MySQL Database commands and data types

There are various SQL commands that can be used with the MySQL database system to perform various actions such as creating tables, deleting tables, inserting data into tables, creating new databases, and more.

MySQL database commands:

Syntax:
USE name_Of_Database_To_Use;
Example:
USE database1;
Syntax:
create database name_Of_Database_To_Be_Created;
Example:
create database database1;
Syntax:
create table name_Of_Table_To_Be_Created();
Example:
create table table1();
Syntax:
select data_To_Be_Selected from table_From_Which_To_Select_Data
Example:
select * from table1
Syntax:
describe name_Of_Table_To_Describe;
Example:
describe table1;
Syntax:
drop name_Of_Table_To_Delete;
Example:
drop table1;
Syntax:
insert into name_Of_Table_To_Insert_Record_Into values(place values to be inserted into table here);
Example:
insert into table1 values(4, 'text string', 'another text string', 6);

In the above example, a new record will be added to the table named table1. The values that will be added to this record are 4, 'text string', 'another text string', and 6.

MySQL is specific about data, you can not just insert any type of data anywhere. Different data types exist for different types of data.

MySQL data types:

Connecting to a MySQL database

A connection to a MySQL database is established through PHP's mysql_connect() function which is then stored in a connection handle.

Syntax:
$connectionHandle = mysql_connect("server", "username", "password");
Example:
<?php $connection = mysql_connect("server1", "user", "pass"); ?>

In the above example, a variable named $connection is used as the connection handle to a MySQL database. The parameters in the mysql_connect() function indicate to connect to a server named "server1" with the user name "user" and the password "pass".

Once a connection to a MySQL database is established, you have to select a database from which to get data. This is achieved with the mysql_select_db() function.

Syntax:
mysql_select_db("name_Of_Data_Base_To_Select", $connectionHandleToUse);
Example:
<?php $connection = mysql_connect("server1", "user", "pass"); mysql_select_db("database1", $connection); ?>

In the above example, a variable named $connection is used as the connection handle to a MySQL database. The parameters in the mysql_connect() function indicate to connect to a server named "server1" with the user name "user" and the password "pass". The database selected is "database1", and the connection handle used is $connection.

Creating a query to and getting data from a database

Once you establish a connection and select a database, the next thing to do is create a query to the selected database. Queries are created by PHP through the mysql_query() function.

Syntax:
$string_Which_Will_Return_Data_From_Query = mysql_query($the_Query, $the_Connection_Handle_To_Use);
Example:
<?php $connection = mysql_connect("server1", "user", "pass"); mysql_select_db("database1", $connection); $theResult = mysql_query("select * from table1", $connection); ?>

In the above example, a variable named $connection is used as the connection handle to a MySQL database. The parameters in the mysql_connect() function indicate to connect to a server named "server1" with the user name "user" and the password "pass". The database selected is "database1", and the connection handle used is $connection. The variable named $theResult is a string which will return the data from the query. The query is specified as the first parameter in the mysql_query() function as "SELECT * from table1". This query will return all the data from the table named table1. The second parameter in the mysql_query() function is $connection - it specifies which connection handle to use for the query.

Closing the connection

After the connection is established, the database selected & queried, and the data received, the connection to the MySQL database needs to be closed. This is achieved through the mysql_close() function.

Syntax:
mysql_close($connectionHandle);
Example:
<?php $connection = mysql_connect("server1", "user", "pass"); mysql_close($connection); ?>
© Copyright 2013-2014 Landofcode.com
Terms of use | Privacy policy | Copyright information