 |
|
PHP Tutorial #7 So now we can connect to databases but what we really need to do is create our own. The code to create a database called fone is as follows:- <?php
$domain = "localhost";
$user = "linux";
$password = "linux";
$new_db = "fone";
$conn = mysql_connect($domain, $user, $password)
OR DIE("Sorry - could not connect to MySQL");
if($new_db)
{
$sql="CREATE DATABASE $new_db";
$result=mysql_query($sql,$conn);
if(!$result)
{
DIE("Sorry - could not create database");
}
}
$result2=@mysql_list_dbs($conn);
for($row=0; $row < mysql_num_rows($result2); $row++)
{
$list .= mysql_tablename($result2, $row)." | ";
}
?>
<html>
<head>
<title>Creating databases</title>
</head>
<body>
<form action="<?php echo($PHP_SELF); ?>" method="post">
Current databases: <?php echo($list); ?> <hr>
</form>
</body>
</html>
The name of the database we wish to create is held in the variable $new_db at the top. We then connect to mysql and report an error if we can't. $result2 holds the result of calling mysql_list_dbs which lists databases and these are extracted and appended to $list in the loop. Once the database has been created (using the $sql statement), refreshing the browser will result in an error as it has already been created. To be sure that this is working, perhaps it would be nice to delete the database again. Here is how:- <?php
$domain = "localhost";
$user = "linux";
$password = "linux";
$new_db = "fone";
$conn = @mysql_connect($domain, $user, $password)
OR DIE("Sorry - could not connect to MySQL");
if($new_db)
{
$sql = "DROP DATABASE $new_db";
$result = mysql_query($sql,$conn);
if(!$result)
{
DIE("Sorry - could not drop database");
}
}
$result2 = @mysql_list_dbs($conn);
for($row = 0; $row < mysql_num_rows($result2); $row++)
{
$list .= mysql_tablename($result2, $row)." | ";
}
?>
<html>
<head>
<title>Deleting databases</title>
</head>
<body>
<form action="<?php echo($PHP_SELF); ?>" method="post">
Current databases: <?php echo($list); ?> <hr>
</form>
</body>
</html>
This works in a similar way to the previous program except that the $sql contains a different command to delete (or drop) the database. Again. once deleted, a browser refresh will show an error. Now you can create and delete the same database over and over again particularly to test the robustness of the error routines. Now we perhaps would like to add tables. <?php
$domain = "localhost";
$user = "linux";
$password = "linux";
$new_db = "fone";
$sql="CREATE TABLE fone(id int auto_increment,
name varchar(30) not null,
number varchar(20) not null,
primary key(id))";
$conn = mysql_connect($domain, $user, $password)
OR DIE("Sorry - could not connect to MySQL");
if($new_db)
{
$result=mysql_select_db($new_db, $conn);
if(!$result)
{
DIE("Sorry - could not select database");
}
$result=mysql_query($sql,$conn);
if(!$result)
{
DIE("Sorry - could not create table");
}
}
$result2=@mysql_list_tables($new_db);
for($row=0; $row < mysql_num_rows($result2); $row++)
{
$list .= mysql_tablename($result2, $row)." | ";
}
?>
<html>
<head>
<title>Creating tables</title>
</head>
<body>
Current tables: <?php echo($list); ?> <hr>
</body>
</html>
We first logon to the mysql database engine as usual then select the database to use. Now we can try to write a table to the selected database using an SQL command held in $sql which looks like this:-
$sql="CREATE TABLE fone(id int auto_increment, name varchar(30) not null, number varchar(20) not null, primary key(id))"; This will create a table called fone with a field called id that will automatically create an integer starting from 1 for each record. Then a name is created which is a string that must contain a value (hence the not null clause). Finally, we add a number which is held as a string for simplicity and identify that the primary key is id. A primary key is the main value that is used to identify a record because there may be duplicate names or numbers in the database whereas id is unique. $result2 holds the result of a search for tables within the database and a $list of them is created in a loop. So once the program is launched in a browser, the table fone will show up as having been created. Just to prove this, we'll delete it again:
<?php
$domain = "localhost";
$user = "linux";
$password = "linux";
$new_db = "fone";
$sql="DROP TABLE fone";
$conn = mysql_connect($domain, $user, $password)
OR DIE("Sorry - could not connect to MySQL");
if($new_db)
{
$result=mysql_select_db($new_db, $conn);
if(!$result)
{
DIE("Sorry - could not select database");
}
$result=mysql_query($sql,$conn);
if(!$result)
{
DIE("Sorry - could not drop table");
}
}
$result2=@mysql_list_tables($new_db);
for($row=0; $row < mysql_num_rows($result2); $row++)
{
$list .= mysql_tablename($result2, $row)." | ";
}
?>
<html>
<head>
<title>Deleting tables</title>
</head>
<body>
Current tables: <?php echo($list); ?> <hr>
</body>
</html>
Once again we use an SQL command (DROP TABLE fone) to delete (or drop) the table and display a blank list of tables. Note that an error occurs if you attempt to delete the table again or create it again before it has been deleted. Next time we'll get into actually putting data into the table. To give you a clue, the SQL statement to achieve this will be:- INSERT INTO fone (name,number) VALUES (fred, 01234 12345) Until the next time:- Please eMail me at: nickjc@nickjc.co.uk if you need help |
|