PHP/JSP Code Generator

 

SQL4X Manager J Enterprise Edition includes a PHP/JSP code generator to automatically create PHP web pages and Java Server Pages (JSP) based on your database tables. The code generator can create list views (with edit/delete/add option and page navigation), input/edit forms and delete pages. The created PHP and JSP code is compatible with Dreamweaver MX and can be executed with any PHP installation starting with PHP 4.x or any JSP 1.2 container (like Tomcat, WebSphere, etc.).

The generated PHP code works with MySQL only. The generated JSP works with any database server that is accessible with SQL4X Manager J.

 

Web Site specific setup

 

Code Generator Setup

The Code Generator Setup has to be executed once for each web site. This is necessary to get some basic information about your site, the code language for the generator (PHP or JSP) and where the generated files will be stored. To start the Code Generator Setup select PHP/JSP Code Generator Setup from the Tools menu.

Add a new web site

Click the + sign on the right side of the Web Site list and enter a name for your new web site. You can use any name you like, but you cannot change the name later on, so choose the name carefully. The site name should tell you what this web site is for.

 

a) Specify Basic Information about your web site

Every major web authoring tool keeps a local copy of your web pages for editing purpose. You need to specify the root folder of your local web site copy with the "Local Web Site Root Folder" selection. SQL4X Manager J generates pathes relative to the folder you specify here. This folder has to be the root folder on your web site.

SQL4X Manager J assumes that files located in this folder will be uploaded to your web server later on by you using your web authoring tool or FTP client. SQL4X Manager J does not include an upload feature.

Addditionally you have to choose the server side scripting language (PHP or JSP) with the Language selection.

 

b) Create Support Files

Now click "Create" in the Support Files section to create the SQL4XJCodeGen folder within your site's root folder. This new folder contains necessary support files like the CSS files (Style Sheets) to format the generated tables and forms.

Special note for eSuite4X - Web Developer's Edition users

Specify /Library/eSuite4X/Apache2/Documents/Sites/Default/htdocs for Local Web Site Root Folder to generate PHP pages for your local Apache/PHP installation.

 
 

c) Specify database connection information for the generated code

Before closing the Code Generator Setup your have to specify the database connection you want to use with this web site. The presented options depend on the selected Language (PHP/JSP).

Database Options for PHP/MySQL Code Generator

Select the "Database" tab to enter the Web Site Database Connection information depending on your working environment:

1. If you use a local web server on your system, you can specify you local MySQL connection information you have used with SQL4X Manager J.

2. If your web server is not located at your local system (ie. you want to upload the generated files to your ISP), you have to specify the connection information for the remote system (ie. the connection information provided by your ISP). In this case you must have either access to the same database server you are using for your PHP pages or you must have a local copy of your database to generate the PHP code.

Connection   Enter a symbolic name for this database connection (ie. "LocalDB").
     
MySQL Server   Enter the IP Address (or DNS name) of your MySQL database server
     
User Name   Enter the MySQL login user name
     
Password   Enter the MySQL login password
     
Database   Enter the name of your MySQL database for which the code has to be generated. If you want to access more than one database you have to create a different Web Site entry for each database.

Click "Save Changes" on the Database tab to save your connection settings.

 

Database Options for JSP Code Generator

If you want to generate JSP code the settings to access your database server are identical to the settings used with SQL4X Manager J. Select your defined SQL4X Manager J database connection from the Connection selection to retrieve the necessary JDBC driver settings. You can change the username/password for the generated web pages here if you want to use a user with limited privileges for the generated web pages.

 

Generate a Simple List

 

What does a simple list display?

A simple list displays your table content or query results. You can either create a simple list with your own SQL queries or based on a selected table.

1. Create a Simple List based on your own SQL Query

Open SQL Viewer and create your own query or use the Query Assistant from the Database Navigator to generate your query. Test your query by running it with the SQL Viewer.

2. Start the PHP Code Generator

The PHP Code Generator is located in the SQL Viewer toolbar (Generate PHP/JSP). If the PHP Code Generator icon is not displayed in the toolbar press ctrl and click on the toolbar background to pop up the toolbar context menu and select "Customize Toolbar...". You can add the PHP Code Generator icon with the customization dialog

 
 

3. Run the Assistant for Simple Lists

From the Code Generator select "Simple List" to start the Code Generator Assistant for Simple Lists.

Site and PHP file settings

First select your Site (this is the name of the web site you have created with the Code Generator Setup) and specify the file name and location of the new PHP file. Press the ... button to display the Save dialog. Your PHP file must be located in the site root folder or any sub-folder of your site root folder. It cannot be saved outside your site root.

Page Style

Now set the Page Style. If you select "Simple List" for Page Style all records will be displayed on one web page. This is great for small results.

If you have a result set with several hundred records (or more) you should select "List with Navigation". This will automatically generate the PHP code necessary to navigate through your result set.

Press "Next >>" to continue. If you have selected "List with Navigation" a settings page for the navigation is displayed. Go on to the next page.

Generate Web Page

The Assistant now displays a summary of your settings. Click "Create Page" to actually create the PHP page or click "<< Back" to make any changes to your settings.

Test the generated PHP page

Before closing the Assistant you should check if the generated web page is really what you wanted. If you want to change a setting you can always go back using the Assistant and re-create the php page.

If you are using a local installation (like eSuite4X - Web Developer's Edition) you can open your web browser and enter http://localhost/<the-name-of-my-php-file> to display the result set in your web browser. If you are using a remote installation you have to upload the PHP pages with your web authoring tool first.

 

Generate an Editable List

 

What does a simple list display?

A simple list displays the contents of a table and includes special icons/links for each line to edit/delete the line and to add a new record.

1. Create an Editable List

Open Database Navigator and select the table you want to display with the Editable List. Make sure you have created a web site entry with the PHP Code Generator Setup for the connection/database you are selecting.

2. Start the PHP/JSP Code Generator

The Code Generator is located in the Database Navigator toolbar (Generate PHP/JSP). If the Code Generator icon is not displayed in the toolbar press ctrl and click on the toolbar background to pop up the toolbar context menu and select "Customize Toolbar...". You can add the Code Generator icon with the customization dialog.

Click the Generate PHP/JSP icon to display the Code Generator window

 
 

3. Run the Editable List Assistant

From the Code Generator select "Editable List" to start the Editable List Assistant.

Site and file settings

First select your Site (this is the name of the web site you have created with the Code Generator Setup) and specify the file name and location of the new PHP/JSP file. Press the ... button to display the Save dialog. Your PHP/JSP file must be located in the site root folder or any sub-folder of your site root folder. It cannot be saved outside your site root.

Unique Row Identifier

You must specify a column which uniquely identifies each record in the table. This ID Column is necessary to pass on the selected record to edit and delete forms. If your table has a primary key, the primary key field is automatically selected.

Recommendation: Your tables should always include a primary key to uniquely identify each record!

Page Style

Now set the Page Style. If you select "Simple List" for Page Style all records will be displayed on one web page. This is great for small results.

If you have a result set with several hundred records (or more) you should select "List with Navigation". This will automatically generate the PHP/JSP code necessary to navigate through your result set.

Check the Include "Edit Record", "Add Record" and "Delete Record" boxes to fit your needs. If you activate the checkboxes links to web pages to process the edit, add or delete action are included in the generated web page. If the file name for your generated list is phpfile.php, the link for "Edit Record" refers to phpfile_edit.php, "Add Record" refers to phpfile_add.php and "Delete Record" refers to phpfile_del.php. These files are not created automatically. You can either create your own pages to add, delete and edit records, or you can generate them with the PHP Code Generator for Input/Edit Forms or Delete Forms.

Press "Next >>" to continue. If you have selected "List with Navigation" a settings page for the navigation is displayed. Go on to the next page.

Generate Web Page

The Assistant now displays a summary of your settings. Click "Create Page" to actually create the PHP/JSP page or click "<< Back" to make any changes to your settings.

Test the generated PHP/JSP page

Before closing the Assistant you should check if the generated web page is really what you wanted. If you want to change a setting you can always go back using the Assistant and re-create the PHP/JSP page.

If you are using a local installation (like eSuite4X - Web Developer's Edition) you can open your web browser and enter http://localhost/<the-name-of-my-generated-file> to display the result set in your web browser. If you are using a remote installation you have to upload the PHP/JSP pages with your web authoring tool first.

 

Generate an Input/Edit Form

 

How the Input/Edit Form Generator works

The Input/Edit Form Generator creates a web page (PHP or JSP, depending on the site settings of the selected site) for adding new records to your table and/or modifying existing records for the selected table. You can use the graphical field editor of the Input/Edit Form Generator to add all necessary fields from your table to the web form, specify the display order, display width and default values.

1. Create an Input/Edit Form

Open Database Navigator and select a table. Make sure you have created a web site entry with the PHP Code Generator Setup for the connection/database you are selecting.

2. Start the PHP/JSP Code Generator

The Code Generator is located in the Database Navigator toolbar (Generate PHP/JSP). If the Code Generator icon is not displayed in the toolbar press ctrl and click on the toolbar background to pop up the toolbar context menu and select "Customize Toolbar...". You can add the Code Generator icon with the customization dialog.

Click the Generate PHP/JSP icon to display the Code Generator window

 
     
 

3. Run the Input/Edit Form Generator

From the Code Generator select "Create Input/Edit Form" to start the Input/Edit Form Generator.

Site and file settings

First select your Site (this is the name of the web site you have created with the Code Generator Setup) and specify the file name and location of the new PHP/JSP file for the Input and/or Edit file. Press the "..." button to display the Save dialog. Your PHP/JSP file must be located in the site root folder or any sub-folder of your site root folder. It cannot be saved outside your site root. A Edit or Input web page will only be generated if you specify a file name to save the generated script.

Unique Row Identifier

You must specify a column which uniquely identifies each record in the table. This ID Column is necessary to pass on the selected record to edit and delete forms. If your table has a primary key, the primary key field is automatically selected.

Recommendation: Your tables should always include a primary key to uniquely identify each record!

Page Style

Now set the Page Style. If you select "Simple List" for Page Style all records will be displayed on one web page. This is great for small results.

If you have a result set with several hundred records (or more) you should select "List with Navigation". This will automatically generate the PHP/JSP code necessary to navigate through your result set.

Check the Include "Edit Record", "Add Record" and "Delete Record" boxes to fit your needs. If you activate the checkboxes links to web pages to process the edit, add or delete action are included in the generated web page. If the file name for your generated list is phpfile.php, the link for "Edit Record" refers to phpfile_edit.php, "Add Record" refers to phpfile_add.php and "Delete Record" refers to phpfile_del.php. These files are not created automatically. You can either create your own pages to add, delete and edit records, or you can generate them with the PHP Code Generator for Input/Edit Forms or Delete Forms.

Press "Next >>" to continue. If you have selected "List with Navigation" a settings page for the navigation is displayed. Go on to the next page.

After processing Input/Edit Form go to

You can specify a web page which should be displayed after your input or edit form has been processed. Click the "..." button to display the Open dialog to select a web page.

 

Click the "Next >>" button to continue with the Field Editor.

     
 

Input/Edit Form Generator Field Editor

Add the fields in the order you want the displayed on the generated web page.

Data Type   Formatting and Display Options
TEXT  

Single Line

For single line text input fields select data type "TEXT". Default values can be specified. Width specifies the width of the input field in number of characters.

Multi Line

For multi line text input fields select data type "TEXT". Default values can be specified. Width specifies the width of the input field in number of characters. Height specifies the height of the input field in number of lines.

     
HIDDEN   Add the specified field's value as hidden field. This option is used to pass on values (like record id's) without actually displaying the value.
     
PASSWORD   Similar to a single line text field, but the value is not displayed directly. Instead of the actually entered characters a placeholder character (*) is displayed.
     
COMBO  

Displays a pop-up or pull-down menu to choose pre-defined values. The values can be manually pre-defined or retrieved from your database with a second query.

Specify menu values manually

Select the "Values" tab from the Combo Box Values input section. The displayed values are not directly stored in the database. In most cases a second (encoded) value is stored instead. To make this possible you have to enter two lines for each combo box value. The first line is the displayed value and the second line is the stored value that is save to the database if the user selects this value.

Example:

Single
1
Married
2

Only "Single" and "Married" are displayed on screen. If the user selects "Married" the value "2" is stored in the database.

Retrieve menu values via SQL Queries

If you want to display values stored in a special value encoding table your query result set must have exactly two columns. The first column is displayed as menu item on screen, the second column is stored in the database if the user selects the value. To enter a SQL Query select the "Query" tab from the Combo Box Values input section.

Example:

select display, value from encoding where type='LANGUAGE'

     
INTEGER   INTEGER fields only accept numerical values.
     
DOUBLE   DOUBLE fields only accept numerical values.
     
DATE   DATE fields only accept date values.
     
CHECKBOX  

The CHECKBOX type displays a checkbox on screen. The following values are stored in your tables for selected/unselected checkbox values:

TYPE  
Selected
Not Selected
 
CHECKBOX Y/N  
Y
N
  
CHECKBOX 1/0  
1
0
  
CHECKBOX -1/0  
-1
0
  

 

     

 

Click the "Next >>" button to go to the code generator.

     
 

Generate Web Page

The Assistant now displays a summary of your settings. Click "Create Page" to actually create the PHP/JSP page or click "<< Back" to make any changes to your settings.

Test the generated PHP/JSP page

Before closing the Assistant you should check if the generated web page is really what you wanted. If you want to change a setting you can always go back using the Assistant and re-create the PHP/JSP page.

If you are using a local installation (like eSuite4X - Web Developer's Edition) you can open your web browser and enter http://localhost/<the-name-of-my-generated-file> to display the result set in your web browser. If you are using a remote installation you have to upload the PHP/JSP pages with your web authoring tool first.

 

 

Generate a Delete Form

The Delete Form Generator works exactly like the Input/Edit Form Generator. Please follow the guidelines provided above to generate a web page to delete a record from your selected table. The fields specified with the Field Editor will be displayed with the confirmation page before deleting the record from the table.