One of the most compelling things PHP has going for it is it support for a wide variety of databases. And this week, PHP 101 is going to take advantage of that database support to create dynamic data-driven Web sites. This primer covers different techniques to select, insert and delete records, together with some tips to track and squash bugs when building SQL-driven sites.
You can use PHP's mysql_fetch_row() function to obtain a simple array of values, and then use these values according to the array index - a slightly different variation of the technique used above. Take a look:
<html>
<head>
</head>
<body>
<?php
// set up some variables
//
server name
$server = "localhost";
// username
$user = "test";
// password
$pass
= "test";
// database to query
$db = "php101";
// open a connection to the database
$connection
= mysql_connect($server, $user, $pass) or
die("Invalid server
or user");
//
formulate the SQL query
$query = "select * from url_list" or die("Error in query");
//
run the query on the database
$result = mysql_db_query($db, $query, $connection)
or
die("Error in query");
// display the result
echo "<table width=450 border=1
cellspacing=0
cellpadding=0>";
echo "<tr><td width=150 align=left>User</td><td
width=100
align=left>Site
#1</td><td width=100 align=left>Site #2</td><td
width=100
align=left>Site
#3</td></tr>";
// with a while loop
// this loop
will iterate as many times as there are records
// and return an array named $myrow
while
($myrow = mysql_fetch_row($result))
{
echo "<tr><td width=150 align=left>$myrow[0]</td><td
width=100
align=left><a href=$myrow[2]>$myrow[1]</a></td><td width=100
align=left><a
href=$myrow[4] >$myrow[3]</a></td><td width=100 align=left><a
href=$myrow[6]>
$myrow[5]</a></td></tr>";
}
// memory flush
mysql_free_result($result);
?>
</body>
</html>
You can also use PHP's mysql_fetch_row() and list() functions to obtain a simple
array of values, and then assign these values to different variables - another variation of the technique we've shown you above. Take a look - only the "while" loop will change:
<html>
<head>
</head>
<body>
<?php
// set up some variables
//
server name
$server = "localhost";
// username
$user = "test";
// password
$pass
= "test";
// database to query
$db = "php101";
// open a connection to the database
$connection = mysql_connect($server, $user, $pass) or die("Invalid server
or
user");
// formulate the SQL query
$query = "select * from url_list" or die("Error
in query");
// run the query on the database
$result = mysql_db_query($db, $query,
$connection) or die("Error in query");
// display the result
echo "<table
width=450 border=1 cellspacing=0 cellpadding=0>";
echo "<tr><td width=150
align=left>User</td><td width=100 align=left>Site
#1</td><td width=100
align=left>Site #2</td><td width=100 align=left>Site
#3</td></tr>";
//
with a while loop
// this loop will iterate as many times as there are records
//
and the values returned will be assigned to different
// variables via list()
while
(list($user, $title1, $url1, $title2, $url2, $title3, $url3) =
mysql_fetch_row($result))
{
echo "<tr><td width=150 align=left>$user</td><td width=100 align=left><a
href=$url1>$title1</a></td><td
width=100 align=left><a
href=$url2>$title2</a></td><td width=100 align=left><a
href=$url3>$title3</a></td></tr>";
}
// memory flush
mysql_free_result($result);
?>
</body>
</html>
In this case, the list() function is used to assign different elements of the
result set t o PHP variables, which are then used when rendering the page.
