Part 1: Adding records to a database

Opus web publications and Flex publications are unable to communicate with database using the standard Database Query actions (which rely on ODBC drivers). However, it is possible to use Post Web Data actions in conjunction with PHP or ASP script files to send and retrieve data from a database on the web server.

The following steps will show you how to set up a MySQL database on the web server, create a PHP file which will write records to this database and produce an Opus publication which will send this information to the PHP file.

Testing the PHP and MySQL installations

This procedure assumes that PHP 4.x and MySQL are installed and correctly configured on your web server. To test this, simply create a file in Notepad containing the following script:

<?php phpinfo() ?>

and save this file in the document root of your webserver (this is the htdocs folder on an Apache web server or the wwwroot folder on an IIS web server) with the name test.php. Once saved, please open your browser and navigate to the address http://webserver/test.php (where webserver is the IP address or domain name of your web server or localhost if the web server is running on the local machine).

If PHP is correctly installed, an information page should appear listing version numbers and installed modules. To confirm whether your PHP installation supports connections to MySQL databases, simply scroll down this list and look for a table named mysql or mysqli. Please also ensure that the MySQL service is running on the web server (this is usually indicated by a green traffic light icon in the system tray or you can run the services.msc applet from the Run command to check if this service is installed and running).

Creating the MySQL database

All being well, please use a graphical front-end (such as MySQL Administrator or phpMyAdmin) or the command line to create a new MySQL database (sometimes called a schema) named mydb. In this database, create a new table named employees containing the following four fields:

first

set this to data type varchar(25)

last

set this to data type varchar(25)

address

data type varchar(255)

position

data type varchar(50)

In real-world situations, you would probably want to add an auto-incrementing primary key as the first field of this database so that each record has a unique numerical record, but this is not necessary for this example.

Creating the PHP file

Now launch Notepad and insert the following script commands:

<?php

$first = $HTTP_POST_VARS['first'];
$last = $HTTP_POST_VARS['last'];
$address = $HTTP_POST_VARS['address'];
$position = $HTTP_POST_VARS['position'];

$DBhost = "localhost";
$DBuser = "username";
$DBpass = "password";
$DBName = "mydb";

$connect = mysql_connect($DBhost,$DBuser,$DBpass);
$db = mysql_select_db($DBName,$connect);
$query = "INSERT INTO employees (first, last, address, position)
           VALUES ('$first','$last','$address','$position')";
$result = mysql_query($query, $connect);

?>

Please replace localhost with the address of the database server (or leave it as localhost if the server is running on the local machine), replace username with a user who has access to the mydb database (for example, root) and replace password with the password for this user (by default, the root user's password is blank, but you may have altered this in the MySQL configuration).

Now save this document in the document root of your web server with the file name insert.php.

Creating the Opus publication

Now create a new publication in Opus and add four text input fields to the page. Set the first input field to store information into a new variable named first, the second to a variable named last, the third to a variable named address and the final field to store information into a variable named position (all of these variables should be to publication variable with an initial blank text value).

Now add a button to the page and apply a Post Web Data action. As we want the Opus publication to be the source of the data, select the Source tab. In the URL field, input the address to the insert.php file on your web server (if the server is running on the local machine, this will probably be http://localhost/insert.php)

elect the Multiple Fields option, then click the Add button four times to create four parameters and rename the parameters to first, last, address and position respectively. Now open the drop-down menu to the right of each parameter and select the corresponding variable from the list. The screen should now resemble the following:

Click Apply and OK to confirm the changes.

Now preview the publication, input some information into each of the fields and click the button to post the data.

You can now check that the data has been added to the table using phpMyAdmin, MySQL Query Browser or launching a mysql command prompt and issuing the command:

SELECT * FROM employees;

This should confirm that the inputted data was successfully written to the database.