MySQL - render_sql doesn't return good id for treemultitable

Suite and Optimus Suite 5
#1

Hi,

i’m currently trying to build a treemultitable with the following connector :

require_once(“dhtmlxConnector_php/codebase/treemultitable_connector.php”);

require_once(“php/config.php”);
$res=mysql_connect($mysql_server,$mysql_user,$mysql_pass);
mysql_select_db($mysql_db);

$tree = new TreeMultitableConnector($res,“MySQL”);
$tree->setMaxLevel(1);
$level = $tree->get_level();

switch ($level) {
case 0:
$tree->render_sql(“SELECT etablissement_id, raison_sociale FROM etablissement WHERE level = 0”,“etablissement_id”,“raison_social”,“”,“”);
break;
case 1:
$tree->render_sql(“SELECT e.etablissement_id, e.raison_sociale, e.etablissement_idp FROM etablissement e WHERE e.level = 1”, “etablissement_id”, “raison_sociale”, “”, “etablissement_idp”);
break;
}

the result is :



the issue is the items “0#1” and “0#4” because they should be respectively “1” and “4”. These items comes from field “etablissement_id” auto incremented which is the primary key of table “etablissement”. Because of false items, child items aren’t attached…

Thanks

#2

Hi,
could you provide complete demo with simple test database dump?

#3

Hi,

here the elements you asked.

the sql dump is :

SET SQL_MODE=“NO_AUTO_VALUE_ON_ZERO”;
SET time_zone = “+00:00”;

CREATE TABLE IF NOT EXISTS etablissement (
etablissement_id bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘PK etablissement’,
raison_sociale varchar(100) COLLATE utf8_unicode_ci NOT NULL,
adresse varchar(100) COLLATE utf8_unicode_ci NOT NULL,
code_postal int(5) NOT NULL,
ville varchar(100) COLLATE utf8_unicode_ci NOT NULL,
siret bigint(14) NOT NULL,
code_ape varchar(5) COLLATE utf8_unicode_ci NOT NULL,
level tinyint(1) NOT NULL,
etablissement_idp bigint(20) DEFAULT NULL COMMENT ‘etablissement parent’,
responsable_id bigint(20) NOT NULL COMMENT ‘FK responsable’,
PRIMARY KEY (etablissement_id),
KEY responsable_id (responsable_id),
KEY etablissement_idp (etablissement_idp)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ;

INSERT INTO etablissement (etablissement_id, raison_sociale, adresse, code_postal, ville, siret, code_ape, level, etablissement_idp, responsable_id) VALUES
(1, ‘holding 1’, ‘8 rue de la republique’, 75001, ‘Paris’, 12345678901234, ‘4305Z’, 0, NULL, 1),
(2, ‘filiale 1’, ‘38 rue de chanzy’, 75002, ‘Paris’, 12345678900001, ‘0432F’, 1, 1, 1),
(3, ‘filiale 2’, ‘32 rue des calvers’, 75003, ‘Paris’, 12345678900006, ‘4329M’, 1, 1, 1),
(4, ‘etablissement 1’, ‘53 rue des champs’, 75004, ‘Paris’, 12345678901207, ‘9432R’, 0, NULL, 1);

CREATE TABLE IF NOT EXISTS responsable (
responsable_id bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘PK responsable’,
nom_responsable varchar(100) COLLATE utf8_unicode_ci NOT NULL,
fonction_responsable varchar(100) COLLATE utf8_unicode_ci NOT NULL,
nom_contact varchar(100) COLLATE utf8_unicode_ci NOT NULL,
fonction_contact varchar(100) COLLATE utf8_unicode_ci NOT NULL,
telephone_contact int(10) NOT NULL,
mail_contact varchar(100) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (responsable_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2 ;

INSERT INTO responsable (responsable_id, nom_responsable, fonction_responsable, nom_contact, fonction_contact, telephone_contact, mail_contact) VALUES
(1, ‘nom1 prenom1’, ‘DAF’, ‘nom2 prenom2’, ‘comptable’, 102030405, 'nom2.prenom2@test.com’);

ALTER TABLE etablissement
ADD CONSTRAINT etablissement_ibfk_6 FOREIGN KEY (responsable_id) REFERENCES responsable (responsable_id) ON DELETE CASCADE;

the html demo is :

Etablissement 
<link rel="STYLESHEET" type="text/css" href="./codebase/dhtmlx.css">
<script src="./codebase/dhtmlx.js" type="text/javascript"></script>

<script src="./codebase/dhtmlxcontainer.js"></script>
<script src="./codebase/dhtmlxcommon.js"></script>

<link rel="stylesheet" type="text/css" href="./codebase/dhtmlxlayout.css">
<link rel="stylesheet" type="text/css" href="./codebase/skins/dhtmlxlayout_dhx_blue.css">
<script src="./codebase/dhtmlxlayout.js"></script>

<link rel="stylesheet" type="text/css" href="./codebase/dhtmlxtree.css">
<script src="./codebase/dhtmlxtree.js"></script>

<style>
html, body {
    width: 100%;
    height: 100%;
    margin: 0px;
    overflow: hidden;
}
</style>

finally the php connector is :

<?php require_once("./dhtmlxConnector_php/codebase/treemultitable_connector.php"); require_once("./config.php"); $conn=mysql_connect($mysql_server,$mysql_user,$mysql_pass); mysql_select_db($mysql_db); $tree = new TreeMultitableConnector($conn,"MySQL"); $tree->setMaxLevel(1); $level = $tree->get_level(); switch ($level) { case 0: $tree->render_sql("SELECT etablissement_id, raison_sociale FROM etablissement WHERE level = 0","etablissement_id","raison_sociale","",""); break; case 1: $tree->render_sql("SELECT e.etablissement_id, e.raison_sociale, e.etablissement_idp FROM etablissement e WHERE e.level = 1", "etablissement_id", "raison_sociale", "", "etablissement_idp"); break; } ?>
#4

Hi,
item ids are correct. Multitable connector generates item ids according the follow principle:
{level}#{id from database}

Item childs are always loaded dynamically, so you should enable auto loading.
Please, modify tree initialization code like here:

arbre = layout_a.attachTree();
arbre.setImagePath("./codebase/imgs/csh_bluebooks/");
arbre.setXMLAutoLoading("./connector.php");
arbre.loadXML("./connector.php");
#5

Thanks, it works great.
Though, it wasn’t mentionned in the page : docs.dhtmlx.com/doku.php?id=dhtm … econnector