Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Once you have defined a database connection, you're ready to retrieve data from your MySQL database. Dreamweaver makes it easy to build simple SQL statements to do this work for you. SQL stands for Structured Query Language, and it's the standard that's used to communicate with the database from any application environment. To get started with SQL, open any PHP file. I am working with the file simplers.php, which you'll find in the root folder of this site. In the Bindings panel, there is a Plus button that lets you create what Dreamweaver refers to as a binding.
A binding is a definition of dynamic data. I'll click the Plus button and choose Recordset (Query). The Recordset dialog box has two modes: Simple and Advanced. If this is the first time you've used this dialog, you'll see the Simple version. You enter the name of the recordset, select a connection, choose a table in that database, and then supply other parameters. There is a button labeled Advanced, and when you click that, you're taken to the Advanced version of the Recordset dialog.
I am going to start with the Simple version. You can name your recordset anything you want. I typically prefix my record sets with the characters rs, so I am going to name this rsPackages. Then I'll pull down the Table list and choose the packages table. You'll see a listing of the available columns in the table. If you'll leave the Columns selection set to All, that means you're retrieving all of the columns in the entire table. If you prefer, you can choose the Selected radio button, and then holding down the Ctrl key on Windows or the Command key on Mac, you can click on the columns you want.
I'll choose these three columns: packageTitle, packageDescription, and packageGraphic. Now, I am going to test my recordset by clicking the Test button. That shows me the data that's returned from the database. I'll click OK to close the dialog, and then I'll also add a Sort parameter. I'll pull down the sort list and choose packageTitle, and then I'll leave this second option set to Ascending. Now I am ready to insert the recordset into my current web page.
I'll click OK, and I won't see any changes to the page in Design view. But I'll go to Code view and expand to fullscreen by pressing the F4 key, and I'll see that a whole bunch of PHP code has been added to my page at the top. First, I'll see a require_ once directive that includes the explorecalifornia.php file; that's the file that defines my database connection. Then there is a sequence of code that creates a select statement. A select statement in SQL retrieves data from the server.
This select statement is retrieving the three selected columns packageTitle, packageDescription and packageGraphic from the packages table, and the order by clause is sorting the data by the title field. That string is my SQL statement. The next line of code executes the query by calling a PHP function called mysql_query. It passes in the query string and the connection. This little bit of code, starting with the phrase, "or die," means that if the query doesn't work for some reason, then the PHP page should be terminated.
If everything goes well, at that point, we start fetching data by calling a function called mysql_ fetch_association, or assoc. And finally, a variable named totalRows_ rsPackages is created, which is based on the function mysql_num_rows. We're passing in the recordset and getting back the total number of rows in the recordset. So, all that code taken together executes the query, retrieves the data from the server and stores it in a set of variables, which you can use in your PHP pages.
I'll show you how to display the data in various ways in some other exercises, but I'll also point out one last bit of code that's placed at the bottom of the page and is very important. It's called mysql_free_result. This releases the reference to the recordset in the PHP server's memory. And it makes sure that you are releasing any memory that's used by storing those record sets when the current page is finished processing on the server.
Get unlimited access to all courses for just $25/month.Become a member