Digital Workshop
http://forum.digitalworkshop.co.uk/

Post Web Data - Part 2: Displaying records from a database
http://forum.digitalworkshop.co.uk/viewtopic.php?f=13&t=745		 Page 1 of 1
Author:  Robin Garrett [ May 4th, 2005, 11:22 am ]
Post subject:  Post Web Data - Part 2: Displaying records from a database
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:

Code:
<?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']."&address=".$row['address']."&position=".$row['position'];
echo $data
?>


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:

- Add four text boxes (not input boxes) to the page
- Right-click on the first text box and select Insert Variable, then create a new variable named first with a default blank text value
- Insert a variable named last (again with a default blank text value) into the second text box
- Insert variables named address and position into the third and fourth boxes respectively
- Now right-click on the page and select Edit Actions
- Apply an On Show trigger followed by a Post Web Data action
- 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
- Select the Destination tab and enable the Multiple Fields option
- Click the Add button four times to create four strings
- Rename each of the strings to match the variable names, first, last, address and position
- 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)
- Click Apply and OK

Now preview the publication. The first records from the database should be displayed in the text boxes.
Author:  jorous [ November 21st, 2005, 1:12 pm ]
Post subject:  Searching for a record to display
Thanks for the tutorial steps finally al worked fine after getting the rights info from my host.
Could you please help with a simple way I can use to search and display a specific record using the post web data action. I would like to be able to search by user or by name and password and once record is found to be displayed on the screen. I managed to display only the first record but have no idea how to search or go to the next record.

Thanks

Joe
Author:  markatnicebrook [ October 2nd, 2009, 10:10 am ]
Post subject: 
Does anyone have an idea how to read multiple rows of data from the database back into the opus publication?

This tutorial explains how to pass a single row of data back, but extending this to multiple rows is not obvious (to me at least!).

e.g. for a database that contains multiple rows of 'day', 'month', 'year', 'state' where:
'state' is either 0, 1 or 2....and is the data I'm interested in
'day' is 0 to 30
'month' is 0 to 11
'year' is 2009, 2010 etc

My current php file is named select.php (where the "include" statement contains the $DBhost, $DBuser, $DBpass, $DBName data...database username, password etc):

<?php
$Day = $HTTP_POST_VARS['newday'];
$Month = $HTTP_POST_VARS['newmonth'];
$Year = $HTTP_POST_VARS['newyear'];
include ("db_info_inc.php");

$connect = mysql_connect($DBhost,$DBuser,$DBpass);
$db = mysql_select_db($DBName,$connect);
$query = "SELECT * FROM contacts WHERE day='$Day' AND month='$Month' AND year='$Year'";
$result = mysql_query($query, $connect);

$row = mysql_fetch_array($result);
$data = "&state=".$row['state'];
echo $data
?>

and the first row is passed to the Opus publication by
adding a "post web action" with:
URL set as: ...select.php

Source - multiple fields - string and variables both set as:
newday
newmonth
newyear
Destination - multiple fields - string and variables both set as:
state

i.e. the database is searched for the row corresponding to
day = newday
month = newmonth
year = newyear
and the value of 'state' read from the database is passed back to the opus variable 'state'

All works OK.

My question is:
How can this be extended to pass back multiple row values to Opus?

e.g. in my example, how do I pass back to Opus all values of 'state' for
month = newmonth
year = newyear

I know that in the php file, the next row can be read by just repeating:
$row = mysql_fetch_array($result);
$data = "&state=".$row['state'];

but I'm totally stuck for an idea of how to pass this back to Opus. I guess I could loop the "post web action" within the Opus publication for all the days in the month, but this is not a very sophisticated solution!

Any help appreciated.

/Mark
Author:  FishBox [ January 6th, 2010, 6:29 pm ]
Post subject: 
You can loop through all the rows found and create a return string with appended "state0","state1", "state2", etc for each found reference:

Code:
$query = "SELECT * FROM contacts WHERE day='$Day' AND month='$Month' AND year='$Year'";
$result = mysql_query($query, $connect);

$return_string="";
$count =0;
while($row = mysql_fetch_array($result)){
   //using the .= appends each state reference to the return_string instead of overwriting it
   $return_string .= "&state".$count."=".$row['state'];
   $count++;
}

echo $return_string;


Of course, in Opus you will need to create the state0, state1, etc. variables as placeholders in the Destination of the Post Web Data object for all the possible state results.

Hope this helps!
Page 1 of 1 All times are UTC [ DST ]
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/