Archive for July, 2009
Working with Databases(PHP & MySQL)
Posted by matin in Uncategorized on July 31, 2009
Working with Databases(PHP & MySQL)
Creating a Database Using MySQL:
At the mysql> prompt that appears, enter SELECT VERSION(), CURRENT_DATE; to confirm that MySQL is working:
mysql> SELECT VERSION(), CURRENT_DATE;
+---------------+--------------+
| VERSION() | CURRENT_DATE |
+---------------+--------------+
| 5.0.20a | 2009-08-06 |
+---------------+--------------+ [1 row in set (0.05 sec)]
Want to see what databases MySQL already comes with? Enter the SHOW DATABASES command, and we'll get something like this:
mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql |
| test |
| ice |
| iu |
| matin |
+----------+[2 rows in set (0.01 sec)]
MySQL comes with two databases built-inmysql, which holds administrative data for MySQL, and test, which is a sample database. To store data about a variety of fruits and vegetables, we might create a new database named, say, "produce," with the CREATE DATABASE command:
mysql> CREATE DATABASE produce;
Query OK, 1 row affected (0.01 sec)
To make this database the default one to work with, enter the USE produce command:
mysql> USE produce
Database changed
Are there any tables in the produce database yet? Try the SHOW TABLES command:
mysql> SHOW TABLES;
Empty set (0.01 sec)
The response is "Empty set," which means there are no tables yet. To create a table named fruit, we need to create its data fields. There are various data types for fields; here are a few (we'll use strings in this example):
- VARCHAR(length). Creates a variable length string
- INT. Creates an integer
- DECIMAL(totaldigits, decimalplaces). Creates a decimal value
- DATETIME. Creates a date and time object, such as 2009-8-15 20:00:00
Here’s how to create the fruit table with name and number fields, both stored in strings:
mysql> CREATE TABLE fruit (name VARCHAR(20), number VARCHAR(20));
Query OK, 0 rows affected (0.13 sec)
mysql> SHOW TABLES;
+-------------------+
| Tables_in_produce |
+-------------------+
| fruit |
+-------------------+[1 row in set (0.00 sec)]
To get a description of this new table, use the DESCRIBE command:
mysql> DESCRIBE fruit;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| number | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+[2 rows in set (0.01 sec)]
Switch to the produce database and use INSERT to load the data into the fruit table:
mysql> USE produce
Database changed
mysql> INSERT INTO fruit VALUES (‘apples’, ’1020′);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO fruit VALUES (‘oranges’, ’3329′);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO fruit VALUES (‘bananas’, ’8582′);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO fruit VALUES (‘pears’, ’235′);
Query OK, 1 row affected (0.00 sec)
That’s it; we can list the data in this table with SELECT to confirm that everything’s as it should be, and then end the session with quit, completing our new database table:
mysql> SELECT * FROM fruit;
+———+——–+
| name | number |
+———+——–+
| apples | 1020 |
| oranges | 3329 |
| bananas | 8582 |
| pears | 235 |
+———+——–+ —— [4 rows in set (0.00 sec)]
Displaying a Data Table(PHP action in a browser.)
We’re ready to see some SQL and PHP action in a browser. Here, we’re going to extract the fruit table from the produce database and display its data in an HTML table. We’ll start by connecting, selecting the database, and getting an object named $result that corresponds to the fruit table:
$connection = mysql_connect("localhost","root","")
or die ("Couldn't connect to server");
$db = mysql_select_db("produce", $connection)
or die ("Couldn't select database");
$query = "SELECT * FROM fruit";
$result = mysql_query($query)
or die("Query failed: " . mysql_error());
Now we can use the mysql_fetch_array function to get successive rows from the table in a while loop and get the name and number fields from each row by name like this:
while ($row = mysql_fetch_array($result))
{
echo "<TR>";
echo "<TD>", $row['name'], "</TD><TD>", $row['number'], "</TD>";
echo "</TR>"; }
Example 1-1. Displaying a database table, phpdatatable.php
<HTML>
<HEAD><TITLE>Displaying tables with MySQL</TITLE> </HEAD>
<BODY> <CENTER><H1>Displaying tables with MySQL</H1>
<?php
$connection = mysql_connect("localhost","root","")
or die ("Couldn't connect to server");
$db = mysql_select_db("produce", $connection)
or die ("Couldn't select database");
$query = "SELECT * FROM fruit";
$result = mysql_query($query)
or die("Query failed: ".mysql_error());
echo "<TABLE BORDER='1'>"; echo "<TR>";
echo "<TH>Name</TH><TH>Number</TH>"; echo "</TR>";
while ($row = mysql_fetch_array($result))
{ echo "<TR>";
echo "<TD>", $row['name'], "</TD><TD>",$row['number'],”</td>”;
echo "</TR>"; }
echo "</TABLE>";
mysql_close($connection);
?> </CENTER></BODY></HTML>
Updating Database Useing PHP Scripts:
Now we can update the value in the pears row of the fruit table like this, where we’re setting the value of the number field to 234:
$query = "UPDATE fruit SET number = 234 WHERE name = 'pears'";
$result = mysql_query($query)
or die("Query failed: ".mysql_error());
Example 1-2. Updating database data, phpdataupdate.php
<HTML>
<HEAD> <TITLE>Updating database data</TITLE> </HEAD>
<BODY><CENTER> <H1>Updating database data</H1>
<?php
$connection = mysql_connect("localhost","root","")
or die ("Couldn't connect to server");
$db = mysql_select_db("produce",$connection)
or die ("Couldn't select database");
$query = "UPDATE fruit SET number = 234 WHERE name =
'pears'";
$result = mysql_query($query)
or die("Query failed: ".mysql_error());
$query = "SELECT * FROM fruit";
$result = mysql_query($query)
or die("Query failed: " . mysql_error());
echo "<TABLE BORDER='1'>"; echo "<TR>";
echo "<TH>Name</TH><TH>Number</TH>"; echo "</TR>";
while ($row = mysql_fetch_array($result))
{
echo "<TR>";
echo "<TD>", $row['name'], "</TD><TD>",$row['number'], "</TD>";
echo "</TR>"; }
echo "</TABLE>";
mysql_close($connection);
?> </CENTER> </BODY></HTML>
Inserting New Data Useing PHP Scripts:
To create a new row for apricots, we can use the SQL INSERT statement like this:
$query = "INSERT INTO fruit (name, number) VALUES('apricots',
'203')";.......
Then we execute this new SQL query to insert the new row:
$query = "INSERT INTO fruit (name, number) VALUES('apricots',
'203')";
$result = mysql_query($query)
or die("Query failed: " . mysql_error());
Example 1-3. Inserting new data, phpdatainsert.php
<HTML>
<HEAD><TITLE> Inserting new data</TITLE></HEAD>
<BODY> <CENTER><H1>Inserting new data</H1>
<?php
$connection = mysql_connect("localhost","root","")
or die ("Couldn't connect to server");
$db = mysql_select_db("produce",$connection)
or die ("Couldn't select database");
$query = "INSERT INTO fruit (name, number) VALUES('apricots',
'203')";
$result = mysql_query($query)
or die("Query failed: " . mysql_error());
$query = "SELECT * FROM fruit";
$result = mysql_query($query)
or die("Query failed: " . mysql_error());
echo "<TABLE BORDER='1'>";
echo "<TR>";
echo "<TH>Name</TH><TH>Number</TH>";
echo "</TR>";
while ($row = mysql_fetch_array($result))
{
echo "<TR>";
echo "<TD>", $row['name'], "</TD><TD>",
$row['number'], "</TD>";
echo "</TR>";
}
echo "</TABLE>";
mysql_close($connection);
?>
</CENTER></BODY></HTML>
Deleting Data(Useing PHP Scripts):
Then we simply execute that new SQL query to delete the apricots row:
$query = "DELETE FROM fruit WHERE name = 'apricots'";
$result = mysql_query($query)
or die("Query failed: " . mysql_error());
Example 1-4. Deleting data, phpdatadelete.php
<HTML>
<HEAD><TITLE>Displaying tables with MySQL</TITLE> </HEAD>
<BODY>
<CENTER> <H1>Displaying tables with MySQL</H1>
<?php
$connection = mysql_connect("localhost","root","")
or die ("Couldn't connect to server");
$db = mysql_select_db("produce",$connection)
or die ("Couldn't select database");
$query = "DELETE FROM fruit WHERE name = 'apricots'";
$result = mysql_query($query)
or die("Query failed: " . mysql_error());
$query = "SELECT * FROM fruit";
$result = mysql_query($query)
or die("Query failed: " . mysql_error());
echo "<TABLE BORDER='1'>"; echo "<TR>";
echo "<TH>Name</TH><TH>Number</TH>"; echo "</TR>";
while ($row = mysql_fetch_array($result))
{
echo "<TR>";
echo "<TD>", $row['name'], "</TD><TD>",
$row['number'], "</TD>";
echo "</TR>";
}
echo "</TABLE>";
mysql_close($connection);
?> </CENTER></BODY></HTML>
Creating a New Table(Useing PHP Scripts):
To create a new table, use the CREATE TABLE SQL statement, as here, where we’re configuring the name and number fields in the new vegetables table:
$query = "CREATE TABLE vegetables (name VARCHAR(20), number
VARCHAR(20))";
$result = mysql_query($query)
or die("Query failed: " . mysql_error());
Now we can insert data into the new vegetables table with INSERT:
$query = "INSERT INTO vegetables (name, number) VALUES('corn',
'2083')";
$result = mysql_query($query)
or die("Query failed: " . mysql_error());
Example 1-5. Creating a new table, phpdatacreate.php
<HTML>
<HEAD> <TITLE>Creating a new table</TITLE> </HEAD>
<BODY>
<CENTER><H1>Creating a new table</H1>
<?php
$connection = mysql_connect("localhost","root","")
or die ("Couldn't connect to server");
$db = mysql_select_db("produce",$connection)
or die ("Couldn't select database");
$query = "CREATE TABLE vegetables (name VARCHAR(20),
number VARCHAR(20))";
$result = mysql_query($query)
or die("Query failed: " . mysql_error());
$query = "INSERT INTO vegetables (name, number) VALUES(
'corn', '2083')";
$result = mysql_query($query)
or die("Query failed: " . mysql_error());
$query = "INSERT INTO vegetables (name, number)
VALUES('spinach', '1993')";
$result = mysql_query($query)
or die("Query failed: " . mysql_error());
$query = "INSERT INTO vegetables (name, number)
VALUES('beets', '437')";
$result = mysql_query($query)
or die("Query failed: " . mysql_error());
$query = "SELECT * FROM vegetables";
$result = mysql_query($query)
or die("Query failed: " . mysql_error());
echo "<TABLE BORDER='1'>";
echo "<TR>";
echo "<TH>Name</TH><TH>Number</TH>";
echo "</TR>";
while ($row = mysql_fetch_array($result))
{
echo "<TR>";
echo "<TD>", $row['name'], "</TD><TD>",
$row['number'], "</TD>";
echo "</TR>";
}
echo "</TABLE>";
mysql_close($connection);
?>
</CENTER></BODY></HTML>
We can even create a whole new database from PHP. Here’s an example, where we’re creating a database named foods using the CREATE DATABASE command:
$query = "CREATE DATABASE IF NOT EXISTS foods";
$result = mysql_query($query)
or die("Query failed: " . mysql_error());
And we'll add a new table to this database, snacks:
$db = mysql_select_db("foods", $connection)
or die ("Couldn't select database");
$query = "CREATE TABLE snacks (name VARCHAR(20), number
VARCHAR(20))";
$result = mysql_query($query)
or die("Query failed: " . mysql_error());
Example 1-6. Creating a new database, phpdatacreatedb.php
<HTML>
<HEAD><TITLE>Creating a new database</TITLE> </HEAD>
<BODY> <CENTER><H1>Creating a new database</H1>
<?php
$connection = mysql_connect("localhost","root","")
or die ("Couldn't connect to server");
$query = "CREATE DATABASE IF NOT EXISTS foods";
$result = mysql_query($query)
or die("Query failed: " . mysql_error());
$db = mysql_select_db("foods", $connection)
or die ("Couldn't select database");
$query = "CREATE TABLE snacks (name VARCHAR(20), number
VARCHAR(20))";
$result = mysql_query($query)
or die("Query failed: " . mysql_error());
$query = "INSERT INTO snacks (name, number) VALUES('tacos',
'2843')";
$result = mysql_query($query)
or die("Query failed: " . mysql_error());
$query = "INSERT INTO snacks (name, number) VALUES('pizza',
'1955')";
$result = mysql_query($query)
or die("Query failed: " . mysql_error());
$query = "INSERT INTO snacks (name, number)
VALUES('cheeseburgers', '849')";
$result = mysql_query($query)
or die("Query failed: " . mysql_error());
$query = "SELECT * FROM snacks";
$result = mysql_query($query)
or die("Query failed: " . mysql_error());
echo "<TABLE BORDER='1'>";
echo "<TR>";
echo "<TH>Name</TH><TH>Number</TH>";
echo "</TR>";
while ($row = mysql_fetch_array($result))
{
echo "<TR>";
echo "<TD>", $row['name'], "</TD><TD>",
$row['number'], "</TD>";
echo "</TR>";
}
echo "</TABLE>";
mysql_close($connection);
?>
</CENTER> </BODY></HTML>
Sorting Data
Then when we read wer data, use the ORDER BY clause to indicate what field we want to sort on. For example, if we want to sort by name of the various fruits, use this statement:
$query = "SELECT * FROM fruit ORDER BY name";
$result = mysql_query($query)
or die("Query failed: ".mysql_error());
Example 1-7. Sorting data, phpdatasort.php
<HTML>
<HEAD> <TITLE>Sorting data</TITLE> </HEAD>
<BODY> <CENTER> <H1> Sorting data</H1>
<?php
$connection = mysql_connect("localhost","root","")
or die ("Couldn't connect to server");
$db = mysql_select_db("produce",$connection)
or die ("Couldn't select database");
$query = "SELECT * FROM fruit ORDER BY name";
$result = mysql_query($query)
or die("Query failed: ".mysql_error());
echo "<TABLE BORDER='1'>";
echo "<TR>";
echo "<TH>Name</TH><TH>Number</TH>";
echo "</TR>";
while ($row = mysql_fetch_array($result))
{
echo "<TR>";
echo "<TD>", $row['name'], "</TD><TD>",
$row['number'], "</TD>";
echo "</TR>";
}
echo "</TABLE>";
mysql_close($connection);
?> </CENTER></BODY></HTML>
Displaying a Table with DB:
Now we can use the query method of the $db object to execute a SQL query like this one, which reads the entire fruit table:
require 'DB.php';
$db = DB::connect('mysql://root:@localhost/produce');
$query = "SELECT * FROM fruit";
$result = $db->query($query);
Example 1-8. Displaying a table with DB, phpdb.php
<HTML>
<HEAD><TITLE>Using DB to display a table</TITLE> </HEAD>
<BODY> <CENTER> <H1>Using DB to display a table</H1>
<?php
require 'DB.php';
$db = DB::connect('mysql://root:@localhost/produce');
$query = "SELECT * FROM fruit";
$result = $db->query($query);
echo "<TABLE BORDER='1'>";
echo "<TR>";
echo "<TH>Name</TH><TH>Number</TH>";
echo "</TR>";
while ($row = $result->fetchRow(DB_FETCHMODE_ASSOC))
{
echo "<TR>";
echo "<TD>", $row['name'], "</TD><TD>", $row['number'],
"</TD>";
echo "</TR>";
}
echo "</TABLE>";
?>
</CENTER> </BODY></HTML>
Inserting New Data with DB:
And now we can execute this query with the $db->query method:
$db = DB::connect('mysql://root:@localhost/produce');
$query = "INSERT INTO fruit (name, number)
VALUES('apricots', '203')";
$result = $db->query($query);
Example 1-9. Inserting database data, phpdbinsert.php
<HTML>
<HEAD> <TITLE>Using DB to insert data</TITLE> </HEAD>
<BODY>
<CENTER><H1>Using DB to insert data</H1>
<?php
require 'DB.php';
$db = DB::connect('mysql://root:@localhost/produce');
$query = "INSERT INTO fruit (name, number)
VALUES('apricots', '203')";
$result = $db->query($query);
$query = "SELECT * FROM fruit";
$result = $db->query($query);
echo "<TABLE BORDER='1'>";
echo "<TR>";
echo "<TH>Name</TH><TH>Number</TH>";
echo "</TR>";
while ($row = $result->fetchRow(DB_FETCHMODE_ASSOC))
{
echo "<TR>";
echo "<TD>", $row['name'], "</TD><TD>",
$row['number'], "</TD>";
echo "</TR>";
}
echo "</TABLE>";
?>
</CENTER> </BODY></HTML>
Updating Data with DB
Here’s another oneupdating data with the DB module. As before, we’ll reduce the number of pears in the fruit table from 235 to 234. Because we’ve got a handle on using SQL with the DB module, there’s no problem here either; just set up the correct SQL query and then execute that query:
$query = "UPDATE fruit SET number = 234 WHERE name = 'pears'";
$result = $db->query($query);
Example 1-10. Updating database data, phpdbupdate.php
<HTML>
<HEAD><TITLE>Using DB to update data </TITLE> </HEAD>
<BODY>
<CENTER><H1>Using DB to update data</H1>
<?php
require 'DB.php';
$db = DB::connect('mysql://root:@localhost/produce');
$query = "UPDATE fruit SET number = 234 WHERE name = 'pears'";
$result = $db->query($query);
$query = "SELECT * FROM fruit";
$result = $db->query($query);
echo "<TABLE BORDER='1'>"; echo "<TR>";
echo "<TH>Name</TH><TH>Number</TH>"; echo "</TR>";
while ($row = $result->fetchRow(DB_FETCHMODE_ASSOC))
{
echo "<TR>";
echo "<TD>", $row['name'], "</TD><TD>",
$row['number'], "</TD>";
echo "</TR>";
}
echo "</TABLE>";
?> </CENTER></BODY></HTML>
The DB module also includes a DB::isError method to handle errors. Whenever we get a result from a DB module method, we can pass it to the DB::isError method. If that method returns trUE, there was an error, and we can display the appropriate error message with the result object’s getMessage method:
$db = DB::connect('mysql://root:@localhost/produce');
if(DB::isError($db)){
die($db->getMessage());
}
Now that we can execute SQL statements using the DB module, we have access to most database applicationsall we have to do to select a different type of database server is to change the name of the server type in the connection string.