How to render SQL (multi tables)?

Suite and Optimus Suite 5
#1

Hi guys,

I’m using dhtlmx first time and I’m struggling a lot.

I want to show a treegrid using following database (mySQL, PHP):
table “part”
id, part_number, others which are not important
table “supplier”
id, name
table “part_relation”
id, id_part, id_parent, id_supplier

Following SQL gives me a list of all parts
“SELECT part.id, part_number, supplier.name, id_parent FROM part
LEFT JOIN part_relation ON part.id = part_relation.id_part
LEFT JOIN supplier ON part_relation.id_supplier = supplier.id’, ‘part.id’, 'part.id, part_number, supplier.name”

I want to show a structured bill of material, where id_parent shows for each part which is its parent part.

I don’t know how to render the SQL to get the right result.

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

require($php_path.“/treegridmultitable_connector.php”);
$treegrid = new TreeGridMultitableConnector($res);

$treegrid->render_sql(‘SELECT part.id, part_number, supplier.name, id_parent FROM part
LEFT JOIN part_relation ON part.id = part_relation.id_part
LEFT JOIN supplier ON part_relation.id_supplier = supplier.id’, ‘part.id’, ‘part.id, part_number, supplier.name’, ‘’, ‘id_parent’);

The result is
-
−

0%23
E84


−

0%23
1845154


−

0%23
…
which is obviously wrong.

Who can help?

Karl

#2

Try to use

[code]$treegrid = new TreeGridMultitableConnector($res);
$treegrid ->setMaxLevel(1);
$level = $tree->get_level();

switch ($level) {
	case 0:
		$tree->render_table("supplier","id","name","","");
		break;
	case 1:
		$tree->render_sql("

SELECT part.id as id, part_number FROM part
LEFT JOIN part_relation ON part.id = part_relation.id_part", “id”, “part_number”, “”, "id_supplier ");
break;
}[/code]

Not quite sure about render_sql part.
Basically - you can create different sql queries for each level, instead of sinlge render sql for all levels.

Also, if you are using connectors only for loading and sql relations are complex, it may have sense to write xml data directly , by custom php code.

#3

Hi Stanislav,

thank you very much for the information.
Unfortunately I’m the whole week on business trip so I can work on the issue not before Saturday. I will post again if I can’t solve it with your advice.

Karl