How to Query a Database with PHP

Working with a common LAMP software stack, PHP is a natural fit with mySQL databases.  In fact, PHP and mySQL are a marriage made in Apache heaven. To help you understand how to use the two together, it’s important to conceptualize how the two fit together. While your Apache server provides the direct HTML output to a user’s browser, PHP provides front end scripting that both reads and writes from a database to produce dynamic on-site content.  The connections between mySQL and PHP make it particularly easy to query, update and work with database driven content on your website.

Common applications which rely upon the interface of PHP and databases include some of the largest sites online including Wikipedia and major social networking applications. By utilizing the open source LAMP stack you can take your application in an entirely new direction, adding new options, features and functions to further extend the capability of your application. When building your application from the ground up, you’ll want to make out the types of tables and database functions necessary to work within your core PHP scripts.

PHP is used to connect to the database and echo the resulting content to user’s, all of which produces a seamless product visible to users. A common example might be when you log into a website: your user information is stored in a database, and then the site is propagated with information according to your preferences.  Since the fine folks at PHP appreciate the LAMP stack they integrated a mysql_connect module to automatically link the two.

Getting Started with Databases in PHP

You can use PHP to remotely connect to your database with a simple mysql_connect command followed by the host name, user and password, and you can further select a specific @myql_select_db command. The integrated commands provide you with a direct connection to your database through PHP, making it much easier to work with information stored in mySQL.

One of the most effective ways to work directly with a database is through a query – for example, if we wanted to store a user’s profile details on a social networking site, we can accomplish this with the following PHP code:

<?php

$hostname = "localhost";
$dbname = "mysql_db_name";
$dbuser = "myusername";
$dbpassword = "mypassword";

if(!mysql_connect($hostname, $dbuser, $dbpassword))
    die("Cannot connect to database server.");

if(!mysql_select_db($dbname))
    die("Cannot select database $dbname");

$query = "INSERT INTO profile (ID, User, type) " .
         VALUES ('$ID','$User','$type');
$result = mysql_query($query);

?>

In this example, the values are entered directly into fields within the table, allowing you to store user information in real time.  Once this data is stored, you can use it to dynamically display on a user’s profile to provide a wealth of data.  For our social networking example, you can put the user details into his or her profile based upon the stored database information:

<?php
   $sql = "SELECT * FROM profile WHERE ID = '$ID';";
   $result = mysql_query($sql);

   if(mysql_num_rows($result))
   {
      echo “Welcome $user, you have qualified for $type status.”;
   }
   else
   {
      echo "Invalid username or password.";
   }
?>

This functionality can be extended in a number of directions based upon the resulting output, and you can utilize the data to properly generate a complete set of profile data. For large scale sites, the ability to use mySQL can help extend, update and enhance the functionality of websites significantly with minimal recoding.