Creating and Accessing MySQL Data with PHP

By Darren W. Hedlund
2005-06-20

Creating and Accessing MySQL data with PHP

This tutorial will hopefully teach you some basics of how to access data with your PHP scripting to gather information with in a MySQL database.

The first step you need to do is get a basic understanding of what MySQL is. If your site can handle or has access to a MySQL database you should try using the PHPmyadmin application to help you use and access your MySQL datases fast and easy.

Once you are able to access PHPmyadmin, start off my creating a simple database.
In this case lets call it bubba.

Now that you have created your first database called bubba, lets add the table information.

Under the name filed enter the word love, and in the field area add in 3 for the fields, and press enter.

Now the next screen will ask you to create the data fields that you require for this new table called love.

In the first one enter in id, the second enter first, and the third enter in last.

For the type for id choose INT and the lenth of 11. Click onto the Primary for th id field. Now under the field Extra, choose the option to auto auto_increment. This is VERY important thing to do for auto assigning an ID number and is used for 75% of all scripts.
For the type for first choose VARCHAR and the length of 25.
For the type for last choose VARCHAR and the length of 25.

If this has been done correctly, you should see the following screen:

Lets add in some information to this database. To make is short and easy simply highlight and copy the following information.

INSERT INTO love VALUES (1, 'Darren', 'Hedlund');
INSERT INTO love VALUES (2, 'Jeff', 'Lewis');
INSERT INTO love VALUES (3, 'Bubba', 'Lovesponge');

Now click onto the SQL tab as shown here:

Once you do this paste thie information you have copied above to the following area:

Click on to the go button, and the information will be added into the database name of buba, and the table name of love.

Accessing the information with PHP

Now you can use many different ways to access a MySQL database, and some are simple, while others are very advanced.
This one is going to try and just teach you the basics that should hopefully get you started in the right direction.

First step is to make two blank files into a folder of your choosing:
index.php
settings.php

Copy and past the following information into the index.php file:

<?
# PULL ADDITIONAL FILES
include_once ("./settings.php");
# CONNECT TO THE MySQL SERVER
$link = mysql_connect($host,$user,$password);
# SELECT THE DATABASE NEEDED
mysql_select_db("$base");
# START THE TABLE INFORMATION FOR THE RESULTING INFORMATION
echo  '<table cellpadding=0 spacepadding=0 border=1 width=100%><tr>';
# BEGIN THE SQL QEURY FOR THE INFORMATION
# SHOW ALL THE CONTENTS
 $result=mysql_db_query($base,"select * from ".$basesites." WHERE first='darren'",$link);
if ($result > 0) 
 {
 $num4=mysql_num_rows($result);
 if($num4!=0)
         {
         $n=0;
         while ( $n<$num4 )
                {
   # call and show the ID
   $id=mysql_result($result,$n,"id");
   # call and show the first
   $first=mysql_result($result,$n,"first");
   # call and show the last
   $last=mysql_result($result,$n,"last");
   $n++;
   if (!$size) $size="0 kb";    
   echo  "<tr>";    
   echo  "<td align=left>$id</td>";
   echo  "<td align=left>$first</td>";
   echo  "<td align=left>$last</td>";
   echo  "</tr>";
   }
  }
  echo "</table>";
 }
?>

Copy and past the following information into the settings.php file:

<?
# Root MySQL database
$base = "bubba";
# MySQL user name
$user = ""; 
# MySQL password
$password = "";
# Host name (ex. localhost)
$host = "localhost";
# Main Table name (ex. mysite)
$basesites= "love";
?>

On the settings.php file make sure to enter in the user name, and password in order to connect to your MySQL database.
Contact your host provider if you do not know this information.

Now access your folder that you have placed in these two files, and you should see the following:

Now if you wanted to only acces a certain field you could change the index.php file looking like this:
$result=mysql_db_query($base,"select * from ".$basesites."",$link);
and add in WHERE first='darren' between ".$basesites." HERE ",$link lie the following:
$result=mysql_db_query($base,"select * from ".$basesites." WHERE first='darren'",$link);

This would result in the php to only grab the following information:

I hope this helps you get some of the basics of writting a PHP script, and accessing a MySQL database.

Tutorial Pages:
» Creating and Accessing MySQL data with PHP

Related Tutorials:
» Zend Framework Tutorial
» Port Scanning and Service Status Checking in PHP
» Web Database Access from Desktop Applications
» CubeCart 3.0 Installation and Configuration
» PHP Site Search Made Easy
» Installing and Configuring Drupal 6.1