PHP Database Manipulation

One of the defining features of PHP is the ease with which you can connect to and manipulate databases. PHP implements functions for connecting to a wide range of databases systems: MySQL, Oracle, MSSQL, Interbase, dBase, and many more. While there are many commercial database systems which cost thousands of dollars and provide thousands of components, the Internet community provides solutions for low-budget users as well. MySQL, for example, doesn't require you to purchase a license if you don't use it for commercial activities.
Most of the manipulating of any database is done while using SQL, which stands for Structured Query Language. This provides standard syntaxes by which different types of database can be queried. While there are a lot of extensions to SQL implemented by database systems, and some queries could work on a system but fail on another one, all of them incorporate some standard manipulating statements.
The way PHP talks to the database is simple. First you need to connect to the database system, which runs as a daemon (service for Windows) in the background. In order to do this, you must have appropriate permissions to connect to the database server from its owner. While you can always install and configure a database system on your own computer for starters, when it comes to hosting a web-site you must talk to a hosting company. They will open an account for you, and give you the information on how to connect to the database server.
Many hosting companies use MySQL, so we'll focus on using this database system. First, you must use the "mysql_connect()" function along with the server's IP, the username and the password required to connect to the server. After you connect, you can start running SQL statements as you like. Don't forget that to close the connection using "mysql_close()". You either must do this everytime you want to manipulate the database, or you use a persistent connection. This kind of connection is implemented when using the Apache web-server, and tells Apache not to terminate the connection after your script stops executing even if you call "mysql_close()". Instead, the connection is left active, waiting for another process to call mysql_pconnect(). So, using this function, you will skip the time required for your computer to connect to the database server.
if(!mysql_connect("localhost", "bob", "secret")) //tries to connect to the database server
die("Unable to connect to the database server!"); //terminates the script, and outputs the error
Depending on the hosting services, you can have access to one or more databases. But before you start changing data, you must also connect to the database of your choice. Selecting a database to work with is also accomplished using a simple function:
mysql_select_db("test_db") or die("Error when selecting the database!");
While we can treat the errors using the "die()" function, and stop the script if something bad happens, sometimes we might want to output more detailed information on the error. Whenever an operation fails, MySQL sets an error number and an error string, so we just have to read it:
if(!mysql_connect($db_ip, $db_user, $db_pass))
die("MySQL Error: " . mysql_error()); //stops the script if unable to connect to the database, and outputs the MySQL error message
All of the database manipulation is done thru SQL statements (also known as queries), which range from basic statements used to insert, modify or delete data in a table to complex statements used to retrieve some particular data using specified filters. You can find a lot of documentation on SQL on the Internet. Here are some of the most important statements:
//creates a table using the specified fields
CREATE TABLE users
(id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
username VARCHAR(20),
password VARCHAR(12),
email VARCHAR(20)
)
//adds a new record into the database
INSERT INTO my_table(id, username, password, email) VALUES (NULL, "bob", "secret", "bob@softwareprojects.org")
//modifies the password of the user "bob"
UPDATE my_table SET password = "very secret" WHERE username = "bobo"
//retrieves the email address of the user "bob"
SELECT email FROM users WHERE username = "bob"
To execute these queries, you must call the "mysql_query()" function. The result of this function is a resource that will be used with other functions, in order to access the information returned by the query.
$sql_result = mysql_query("SELECT password FROM users WHERE username='Bob'")
or die("Error or running query: " .$mysql_error())
$row = mysql_fetch_array($sql_result, MYSQL_NUM);
While it may seem that "mysql_fetch_array()" retrieves all the results from the query, you should know that it only returns one row at a time. After a call to this function, the internal index of $sql_result will be incremented by one, so the next time the next row will be retrieved, if there is any.
There are a lot of other built-in functions for MySQL, for a detailed explication of each one you should consult the PHP documentation.