Basic mySQL Queries And PHP
- By Richard Richi
- Published 09/21/2007
- MySQL , PHP
- Unrated
Richard Richi
View all articles by Richard Richi
With the development and availability of open source and powerful tools like PHP and mySQL, combining the power of HTML and dynamic data has never been easier. Now with just a simple learning curve, anyone can get started in creating a truly dynamic web site, one that interacts with its users in every possible way.
This tutorial will help you get started in working with mySQL and PHP. It will assume that you have no prior experience in using mySQL with PHP before. After reading this tutorial, you should get an idea on what a database is, how to use it, and how to access it. You can then go ahead and start reading on the many more advanced topics regarding database systems.
Introduction to mySQL
MySQL has become a very popular database for usage in personal and commercial web sites. It is fast, easy, supported by a wide scale of web based scripting languages, and best of all its free to use. Before we start showing off code, there is one rule to remember when working with mySQL and PHP. That rule is very simple; a basic query consists of several parts in PHP:
What is a Query?
A query is simply a command to issue to the database to perform a certain task. The most basic of queries are those to fetch data, edit data, and delete data. Of course there are many more, but for the sake of this tutorial we will be only covering these basics among several others.
How a Database is Divided
You could think of a database as a notebook and the different tables in it as different sections of the notebook.
Think about, for example, your math notebook for school. You probably divided it into different sections; Lectures, Sections, Homework, Labs, Handouts. But whenever you use the notebook, regardless of the section your working on, your using it for a common purpose and that's math! A database can be thought of the same way. It organizes different tables holding different data but revolving around the same purpose.
Let's provide a real life example though of a database instead of all those school related analogies. The LimitLess Studios web site uses a database on a mySQL server to serve dynamic content. Our database is divided into several tables:
If sections of a notebook are like tables in a database, then the individual elements for each section are like the fields for each table. Confused? Let's break it up a second.
Fields, or Attributes if you want to use the literal term, of a table are the different elements that define how the data will be stored in a table. For example, take a look at any page in your notebook and look at some common elements between them. You might have a page number, a date, a lecture number, and finally the actual notes for the lecture. Even though the elements are the same, no two pages will contain the same value for the same element. For example, you can't have two pages numbered 2 in your notebook, but rather page 1 and page 2.
The same analogy can be applied to tables. Consider our Users table mentioned before. The fields that make it up can be:
Again look at your notebook, each page has a content for each element, whether it be the date or page number. Yet this content is different but they share the same basic layout. You can't have a page without a page number for example.
Okay enough with the introductions. Let's get down to business and show some code to create a database and one table so we can show examples on some basic queries. Usually, depending on your host, you will not have privilege to create a database unless they do it for you or through some sort of Control Panel they have provided.
Which ever the way, create a database called examples. On the next page, we will provide examples on how to create the table through code or using phpMyAdmin if you have it available to you. phpMyAdmin greatly simplifies the task of creating tables in a database - So if you have it, it would be better to use it!
Okay let's see some code. Use the below code to create a mySQL table using PHP. An explanation will follow but pay close attention to the comments and you should get an idea of what is going on.
What have we done??? You can pretty much guess. Let's divide the code into sections and explain what is going on in each section so that things are easier to understand.
Basically what we are doing here is opening a link to mySQL on the first line. This function, mysql_connect takes three arguments; the address of the mySQL server; this will usually be 'localhost', the User Name, and the Password for the mySQL server.
After this, we selected the database we previously created so we can issue queries to it. Selecting the database is only required once during an entire script's execution, unless the mySQL connection is closed.
This tutorial will help you get started in working with mySQL and PHP. It will assume that you have no prior experience in using mySQL with PHP before. After reading this tutorial, you should get an idea on what a database is, how to use it, and how to access it. You can then go ahead and start reading on the many more advanced topics regarding database systems.
Introduction to mySQL
MySQL has become a very popular database for usage in personal and commercial web sites. It is fast, easy, supported by a wide scale of web based scripting languages, and best of all its free to use. Before we start showing off code, there is one rule to remember when working with mySQL and PHP. That rule is very simple; a basic query consists of several parts in PHP:
- Connect To mySQL
- Select The Database
- Define The Query
- Issue The Query
- Return The Data If Any
What is a Query?
A query is simply a command to issue to the database to perform a certain task. The most basic of queries are those to fetch data, edit data, and delete data. Of course there are many more, but for the sake of this tutorial we will be only covering these basics among several others.
How a Database is Divided
You could think of a database as a notebook and the different tables in it as different sections of the notebook.
Think about, for example, your math notebook for school. You probably divided it into different sections; Lectures, Sections, Homework, Labs, Handouts. But whenever you use the notebook, regardless of the section your working on, your using it for a common purpose and that's math! A database can be thought of the same way. It organizes different tables holding different data but revolving around the same purpose.
Let's provide a real life example though of a database instead of all those school related analogies. The LimitLess Studios web site uses a database on a mySQL server to serve dynamic content. Our database is divided into several tables:
- News Table - Houses all our news that we publish on the Home Page.
- Tutorial Table - Houses all our tutorials.
- User Table - Houses information on our users like User Names and Passwords.
If sections of a notebook are like tables in a database, then the individual elements for each section are like the fields for each table. Confused? Let's break it up a second.
Fields, or Attributes if you want to use the literal term, of a table are the different elements that define how the data will be stored in a table. For example, take a look at any page in your notebook and look at some common elements between them. You might have a page number, a date, a lecture number, and finally the actual notes for the lecture. Even though the elements are the same, no two pages will contain the same value for the same element. For example, you can't have two pages numbered 2 in your notebook, but rather page 1 and page 2.
The same analogy can be applied to tables. Consider our Users table mentioned before. The fields that make it up can be:
- Email Address
- User Name
- Password
Again look at your notebook, each page has a content for each element, whether it be the date or page number. Yet this content is different but they share the same basic layout. You can't have a page without a page number for example.
Okay enough with the introductions. Let's get down to business and show some code to create a database and one table so we can show examples on some basic queries. Usually, depending on your host, you will not have privilege to create a database unless they do it for you or through some sort of Control Panel they have provided.
Which ever the way, create a database called examples. On the next page, we will provide examples on how to create the table through code or using phpMyAdmin if you have it available to you. phpMyAdmin greatly simplifies the task of creating tables in a database - So if you have it, it would be better to use it!
Okay let's see some code. Use the below code to create a mySQL table using PHP. An explanation will follow but pay close attention to the comments and you should get an idea of what is going on.
QUOTE
<?php
// Open Connection To mySQL.
// Replace 'username' and 'password' with appropiate login values for your server.
$mysqlLink = mysql_connect( 'localhost' , 'username' , 'password' );
// Select mySQL Database.
mysql_select_db(
'examples' , $mysqlLink );
// Create Query To Create Table.
$sql = 'CREATE TABLE `table0` ('
. ' `ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, '
. ' `Name` TEXT NOT NULL, '
. ' `Age` INT NOT NULL'
. ' )'
. ' TYPE = myisam;';
// Issue Query.
mysql_query( $sql );
// Close mySQL Connection.
mysql_close( $mysqlLink );
?>
// Open Connection To mySQL.
// Replace 'username' and 'password' with appropiate login values for your server.
$mysqlLink = mysql_connect( 'localhost' , 'username' , 'password' );
// Select mySQL Database.
mysql_select_db(
// Create Query To Create Table.
$sql = 'CREATE TABLE `table0` ('
. ' `ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, '
. ' `Name` TEXT NOT NULL, '
. ' `Age` INT NOT NULL'
. ' )'
. ' TYPE = myisam;';
// Issue Query.
mysql_query( $sql );
// Close mySQL Connection.
mysql_close( $mysqlLink );
?>
What have we done??? You can pretty much guess. Let's divide the code into sections and explain what is going on in each section so that things are easier to understand.
QUOTE
<?php
// Open Connection To mySQL.
// Replace 'username' and 'password' with appropiate login values for your server.
$mysqlLink = mysql_connect( 'localhost' , 'username' , 'password' );
// Select mySQL Database.
mysql_select_db( 'examples' , $mysqlLink );
?>
// Open Connection To mySQL.
// Replace 'username' and 'password' with appropiate login values for your server.
$mysqlLink = mysql_connect( 'localhost' , 'username' , 'password' );
// Select mySQL Database.
mysql_select_db( 'examples' , $mysqlLink );
?>
Basically what we are doing here is opening a link to mySQL on the first line. This function, mysql_connect takes three arguments; the address of the mySQL server; this will usually be 'localhost', the User Name, and the Password for the mySQL server.
After this, we selected the database we previously created so we can issue queries to it. Selecting the database is only required once during an entire script's execution, unless the mySQL connection is closed.