Most of the tutorials, snippets, and behaviors that you will find are for using PHP with mySQL. There is virtually no information on using ASP with mySQL. For many people, this is a very viable option as mySQL is a much more powerful database than using an Access file and is a free download versus having to pay for Microsoft’s SQL Server. This makes mySQL an excellent choice for small to medium sized sites that want a faster database solution.
This article will cover connecting to the database and the few things to watch out for when working with ASP and mySQL.
Requirements
Dreamweaver MX•
• mySQL (http://www.mysql.com)
• A web browser
• Microsoft IIS 5.0 or later
Getting Started
Once you have downloaded and installed mySQL, you need to create a user account for use with your web database. This technote,
http://www.macromedia.com/support/dreamweaver/ts/documents/mysql_config.htm, explains how to configure mySQL and set up a site definition.To manually create the connection string, the following example will get you started:
"Driver={mySQL}; Server=
server_address; port="3306;" option="131072;" stmt=";" Database=database_name; Uid=User_Account; Pwd=User_Password"Working with mySQL
Whether you are using Access, SQL Server, or mySQL, virtually everything you will do within Dreamweaver will be identical. All of the tutorials and code snippets all work the same. The only issue you will run into is when working with Date fields. Unlike Access and SQL Server, mySQL stores dates in a YYYY-MM-DD format although normal display functions display the dates as MM/DD/YYYY format the same as Access or SQL Server.
If you try to send a date to mySQL in the more common MM/DD/YYYY format, it will effectively be translated to 0000-00-00 rendering the saved date useless.
When using Server Behaviors to create update and insert record fields, there is no translation done, thus your dates will not function properly.
To correct this problem, we need to add some code to our pages to convert from our standard date format to mySQL’s required format.
Dreamweaver will create a list of the fields that are available for inserting and updating. We will need to look at this list to determine how to write our translation code. The following is the fields list for a simple database:
MM_columnsStr = "StartDate|',none,NULL|EventDate|',none,NULL|EventText|',none,''"
In this example, we have two date fields and a text field. Dreamweaver will eventually split this string up to create the insert and update queries, but we will analyze it to get the information we need to create our code. The string is pipe-delimited so when split into an array, it will look like this:
MM_columns(0) = StartDate
MM_columns(1) = none,NULL
MM_columns(2) = EventDate
MM_columns(3) = none,NULL
MM_columns(4) = EventText
MM_columns(5) = None,’’
While the two date fields are indicated as being in positions 0 and 2, those positions store the field name, the position following the field name will actually be used to store the field value. These means the two positions we need to convert to mySQL date format are positions 1 and 3.
In the page you are working on, find the line that reads:
' create the sql update statement
Immediately after this line, we are going to insert our custom code. The first step is to split the date from the input form into a new array
Dim TmpDate TmpDate = Split(MM_fields(1),"/")
If we had entered 02/23/1965 into our form field, our temporary array would now look like this:
TempDate(0) = 02
TempDate(1) = 23
TempDate(2) = 1965
Next, we take the new date array we created and convert it into mySQL format:
MM_fields(1) = TmpDate(2) & "-" & TmpDate(1) & "-" & TmpDate(0)
Our date now looks like: 1965-02-23
With the date now in the proper format, mySQL will accept it into its Date/Time format.
Since our example has two date fields, the final code will look like this:
' create the sql update statement
TmpDate = Split(MM_fields(1),"/")
MM_fields(1) = TmpDate(2) & "-" & TmpDate(1) & "-" & TmpDate(0)
TmpDate = Split(MM_fields(3),"/")
MM_fields(3) = TmpDate(2) & "-" & TmpDate(1) & "-" & TmpDate(0)
Once we have added this code snippet to both the data insert and data update pages, our code is now totally compliant with mySQL.
Kerry Garrison is the Product Development Manager for XO Communications (
http://www.xo.com) and the publisher of Wildhobbies.com (http://wildhobbies.com), the #1 On-Line Hobby Magazine on the web. Kerry Garrison is also heavily involved in creating large dynamic websites for clients through his business, Remote Brains (http://www.remotebrains.com) and can be contacted at kerryg@wildhobbies.com or from http://kerryg.wildhobbies.com.
Comments
Er....what??
RE: Er....what??
Ah!
I see! Thnx kerry. Have you written any tutorials on creating a 'cart' system using asp and mySQL?
cheers,
Jim
RE: Ah!
You must me logged in to write a comment.