Converting a Regular FLATTEXT Script to a FLATTEXT for MySQL Script

Working with MySQL, unlike FLATTEXT, requires that you think very carefully about the type of information that you plan to store in each field or column.  SQL requires that you create columns of specific types that can hold specific classes of information.  Therefore, there is no automated way to convert your regular FLATTEXT script to a MySQL script.  However, the process is easy enough and here is how you do it.  Once you become familiar with the process, you'll be able to create new projects in a matter of minutes.

1) Take a look at your existing field structure. Open your existing FLATTEXT data file and take a careful look at your data.  Make a list of all fields that are in your data file and make a note of the type of information that each field contains.

2) Create your MySQL database. If you are on a virtual host, consult your server documentation about what you need to do to create (or request that your server administrator create) a MySQL database for you.  Once the initial database is created, you will select (or receive) the following:

• The database name
• The user id associated with this database
• The password associated with this user id

3) Download and install the FLATTEXT for MySQL admin utility. Install and invoke the admin utility according to the instructions that came with it.  If you encounter problems with the installation, please contact us. 

4) Create a table.  Using the FLATTEXT for MySQL admin utility connect to database, select the option to Add a Table on the main options screen.  Provide a name for each column, making sure not to use any of the names reserved by MySQL.  

5) Set column properties for table.  The above step created all of your columns as type: varchar(255)  This is a variable length field that can hold up to 255 characters.  Use the option to Set or Change Column Types, Sizes, or Names, giving each column a type and size that best fits the data that you intend to store in it.  Please make sure that you read the column type tutorial, since not understanding the basic column types can cause serious heartache down the road.

6) Import your text file.  In Step 3, above, you installed the admin utility and at the top of the script, you specified a full or relative path to your MySQL working directory.  Send your existing data file using a program like WsFTP to your working directory in ASCII mode.  Use the option Import a Delimited Text File into Existing Table to import your file into your table.  Note: before actually importing it, make sure to select the option to Examine Text File... first. This will catch many (but not all!) mismatches between your actual data and your column configuration.

7) Make sure that your table imported correctly and fully.  Use the Query a Table option on the main admin page to search your database for various columns.  Make sure that each column imported correctly and fully.  If you plan to create a script that is addable or editable online, then you need to have a column that contains unique values to key all of your changes to.  You can add a unique column automatically by using the Add a Unique Column option in the admin tool.  If all of your records imported correctly, you are ready to click the Design a Web Interface in Perl option to create your script.  If it didn't, flush the data out of your table using the Empty a Table option, check your column properties and delimiter, and try Step 6 again.

8) Follow instructions in the script generator and install instructions when your script is generated. Each script has a unique script id. You can use this code to edit your script up to three months after it was first created.  However, if you change the field order or structure of your MySQL table, you will need to create a new script to work with this "new" table.