close ad
 
Important WebAssist Announcement
open ad
View Menu

Web development tutorial

Insert, update, delete records with MySQLi Server Behaviors

Tutorial created by Ray Borduin, WebAssist

Categories: MySQLi Server Behaviors, Dreamweaver, PHP

rating

In this MySQLi Server Behaviors tutorial, you will learn to use the data management server behaviors to give users the ability to insert, update, and delete data in a database from a PHP page. From the individual insert, update, and delete interfaces, you will also learn how to choose a trigger, such as a form post, and specify how to manipulate records in your database.

You'll find a linked index to the tutorials in the MySQLi Server Behaviors series at the bottom of this page.

arrow downWhat 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.

arrow downWhat is MySQLi?

MySQL code is now deprecated so you must transition to MySQLi for your websites to continue to work when the next version of PHP is released.

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.

arrow downCreate 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.

arrow downAdd 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).

arrow downInsert 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.

arrow downUpdate 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.

WebAssist's MySQLi Update Record interface has enhancements as well. There are 7 choices of settings in the General tab. The Column Bindings tab offers the ability to ignore blank values. For example, if there is an email field, users can fill it in with an update. But if they leave it blank, the current email address will remain.
  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.

arrow downDelete 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.

arrow downCreate 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.

arrow downWhere 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.

arrow downMySQLi 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.

arrow downReviews 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.

Sign in to add comments
rating
rating

henrik441511: 9 Years, 4 Months, 1 Week, 6 Days, 9 Hours, 26 Minutes ago

For those - like me - who did not figure out what is going on, on the Insert records into a database section;
simply use a <form> </form>
as:
<form action="Name-of-four-file.php" method="post">
<input name="username" type="text" class="" placeholder="type name..."> <br />
<input name="userpassword" type="text" class="" placeholder="Type a Password...">
<input name="submit" type="submit" value="Login" class="" style="margin-bottom:0px;" />
</form>

The form is visible in the Bindings panel. Here your can see your variables. (in this example is: username and userpassword)
Now you can continue from:
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.

Team WebAssist: 9 Years, 4 Months, 1 Week, 5 Days, 14 Hours, 36 Minutes ago

henrik441511, Great point. We added a little bit to the getting started section to make it clear that you need not just a php page, but an actual form. Thank you!

: 9 Years, 3 Months, 5 Days, 12 Hours, 12 Minutes ago

Hello, thanks for the tut...what about updating a user's password with encryption..i cannot see anything like that here..thanks

Team WebAssist: 9 Years, 3 Months, 3 Days, 9 Hours, 51 Minutes ago

Please click Help in the main website menu to have technical support assist you with how you want to encrypt passwords.

: 8 Years, 11 Months, 3 Weeks, 1 Day, 18 Hours, 22 Minutes ago

must i buy your sqli converter to use thiss code

melanie-p: 8 Years, 10 Months, 2 Weeks, 4 Days, 15 Hours, 32 Minutes ago

If you're using WA Validation on the form, should you select the column values from the validated or unvalidated form?

: 7 Years, 10 Months, 3 Weeks, 3 Days, 2 Hours, 10 Minutes ago

Excuse the question, performing these steps I prevent cross site scripting? thank you very much

: 6 Years, 8 Months, 2 Weeks, 3 Days, 15 Hours, 20 Minutes ago

Thanks for the wonderful tutorial, it did help. Please can you take us round how to update, insert and delete multiple database records

FPhu61701534: 5 Years, 10 Months, 1 Day, 17 Hours, 7 Minutes ago

Does MySQLi conflict with DataAssist or do they complement each other? Do I need both installed or am I to choose either one? Does MySQLi come with features found in DataAssist, such as the ability to create forms for inserting and updating records?

: 4 Years, 10 Months, 2 Weeks, 6 Days, 22 Hours, 7 Minutes ago

thanks for great help.

, here i found some tutorial on Code for Insert View Update Delete In Php MySQL for more detail <a href="http://www.sensible-computers.com/blog/Code-for-Insert-View-Update-Delete-In-Php-MySQL/17">Click Here</a>

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.