(no subject)
"Hameed U. Khan" <[email protected]> Fri, 11 Aug 2006 20:37:43 +0500
| Newsgroups | gmane.text.rlib.user |
|---|---|
| Message-ID | <[email protected]> |
Hi, I'm having problems in executing and displaying more than 2 sql queries. I want to generate two report tables. I have a table in my mysql named clean. Which have following structure. ID : STATUS : DATE : TIME: IP : from : to I want to split tables based on the status field. The queries I'm executing are: 1. SELECT * FROM clean WHERE status = 'sent' 2. SELECT * FROM clean WHERE status = 'recv' The output which I'm trying to get is: ______________________________ Sent --------------------------------------------------- rows here... ____________________________ Recv ------------------------------------------------ rows here... I've attached all the files and mysql dump. Kindly If any can guide me how can I solve this scenario, I'll be very thankfull to you. Thanks. -- Hameed U. Khan Registered Linux User #: 354374 "Beware of programmers who carry screwdrivers." ------------------------------------------------------------------------- Using Tomcat but need to do more? Need to support web services, security? Get stuff done quickly with pre-integrated technology to make your job easier Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642 _______________________________________________ Rlib-users mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/rlib-users
clean.sql
(text/x-sql, 2.1 KB)
-- MySQL dump 10.8 -- -- Host: localhost Database: qmail -- ------------------------------------------------------ -- Server version 4.1.7 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE="NO_AUTO_VALUE_ON_ZERO" */; -- -- Table structure for table `attach` -- DROP TABLE IF EXISTS `clean`; CREATE TABLE `clean` ( `id` int(10) NOT NULL auto_increment, `status` varchar(5) default NULL, `log_date` date default NULL, `log_time` time default NULL, `subject` varchar(50) default NULL, `ip_addr` varchar(50) default NULL, `from_add` varchar(50) default NULL, `to_add` varchar(255) default NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Dumping data for table `clean` -- /*!40000 ALTER TABLE `clean` DISABLE KEYS */; LOCK TABLES `clean` WRITE; INSERT INTO `clean` VALUES (1,'recv','2006-08-10','05:48:26','test mail','255.255.255.255','[email protected]','[email protected],[email protected]'),(2,'sent','2006-08-10','06:28:56','','127.0.0.1','[email protected]','[email protected]'),(3,'sent','2006-08-10','06:36:40','','127.0.0.1','[email protected]','[email protected]'),(4,'sent','2006-08-10','06:37:08','','127.0.0.1','[email protected]','[email protected]'),(5,'sent','2006-08-10','06:41:03','','127.0.0.1','[email protected]','[email protected]'),(6,'recv','2006-08-10','06:44:12','','127.0.0.1','[email protected]','[email protected]'),(7,'recv','2006-08-10','06:45:48','','127.0.0.1','[email protected]','[email protected],[email protected]'),(8,'recv','2006-08-10','06:48:35','','127.0.0.1','[email protected]','[email protected],[email protected]'),(9,'recv','2006-08-10','06:49:53','','127.0.0.1','[email protected]','[email protected],[email protected]'); UNLOCK TABLES; /*!40000 ALTER TABLE `clean` ENABLE KEYS */; --
example.php
(application/x-php, 721 B)
<?php
dl ("rlib.so");
$hostname = "localhost";
$username = "root";
$password = "";
$database = "qmail";
$rlib = rlib_init();
rlib_add_datasource_mysql($rlib, "clean_sent", $hostname, $username, $password, $database);
rlib_add_query_as($rlib, "clean_sent", "select * FROM clean where status='recv'", "rclean");
rlib_add_query_as($rlib, "clean_sent", "select * FROM clean where status='sent'", "sclean");
rlib_add_report($rlib, "products.xml");
rlib_set_output_format_from_text($rlib, "html");
rlib_execute($rlib);
header(rlib_get_content_type($rlib));
rlib_spool($rlib);
rlib_free($rlib);
?>
products.xml
(text/xml, 6.5 KB)
<?xml version="1.0"?>
<!DOCTYPE report >
<Part layout="fixed" orientation="landscape" iterations="1">
<PageHeader>
<Output>
<Line fontSize="11">
<literal>Qmail Server Report</literal>
</Line>
<HorizontalLine size="4" bgcolor="'white'"/>
</Output>
</PageHeader>
<pr>
<pd>
<Report fontSize="9" query="rclean">
<alternate>
<nodata>
<output>
<line fontsize="12" >
<literal> No Data </literal>
</line>
</output>
</nodata>
</alternate>
<ReportHeader>
<Output>
<HorizontalLine size="4" bgcolor="'white'"/>
<HorizontalLine size="1" bgcolor="'black'"/>
<Line fontSize="12">
<literal>Clean Recieve</literal>
</Line>
<HorizontalLine size="1" bgcolor="'white'"/>
</Output>
</ReportHeader>
<Detail>
<FieldHeaders>
<Output>
<HorizontalLine size="1" bgcolor="'black'"/>
<Line bgcolor="'0xe5e5e5'">
<literal width="15" col="3">Date</literal>
<literal width="1"/>
<literal width="15" col="4">Time</literal>
<literal width="1"/>
<literal width="15" col="1">File</literal>
<literal width="1"/>
<literal width="20" col="2">Origin IP</literal>
<literal width="1"/>
<literal width="35" col="3">From</literal>
<literal width="1"/>
<literal width="35" col="4">To</literal>
</Line>
<HorizontalLine size="1" bgcolor="'black'"/>
<HorizontalLine size="4" bgcolor="'white'"/>
</Output>
</FieldHeaders>
<FieldDetails>
<Output>
<Line bgcolor="iif(r.detailcnt%2,'0xe5e5e5','white')">
<field value="log_date" width="15" align="left" col="3"/>
<literal width="1"/>
<field value="log_time" width="15" align="left" col="4"/>
<literal width="1"/>
<field value="subject" width="15" align="left" col="1"/>
<literal width="1"/>
<field value="ip_addr" width="20" align="left" col="2"/>
<literal width="1"/>
<field value="from_add" width="35" align="left" col="3" memo="yes"/>
<literal width="1"/>
<field value="to_add" width="35" align="left" col="4" memo="yes"/>
</Line>
</Output>
</FieldDetails>
</Detail>
</Report>
<Report fontSize="9" query="sclean">
<alternate>
<nodata>
<output>
<line fontsize="12" >
<literal> No Data </literal>
</line>
</output>
</nodata>
</alternate>
<ReportHeader>
<Output>
<HorizontalLine size="40" bgcolor="'white'"/>
<HorizontalLine size="1" bgcolor="'black'"/>
<Line fontSize="12">
<literal>Clean Sent</literal>
</Line>
<HorizontalLine size="1" bgcolor="'white'"/>
</Output>
</ReportHeader>
<Detail>
<FieldHeaders>
<Output>
<HorizontalLine size="1" bgcolor="'black'"/>
<Line bgcolor="'0xe5e5e5'">
<literal width="15" col="3">Date</literal>
<literal width="1"/>
<literal width="15" col="4">Time</literal>
<literal width="1"/>
<literal width="15" col="1">File</literal>
<literal width="1"/>
<literal width="20" col="2">Origin IP</literal>
<literal width="1"/>
<literal width="35" col="3">From</literal>
<literal width="1"/>
<literal width="35" col="4">To</literal>
</Line>
<HorizontalLine size="1" bgcolor="'black'"/>
<HorizontalLine size="4" bgcolor="'white'"/>
</Output>
</FieldHeaders>
<FieldDetails>
<Output>
<Line bgcolor="iif(r.detailcnt%2,'0xe5e5e5','white')">
<field value="sclean.log_date" width="15" align="left" col="3"/>
<literal width="1"/>
<field value="sclean.log_time" width="15" align="left" col="4"/>
<literal width="1"/>
<field value="sclean.subject" width="15" align="left" col="1"/>
<literal width="1"/>
<field value="sclean.ip_addr" width="20" align="left" col="2"/>
<literal width="1"/>
<field value="sclean.from_add" width="35" align="left" col="3" memo="yes"/>
<literal width="1"/>
<field value="sclean.to_add" width="35" align="left" col="4" memo="yes"/>
</Line>
</Output>
</FieldDetails>
</Detail>
</Report>
</pd>
</pr>
<PageFooter>
<Output>
<Line>
<literal>Page: </literal>
<field value="r.pageno" width="3" align="right"/>
</Line>
</Output>
</PageFooter>
</Part>