 |
|
PHP Tutorial #6 PHP was principally designed to make database access over the Internet that much easier and now having laid most of the foundation, we can begin to build simple database applications. A database is simply a collection of data that is assembled in a structured way (consisting of tables of data) for easy viewing using a language called Structured Query Language (SQL). But, I hear you ask, I thought we were using PHP. We are but it is sometimes easier to talk to databases in their own language and this can be achieved using PHP although PHP provides other comands so we are not reliant totally on SQL. However, the first thing to do is become familiar with using MySQL. If you have followed this tutorial then you should be able to run the MySQL monitor and get the mysql> prompt. However, this will be in root mode and it is wise to create a user. This is achieved by logging into root mode and typing at the prompt:- grant all privileges on *.* to user-name@domain identified by "password" with grant option; The user name will be your current user name and the domain is the local host. For example on my machime it would be linux@localhost with the password being anything you can easily remember more than 4 characters long and must be entered in quotes. Ensure that you add the semi-colon at the end otherwise you'll get an error. Login as your current user and enter at the mysql prompt:- mysql -u user-name -p and you will be prompted for the password. Once completed, you can try a few commands. Try this:- SHOW DATABASES; If all is well then you will get a list of names including mysql. The try:- USE mysql; followed by:- SHOW TABLES; remebering to end the line with a semi-colon each time. This will then list all the tables in the database named mysql. Typing QUIT will get you out. If you make a mistake then type /c as prompted. With those few tasks completed, it's back to PHP. Create a new file (called dbconn.php for example) and enter this into it:- <?php
$domain = "localhost";
$user = "linux";
$password = "linux";
$conn = mysql_connect( $domain, $user, $password );
if($conn)
{
$msg="Congratulations $user, You are connected to MySQL";
}
?>
<html>
<head>
<title>PHP Connection to MySQL test</title>
</head>
<body>
<h3>
<?php echo($msg); ?>
</h3>
</body>
</html>
This file is slightly different from all the rest so pay attention. The PHP code is now at the top pf the page and not contained in the main body of the HTML. However, there is an important piece of code between the H3 tags which displays a message that has been created by the PHP code further up. This makes creating PHP code a little easier particularly when it comes to putting all the PHP code in its own file (we'll come to that later). This also means that I will be showing all the code including the HTML from now on. When you run this file in a browser, it should show that you are connected to the mysql database. You will need to substitute your own user name and password (you may be worried that showing your password is inherently unsafe from a security point of view and you'd be right - we'll explore that another time). Using this command in the HTML: <?php echo($msg); ?> ties it up with the PHP code which knows what the contents of $msg is. This may take a little time to get used to but it does make life easier. So now we can connect to the database with our new user name. Now to display all the database names as we did before in the mysql monitor. This is how it's done:- <?php
$domain = "localhost";
$user = "linux";
$password = "linux";
$conn = mysql_connect($domain, $user, $password)
OR DIE("Sorry - could not connect to MySQL");
$sql="SHOW DATABASES";
$result=mysql_query($sql,$conn);
if(!$result)
{
DIE("Sorry - could not list databases");
}
// construct a list of DB's
for($row=0; $row < mysql_num_rows($result); $row++)
{
$db_list .= mysql_tablename($result, $row)."<br>";
}
?>
<html>
<head>
<title>PHP List MySQL DB's</title>
</head>
<body>
<h3>
<?php echo($db_list); ?>
</h3>
</body>
</html>
which on my system displayed:- mysql which are the databases currently available on my system. Now for the tables inside the databases. <?php
$domain = "localhost";
$user = "linux";
$password = "linux";
$conn = mysql_connect($domain, $user, $password)
OR DIE("Sorry - could not connect to MySQL");
mysql_select_db("mysql")
OR DIE("Sorry - could not connect to mysql database");
$result=mysql_list_tables("mysql");
$list = "mysql<BR>";
for($num=0; $num < mysql_num_rows($result); $num++)
{
$list .= " - ".mysql_tablename($result,$num)."<br>";
}
?>
<html>
<head>
<title>PHP List MySQL Tables</title>
</head>
<body>
<?php echo($list); ?>
</body>
</html>
Here we are selecting the database to use (in this case mysql) then listing alll the tables within it. The loop cycles throught the tables (hels in rows) and constructs a list of them for displaying. On my system, the output looks something like this: mysql That is probably enough for you to be getting on with. Next time we'll create and delete some databases. Until then... Please eMail me at: nickjc@nickjc.co.uk if you need help |
|