DB Programming

Database programming

When PHP is brought together with a database such as MySQL it allows a lot more interactivity with the user to occur. Data can be saved from visit to visit components like forums and other features can be added to the website.

What is a database?

A database in this context is a RDBMS in that the database is made up of tables, which are related to one another by foriegn keys, so a user row can have multiple email rows relating to it. A database can be queried by using SQL which is a language to easily; add, update and select data. However SQL also allows you to alter the strucutre of a database.

PHP and MySQL

Of course PHP can handle other database engines such as Microsoft SQL Server and Oracle, plus many more. The function names for other database servers are very similar except with a different first part so mysql_connect and mssql_connect for SQL Server. In order to connect to a database you first need to connect to a database, to do this you use the mysql_connect as shown below.
$link = mysql_connect('localhost', 'username', 'password');
The variable localhost above is the server that the mysql database is located on. Localhost means that it is located on the local machine, so the same machine as the script is executing on, however this could equally be a remote server and could be indicated by the servers name or a IP address.
The variables username and password is the username and password of the mysql database server. All these details will either be initially set by you when you install MySQL or will be given to you by your web host if they you have MySQL installed. If the connection to the database wasn't successful then mysql_connect function will return a false so a faliure can be detected like so.
$link = mysql_connect('localhost', 'username', 'password');
if (!$link)
{
    die('Unable to connect to database: ' . mysql_error());
}
The die function stops execution at this point and outputs a fatal error. There would usually not be too much point in continuing execution because most scripts will be relying on the connection to the database, however this is not always the case. The mysql_error returns the last error that occured on the database as a string which provides further details as to what went wrong this function is very useful in debugging errors and should always be used when a database function fails.
The mysql_close allows you to close a connection to the database from within a script however usually this isn't neccarry as the connection will be closed anyway by PHP at the end of the script.
mysql_close($link);
Before you execute a SQL query you will need to select the MySQL database you wish to execute the query on to do this you use the mysql_select_db function which will ensure the currently open connection will use that database so for instance.
if (!mysql_select_db('phprocks'))
{
 die("Can't select phprocks database! The error occured: " . mysql_error());
}
As before if a error occurs a false is returned by the function which allows the error to be dealt with as above. A query can be executed with the mysql_query function this allows you to run a SQL query as a string.
$query = "SELECT * FROM Users";
$result = mysql_query($query);
if (!$result)
{
 die("An error occured while running the query: '" . $query . "', the error is: " . mysql_error());
}
Again this will return false if a error occurs allowing the error to be captured as above. Otherwise the mysql_query function will return a resultset, which contains all the results of the query.
There are several options in processing this data you can return the array as a associative array with the mysql_fetch_assoc function, return it as a row with the mysql_fetch_row function or return it as a object with the mysql_fetch_object function.
All three functions to almost exactly the same task but return the data in slightly different formats as can be seen below.
$query = "SELECT name, age FROM Users";
$result = mysql_query($query);

while ($row = mysql_fetch_assoc($result))
{
    echo $row["name"];
    echo $row["age"];
}
As you can see with the mysql_fetch_assoc function you access the column values of the row by passing the column name present in the query as the associative array key.
$query = "SELECT name, age FROM Users";
$result = mysql_query($query);

while ($row = mysql_fetch_row($result))
{
    echo $row[0];
    echo $row[1];
}
When using the mysql_fetch_row function you access the column values of the row by passing the index position of the column, so the first column in the query is accessed by the index 0 and the second by the index 1 etc.
$query = "SELECT name, age FROM Users";
$result = mysql_query($query);

while ($user = mysql_fetch_object($result))
{
    echo $user->name;
    echo $user->age;
}
When using the mysql_fetch_object function you access the column values of the row by accessing the properties of the returned object, were each column name is a property name.

No comments:

Post a Comment