Editor-1.0.0 == Creating complex SQL Queries.

alanchavez

Hi,
I've been playing with the free version of editor to see if I'm capable to suit it to my needs, but I already encounter my first problem,

I like to have many tables to keep consistency among my information,
In my scenario I have a table called `persons` and another one called `roles`.
In `persons` I have a column called `role_id`
In `roles` I have another column called `id`, and `description`

What my query does it's basically, grabs `role_id` from `persons` and matches it against `role`.`id` and prints `description` for that ID, pretty simple.

A query that I would use in another environment would be something like:
[code]
$strSQL = "SELECT CONCAT(`persons`.`firstname`,\" \",`persons`.`lastname`) AS `fullname`, `roles`.`description` FROM `persons` LEFT JOIN `roles` on `persons`.`role_id`= `roles`.`id`";
[/code]

I've been playing with the examples, but all I can get is to print the role_id, but not the name given for that role_id, and I was wondering if anyone here have done something similar to this, and if so; how do you implement it.

Thanks!

allan

Hi,

How are you implementing the JOIN on the server-side for Editor? In the 1.0.0 package my ready made PHP classes don't currently have support for pulling data in from multiple linked tables (i.e. JOINs). This is a planned feature for 1.1.0.

Having said that, it is entirely possible to put together a simple PHP script which will perform the queries that you need on your specific schema without using my classes. Editor's client/server interaction is filled defined here http://editor.datatables.net/server/ .

Regards,
Allan

PeteB

I have a similar setup for my rugby team database.

I have 3 tables: players, honours, and playerhonours

To update the playerhonours table I have an Ajax module to obtain the list for mthe datatable, and then an Editor function to control the updates:

The Ajax code:[code] /*
* Script: DataTables server-side script for Php and MySQL
* Copyright: 2010 - Allan Jardine
* License: GPL v2 or BSD (3-point)
*/

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Easy set variables
*/

/* Array of database columns which should be read and sent back to DataTables. Use a space where
* you want to insert a non-database field (for example a counter or static image)
*/
$aColumns = array( 'hpID', 'honname', 'hpseasonID', 'lastnme' , 'firstnme', 'hphonourID', 'hpplayerID' );


/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "hpID";

/* DB table to use */
$sTable1 = "honourplayers";
$sTable2 = "honours";
$sTable3 = "players";

$sTable = $sTable1 . ' LEFT JOIN ' . $sTable2 . ' ON (' . $sTable2 . '.honourID = ' . $sTable1 . '.hphonourID)';
$sTable = $sTable . ' LEFT JOIN ' . $sTable3 . ' ON (' . $sTable3 . '.playerID = ' . $sTable1 . '.hpplayerID)';
[/code]

and the Editor call: [code]/*
* Php implementation used for the adm_honourplayers.php MySQL access
*/

include( "include/db.php" );
include( "include/DTEditor.pdo.class.php" );

$editor = new DTEditor(
$db, // DB resource
'honourplayers', // DB table
//'id', // Primary key
'hpID', // Primary key
'row_', // ID prefix to add to the TR rows (makes it valid and unique)
array( // Fields
new DTField( array(
"name" => "hpID",
"dbField" => "hpID",
"set" => false,
"dynamicGet" => true
)),
new DTField( array(
"name" => "hphonourID",
"dbField" => "hphonourID",
"validator" => "DTValidate::required"
)),
new DTField( array(
"name" => "hpseasonID",
"dbField" => "hpseasonID",
"validator" => "DTValidate::required"
)),
new DTField( array(
"name" => "hpplayerID",
"dbField" => "hpplayerID",
"validator" => "DTValidate::required"
))
)
);[/code]

HTH

Pete.