Opus Pro Tutorial:

Web Databases

Part 2: Displaying Records

Creating the PHP File

You will be unable to use the original script to retrieve data from the database as the HTTP_POST_VARS functions are only able to import post data and cannot send information back to the publication.

To send information back to the publication, you will need to pull the records from the database into an array (using the mysql_fetch array() function), assign each of the strings to an index of the returned array, then issue name-value pairs using the echo function. For example: 

<?php

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

$connect = mysql_connect($DBhost,$DBuser,$DBpass);
$db = mysql_select_db($DBName,$connect);
$query = "SELECT * FROM employees";
$result = mysql_query($query, $connect);

$row = mysql_fetch_array($result );
$data = "&first=".$row['first']."&last=".$row['last'].n ↵
  "&address=".$row['address']."&position=".$row['position'];
echo $data

?>

Important note: For clarity of layout, the line: 

$data = "&first=".$row['first']."&last=".$row['last'].n ↵
  "&address=".$row['address']."&position=".$row['position'];

has been wrapped. In your script it should be on a single line, without the " ↵".

You will again need to ensure that the username and password values on lines 4 and 5 are changed to a user who has read/write access to the 'mydb' database.

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

Creating the Opus publication

Now launch Opus, create a blank new publication and perform the following steps:

  1. Add four text boxes (not input boxes) to the page
  2. Right-click on the first text box and select Insert Variable, then create a new variable named first with a default blank text value
  3. Insert a variable named last (again with a default blank text value) into the second text box
  4. Insert variables named address and position into the third and fourth boxes respectively
  5. Now right-click on the page and select Edit Actions
  6. Apply an On Show trigger followed by a Post Web Data action
  7. In the URL field, type in your domain name followed by a forward slash and the file name select.php. For example, http://www.digitalworkshop.com/select.php or if the web server is running on the local machine http://localhost/select.php
  8. Select the Destination tab and enable the Multiple Fields option
  9. Click the Add button four times to create four strings
  10. Rename each of the strings to match the variable names, first, last, address and position
  11. Open the drop-down menus to the right of the strings and assign each string to the corresponding variable (for example, assign the string first to the variable first)
  12. Click Apply and OK

Now preview the publication. The first records from the database should be displayed in the text boxes.

using MySQL and php in Opus Pro