mB0D69xu

1One query to get data out of 2 tables in mysql (with php)
2CREATE TABLE IF NOT EXISTS `table1` (
3  `id` int(11) NOT NULL auto_increment,
4  `numero_membre` varchar(255) NOT NULL default '',
5  `membre` tinyint(1) NOT NULL default '0',
6    (...ALOT OF FIELDS, I DONT FEEL THE NEED TO SHOW THEM ALL)
7  `ordering` int(10) NOT NULL default '0',
8  `dateModified` datetime NOT NULL default '0000-00-00 00:00:00',
9  `dateCreated` datetime NOT NULL default '0000-00-00 00:00:00',
10  PRIMARY KEY  (`id`)
11);
12
13CREATE TABLE IF NOT EXISTS `table2` (
14  `id` int(10) NOT NULL auto_increment,
15  `nom` varchar(255) NOT NULL default '',
16  `type` varchar(255) NOT NULL default '',
17  `published` tinyint(1) NOT NULL,
18  `ordering` int(10) NOT NULL,
19  `datemodified` datetime NOT NULL default '0000-00-00 00:00:00',
20  `datecreated` datetime NOT NULL default '0000-00-00 00:00:00',
21  PRIMARY KEY  (`id`)
22);
23
24CREATE TABLE `table3` (
25    `id` INT(10) NOT NULL AUTO_INCREMENT,
26    `idchamps` VARCHAR(255) NOT NULL DEFAULT '',
27    `idprofil` VARCHAR(255) NOT NULL DEFAULT '',
28    `value` VARCHAR(255) NOT NULL DEFAULT '',
29    PRIMARY KEY( `id` )
30);
31	
32function getExportObject() {
33    // --> FOR EXPORT
34    //Building Query
35    $postdata   =   JRequest::get( 'post' );
36    $exportFields           = $postdata['addfield'];        //Table 1
37    $exportCustomFields = $postdata['addcfield'];       //table 2 name, table 3 value
38    $sqlSelect = '';
39    //Main profil Fields...
40    foreach($exportFields as $key => $field) {
41        if ($sqlSelect!='') $sqlSelect .= ', ';
42        $sqlSelect .= '`'.$field.'`';
43    }
44
45    //Add filters to the SELECT
46    //GET LIST OF ALL FIELDS....
47    $sqlFilter  = '';
48    $loopFields = $this->getFields();
49    foreach($loopFields as $key => $field) {
50        if (JRequest::getVar('filter_'.$field)!='') {
51            if ($sqlFilter=='') { $sqlFilter .= " WHERE "; }
52            else { $sqlFilter .= " AND "; }
53
54            switch(JRequest::getVar('filter_'.$field)) {
55                case 'EQUAL'                : $sqlFilter .= "`".$field."`='".JRequest::getVar('filter_val_'.$field)."'"; break;
56                case 'NOT_EQUAL'        : $sqlFilter .= "`".$field."`!='".JRequest::getVar('filter_val_'.$field)."'"; break;
57                case 'CONTAINS'         : $sqlFilter .= "`".$field."` LIKE '%".JRequest::getVar('filter_val_'.$field)."%'"; break;
58                case 'NOT_CONTAINS' : $sqlFilter .= "`".$field."` NOT LIKE '%".JRequest::getVar('filter_val_'.$field)."%'"; break;
59                default                         : die('Utilisation invalid. SVP. essayez de nouveau.'); break;
60            }
61        }
62    }
63    //PUTTING IT ALL TOGETTHER
64    //"SELECT t1.*, t2.nom, t3.value FROM table1 t1 LEFT JOIN table3 t3 INNER JOIN table2 t2 ON t3.fieldname = t2.nom ON t1.id = t3.idprofil"
65    $query = "SELECT ".$sqlSelect." FROM `table1`".$sqlFilter;
66    echo $query;
67    exit();
68    $this->_db->setQuery($query);
69    $exportList = $this->_db->loadObjectList();
70    return $exportList;
71} //End of getExportObject()
72	
73SELECT t1.*, t2.nom, t3.value
74    FROM table1 t1
75        LEFT JOIN table3 t3
76            INNER JOIN table2 t2
77                ON t3.table_2_id = t2.id
78            ON t1.id = t3.idprofil