Use PHP to connect to MySql database

In this lesson it will be shown how to use PHP to connect, run SQL query to retrieve data and insert a new record into MySql database.

If you are starting with PHP and MySql development I would recommend that you install WAMP server which includes complete environment for developing PHP applications and also has phpMyAdmin and MySql database included.

Installation instructions for WAMP server can be found on:
Setting up PHP and MySql testing server with WAMP

If you want to learn how to create a database, please take a look at lesson:
Create MySql database with phpMyAdmin

For connecting to MySQL database we will use extension mysqli.

The syntax for creating a new object is simple:
$mysqli = new mysqli("host", "username", "password", "database");

The following example will connect to the my_db database on local computer with root username and empty password.
$mysqli = new mysqli("localhost", "root", "", "my_db");

After creating connection it is also recommended to check if the connection was successful:
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}


Besides mysqli there also exists extenstion called mysql which can also be used for connecting to MySql database. Because mysql extension is deprecated as of PHP 5.5, you should always use mysqli.

Now we will prepare a new SQL query which will return all the data from table my_table.

$query = "SELECT * FROM my_table";
$result = $mysqli->query($query);

The result of the query is now stored in the $result variable.

We will now use while loop to go through all the records which were returned from the database.

Each single record is stored in the variable $row and we can print out specific filed by also specifying the field name.

while ($row = $result->fetch_assoc()) {
  echo $row['number'].'<br />';
  echo $row['text'].'<br />';
  echo $row['date'].'<br />';
  echo '------------------------ <br />';
}

<br /> tag is added just for better formatting of output.

Inserting data back to the database can also be done very easy.

First we need to prepare a proper SQL command for inserting data.

After that we just run query and a new record will be inserted.

This is shown in the example below:
$query="INSERT INTO my_table(id, number, text, date) VALUES(0,987,'hello','2013-05-21')";
$mysqli->query($query);

Below you can find all the code from this lessons together, so it will be easier to copy it to your php program.

$mysqli = new mysqli("localhost", "root", "", "my_db");

if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

$query = "SELECT * FROM my_table";
$result = $mysqli->query($query);

while($row = $result->fetch_assoc()) {
  echo $row['number'].'<br />';
  echo $row['text'].'<br />';
  echo $row['date'].'<br />';
  echo '------------------------ <br />';
}

//insert new record
$query="INSERT INTO my_table(id, number, text, date) VALUES(0,987,'hello','2013-05-21')";
$mysqli->query($query);


And of course if you have any additional questions just ask on forum.