Using MySQL from PHP

by John Coggeshall
02/19/2004

Welcome back to PHP Foundations. My previous column finished the crash course on using MySQL to store and retrieve data from a database using the Structured Query Language (SQL). In today's column, I will begin to use everything I have shown you thus far to work with and create database-driven web pages using PHP. Let's get started by discussing how a database interacts with a web application.

Database-Driven Architecture

For those of you who have been reading my column on a regular basis since it began (thank you, I'm aware of at least a few of you), way back in 2001 I provided you with a flow diagram outlining how PHP works within a web server. Back then, the diagram was pretty generic, but today I'll revisit it in more detail to describe a database-driven architecture.

In database-driven applications, three different players produce the final output of the web page you view with your client: the web server, the scripting language (PHP), and the database back end (MySQL). When the client browser requests a page from your web site, the following steps occur:

The web server receives the request via HTTP for a particular web page and resolves and retrieves the requested file.
Depending on the nature of the file (i.e., if it ends in .php), it is pre-processed using, in our case, the PHP engine.
The script's application and presentation logic executes, performing database queries as necessary.
The PHP engine uses the results from the database in its application logic to construct the HTML document, returning it to the web server and, finally, the client.

We will focus on steps three and four in our discussions here. Looking at those steps in more detail, we can summarize the process of accessing and working with a database connection from within a PHP script in the following steps. (The steps in parentheses are optional, depending on circumstance.)

Establish a connection to the database server.
(Validate any user input.)
Select the database on the server to use.
Execute the desired query against the database.
(Retrieve and process the results.)
Create HTML or perform actions based on results.
(Close the database connection.)

Connecting to a MySQL Database

From a development standpoint, connecting and executing queries from PHP is as simple as calling the appropriate functions. Let's look at the basic functions used in almost every database-driven application. As I have already explained, the first step is to connect to the database — in our case, this is done via the mysql_connect() function, whose syntax follows:

mysql_connect([$server [, $username [, $password [, $new_link [, $flags]]]]])

If you have worked with MySQL's mysql client application, most of these parameters should already make sense to you. The first parameter, $server, is the address of the MySQL server to connect to using the username and password provided by the $username and $password parameters. When I say "address," however, I am not necessarily talking about a TCP/IP address. This parameter can take multiple forms:

// Connect to the server at hostname using the default port
$server = 'hostname'

// Connect to the server at hostname using the specified port
$server = 'hostname:port'

// Connect to the server on the local machine using the provided local socket
$server = ':/path/to/socket'

Selecting the Database to Use

Once you have a database connection, the next step is to select the database that you will be performing queries against. (Remember the SQL USE statement?) To do this, you'll need the mysql_select_db() function which has the following syntax:

mysql_select_db($database [, $link]);

where $database is the name of the database to use, and the optional parameter $link is the database connection resource returned from the mysql_connect() function. This function will attempt to select the specified database and return a Boolean indicating success or failure.

Note: As is the case with almost all of the MySQL extension functions, the $link parameter is optional. In every case, PHP will use the last opened connection. If no connection is open, it will attempt to open one automatically. It is strongly recommended that you provide a $link explicitly to avoid problems as your applications become more advanced.

Performing a Query Against a Database

Now that you know how to connect to the MySQL database, let's see how to perform a query against the database from within PHP. To do this, use the appropriately named mysql_query() function, whose syntax is as follows:

mysql_query($query [, $link]);

where $query is a single SQL query to execute (without the terminating semi-colon or \g) and the optional parameter $link is the value returned from mysql_connect(). As usual, PHP will use the last opened connection if you do not provide the $link parameter.

Upon successful execution, mysql_query() will return a resource representing the result set or a Boolean false if the query failed. Note that a query is considered a success even if no results are returned; mysql_query() will only fail if the query itself was malformed or otherwise unable to execute on the server. Determining if any results were actually returned from a query requires a different method.

Retrieving a Result Set

Now that you know how to perform a query, it's time to learn how to access the data from a result set. PHP has many different methods to accomplish this task, but they all have the same general form. For our purposes, I'll explain things in the context of the mysql_fetch_row() function. Its syntax is as follows:

mysql_fetch_row($result);

where $result is the result resource returned from a successful query executed using the mysql_query() function. This function will return a single row from the result set as an enumerated array, where element zero represents the first column, element one represents the second, and so on. Each subsequent call will return the next row in the result set until no more rows remain. Then, mysql_fetch_row() will return a Boolean false. Generally, this function is used in conjunction with a while loop to traverse the entire array as shown in the below example snippet:

<?php

/* Connection code omitted */

$result = mysql_query("SELECT * FROM books");

if(!$result) die("Query Failed.");

while($row = mysql_fetch_row($result)) {

    /*  
    $row[0] now contains the first column of the current row,
    index 1 is the second, etc.
    */

}
?>

As I stated earlier, mysql_fetch_row() is not the only function available that allows you to access rows of a result set in this fashion. Each of the following functions has an identical syntax and use as mysql_fetch_row(), but each provides the row in a different format as described:

Return the current row as an associative array, where the name of each column is a key in the array.
```
$row = mysql_fetch_assoc($result) 
$row['column_name']
```
Return the current row with both associative and numeric indexes where each column can either be accessed by 0, 1, 2, etc., or the column name.
```
$row = mysql_fetch_array($result) 
$row[0]

// or 

$row['column_name']
```
Return the current row as an object with member variables for each column in the result set.
```
$row = mysql_fetch_object($result) 

$row->column_name
```

Closing a Database Connection

Although it is not strictly necessary, sometimes it is advantageous to close an open connection to the database when you no longer need it, instead of waiting for the end of the request, when PHP will do so automatically. Use the mysql_close() function with the following syntax:

mysql_close($link)

where $link is the database connection resource returned from the mysql_connect() function.

More PHP/MySQL to Come

That's all for today! My next column will introduce even more MySQL PHP functions that do things like determine the number of rows in the result set and deal with errors. Soon after that, I'll pull together all of these ideas to create a front end to the book information database we worked with previously. See you then!

John Coggeshall is a a PHP consultant and author who started losing sleep over PHP around five years ago.

Read more PHP Foundations columns.

Return to the PHP DevCenter.

Comments on this article

Showing messages 1 through 14 of 14.

converting from files to db
2009-09-13 15:41:39 wizardeyes [View]

Hello, I have been programming my wiki-style site for a few years but only recently been able to go live on a php-supporting host. I have played with php and learned about it quite well regarding files, text and logic. However, building a site that contains thousands of articles, each containing several parts, I figured I would start out as a newbie using text files that get included. This type of "mudulation" helped me, but now is getting a bit of a nusance.

I need to have several different db's for the management, members, articles, history, logs etc. And I want them to be stored on the site so I can FTP backups. I do have some experience in Excell and am looking to do something similar. How can I convert my data processing from filing to db stored on the site's folder system rather than "localhost"? something like "memberships.db" or whatever. I look at the php codes and they never call a file. It reminds me of using a registry editor, where the files aren't "files". Anyone care to help?

drop down date option not inserting into database
2009-03-12 06:11:24 krishkrish [View]

i want to post my date into database. i am putting drop down box for date. it doesn't accept the input. it display (0-zero) in database. i am using separate function for to get date month year. how i correct my problem . anybody please help me.

insertind date data into table
2009-03-04 22:02:35 krishkrish [View]

i have a poblem in inserting data into table. i am puting dateof birth as select oprion as month, year,date. the user select this and post to table. but i create only date_of birth field as date data type. hoe i insert these there input into one field in table. please anybosy help me. the following coding only use. is ther eany error suggesstion please reply me.

<?php
// Connects to your Database
$host="localhost"; //Host Name
$username = " root"; //mysql user name
$password =" sivagami"; //mysql password
$db_name ="tms" ;//database name
$tbl_name ="employee" ; //Table Name

mysql_connect("localhost", "root", "sivagami") or die(mysql_error());
mysql_select_db("tms") or die(mysql_error());

$emp_id =$_POST('emp_id');
$emp_name =$_POST('emp_name');
$address =$_POST('address');

$bdayyear = $_POST['yyyy'];
$bdaymonth = $_POST['mm'];
$bdayday = $_POST['dd'];

$date_of_birth = " $bdayyear, $bdaymonth,$bdayday"; // or whatever format you want

// It would be printed as, for example ' December 22, 1987 ' Though you can change the format.

// When you insert the Bday into the mysql table, just use the $bdayfull variable

// $date_of_birth = $_POST('date_of_birth');
// $date_of_join =$_POST ('date_of_join');

$bdayyear = $_POST['yyyy'];
$bdaymonth = $_POST['mm'];
$bdayday = $_POST['dd'];

$date_of_join = " $bdayyear, $bdaymonth,$bdayday"; // or whatever format you want

$designation =$_POST('designation');

$sql ="INSERT INTO $tbl_name (emp_id, emp_name,address,date_of_birth,date_of_join,designation) VALUES ( '$emp_id', '$emp_name','$address','$phone','$date_of_birth', '$date_of_join','$designation' )"or die(mysql_error());

$result= mysql_query($sql) or die('Error updating database');
echo "successfull";
mysql_close();
?>

mysql php database connectivity problm
2009-03-02 07:30:17 krishkrish [View]

<?php
$servername='localhost';
$dbusername='root';
$dbpassword='sivagami';
connecttodb($servername,$dbname,$dbusername,$dbpassword);
function connecttodb($servername,$dbname,$dbuser,$dbpassword)
{
global $link;
$link=mysql_connect ("$servername","$dbuser","$dbpassword");
if(!$link){die("Could not connect to MySQL");}
echo 'Connected Successfully';
$database = 'tms';
mysql_select_db($database [,, $link]);

}
?>

is this correct or not. it display the message connected successfully. but if i insert it doesn't work. what to do. any body help me.

Getting PHP script to contruct MYSQL query
2008-02-25 13:10:03 Fletch187 [View]

Hi There, I am a newbie to PHP and struggling to figure out how I can develop my script to construct the query based on available form data.

Example:
Form entered by user has 6 possible outputs
Name, Lname, Town , Postcode , Telephone......
User only fills in Name & Postcode
How can I construct a script to create $query = Name & Postcode, having checked all variables for the existants of strings from the form post.

The reason I want to do this is to give the user options on what they want to search with....

Thanks for any advoce / examples you can give me...
Fletch187

Using mysql from PHP
2007-08-14 03:53:45 suamya [View]

Hello,

In a single PHP code, I want to connect to two different hosts with totally different authentication information. Is it possible?

Right now snippet of my code looks something like this:
<?php
.....
$conn_1=mysql_connect('host1','user1','pass1');
$conn_2=mysql_connect{'host2','user2','pass2');
$db_list = mysql_list_dbs($conn_2);

while ($row = mysql_fetch_object($db_list)) {
echo $row->Database . "\n";
}
?>

However when I try to list all the databases in host2, it instead lists the database in host1.
host1 and host2 are different machines and the databases in each of them are entirely different.

I read that the new_link parameter might be used here but I couldnt figure out how to do that.

I am new to PHP...so any help would be great!!

-suamya.

Connect to MySQL DB
2006-05-09 17:00:30 Cleo [View]

I have a hosting account with GODADDY.com where I've been assigned a MySQL database. But I'm having trouble connecting to it. My code is as follows:

//Connect To Database
$hostname="mysql.secureserver.net";
$username="cdphillips";
$password="*******";
$dbname="cdphillips";
$usertable="Contacts";
$yourfield = "F_Name";
$conn=mysql_connect($hostname,$username, $password) or die ('Error connecting to mysql');
mysql_select_db($dbname);

# Check If Record Exists

$query = "SELECT * FROM $usertable";

$result = mysql_query($query);

if($result)
{
while($row = mysql_fetch_array($result))
{
$name = $row["$yourfield"];
echo "Name: ".$name."
";
}
}

What's wrong please?

Results not displaying first record?
2005-12-01 02:57:31 Electra [View]

>sql query:

$result = mysql_query("SELECT * FROM `events`") or die(mysql_error());
while($row = mysql_fetch_array($result)) {
$ID = $row["ID"];
$Dt = $row["Dt"];
$Event = $row["Event"];

echo $Dt . "$nbsp;nbsp;" . $Event . "
";
}

>There are 3 records in the database, but only records from the 2nd row are displayed. Using mysql_fetch_row($result) displays nothing? I've also added LIMIT 0,30 to the query to try and force the first row, but with no luck.

escaping syntax for MySQL in PHP - need help
2005-07-06 02:00:07 gluino [View]

Hi,

I'm new to MySQL / PHP, most of my problems are with quotes and escaping quotes etc...

Can you spot my error(s)?

while($row = mysql_fetch_array($result)) { echo "<option value=\"$row['coyid']\">$row['name']</option>"; }

PHP error is:
"Parse error: parse error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /var/www/html/test/dataentry.php on line 62"
(line 62 is the echo line above)

Thanks.

trouble getting PHP5 to connect to SQL database
2005-04-10 23:23:31 jtomsovic [View]

I am new to PHP and MySQL. I am using windows
XP OS. I have installed the Apache server, MySQL and PHP5. When I run "localhost" it shows that the apache sever is running. I can run a test which shows that PHP and Apache work together. I have SQL running and I can create databases, etc uing the command prompt or the MySQL Admin program.
But I cannot get PHP5 to connect to the databases. What am I doing wrong? Help is very much appreciated.

Below are the paths to the various programs
C:\Program Files\Apache Group\Apache2>
C:\Program Files\MySQL\MySQL Server 4.1\bin\mysql.exe
C:\PHP5>

PHP tutorial
2004-12-01 04:53:39 Roommatesville.com [View]

Roommatesville would like to thank you for detailing more of the sytax of PHP and Mysql.

--www.roommatesville.com

Abstraction Layer
2004-09-08 06:27:48 Steve_K [View]

I recommend to use an abstraction Library for database access.
The built in mysql commands in php are quite nice, but if you ever need to change your database to Oracle f.e., you have a lot of work to do :)
You may say that this is not neccessary, so did i.
But spending dozend of hours editing my source made me think over it.
In my case, i use ADOdb, which is really easy to use and its OpenSource too.
ADOdb at sourceforge
Give it a try. Once you are familiar with the basic commands its easy to use and it has some quite powerful function, too.
If you ever need to switch the type of your database, you just edit one word and that's it :)
Maybe this is helpful to some of ya.
best regards
Steve
-------
No one started as a master:
My very first site. Back online *g*

JDBC Connecting to mySQL server at Unix
2004-02-25 08:37:06 weckenmc [View]

I just read your article on PHP connect to mySQL database.

// Connect to the server at hostname using the specified port
$server = 'hostname:port'

I need to connect from my Windows app w/JDBC to the mySQL server on a Unix platform across the net. In your connect statement above, how do I get the port value?

Any insights is very much appreciated!
Thanks,
Cristl

Overdone?
2004-02-23 04:33:53 goyanks [View]

I'd dare say that there are well over 1,000 tutorials out there on the Web devoted to basic PHP/MySQL interaction, do we really need another one? Now I'm all for learning about a topic from more than one point of view, but hasn't this one been done, oh perhaps a bazillion times or so? How about touching upon some more timely topics, say PHP 5?

View All RSS Feeds >

800-889-8969 or 707-827-7019
Monday-Friday 7:30am-5pm PT
©2011, O'Reilly Media, Inc.
All trademarks and registered trademarks appearing on oreilly.com are the property of their respective owners.