|
Now that you’ve learned the basics of PHP, you'll want to start back-ending your
website with database connectivity. The most common PHP-connective database
server is called mySQL and, thankfully, it is as easy to use as PHP itself! In
this tutorial you will learn how to connect to a database, extract data and
print it on a page, order data, insert data into a database, edit data and
delete data.
Connecting and selecting the database
PHP has its own function to connect to mySQL databases which, ironically enough,
is called mysql_connect(). The arguments contained within its brackets
are defined as host, user, password. I remember this by the acronym: HUP
(because it sounds cool). The host is “localhost” by default. Most web servers
have this host added if not defaulted, so we need not worry about this argument
too much. The second is the username that has permissions to access the database
you are connecting to... but at the moment we are just connecting to the
server, so this username is a username that has permissions to just connect to
the mySQL server. Fairly obviously, the password is the password for the
username; it is not encrypted in any way.
For those who don’t understand this, here’s an example of a PHP script
connecting to a database server:
<?
mysql_connect(“localhost” , “joeblog” , “opensesame”);
?> |
Now we need to select the individual database, and for this we use a function
called mysql_select_db() whose sole argument is the database name as it
appears on the server (this is case sensitive!).
Here’s some example PHP code showing us how to select a database:
<?
mysql_select_db(“joes_database”);
?> |
Now that we are successfully connected to a database, we now
can start managing our data! The 4 main mySQL commands are SELECT, INSERT,
UPDATE, and DELETE. I think it’s self explanatory what these commands do…
Selecting Data
All of the mySQL commands use the PHP function mysql_query() to execute them.
Selecting is probably one of the most commonly used mySQL commands, used to
retrieve (and often display) data from your chosen database. To enable us
to do this we will have to venture a little bit into mySQL language, but don't
worry - its not too hard!
The basic format of mySQL SELECT command is as follows:
SELECT columnname FROM tablename
You can select all of the columns by using a * instead of the column name (this
is commonly called a wildcard). You cannot just echo a mySQL command like this,
however, because if you do it will output something along the lines of 'Array
#1'. We need to play with the function mysql_fetch_array() to
actually use the data. Usually this function is used in a while() PHP
statement, which cycles through each row in a mySQL table and loads the contents
of the selected column.
You can add ORDER BY commands into the end of SELECT commands to make them order
the data that mySQL will select. The format is thus:
ORDER BY ColumnName ASC/DESC
Of course you don’t write ASC/DESC, you just choose one - ASC means ascending,
DESC means descending. Now, imagine we have a database called joeblog
containing a table of data called JoeBlog. Here’s an example of how we
cycle through the 4 rows of data that are in the table JoeBlog and column Joe:
<?
Mysql_connect(“localhost”,”joeblog”,”opensesame”) or die(mysql_error());
Mysql_select_db(“joes_database”);
$query = mysql_query(“SELECT * FROM JoeBlog ORDER BY Joe ASC”) or
die(“There was an error!”);
while( $row = mysql_fetch_array ( $query ) ) {
echo $row[‘Joe’].’ this is one of the columns.<br>’;
}
echo ‘Finished!’;
?> |
In the variable named “row” the name in the square brackets and apostrophes is
the name of the column we are printing data from. It is necessary to use all the
variables, because it makes the whole process a lot smoother, and tidier - we
don’t want a line of code with 3 or 4 functions inside each other!
And that's it - we’ve just successfully printed 4 lines of data from a database!
I didn’t introduce our friend mysql_error() before, but it tells us of the
nature of a mySQL bug if one occurs. Its a nice little debug feature
included as standard in PHP.
- Tutorial written by Scrowler
|
