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:
Code:
<?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 databaseAll 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 fileNow launch Notepad and insert the following script commands:
Code:
<?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 publicationNow 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)
Select 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:
Code:
SELECT * FROM employees;
This should confirm that the inputted data was successfully written to the database.
Login
FAQ
Search
this is what we're loking for!
