Write to UsCreating a Simple Photo Album with PHP and MySQL (Part 2)
Introduction
The Database
Getting Access to the Database
The End
In the previous tutorial, we created a primitive Online photo album using a DataSet and a DataReader. If you read the previous tutorial, you may remember that the DataReader ran into problems when the database had a null value for the field called width, which allowed null values. This approach, although similar, uses sqlDataReader and has ASP:Label replacing the DataReader.
Once again the first version of our photo album is a shadow of what the last version will be. You can't update the pictures, and you can't insert new pictures, and you won't be able to click on any links. In other words, this photo album is as simple as a photo album can be, and that makes it easier for us to understand what's going on.
The first thing we need to create our simple photo album is a SQL database. The previous tutorial tells you how. An image must have a width and a height if you want it to fix in a webpage. Sometimes, however, the image is small enough to fit on the webpage without being modified through HTML. In cases like this, you may want the width and height to be NULL, and that could become a problem if you are using a Repeater or a DataGrid to display your album pictures.
Getting Access to the Database
Once again, we will use C-Sharp for our script. We need two namespaces: System.Data and System.Data.SqlClient. The latter is the one that contains most of the ADO.NET classes, and System.Data.SqlClient contains the classes we will need to interact with the SQL database.
OnLoadData is the function that will be called as soon as the page loads for the first time. Inside that function we will get access to the database that contains the landscape pictures.
<%@ Page Language="C#" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">
public void Page_Load(Object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
OnLoadData(sender, e);
}
}
| public void OnLoadData(Object sender, EventArgs e)
|
OnLoadData is the function that gets called when the page is loaded for the first time. Once again we use SqlConnection to connect to the database. However in the previous tutorial, we used SqlDataAdapter because this is the class that allows the Dataset and the database to communicate. But now we are using a sqlDataReader, which uses SqlCommand. The sqlDataReader class reads a stream of rows from a SQL Server database. Notice that once declared, dr gets data from SqlCommand, which in turn uses the ExecuteReader() method to get information from the database. We use while(dr.Read()) to extract everything inside the Landscapes database, and the data is sent to a function called BuildAlbum (more on this function later). Pic_Album is the id-name of the ASP:label control that will put the data from Landscapes on the webpage. When this process has been completed, dr will be closed, and the connection will be closed.
|
|
private String BuildAlbum(SqlDataReader dr) |
The function BuildMoreInfoText is declared as a private String. The StringBuider class is part of the System.Text namespace, and is used to append, insert, or remove characters from a string. In our case, we use the Append method to add data to the StringBuilder sb.
Notice the separation between the ""s in landscape.jpg. Even though it is unlikely that this will affect the performance of the script, it is not exactly pleasing. This is the reason why you see this: dy = dr["filename"].ToString().Trim(); dr["filename"] is turned into a string and then, using the Trim() method from the String class, we remove the extra spaces that originate not from the script but from the SQL database itself. We have created an entire table with pictures without using a repeater, or a DataGrid. Now the sb StringBuilder is turned into a string and returned to Pic_Album.Text. The sb.ToString() is what people will see when they get access to the webpage. Nothing more than tables with pictures in them. |
What remains is simple HTML, except for the asp:Label control whose id is MoreInfo. This control will receive the data coming from BuildAlbum(dr), which in turn receives data created by the StringBuilder sb using data coming from the SqlDataReader dr. Sounds complex, but it isn't. Read the previous tutorial and then this one, and you'll get a pretty good idea of how you want to design the basis for an Online Photo Album.
<html>
<head>
<title>Photo Album</title> </head>
<body style="FONT-SIZE: 9pt; FONT-FAMILY: arial" bgcolor="ivory">
<h2>Landscape Pictures
</h2>
<asp:Label id="MoreInfo" runat="server"></asp:Label>
</body>
</html>