Digital Workshop

Welcome to the Digital Workshop Message Boards
It is currently December 30th, 2024, 12:25 am

All times are UTC [ DST ]




Post new topic Reply to topic  [ 4 posts ] 
Author Message
 Post subject: Post Web Data - Part 2: Displaying records from a database
PostPosted: May 4th, 2005, 11:22 am 
Offline

Joined: October 25th, 2004, 12:27 pm
Posts: 526
Location: Digital Workshop
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.


For this message Robin Garrett has been thanked by : mackavi


Top
 Profile Visit website  
 
 Post subject: Searching for a record to display
PostPosted: November 21st, 2005, 1:12 pm 
Offline

Joined: August 24th, 2005, 8:48 pm
Posts: 3
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


For this message jorous has been thanked by : mackavi


Top
 Profile  
 
 Post subject:
PostPosted: October 2nd, 2009, 10:10 am 
Offline

Joined: December 11th, 2004, 12:51 am
Posts: 36
Location: UK
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


For this message markatnicebrook has been thanked by : mackavi


Top
 Profile  
 
 Post subject:
PostPosted: January 6th, 2010, 6:29 pm 
Offline

Joined: February 18th, 2005, 5:44 pm
Posts: 17
Location: Michigan (USA)
Opus: V7.04
OS: Win7 32-bit
System: Pentium QUAD 4GB RAM ATI HD 57XX
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!


For this message FishBox has been thanked by : mackavi


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 4 posts ] 

All times are UTC [ DST ]


Who is online

Users browsing this forum: No registered users and 0 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group