Webmaster Resources Center

Welcome to the Bravenet Resource Center. Use these resources to help make your web pages more advanced and fun to use.

Articles & Tutorials

PHP and MySQL Basics

by Jerrett Taylor


« Prev · 1 · 2 · 3 · 4 · Next »

Summary : This is an introduction to using MySQL with PHP. This article will take you through creating a table, inserting data into it, and retrieving data. This article assumes you have a host with PHP and MySQL setup, and access to both.

There is no way I can possibly cover everything you might want to do with PHP and MySQL in a single article, so I will focus on the basics. This article will cover the following topics:

  • Creating a table in MySQL
  • Inserting data into your MySQL table
  • Pulling multiple rows from MySQL
  • Pulling a single row from MySQL

Multiple rows might be used in something like a news script, search results, or link database, and single row might be used for something like displaying user information or the article that you are reading right now.

This article is going to assume you have an understanding of the basics of PHP. If you do not you might still be able to get through this without any problems, but you may need to consult the php manual if you get stuck!

Creating your MySQL table can be done a few different ways. The three most common ways would be to do it direct from MySQL's command line, to use a visual interface like PhpMyAdmin (www.phpmyadmin.net), or to code the create table into a PHP file using mysql_query()

When creating a SQL tables, it is important that you familiarize yourself with the available column types. A full list is available on MySQL's site, and the link is provided below, however for this article I will explain the ones which we are using.

Replacing USER, PASS and DATABASENAME in the code below and running the script will create the table "news" in your database. You can also create the table using PhpMyAdmin or directly through MySQL, however I am giving the code below as an example because it doesn't require logging into MySQL, or downloading PhpMyAdmin.

<?
// connect to mysql and select database
$db = mysql_pconnect("localhost","USER","PASS");
mysql_select_db("DATABASENAME",$db);

// build query
$create = "CREATE TABLE news(
id INT(5) unsigned NOT NULL auto_increment,
author VARCHAR(32),
content TEXT,
posted DATE,
PRIMARY KEY (id));";

// run the query
mysql_query($create);
?>

A short explanation of what is being used to construct the above query:

  • INT: INT holds a normal sized integer up to 4294967295.
  • VARCHAR: VARCHAR holds a variable length character, up to 255 characters.
  • TEXT: TEXT is used for holding large amounts of data, up to 65535 characters.
  • DATE: DATE holds a date in YYYY-MM-DD format.
  • PRIMARY: this signifies which is the primary column. this must always be unique.
  • NOT NULL/NULL: Signifies weither or not the column can be null (empty/nothing).
  • auto_increment: automaticly assigns an ID for each insert.
  • unsigned: signed integers can be negatives, unsigned can't.

I'm not going to go to much into MySQL Column Types, that could be a whole article on it's own. If you want to learn more about this, the best place to do it is MySQL's manual: http://dev.mysql.com/doc/mysql/en/Column_types.html

« Prev · 1 · 2 · 3 · 4 · Next »