the science bit

Submitting to and retrieving information from an sql database using Dreamweaver.

Previously, we’ve created an image upload page which adds pictures to a directory on a server. All well and good. But what we really want is to add the ability to submit information about an uploaded image to a database so we can easily display it at a later point. As part of an image gallery for instance. So lets do that.

Open the ‘uploader.php’ page you previously made (or make a copy of it if you want to keep the original) and add another text field called ‘description’ to the form.

We’re going to add a new MySQL Connection using the built-in features in Dreamweaver, so make sure you can see the Databases tab of your Application panel (Window > Databases) and then hit the small + icon. In the following window, enter all the requested information to set up your connection. For the database, use the ‘Select…’ button as this will tell you whether your connection actually works and allow you to select the correct database.

Then, in the same Application panel, goto the Server Behaviors tab and add an Insert Record behavior. Use your database connection from the drop down box and select the correct table you wish to submit to (in our case, it will be the images table we previously set up), then make sure that each field gets the correct info (e.g. des gets value from FORM.description). This will create PHP code which will extract the information from your form and submit it to the images table in the appropriate columns. ‘id’ will remain unused in relation to the form because it’s an auto-incrementing column so it generates its own value.

Returning to your code view, you’ll notice that this has created a large amount of code at the top of the page. Frankly, a lot of it is surplus and not explicitly required to submit our information to the database. There are cleaner ways to achieve this but Dreamweaver does get the job done, and it offers an easy interface to achieve many PHP/SQL related tasks so we shall persevere. That said, we do need to make some alterations to this particular code so that it works seamlessly with our upload script. Find the line that reads:

GetSQLValueString($_POST['imgFile'], "text"),

and change it to:

GetSQLValueString($filePath, "text"),

This is the line that decides what to submit to your ‘imgfile’ column, and in the first instance it obtains the information posted from your ‘imgFile’ input field. However, we want it to save the full path to the image which is why we change it to the $filepath variable which in our case will end up being ‘uploads/FILE_NAME.jpg’.

The other thing to change is the position of our original submission code (it should be top and tail ended with a comment to indicate it’s position). The reason for this is because when we edited the previous bit of code, we are telling the submission part of the code to utilise the $filepath variable but as it stands, that variable hasn’t actually been defined. So we need to place the ‘form submission code’ above everything else. Just select it all, cut it (Apple+x) and paste it at the top of your page (beneath the very first ‘connections’ line).

Going back to the very first line briefly, this is important to keep at the top as it brings in the information your page needs to connect to your database.

<?php require_once('Connections/myDB.php'); ?>

If you open the ‘myDB.php’ page which is now in a directory called Connections, you will see that it contains your database name and password. It’s important to make sure you upload this file too. So do that now. Upload your ‘uploader’ page too and point to it in a browser. You should be able to upload images as before, but behind the scenes, it’ll create an entry in your database table also. We’ll check on that shortly. As a note, it may be worth increasing the $maxFileSize variable to allow for a larger image.

After you have uploaded 2 or 3 images, return to Dreamweaver and create a new page, save it as ‘gallery.php’. Under the Bindings tab of the Applications palette, add a new Recordset (Query). This is where we’re going to establish what information to retrieve from the database.

Call it ‘getpics’ (nice and descriptive) and set up the rest as outlined below.

This will again add a chunk of code to your page, but more importantly for us, it will give you access to some dynamic data in the Bindings tab (see picture on left).
If you now switch your view to ‘design’ (or ’split’) so you can enter some text to your page, write something along the lines of:

You are looking at image number:
Description:

You can then drag recordset data onto your page where you want them to be displayed.

Then, to display the image, place your cursor wherever you want your image to be displayed and goto Insert > Image. Then select ‘Data Sources…’ as the location to find your image, and then select the ‘imgfile’ option. This will create a dynamic image holder on your page which will get it’s information (the filepath) from your database.

This page as it is will only display the latest image uploaded, so to allow us to see more than one image, do the following.

Highlight all 3 ‘dynamic’ parts on your page (and also, it’s probably best to include an empty paragraph below them. You’ll see why.);

Then goto Server Behaviors and add a new Repeat Region. Make sure that it will repeat your ‘getpics’ recordset (that should be the only one present) and set it to show all records for now.

This creates a ‘do while’ loop on your page that will execute the code which displays your image (and the description etc) for as long as there is data to be shown. So when you have a look at your final page, you should see all the images you previously uploaded, and it’ll update for every new image you upload from now!