This is a migrated thread and some comments may be shown as answers.

Select database where [mysql] [GANTT]

7 Answers 445 Views

Chart

This is a migrated thread and some comments may be shown as answers.

This question is locked. New answers and comments are not allowed.

Valerius asked on 27 Oct 2017, 08:33 PM

Hi there, can you guys help my to create select database where.... There is a code that working good.

require_once '../lib/DataSourceResult.php';
require_once '../lib/Kendo/Autoload.php';

if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    header('Content-Type: application/json');

    $request = json_decode(file_get_contents('php://input'));

    $result = new DataSourceResult('mysql:host=localhost:3306 ;dbname=database_4', 'user', 'password');

    $type = $_GET['type'];

    $operation = $_GET['operation'];

    if ($type == "dependency") {
        $columns = array('ID', 'PredecessorID', 'SuccessorID', 'Type');

        $table = "GanttDependencies";
    } else {
        $columns = array('ID', 'ParentID', 'OrderID', 'name', 'start_date', 'due_date', 'progress', 'Expanded', 'Summary');

        $table = "GanttTasks";
    }

Is there any possible to put there a select with some conditions?

For example I would like to select all the records where id = 10.
Can you help my in this?

7 Answers, 1 is accepted

answered on 31 Oct 2017, 12:29 PM

Hello Valerius,

A complete example on how to configure the Gantt widget dependencies with the PHP Wrappers can be found in this part of the Remote Binding Documentation. There, you will find a sample approach on how to implement the create, read, update and destroy end-points for the Gantt widget:

<?php

if ($type == 'read') {

$statement = $db->prepare('

SELECT *,

strftime(\'%Y-%m-%dT%H:%M:%SZ\', Start) as Start,

strftime(\'%Y-%m-%dT%H:%M:%SZ\', End) as End

FROM GanttTasks

');

$statement->execute();

$result = $statement->fetchAll(PDO::FETCH_ASSOC);

}

?>

I hope this helps.

Regards,
Dimitar
Progress Telerik

Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.

answered on 05 Nov 2017, 08:27 PM

Hi Dimitar, One thing. Could you please put this code:

<?php

if ($type == 'read') {

$statement = $db->prepare('

SELECT *,

strftime(\'%Y-%m-%dT%H:%M:%SZ\', Start) as Start,

strftime(\'%Y-%m-%dT%H:%M:%SZ\', End) as End

FROM GanttTasks

');

$statement->execute();

$result = $statement->fetchAll(PDO::FETCH_ASSOC);

}

?>

to the template below: I would like to check if I'm doing it right 'cause I'm using db to show the gantt results.Can you please check Yours example, and tell my where exactly I could use above condition?

<!DOCTYPE html>

<html>

<head>

<title></title>

<link rel="stylesheet" href="styles/kendo.common.min.css" />

<link rel="stylesheet" href="styles/kendo.default.min.css" />

<link rel="stylesheet" href="styles/kendo.default.mobile.min.css" />

<script src="js/jquery.min.js"></script>

<script src="js/kendo.all.min.js"></script>

</head>

<body>

<?php

require_once '../lib/DataSourceResult.php';

require_once '../lib/Kendo/Autoload.php';

if ($_SERVER['REQUEST_METHOD'] == 'POST') {

header('Content-Type: application/json');

$request = json_decode(file_get_contents('php://input'));

$result = new DataSourceResult('sqlite:..//sample.db');

$type = $_GET['type'];

$operation = $_GET['operation'];

if ($type == "dependency") {

$columns = array('ID', 'PredecessorID', 'SuccessorID', 'Type');

$table = "GanttDependencies";

} else {

$columns = array('ID', 'ParentID', 'OrderID', 'Title', 'Start', 'End', 'PercentComplete', 'Expanded', 'Summary');

$table = "GanttTasks";

}

switch($operation) {

case 'create':

$result = $result->create($table, $columns, $request, 'ID');

break;

case 'update':

$result = $result->update($table, $columns, $request, 'ID');

break;

case 'destroy':

$result = $result->destroy($table, $request, 'ID');

break;

default:

$result = $result->read($table, $columns, $request);

break;

}

echo json_encode($result, JSON_NUMERIC_CHECK);

exit;

}

// tasks datasource

$transport = new \Kendo\Data\DataSourceTransport();

$create = new \Kendo\Data\DataSourceTransportCreate();

$create->url('index.php?type=task&operation=create')

->contentType('application/json')

->type('POST');

$read = new \Kendo\Data\DataSourceTransportRead();

$read->url('index.php?type=task&operation=read')

->contentType('application/json')

->type('POST');

$update = new \Kendo\Data\DataSourceTransportUpdate();

$update->url('index.php?type=task&operation=update')

->contentType('application/json')

->type('POST');

$destroy = new \Kendo\Data\DataSourceTransportDestroy();

$destroy->url('index.php?type=task&operation=destroy')

->contentType('application/json')

->type('POST');

$transport->create($create)

->read($read)

->update($update)

->destroy($destroy)

->parameterMap('function(data) {

return kendo.stringify(data);

}');

$taskModel = new \Kendo\Data\DataSourceSchemaModel();

$idField = new \Kendo\Data\DataSourceSchemaModelField('id');

$idField->type('number')

->from('ID')

->nullable(true);

$orderIdField = new \Kendo\Data\DataSourceSchemaModelField('orderId');

$orderIdField->from('OrderID')

->type('number');

$parentIdField = new \Kendo\Data\DataSourceSchemaModelField('parentId');

$parentIdField->from('ParentID')

->defaultValue(null)

->type('number');

$startField = new \Kendo\Data\DataSourceSchemaModelField('start');

$startField->from('Start')

->type('date');

$endField = new \Kendo\Data\DataSourceSchemaModelField('end');

$endField->from('End')

->type('date');

$titleField = new \Kendo\Data\DataSourceSchemaModelField('title');

$titleField->from('Title')

->defaultValue('')

->type('string');

$percentCompleteField = new \Kendo\Data\DataSourceSchemaModelField('percentComplete');

$percentCompleteField->from('PercentComplete')

->type('number');

$summaryField = new \Kendo\Data\DataSourceSchemaModelField('summary');

$summaryField->from('Summary')

->type('boolean');

$expandedField = new \Kendo\Data\DataSourceSchemaModelField('expanded');

$expandedField->from('Expanded')

->defaultValue(true)

->type('boolean');

$taskModel->id('id')

->addField($idField)

->addField($parentIdField)

->addField($orderIdField)

->addField($startField)

->addField($endField)

->addField($titleField)

->addField($percentCompleteField)

->addField($summaryField)

->addField($expandedField);

$schema = new \Kendo\Data\DataSourceSchema();

$schema->model($taskModel)

->data("data");

$tasks = new \Kendo\Data\DataSource();

$tasks->transport($transport)

->schema($schema)

->batch(false);

// dependencies datasource

$transport = new \Kendo\Data\DataSourceTransport();

$create = new \Kendo\Data\DataSourceTransportCreate();

$create->url('index.php?type=dependency&operation=create')

->contentType('application/json')

->type('POST');

$read = new \Kendo\Data\DataSourceTransportRead();

$read->url('index.php?type=dependency&operation=read')

->contentType('application/json')

->type('POST');

$update = new \Kendo\Data\DataSourceTransportUpdate();

$update->url('index.php?type=dependency&operation=update')

->contentType('application/json')

->type('POST');

$destroy = new \Kendo\Data\DataSourceTransportDestroy();

$destroy->url('index.php?type=dependency&operation=destroy')

->contentType('application/json')

->type('POST');

$transport->create($create)

->read($read)

->update($update)

->destroy($destroy)

->parameterMap('function(data) {

return kendo.stringify(data);

}');

$dependenciesModel = new \Kendo\Data\DataSourceSchemaModel();

$idField = new \Kendo\Data\DataSourceSchemaModelField('id');

$idField->from('ID')

->type('number');

$typeField = new \Kendo\Data\DataSourceSchemaModelField('type');

$typeField->from('Type')

->type('number');

$predecessorIdField = new \Kendo\Data\DataSourceSchemaModelField('predecessorId');

$predecessorIdField->from('PredecessorID')

->type('number');

$successorIdField = new \Kendo\Data\DataSourceSchemaModelField('successorId');

$successorIdField->from('SuccessorID')

->type('number');

$dependenciesModel->id('id')

->addField($idField)

->addField($typeField)

->addField($predecessorIdField)

->addField($successorIdField);

$schema = new \Kendo\Data\DataSourceSchema();

$schema->model($dependenciesModel)

->data("data");

$dependencies = new \Kendo\Data\DataSource();

$dependencies->transport($transport)

->schema($schema)

->batch(false);

// columns

$idColumn = new \Kendo\UI\GanttColumn();

$idColumn->field("id")

->title("ID")

->width(50);

$titleColumn = new \Kendo\UI\GanttColumn();

$titleColumn->field("title")

->title("Title")

->editable(true)

->sortable(true);

$startColumn = new \Kendo\UI\GanttColumn();

$startColumn->field("start")

->title("Start Time")

->format("{0:MM/dd/yyyy}")

->width(100)

->editable(true)

->sortable(true);

$endColumn = new \Kendo\UI\GanttColumn();

$endColumn->field("end")

->title("End Time")

->format("{0:MM/dd/yyyy}")

->width(100)

->editable(true)

->sortable(true);

// gantt

$gantt = new \Kendo\UI\Gantt('gantt');

$gantt->dataSource($tasks)

->dependencies($dependencies)

->height(700)

->addView(

'day',

array('type' => 'week', 'selected' => true),

'month'

)

->addColumn($idColumn, $titleColumn, $startColumn, $endColumn)

->showWorkHours(false)

->showWorkDays(false)

->snap(false);

?>

<?php

echo $gantt->render();

?>

</body>

</html>

answered on 06 Nov 2017, 07:54 PM

Guys, I'm looking for the solution, and cannot find anything right. You're showing a full examples but it's not related to the documentation I can find on your pages.... I cannot find nothing which can help me to SELECT DATABASE WHERE basing on Your examples:http://demos.telerik.com/php-ui/gantt/index

My last question here. Is there any possible to SELECT database with some conditions like:
SELECT ID, ParentID, OrderID, Title, Start, End, PercentComplete, Expanded, Summary WHERE ID = 100
Can I use this example, can you help me to connect these two things?

http://demos.telerik.com/php-ui/gantt/index,
and put there some conditions I've wrote above?

answered on 07 Nov 2017, 07:06 AM

Hello Valerius,

For a complete example with detailed description and steps on how to configure the Gantt widget to work with remote data, you can refer to the following Gantt Remote Binding Documentation.

In the code sample provided, the tasks and dependencies DataSources are configured, but not the remote end-points that return the JSON data. Below you can find sample steps to follow (more detailed exmplanation is available in the above documentation article):

1) Configure the Tasks DataSource:

<?php

$taskTransport = new \Kendo\Data\DataSourceTransport();

// Configure the remote service - a PHP file called 'tasks.php'

// The query string parameter 'type' specifies the type of CRUD operation

$taskRead = new \Kendo\Data\DataSourceTransportRead();

$taskRead->url('tasks.php?type=read')

->contentType('application/json')

->type('POST');

....
?>

2) Create file performing CRUD on tasks:

<?php

header('Content-Type: application/json');

$request = json_decode(file_get_contents('php://input'));

$result = new DataSourceResult('sqlite:..//sample.db');

$type = $_GET['type'];

$data = null;

if ($type == 'read') {

// The 'read' method accepts table name, array of columns to select and request parameters as array

$data = $result->read('GanttTasks',

array('Title', 'strftime(\'%Y-%m-%dT%H:%M:%SZ\', Start) as Start', 'strftime(\'%Y-%m-%dT%H:%M:%SZ\', End) as End', 'ID', 'OrderID', 'ParentID', 'PercentComplete', 'Summary', 'Expanded'),

$request);

}

header('Content-Type: application/json');

echo json_encode($data, JSON_NUMERIC_CHECK);

?>

Regards,
Dimitar
Progress Telerik

Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.

answered on 08 Nov 2017, 10:14 PM

Hi Dimitar, Can you please explain why demo samples (of gantt) are totally different like examples in the documentation? This dosen't works at all...

Only what I've tried to do is to add some simple condition for tasks of gannt basing on the demo...(I've put here the demo example from your site three times.)
http://demos.telerik.com/php-ui/gantt/index

All the answer I've got here relates to the documentaion which is fully misundersttanding for me, and I don't really know what's going on there.

Or you're don't understand what I'm asking for or kendo is to hard to me...
Thanks, I'm not buy this.

answered on 10 Nov 2017, 03:08 PM

Hello Valerius,

I am really sorry to see your frustration about the difference in the resources that we have on the matter. I understand it, because Indeed you are right and there is a difference between the documentation and our demo site. There is some lack of examples, in order to achieve a parity in a code usage demonstration point of view. We will update our documentation, in order to provide accurate examples in the scenario that we discuss in this case.

As for the issue itself - in order to query the result set with specific Where clause, you can use the DataSourceResult object. Actually this object is created, in order to ease such queries. This could be done by adding a DataSourceFilterItem. These filter items are interpreted as where clause parameters. In other words, you can create such item, define your where clause (ID=[value]) and add this item to the dataSource transport method:

$tasks = new \Kendo\Data\DataSource();

$filter = new \Kendo\Data\DataSourceFilterItem();

$filter-$filter->field('ID')

->operator('eq')

->value('7');

$tasks->transport($transport)

->schema($schema)

->addFilterItem($filter)

->serverFiltering(true)

->batch(false);

Also, note that the serverFiltering should be enabled, so that the filter could be correctly applied.

Having in mind that you use the index demo for testing purposes, the above-demonstrated implementation should render only the first task in the Gantt.

Below you can find the documentation article, describing the DataSourceFilterItem:

https://docs.telerik.com/kendo-ui/api/php/Kendo/Data/DataSourceFilterItem

Regards,
Nencho
Progress Telerik

Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.