PHP Programming/MySQL

MySQL
MySQL is the most popular database used with PHP. PHP with MySQL is a powerful combination showing the real power of Server-Side scripting. PHP has a wide range of MySQL functions available with the help of a separate module. In PHP5, this module has been removed and must be downloaded separately.

MySQL allows users to create tables, where data can be stored much more efficiently than the way data is stored in arrays.

In order to use MySQL or databases in general effectively, you need to understand SQL, or Structured Query Language.

Note that this page uses the mysqli functions and not the old mysql functions.

Connecting to the MySQL server
PHP has the function mysqli_connect to connect to a MySQL server that handles all of the low level socket handling. We will supply 4 arguments; the first is the name of your MySQL server, the second a MySQL username, third a MySQL password and last a database name. In this example, it is assumed your server is localhost. If you are running a web server on one system, and MySQL on another system, you can replace localhost with the IP address or domain name of the system that MySQL resides on (ensure all firewalls are configured to open the appropriate ports). mysqli_connect returns a link_identifier that we can now use for communicating with the database. We will store this link in a variable called $cxn.

Running a Query
We have connected to the mysql server and then selected the database we want to use, now we can run an SQL query over the database to select information, do an insert, update or delete. To do this we use mysqli_query. This takes two arguments: the first is our link_identifier and the second is an SQL query string. If we are doing a select sql statement mysqli_query generates a resource or the Boolean false to say our query failed, and if we are doing a delete, insert or update it generates a Boolean, true or false, to say if that was successful or not.

The basic code for running a query is the php function "mysqli_query($cxn, $query)". The "$query" argument is a MySQL query. The database argument is a database connection(here, the connection represented by $cxn). For example, to return the query "SELECT * FROM customers ORDER BY customer_id ASC", you could write

However, this straightforward method will quickly become ungainly due to the length of MySQL queries and the common need to repeat the query when handling the return. All (or almost all) queries are therefore made in two steps. First, the query is assigned a variable (conventionally, this variable is named "$query" or "$sql_query" for purposes of uniformity and easy recognition), which allows the program to call simply "mysqli_query($cxn, $sql_query)".

Secondly, to handle the information returned from the query, practical considerations require that the information returned also be assigned to a variable. Again by convention rather than necessity (i.e. you could name it anything you wanted), this information is often assigned to "$result", and the function is called by the assignment of the variable.

It is important to understand that this code calls the function mysqli_query, in addition to assigning the return to a variable "$result". [NOTE: The queries that ask for information -- SELECT, SHOW, DESCRIBE, and EXPLAIN -- return what is called a resource. Other types of queries, which manipulate the database, return TRUE if the operation is successful and FALSE if not, or if the user does not have permission to access the table referenced.]

To catch an error, for debugging purposes, we can write:

NOTE: The semi colon for the function before the die statement is omitted.

If the function mysqli_query returns false, PHP will terminate the script and print an error report from MySQL (such as "you have an error in your SQL syntax") and the query.

Thus, our final code would be, assuming that there were a database connection named $cxn:

Putting it all together
In the previous sections we looked at three commands, but not at how to use them in conjunction with each other. So let's take a look at selecting information for a table in our mysql database called MyTable, which is stored in a mysql database called MyDB.

NOTE: If the link identifier is not specified, the last link opened by mysql_connect is assumed.

The Create Database Query
[By admin@technofranchise.com : We have used my_sqli connector that is the latest construct. There are some other tutorials that use my_sql construct to make the database connections (Don't confuse with it. Our constructor is the latest one) The creation of the database is our first step when accessing the backend MySql Server with php script. This can be achieved by connecting with the server. After that, creating the database.

The Create Table Query
The process of creating the table is as easy as creating the database. We have to execute the create table query using the mysqli construct

Getting Select Query Information with older connector construct
Well that doesn’t help, because what are we to do with $result? Well when we do a select query we select out information from a database we get back what is known as a resource, and that is what is stored in $result, our resource identifier. A resource is a special type of PHP variable, but lets look at how to access information in this resource.

We can use a function called mysql_fetch_assoc it takes one parameter, our resource identifier $result, and generates an associative array corresponding to the fetched row. Each column in the table corresponds to an index with the same name. We can now extract out information and print it like so:

PHP + MySQL + Sphinx
Once you understand the basics of how MySQL functions with PHP you might want to start learning about full text search engines. Once your site gets large (millions of database records) MySQL queries will start to get painfully slow, especially if you use them to search for text with wildcards:

There are many free/paid solutions to stop this problem. A good open source full text search engine is Sphinx Search. There is a WikiBook on how to use it with PHP and MySQL that explains the concepts of how Indexing works. You might want to read it before reading the official documentation.