Samples Product Information Home Page Download Pricing

The Database Editor Utility

The Database Editor utility comprises two forms. The first form is the dbMenu form from the previous chapter and you may notice that all the values you entered in CleverCat are displayed on this page in their appropriate fields.

You can check that the connection values are correct by entering your password. If correct, a message should be displayed indicating you are connected to the mySQL table.

Even though you entered the password in CleverCat, you need to enter it again here. This utility has the ability to modify your databases so you need to make sure it is only used by authorized persons.

You may also notice that you can change the values on this form. This utility can modify any database on your server if you enter the correct values.
So, you could have actually run the utility without any pre-configuration at all and entered all the column names here if you knew what they were. But, by following along with the tutorial you will have a better understanding of how these columns are used.

Check the Add Columns box and click the Continue button to open the Database Editing form.

 

The Continue button on the dbMenu form does more than just open this form. It runs a function that adds new columns to the mySQL table if they do not already exist. The names of the columns are now displayed as the field labels in the bottom half of this form.

As this is the first time you will be using this utilty, the table will be empty and the selection boxes at the top of the page will also be empty.

The only option available to you at this point is to Upload a CSV file.

Click the Choose File button to select the dbImport.csv file in your local output folder.


If the filename is displayed next to the box you can click the Upload File button to continue. This should take you to a new page which displays the results of the upload.

The number of records uploaded should equal the number of rows, minus the Header row, in the CSV file.

 

Click the Go Back button to return to dbMenu. If you need to add an additional columns to the mySQL table, you could do that now by entering the new names in one of the empty fields. The columns names already on the form will not be duplicated. To open the Database Editing form with no changes, just leave the Add Columns box unchecked and click Continue.

The dbEditor form now shows the data in the selected mySQL table. You can select a category in the upper-left window and an individual record in the upper-right window. The full record will then be displayed in the bottom half of the form.

The record selector in the upper-right displays only the first description field. In my example, that happens to be Description but the name of the field has no effect on which field is shown.

If a sorting column was uploaded, such as order1 in this example, the records will be sorted by that number. Otherwise, the records will be sorted alphabetically on the first description field.

Note that there are only as many description fields as they were labels on the dbMenu form. If your data requires more columns, you would enter those on CleverCat's PHP tab, which configures the dbMenu form, which defines how many fields are displayed on the dbEditor form.

You can edit the data in your mySQL table directly on this form. Change the text in any description field and press the Enter or Tab key. Changes will be saved as you make them.

 

This is a good time to go back and have another look at the database using phpMyAdmin to compare how the same data is shown and can be edited there. phpMyAdmin is not difficult to use but can look intimidating if you are not familiar with it. Once you are used to it, it's really not much different from a spreadsheet.

Login to phpMyAdmin and select the myInventory table as you did in the earlier chapter. If you are following along with the sample data, your myInventory table structure should resemble the one here.
Compare the screenshot above to the two below to see that the same Headers / Labels are shown.

 

Click the Browse tab to see the uploaded data. Again, if you are following the tutorial, your screen should resemble this screenshot. Check that the data is in the correct columns - category in the category column, price in the price column and so on.

There are controls on the left to Edit or Delete records but most versions of phpMyAdmin allow you to edit data by double-clicking the text to open an editing box. So, phpMyAdmin and CleverCat's Database Editing Utility provide most of the same functions and you can use whichever you feel most comfortable with.

 

If your screens do not resemble those shown here, take some screenshots and contact us for advice or repeat the steps above. You can delete the data in this table by selecting rows and using the Delete or Drop function or delete the entire table by selecting Drop under the Operations tab.

 

As mentioned before, you can use this utility without any pre-configuration. Further description of the functions will be useful to those users who intend to do so. You can skip the rest of this chapter if this does not interest you.

 

Return to Tutorial Selection or Continue to the next chapter: PHP Templates

 

The dbMenu form is divided into three sections. The top section is for the values you need to connect to a mySQL database. The middle section is for the names of the columns which control how the data is organized and displayed and the bottom section is for the names of the columns which contain the product's text descriptions.
Each of the fields in the bottom section is defined as data type text. In the middle section, the field types vary. The Index field, Print Order and Detail Order are all data type int(11) and Category, Subcategory, Photo 1, Photo 2 and Title are all varchar(255). If you are creating your mySQL table manually, you don't have to use these data types.

In the CSV file created by CleverCat, the Column Headers match the labels shown in the Database Editing Utility. However, the column names in the CSV file are not actually used to identify columns when uploading. Instead, the order of the columns is used.

In my example, Description is in the eighth field on the form (after invID, category and so on), excluding empty fields such as Subcategory, and will be the eighth column in the CSV.
When you click Continue on the dbMenu form, the field text is passed to the dbEditor form, where you see those names as the field labels. The same names are also passed to the Upload function.
When the upload function starts, it gets the first column name, invID, from dbEditor, finds the invID column in the mySQL table but imports the first column from the CSV regardless of the name at the top of the column. In fact, the entire first row in the CSV is ignored because it is assumed to be the header row.
The Upload function continues through all the names in their order on the form.

So, if you are using the dbEditor form to upload a CSV which was not created by CleverCat, make sure the column order matches the order of names on the Database Editing forms.

 

 

Return to Tutorial Selection or Continue to the next chapter: PHP Templates