Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Dreamweaver CS5's recordset builder function can build either simple recordsets, which retrieve data from a single table, or if you have a little bit of knowledge of SQL, you can use the advanced recordset builder. I'm going to be working in a file named advancedrs.php. For this demonstration, I'll use the file advancedrs.php, which you'll find in the root folder of the current side. You build an advanced recordset by going to the Bindings panel, clicking the Plus button, and choosing Recordset or Query, and then click the Advanced button.
The advanced recordset builder requires a little bit of knowledge of SQL. Whatever you know how to do with SQL, you'll be able to do here. I'm going to name this recordset rsTours, and then I'll go down to the Tables list, which you'll find in the Database items pane. I'm going to build a recordset that joins or links two tables together: packages, and tours. The packages table has a packageId, primary key column, and three descriptive columns for the Title, Description, and Graphic.
The tours table also has a primary key column, named tourId, and has a foreign, named packageId. I'm going to use the primary key and the foreign key to join these tables together in an SQL statement. I'll use the recordset builder to help me build this statement. I will start with the tours table. For each column that I want to include in the data returned from the server, I'll click on the column and then click the Select button. I'll start with the primary key column, tourId, and I'll click Select, and I'll see in the SQL statement that two tables are included but only one column.
I'm going to clean this up a little bit by removing the admin table, and then I'll test my query to make sure that it's valid, and I'm getting back this single column from the one table in the query. I'll click OK to close the dialog, and then I'll continue. I'll also include the name, the blurb, the description, and the price. For each column that I want to include, I choose the column and click Select. Now I'm going to include columns from the packages table.
In the Database items list, I'll move back to the packages table, and I'll choose packageId and packageTitle. So now in my Select statement, I have the tourId, and a number of other columns from the tours table, and then columns from the packages table. Now, I have to describe how the two tables will be joined together. This is where a little bit of SQL knowledge goes a long way. I'll go back to the tours table and choose the packageId column, and then click Where.
Then I'll click at the end of the statement, after the Where clause, and type in the Equals operator. Then I'll go back to the packages table, and choose the packageId column, and click the Where clause again. So now I'm saying that I'm selecting all these columns from these two tables where the packageId from tours matches the packageId from packages. I'm ready to test my query. I'll click the Test button, and I should see all of the data coming back, joined together correctly.
In the first row, I have tourId, the name, the blurb, description, and price, and then in same row, I have the packageId and the associated packageTitle. I'll click OK, and then click OK again. Just as with this simple recordset, all of the finished PHP code is added to my page. I'll scroll down to where the actual SQL statement is placed, and show you that it's just like the simple recordset. It's just an SQL statement.
The more you know about SQL, the more complex and powerful your queries can be. I'd like to point out one thing that's interesting about this SQL statement. Notice that the word "description" was placed in quotes, while the other column names weren't. That's because Dreamweaver looks at the word "description," and identifies it as a potential reserved keyword in the SQL language. It isn't a reserved keyword in all versions of SQL, but just to make sure, Dreamweaver wraps the name of the column in those quotes.
If you have any similar troubles with column names or other elements of your database structure where those names collide with reserved words in the SQL language, you can fix your code in exactly the same way, by wrapping the term in quotes as needed. Now I'll test the page, looking at it in Live View, and I won't see any data yet. But again, that's because I'm not doing anything to display the data on the screen. I'll show how to do that in a separate video.
Get unlimited access to all courses for just $25/month.Become a member