Insert, update, delete records with MySQLi Server Behaviors

Tutorial created by Ray Borduin, WebAssist

Categories: MySQLi Server Behaviors, Dreamweaver, PHP

2 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. Saved PHP page with a form that allows users to insert information, for example an email address.
   
6. Connection in Dreamweaver to your database. For assistance, view this tutorial: Create a PHP database connection.

next section

What is MySQLi?

Before we begin with the steps in this tutorial, let's discuss why WebAssist created the MySQLi Server Behaviors interface. The original MySQL library that the original Dreamweaver server behaviors is based on is now deprecated in PHP as well as Dreamweaver. Because MySQL will be removed in the future, we have taken the functionality that used to exist within Dreamweaver for MySQL and recreated it in the MySQLi Server Behaviors extension as both a replacement and an improvement.

next section

Create a MySQLi connection

To begin, your first step is to create a MySQLi database connection.

1. Open a php page in your Dreamweaver site.
   
   
2. Go to the Databases panel, click the + icon, and choose Dynamic MySQLi Connection.
   
   
   
   
   
3. Fill in your connection name and choose the default Charset, as indicated.
   
   
4. Continue filling in the fields for your MySQL server, User name, and Password.
   
   
5. Use the Select button to search for and select your database.
   
   
   
   
   
6. Test your connection and click OK.
   
   
   
   
   
7. Click OK to close the Dynamic MySQLi Connection window.
   
   
8. Now you can see the MySQLi connection in the Databases panel.
   
   
   

next section

Add a MySQLi recordset

Next, you'll create a MySQLi recordset on your PHP page so that a specific set of data can be retrieved from your database.

1. Open a saved PHP page on your site.
   
   
2. In the Server Behaviors panel, click the + icon and go to WebAssist > MySQLi > MySQLi Recordset.
   
   
3. If the recordset dialog is in Advanced mode, click the Simple button.
   
   
4. Enter a recordset name in the recordset Name field.
   
   
5. Select your MySQLi database connection.
   
   
6. Select the database table from which you wish to pull records. In this tutorial, the example shown is users.
   
   
7. Click the All Columns radio button and sort the records by one of your database columns. In this tutorial, the example shown is UserEmail.
   
   
   
   
   
8. Test and click OK to generate your MySQLi recordset.
   
   In this tutorial, we are adding simple recordsets. However, an upcoming tutorial in the MySQLi Server Behaviors series will walk you through the steps of using the advanced MySQLi Server Behaviors visual query building tool. See the last section on this page for a link to the tutorial.
   
   
9. Now you will see the recordset added in the Server Behaviors panel.
   
   
   
   
   
10. In code view, you can view the recordset added to the PHP page.
    
    
    
    
    
11. Save the page (Ctrl or Cmd + S).

next section

Insert records into a database

You can allow users to insert new records into a table in your database from a saved PHP page with the MySQLi Insert Record interface. The interface has two tabs, General and Column Bindings.

1. Open the PHP page where you will grant users the ability to insert records, for example, a form that allows users to insert an email address.
   
   
   
   
   
2. In the Server Behaviors panel, click the + icon and choose WebAssist > MySQLi > Data Management > Insert Record to open the WebAssist MySQLi Insert Record interface.
   
   
3. Click on the General tab where you will select your general settings.
   
   General tab
   
   1. Trigger: Click the Trigger select list to choose when and where to insert your record.
      
      By adding triggers, you can control when the insert, update, or delete server behaviors take place.
      1. Any form post: The server behavior is applied to a form on the current page to trigger the data insert.
         
      2. Before page load: The server behavior is applied to the current page to trigger the data insert when the page loads.
         
      3. Current page submit: The server behavior is triggered when a page is submitted.
         
      4. Button: When the submit button is clicked, the server behavior is triggered.
         
      5. Rules: Multiple conditional rules based on other WebAssist Server Behaviors.

      
      

   2. Dynamic data lightning bolt: Instead of using a trigger in the select list, create a custom trigger by clicking the lightning bolt to open the interface where server-side code can be specified.
      
      
   3. Connection: Choose your MySQLi connection.
      
      
   4. Insert table: Specify the database table into which the record should be inserted.
      
      
   5. (Optional) Save id in session as: You can specify a session variable that will contain the record ID of the newly inserted record.
      Think of "Save id in session as" as temporary storage. For example, your user types their email address into the form. The session variable temporarily stores the record ID in the session.
   6. After inserting, go to: Use this field to redirect to a specific page after the record is inserted, for example, a Thank You page confirming the submission is complete.
      If you do not want users redirected to another page, you should leave this field blank.
      
      
      

   
   

4. Move to the Column Bindings tab where you will designate a value to be inserted in the table selected in the General tab.
   
   Column Bindings tab
   
   WebAssist's MySQLi Insert Record interface offers the option to 1) type in a value or 2) bind a value to a session variable, form element, or recordset. You will find this is an enhancement to the native Dreamweaver insert record server behaviors which limits you to getting values from a form.
   1. Value: Select the column you wish to bind a value to.
      
      
      
      
      
   2. Now you will designate the value you want inserted into a table. You can enter either a static value or a variable. Alternately, you can use the dynamic data lightning bolt button to select a value from Bindings.
      
      
      
      
      
      
      
      
      
   1. Submit as: Make a selection in the Submit as list to designate the data type you will be inserting.
      
      You may find that the data type has been preset based on the column type, for example, Text.
      1. Text
         
      2. Integer
         
      3. Double
         
      4. Date
         
      5. Checkbox Y,N
         
      6. Checkbox 1,0
         
      7. Checkbox -1,0

      
      

   2. Blank value: Select the type of value used in the event that the value you have selected is blank.
      
      WebAssist's MySQLi Insert Record Interface allows you to specify a blank value, a new feature which in the past required hand coding.
      1. Empty String
         
      2. Null
         
      3. Db Default
         
      4. Current Date
         
      5. 0
         
      6. N

      
      

      

   
   

5. Click OK to apply the Insert Record Server Behavior to the current page.
   
   
   
   
   
6. If you'd like to make changes in the future, you can edit the MySQLi Insert Record Server Behavior from the Server Behaviors panel.
   
   
   

next section

Update records in a database

You can allow users to update records in a table in your database from a saved PHP page with the MySQLi Update Record interface. The interface has two tabs, General and Column Bindings.

1. Open the PHP page where you will allow users to update records.
   
   
2. In the Server Behaviors panel, click the + icon and choose WebAssist > MySQLi > Data Management > Update Record to open the WebAssist MySQLi Update Record interface.
   
   
3. Click on the General tab where you will select your general settings.
   
   General tab
   
   1. Trigger: Click the Trigger select list to choose when and where to update the record.
      
      By adding triggers, you can control when the insert, update, or delete server behaviors take place.
      1. Any form post: The server behavior is applied to a form on the current page to trigger the updated data insert.
         
      2. Before page load: The server behavior is applied to the current page to trigger the updated data insert when the page loads.
         
      3. Current page submit: The server behavior is triggered when a page is submitted.
         
      4. Button: When the submit button is clicked, the server behavior is triggered.
         
      5. Rules: Multiple conditional rules based on other WebAssist Server Behaviors.

      
      

   2. Dynamic data lightning bolt: Instead of using a trigger in the select list, create a custom trigger by clicking the lightning bolt to open the interface where server-side code can be specified.
      
      
   3. Connection: Choose your MySQLi connection.
      
      
   4. Update table: Specify the database table where the record will be updated.
      
      
   5. Lookup column: Specify the column to be used to locate the record being updated.
      
      
   6. Column Type: Specify the data type of the lookup column. You can select any of the following:
      
      You may find that the data type has been preset based on the column type.
      1. Integer
         
      2. Text
         
      3. Date
         
      4. Floating point number

   7. Lookup value: You may find that the value field has been preset. Or you can click the dynamic data button to choose a value that will be used to select the record to be deleted.
      
      
   8. After updating, go to: Use this field to redirect to a specific page after the record is updated, for example, a Thank You page confirming the update is complete. You can locate the pathway by clicking the folder icon.
      
      If you do not want users redirected to another page, you should leave this field blank.
      
      
      

   
   Column Bindings tab
   

   1. Value: Select the column you wish to bind a value to.
      
      
      
      
      
   2. Now you will designate the value you want updated in a table. You can enter either a static value or a variable. Alternately, you can use the dynamic data lightning bolt button to select a value from Bindings.
      
      
      
      
      
      
      
      
      
      
      
   3. Submit as: Make a selection in the Submit as list to designate the data type you will be updating.
      
      You may find that the data type has been preset based on the column type, for example, Text.
      1. Text
         
      2. Integer
         
      3. Double
         
      4. Date
         
      5. Checkbox Y,N
         
      6. Checkbox 1,0
         
      7. Checkbox -1,0

      
      

   4. Blank value: Select the type of value used in the event that the value you have selected is blank:
      
      WebAssist's MySQLi Update Record Interface allows you to specify a blank value, a new feature which in the past required hand coding.
      1. Empty String
         
      2. Ignore
         
      3. Null
         
      4. Db Default
         
      5. Current Date
         
      6. 0
         
      7. N

      
      

      

   
   

4. Click OK to apply the Update Record Server Behavior to the current page.
   
   
   
   
   
5. If you'd like to make changes in the future, you can edit the MySQLi Update Record Server Behavior from the Server Behaviors panel.
   
   
   

next section

Delete records in a database

You can allow users to delete records in a table in your database from a saved PHP page with the MySQLi Delete Record interface.

1. In the Server Behaviors panel, click the + icon and choose WebAssist > MySQLi > Data Management > Delete Record to open the WebAssist MySQLi Delete Record window.
   
   1. Trigger: Click the Trigger select list to choose where to delete your record:
      
      1. Any form post: The server behavior is applied to a form on the current page to trigger the data insert.
         
      2. Before page load: The server behavior is applied to the current page to trigger the data insert when the page loads.
         
      3. Current page submit: The server behavior is triggered when a page is submitted.
         
      4. Rules: Multiple conditional rules based on other WebAssist Server Behaviors.

      
      

   2. Dynamic data lightning bolt: Instead of using a trigger in the select list, create a custom trigger by clicking the lightning bolt to open the interface where server-side code can be specified.
      
      
   3. Connection: Choose your MySQLi connection.
      
      
   4. Table: Specify the database table where the record will be deleted, in this example, users.
      
      
   5. Lookup column: Specify the column to be used to locate the record being deleted, in this example, UserEmail.
      
      
   6. Column Type: Specify the data type of the lookup column. You can select any of the following:
      
      You may find that the data type has been preset based on the column type.
      1. Integer
         
      2. Text
         
      3. Date
         
      4. Floating point number

      
      

   7. Lookup value: You may find that the value field has been preset based on the table and column type. Or you can click the dynamic data button to choose a value that will be used to select the record to be deleted.
      
      
   8. After deleting, go to: Use this field to redirect to a specific page after the record is deleted, for example, a Thank You page. Find the pathway by clicking the folder icon.
      
      If you do not want users redirected to another page, you should leave this field blank.
      
      
      

   
   

2. Click OK to apply the Delete Record Server Behavior to the current page.
   
   
   
   
   
3. To make changes in the future, edit the MySQLi Delete Record Server Behavior from the Server Behaviors panel.
   
   
   

next section

Create multiple parameters

In the MySQLi Delete (and Update) Record interfaces, only one value can be looked up at a time. But with simple hand coding, we can look up a record based on more than one value by duplicating the "bindParam" record server behavior.

1. In this example, we will bind two parameters by adding a new value beneath the existing UserEmail value.
   
   
2. In code view, copy (Ctrl or Cmd + C) the "bindParam" record server behavior (line 7) and paste it (Ctrl or Cmd + P) directly beneath (line 8).
   
   
   
   
   
3. Create a multiple parameter, UserID, by replacing the "s" with "i" and 'UserEmail' with 'UserID' in the second line of code.
   

   $DeleteQuery->bindParam("s","".($_GET['UserEmail'])  ."");
   $DeleteQuery->bindParam("i","".($_GET['UserID'])  ."");

   
   
4. This allows us to bind multiple parameters, in this example, UserEmail and UserID.
   
   
   

next section

Where do you go next?

Now that you know how to manipulate records in your database using MySQLi insert, update, and delete server behaviors, learn how to use the WebAssist's MySQLi user authentication interfaces at User authentication.

MySQLi Server Behaviors series

With WebAssist's MySQLi Server Behaviors, you can quickly create various server behaviors to generate streamlined MySQLi code in Dreamweaver. The following tutorials in the MySQLi Server Behaviors series for websites with custom database driven pages demonstrate how to insert, update, and delete records, add user authentication, record count, pagination links, and show if statements. Additionally, you will learn how to use WebAssist's Data Modeling interface and Query Builder tool.

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: The 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 code to be generated on a PHP page to log in users stored in the database. You can also log out users, 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 tool: Watch the video demonstrating how to use the stand alone Data Modeling interface that allows users to set up joins which will be automatically applied whenever tables are added in the MySQLi recordsets. The Query Builder tool allows users to drag and drop database tables onto a grid and create relationships between database tables to create advanced SQL statements.
   
   
5. Getting started with MySQLi Server Behaviors: Learn how to generate a simple results page by creating a recordset, adding a binding to a PHP page, and wrapping a repeat region around that binding. Then you'll be walked through adding a record count, pagination links, and show if statement. Once you understand the concepts, you can build on your knowledge to create custom database driven pages for your website.

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.

2 ratings | Sign in to rate this tutorial