Getting started with MySQLi Server Behaviors

Tutorial created by Ray Borduin, WebAssist

Categories: MySQLi Server Behaviors, Dreamweaver, PHP

4 ratings | Sign in to rate this tutorial | View comments

next section

What do you need to start?

1. Dreamweaver CS4 or higher; Dreamweaver CS6 or higher to use the Query Builder
   
2. MySQLi Server Behaviors installed and activated.
   
3. Site defined in Dreamweaver. For assistance, watch this tutorial: Setting up a site in Dreamweaver.
   
4. MySQL database. If you do not have one, you can download our free eCommerce MySQL database in this tutorial.
   
5. A saved PHP web page.

next section

Create a MySQLi connection

First we will create a Dynamic MySQLi connection to your database.

1. Open your PHP page.
   
   
2. In the Databases panel, click the + button and choose Dynamic MySQLi Connection.
   
   
   
   
   
3. Name your connection and fill in the credentials to connect to your chosen MySQL database.
   
   
   
   
   
4. Click the Test button to be sure you have a valid connection to your database.
   
   
5. Click OK.

next section

Create a MySQLi recordset

Next we will create a MySQLi recordset so that your PHP page can retrieve a specific set of data from your database.

1. In the Server Behaviors panel, click the + sign and Select WebAssist > MySQLi > MySQLi Recordset.
   
   
   
   
   
2. If the recordset dialog is in Advanced mode, click the Simple button.
   
   
3. Enter a recordset name in the recordset Name field.
   
   
4. Select the MySQLi database connection we set up.
   
   
5. Select the database table you want to pull records from. In this example, we chose Products.
   
   
6. Use all columns and sort the records by one of your database columns if you’d like.
   
   
   
   
   
7. Click the test button to make sure you will be displaying data from your database.
   
   
8. Click the Advanced button and then the Query Builder button to access the MySQLi Server Behaviors visual query building tool.
   
   Because we are building a simple results page here, this Getting Started Guide provides an overview the Query Builder interface without going into detail. However, an upcoming tutorial in the MySQLi Server Behaviors series will walk you through the steps of using the advanced Query Builder tool. See the last section of this Getting Started Guide for a list of upcoming tutorials in this series.
   
   
   
   
   
9. In the Query Builder interface, you can create your recordset much more visually by dragging and dropping tables from the lefthand column onto the grid.
   
   
10. Place your cursor to the right of a column name and drag it to a column name in another table to create relationships between tables. The SQL statement will be written for you below the grid.
    
    
11. If you should choose to edit the SQL manually to create even more advanced recordsets, click on the Advanced tab below the grid and simply click in your SQL statement to make manual edits.
    
    
    
    
    
12. Click OK to generate your MySQLi recordset on your PHP page. You will see the recordset added in the Server Behaviors panel and code added to the page when you view the page in Code View or Split View.
    
    
13. Save the page (Ctrl or Cmd + S).

next section

Add a binding to your page

1. In the Bindings panel, expand your MySQLi recordset.
   
   
   
   
   
2. In Design View, drag one of your bindings onto your page.
   
   
   
   
   
3. Click Save.
   
   
4. Go to File > Preview in Browser to preview your page in the browser to make sure you are displaying the first record from the recordset we created.

next section

Display all of your records

When building a results page, you typically display all the results returned from your recordset. We can accomplish this by wrapping a repeat region around the recordset.

1. Click on your binding in Design View.
   
   
2. In the Server Behaviors panel, click the + button, then WebAssist > MySQLi > MySQLi Repeat Region.
   
   
   
   
   
3. Select the MySQLi recordset we created and type how many records to show at a time (or select All records).
   
   
   
   
   
4. Click OK. You will see the repeat region server behavior added in the Server Behaviors panel and a repeat tab added to your binding.
   
   
   
   
   
5. Save your page.
   
   
6. Go to File > Preview in Browser to preview your page in the browser to see the records from the recordset we created, now repeating on the page.

next section

Add a record count

To display to your users how many total results are returned, you can use the record count server behavior.

1. Click outside of the repeat region in Design View and create a paragraph break by hitting return on your keyboard.
   
   
2. In the Server Behaviors panel, click the + button, then WebAssist > MySQLi >Display Record Count > MySQLi Display Total Records
   
   
   
   
   
3. Select the MySQLi recordset we created and click OK. You will see the record count server behavior added in the Server Behaviors panel and a binding added to your page.
   
   
   
   
   
4. Save your page.
   
   
5. Go to File > Preview in Browser to preview your page in the browser to see the total records display on your page.

next section

Add pagination links

If your results page returns a lot of records but you've chosen to display only a certain number of them per page (in the repeat region step), you will likely want to add pagination links so your users can navigate from one page of results to the next (and back).

1. After your record count binding in Design View, create a paragraph break by hitting return on your keyboard.
   
   
2. In the Server Behaviors panel, click the + button, then WebAssist > MySQLi > Recordset Paging > MySQLi Move to Next Page
   
   
   
   
   
3. Select the MySQLi recordset we created and click OK. You will see the paging server behavior added in the Server Behaviors panel and a text link added to your page.
   
   
   
   
   
4. You can repeat these steps to create more pagination links. Most people use all 4: First, Previous, Next, Last.
   
   
5. Save your page.
   
   
6. Go to File > Preview in Browser to preview your page in the browser to page through your results.

next section

Add a Show If statement

You can use the Show If set of server behaviors for different scenarios. Many people use the “Show If Recordset is Empty” to display a text statement to users when no results are found, and display the data when results from the recordset are found. As that example will be difficult to preview for this example (because we already know we are returning results from our recordset), we will use the Show If First Page server behavior to display some text only on the first page of results.

1. At the top of your page in Design View, type any statement you want.
   
   
2. Select the text with your cursor.
   
   
   
   
   
3. In the Server Behaviors panel, click the + button, then WebAssist > MySQLi > Show Region > MySQLi Show If First Page.
   
   
   
   
   
4. Select the MySQLi recordset we created.
   
   
   
   
   
5. Click OK. You will see the Show If server behavior added in the Server Behaviors panel and a Show If tab surrounding your text in design view.
   
   
6. Save your page.
   
   
7. When you preview your page in the browser, you will see “text here” display on your page because it is your first page of results. If you have enough results to navigate to the next page of results, you will see that “text here” does not display on the other pages. You can use this set of server behaviors to display what you need to for various “show if” scenarios.

MySQLi Server Behaviors series

Now that you have finished this Getting Started Guide, you can begin building custom database driven pages with these concepts. The following tutorials in the MySQLi Server Behaviors series demonstrate how to use the Insert, Update, and Delete MySQLi server behaviors, the Data Modeling tool, the Query Builder tool, and the User Authentication server behaviors. Here are summaries of these tutorials.

1. MySQLi Server Behaviors series: An overview of four in-depth tutorials to help you transition to MySQLi so that your websites will continue to work when the next version of PHP is released.
   
   
   
   
   
2. Insert, update, and delete records: This data management interface allows users to insert, update, and delete data in a database from a PHP page. From the individual insert, update, and delete interfaces, users can choose a trigger, such as a form post, and specify how to manipulate records in a database.
   
   
   
   
   
3. User authentication: The MySQLi user authentication interface allows users to generate code on a PHP page to log in or log out users stored in the database. Users can also check if a username exists with the Check New Username server behavior and restrict access to a specific page based on user credentials.
   
   
   
   
   
4. Data Modeling and Query Builder video: The stand alone data modeling interface allows users to set up joins which will be automatically applied whenever tables are added in the MySQLi recordsets. The Query Builder interface allows users to drag and drop database tables onto a grid and create relationships between database tables to create advanced SQL statements.
   
   
   

Reviews and comments

Comments will be sent to the author of this tutorial and may not be answered immediately. For general help from WebAssist, please visit technical support.

4 ratings | Sign in to rate this tutorial