MySQL Database and PHP
Almost all websites have content which is dynamic. I mean content is into database and PHP extract content from there.
The most used database on the internet is MySQL. Mysql is free and is the quickiest database on the web.
You can download Mysql Database from mysql.com
Mysql and PHP for beginners
This article assumes a few things.
You know how to use PHP. If you don't, the best place to start is by reading Welcome to PHP
You know how to run basic queries in a database. If you don't, I suggest you visit Getting started with SQL or parts 1 and 2 of Introduction to Databases for the Web
You have access to a server (or servers) running PHP and MySQL. If yuo don't, you can get hold of the software, for free, along with installation instructions at the PHP site and the MySQL site.
The server running PHP can connect to the server running MySQL. Test this from the command line first. If you can't connect without PHP, you're not going to be able to connect with PHP. If you're using other servers, you may need to ask the systems administrator for help
There's an existing database and table already running on MySQL, which we'll use with the PHP scripts. The database is called first_test, and has a table called people with some data in it. Run the following SQL to create and populate the table:
mysql> CREATE DATABASE first_test;
Query OK, 1 row affected (0.31 sec)
mysql> USE first_test;
Database changed
mysql> CREATE TABLE people (
id int UNIQUE NOT NULL,
first_name varchar(40),
surname varchar(50),
PRIMARY KEY(id)
);
Query OK, 0 rows affected (0.24 sec)
mysql> INSERT INTO people VALUES(1,'Ann','Brache');
Query OK, 1 row affected (0.09 sec)
mysql> INSERT INTO people VALUES(2,'Narcizo','Levy');
Query OK, 1 row affected (0.02 sec)
mysql> INSERT INTO people VALUES(3,'Tony','Crocker');
Query OK, 1 row affected (0.00 sec)
Your table should now look as follows:
mysql> SELECT * FROM people;
+----+------------+---------+
| id | first_name | surname |
+----+------------+---------+
| 1 | Ann | Brache |
| 2 | Narcizo | Levy |
| 3 | Tony | Crocker |
+----+------------+---------+
3 rows in set (0.19 sec)
Connecting
There are many MySQL functions in PHP. Perhaps you've taken a look at the official documentation and been intimidated by what looks like an endless task. But the good news is that to perform basic queries from within MySQL is very easy. This article will show you how to get up and running. Once you're comfortable with the basics, you can start to investigate the other functions, and you'll see that many of them are just duplicate ways of doing the same thing. Let's get started. The first thing to do is connect to the database.
All mysql functions begin with mysql_, so it comes as no surprise that the function to connect to MySQL is called mysql_connect. Let's assume you would connect to MySQL from the server you're running PHP with the following details:
Username pee_wee
Password let_me_in
From the command line, you'd type the following:
mysql -upee_wee -p
You'd enter the password once the prompt appears (you can also enter it directly on the command line, but get into the habit of doing it this way - it's more secure!). PHP can connect in the same way. The mysql_connect() function looks as follows:
resource mysql_connect ( [string server [, string username [, string password [, bool new_link [, int client_flags]]]]])
This function returns a resource which is a pointer to the database connection. It's also called a link identifier, or a database handle, and we'll use it in later functions.
Let's replace your connection details, and run this in a script:
<?php
$username = "pee_wee";
$password = "let_me_in";
$hostname = "localhost";
$dbh = mysql_connect($hostname, $username, $password)
or die("Unable to connect to MySQL");
print "Connected to MySQL<br>";
// you're going to do lots more here soon
mysql_close($dbh);
?>
All going well, you should see "Connected to MySQL" when you run this script. If you can't connect to the server, make sure your password, username and hostname are correct, and that you've copied the script exactly.
The last line of the script contains another MySQL function -
mysql_close(). Although this isn't strictly speaking necessary, PHP will automatically close the connection when the script ends, you should get into the habit of closing what you open. If you start developing more serious applications, or move to other, less tolerant languages, you will find the transition more difficult if you haven't learnt the basics well from the beginning.
Once you've connected, you're going to want to select a database to work with. Let's assume the database is called first_test. To start working in this database (the equivalent of typing USE first_test in MySQL), you'll need the mysql_select_db() function.
bool mysql_select_db ( string database_name [, resource link_identifier])
To change to the first_test database, add the
mysql_select_db() function call to your script, as follows:
<?php
$username = "pee_wee";
$password = "let_me_in";
$hostname = "localhost";
$dbh = mysql_connect($hostname, $username, $password)
or die("Unable to connect to MySQL");
print "Connected to MySQL<br>";
$selected = mysql_select_db("first_test",$dbh)
or die("Could not select first_test");
// you're going to do lots more here soon
mysql_close($dbh);
?>
