Tutorials

Retrieving data from a database

To create a web page that returns results of a database query, you would need to create a script that performs the following steps:

1. Connect to a database server.
2. Select a database.
3. Query the database.
4. Retrieve the results of the query.
5. Print the results of the query.

In a web site with a PHP/MySQL backend, that process can expressed with the following code:

Example 2-2-1
// 1. Connect to a database server.
$Conn = mysql_connect('your-mysql-host.com', 'mysql_login', 'mysql_password');

// 2. Select a database.
mysql_select_db('mymusic', $Conn);

// 3. Query the database.
$result = mysql_query('Select * From Artists', $Conn);

// 4. Retrieve the results of the query.

while ($recordset = mysql_fetch_object($result))
{
// 5. Print the results of the query.
     echo "<p>$recordset->Name</p>";
}


For a simple query, that code suffices. But if your pages include multiple queries, and each query requires complex joins, repeating that code can get messy.

Example 2-2-2
//Connect to the server and choose the mymusic database
$Conn = mysql_connect('your-mysql-host.com', 'mysql_login', 'mysql_password');
mysql_select_db('mydatabase', $Conn);

//Retrieve artist information for the band FooBar Fighters
$query = "Select * From Artists Where Name='FooBar Fighters'";
$result = mysql_query($query, $Conn);
$recordset_artist = mysql_fetch_object($result);
echo "<p>$recordset_artist->Name</p>";

//Retrieve albums by FooBar Fighters, with track info for each album
$query = '';
$query .= "Select Al.*, Tr.* ";
$query .= "From Albums as Al Left Join Tracks as Tr ";
$query .= "on Tr.AlbumID=Al.AlbumID ";
$query .= "Where Al.ArtistID=$recordset_artist->ArtistID ";
$query .= "Order By Al.AlbumTitle, Tr.TrackNum";

$result = mysql_query($query, $Conn);

echo "<p>";
while ($recordset_albums = mysql_fetch_object($result))
{
      [ ...display results... ]
}
echo "</p>";


In Vigilante, that code can be reduced by a few lines.

Example 2-2-3
include("$DOCUMENT_ROOT/includes/common.php");
SQL();

//Retrieve artist information for the band FooBar Fighters

$recordset_artist = SelectRecord("Artists", "Name='FooBar Fighters'", "rs");
echo "<p>$recordset_artist->Name</p>";

//Retrieve albums by FooBar Fighters, with track info for each album

$result = SelectJoinedRecord(
"Albums as Al Left Join Tracks as Tr on Tr.AlbumID=Al.AlbumID",
"Al.*, Tr.*",
"Where Al.ArtistID=$recordset_artist->ArtistID Order By Al.AlbumTitle, Tr.TrackNum",
"row");

echo "<p>";
while ($recordset_albums = mysql_fetch_object($result))
{
      [ ...display results... ]
}
echo "</p>";


In Example 2-2-3, the script first reads in Vigilante's libraries. A call to the function SQL() establishes a connection to the database.

In the first query, Vigilante pieces together a Select query with a specified table ("Artists") and a Where clause ("Name='FooBar Fighters'"), then returns the result as an object. For Select queries, Vigilante can return results as a resource or as an object.

In the second query, Vigilante pieces together a more complex Select query and returns the result as a resource, which can then be used with mysql_fetch_object() to write the output.