Transwiki: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.

How to - Step By Step

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.

<?php
  $cxn = mysqli_connect ("localhost", "your_user_name", "your_password", "database_name");
?>

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

<?php
    mysqli_query($cxn, "SELECT * FROM customers ORDER BY customer_id ASC");
?>

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)".

$sql_query = "SELECT * FROM customers ORDER BY customer_id ASC";

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:

<?php
   $result = mysqli_query ($cxn, $sql_query)
     or die (mysqli_error () . " The query was:" . $sql_query);
?>

NOTE: The semi colon for the function before the die statment 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:

<?php 
$sql_query = "SELECT * FROM customers ORDER BY customer_id ASC";
$result = mysqli_query ($cxn, $sql_query)
    or die (mysqli_error () . " The query was:" . $sql_query);
?>

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.

<?php
	 //Connect to the mysql server and get back our link_identifier
	 $link = mysql_connect ("your_database_host", "your_user_name", "your_password")
                   or die('Could not connect: ' . mysql_error());
 
	 //Now, we select which database we would like to use
	 mysql_select_db ("MyDB", $link) or die('could not select database'); 
 
 //Our SQL Query
 $sql_query = "Select * From MyTable";
 
 //Run our sql query
 $result = mysql_query($sql_query)or die('query failed'. mysql_error());

 
	 //Close Database Connection
	 mysql_close ($link);
 ?>

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.

<?php
$cn=mysqli_connect("localhost","your_username","my_password"); 

//connecting the server
if (mysqli_connect_errno())
{
echo "Error in establisihng the connection:" . mysqli_connect_error();
}
$sql_query="CREATE DATABASE  MyDB";
if (mysqli_query($cn,$sql_query))
{
echo "Database has been created";
}
else
{
echo "Error while creating the database: ” . mysqli_error($cn);
}
?>

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

<?php
$cn=mysqli_connect("localhost","my_username","my_password","MyDatabase");

if (mysqli_connect_errno())
{
echo "Connection failed : " . mysqli_connect_error();
}
$sql_query="CREATE TABLE  MyTable(firstName VARCHAR(18), lastName VARCHAR(18), salary DECIMAL(5,4) )";
if (mysqli_query($cn,$sql_query))
{
echo "Table created successfully";
}
else
{
echo "Error encountered  while creating the table : " . mysqli_error($cn);
}
?>

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 
 //Connect to the mysql server and get back our link_identifier
 $link = mysql_connect("localhost", "your_user_name", "your_password")
         or die('Could not connect: ' . mysql_error());


 //Now, we select which database we would like to use
 mysql_select_db("MyDB") or die('could not select database'); 

 //Our SQL Query
 $sql_query = "Select * From MyTable";

 //Run our sql query
 $result = mysql_query($sql_query)or die('query failed'. mysql_error());

 //iterate through result
 while($row = mysql_fetch_assoc($result))
 {
     //Prints out information of that row
     print_r($row);
     echo $row['foo'];
     //Prints only the column foo.
 }
 
 // Free resultset (optional)
 mysql_free_result($result);


 //Close the MySQL Link
 mysql_close($link);
 ?>

Inserting the records with latest connector construct

<?php
$cn=mysqli_connect("localhost","your_username","your_password","MyDB");
if (mysqli_connect_errno())
{
echo "Connection failed : " . 
mysqli_connect_error();
}
mysqli_query($cn,"INSERT INTO MyTable(firstName, lastName, salary) VALUES ('George','Smith' ,55000)");

mysqli_close($cn);

?>

Updating the records with latest connector construct

<?php
$cn=mysqli_connect("localhost","your_username","your_password","MyDB");
if (mysqli_connect_errno())
{
echo "Connection failed : " . mysqli_connect_error();
}
mysqli_query($cn,”Update MyTable Set salary=6000 Where firstName='George' AND lastName='Smith' ");
mysqli_close($cn);
?>

Delete the records

<?php
$cn=mysqli_connect("localhost","your_username","your_password","MyDB");
if (mysqli_connect_errno())
{
echo "Connection failed : " . mysqli_connect_error();
}
mysqli_query($cn,"Delete From MyTable Where firstName='George' AND lastName='Smith' “);
mysqli_close($cn);
?>

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:

WHERE content='%text%')

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.

External links