How to connect to MySQL using PHP

This brief article describes step-by-step how to connect to a MySQL database using PHP.

If you want to provide content coming from a database, you will at least have to know the following methods:

  • mysql_connect($server, $user, $password) — opens a connection to a database server
  • mysql_select_db($database_name) — chooses a database within the database server
  • mysql_query($sql_query_string) — sends a SQL statement to the DBMS
  • mysql_fetch_row($result) — goes through the result set coming from the DBMS
  • mysql_insert_id() — gets the last generated ID coming from an auto_increment attribute
  • mysql_error() — gives the error string coming from the DBMS in case of an error

There are different scenarios of data readout we want to take a look at.

Database connection and simple data readout

The following code is an example on how to do a full PHP – database interaction roundtrip:

1
2
3
4
5
6
7
8
<?php
$server = "dbs.your-domain.com";
$user = "bob";
$password = "P4ssW0rD!";
$database = "wordpress";
mysql_connect($server, $user, $password);
mysql_select_db($database);
?>

The code above enables you to build a working database connection from your PHP script. However we have not read any data yet.

1
2
3
4
5
<?php
$stmt = mysql_query("SELECT TEXT FROM TEST") or die ( mysql_error() );
$row = mysql_fetch_row($stmt);
echo $row[0]; // gives the first item of TEXT in TEST
?>

The last two lines will allow data readout, but only for the first row of one attribute.

Reading one attribute and all rows of a table using WHILE loop

If you wanted to read all rows of table TEST for attribute TEXT you’d have to use a WHILE loop as discussed earlier:

1
2
3
4
5
6
<?php
$stmt = mysql_query("SELECT TEXT FROM TEST") or die ( mysql_error() );
while ($row = mysql_fetch_row($stmt)) {
echo $row[0]; // gives the current item of TEXT in TEST
}
?>

The WHILE loop above enables you to echo all values of attribute TEXT in TEST.

Reading several attributes and all rows of a table using WHILE and FOR loops

If you wanted to read all rows of table TEST for more than one attribute you can do the following:

1
2
3
4
5
6
7
8
9
<?php
$stmt = mysql_query("SELECT ID, TEXT FROM TEST") or die ( mysql_error() );
$column_count = mysql_num_fields($stmt);
while ($row = mysql_fetch_row($stmt)) {
for ($j = 0; $j < $column_count; $j++) {
echo $row[$j]; // gives the current item of the current attribute in TEST
}
}
?>

You notice that we put a FOR loop inside the WHILE loop which counts through every column (attribute) of the table we are fetching.

The advantage of the code above is that you can re-use it for any table and any attribute you like.

I hope this PHP MySQL tutorial article helps you in getting started with connecting to MySQL using PHP.

Leave a Reply