Start Building Professional
Web Apps Today


 
Categories Question details Back To List
Question  posted by Barry on Nov 10, 2009 16:17
open dhtmlx forum
Grid, Connector & MySQL Views

Hi,

I'm trying to query a mysql (crosstab) view based on another view (complex joins) using dhtmlxgrid.

The headers of the grid with the filters appear but then I get a blank alert message and no data is displayed in the grid.

I get the following message in the log file:


Undefined offset: 14 at /home/barry/public_html/beauxcreations.com/grade/report/quickfilter/codebase/grid_connector.php line 143

!!!Uncaught Exception
Code: 0
Message: Incorrect dataset minimization, master field not found.

I'm using the 'student' field (first and last name) as the master field.

In my php file I have:

<?php
require_once("config.php");
$res=mysql_connect($mysql_server,$mysql_user,$mysql_pass);
mysql_select_db($mysql_db);
require("codebase/grid_connector.php");

$grid = new GridConnector($res);

$grid->dynamic_loading(50);
$filter1 = new OptionsConnector($res);

$filter1->render_sql("SELECT `student` , `Upload a single file` FROM `my_crosstab_view`", "student","`Upload a single file`");

$grid->set_options("student",$filter1);

$grid->render_sql(("SELECT `student` , `Upload a single file` FROM `my_crosstab_view`", "student","`Upload a single file`");

?>

My grid configuration looks like this:

<script>
mygrid = new dhtmlXGridObject('gridbox');
mygrid.setImagePath("common/imgs/");
mygrid.setHeader("Student, Upload a singlefile");
mygrid.attachHeader("#connector_text_filter,#connector_select_filter");
mygrid.setInitWidths("100,100");
mygrid.setColTypes("txttxt,txttxt");
mygrid.setColSorting("connector,connector");
mygrid.enableSmartRendering(true);
mygrid.enableMultiselect(true);
mygrid.setSkin("dhx_skyblue");
mygrid.enableAlterCss("even","uneven");

mygrid.init();

mygrid.loadXML("allgrades.php");
var dp = new dataProcessor("allgrades.php");
dp.init(mygrid);

</script>

Actually my query has 14 fields but I made it shorter to be easier to read. Each field name is quite long and contains spaces but no special characters.

Here is the first view's sql:
CREATE VIEW `my_grades` AS select concat(`user`.`firstname`,' ',`user`.`lastname`) AS `student`,`course`.`shortname` AS `shortname`,`grade_items`.`itemname` AS `itemname`,`grade_grades`.`finalgrade` AS `finalgrade`,`grade_grades`.`feedback` AS `feedback` from (((((`grade_items` join `user`) join `role_assignments` on((`role_assignments`.`userid` = `user`.`id`))) left join `grade_grades` on(((`grade_grades`.`itemid` = `grade_items`.`id`) and (`grade_grades`.`userid` = `user`.`id`)))) join `course` on((`grade_items`.`courseid` = `course`.`id`))) join `grade_categories` on((`grade_items`.`categoryid` = `grade_categories`.`id`))) where ((`role_assignments`.`roleid` = 5) and (`grade_items`.`itemtype` = 'mod'));

Here is the 'my_cross_tab' view's sql:
CREATE VIEW `my_crosstab_view` AS select `my_grades`.`student` AS `student`,sum(if((`my_grades`.`itemname` = 'Upload a single file'),`my_grades`.`finalgrade`,0)) AS `Upload a single file` from `my_grades` group by `my_grades`.`student`;

The views work perfectly in phpmyadmin so I'm not sure why it's not working with the dhtmlxgrid.

Any idea what the problem might be?
Answer posted by Stanislav (support) on Nov 11, 2009 01:43
a) Do you have connector_select_filter for any other columns, except of the second one? Be sure to use set_options for each column with select-filter. 
b) try to change 
$filter1->render_sql("SELECT `student` , `Upload a single file` FROM `my_crosstab_view`", "student","`Upload a single file`"); 
as
$filter1->render_table("my_crosstab_view","student","student(label),`Upload a single file`(value)"); 
Answer posted by Barry on Nov 11, 2009 09:45
Following your instructions, the grid is partly showing. 

My grid's data values are still blank/empty except for the student names, column headers and filters which now appear to work fine.

For each row in my query I now get a 'Undefined Index:' notices in my log file for each column except the student field.

How can I get the data to actually appear in my grid?

Answer posted by Barry on Nov 11, 2009 09:53
My grid configuration looks like this (created dynamically with php):

<script>
	mygrid = new dhtmlXGridObject('gridbox');
	mygrid.setImagePath("common/imgs/");

mygrid.setHeader("student, A database of web links, A listening quiz, A quiz with a password, A standard forum for general use, Advanced uploading of files, An FAQ-style glossary, Attendance, Barcode Scannig Attendance Module Support Forum, eXe SCORM package, Lesson 1 - Basic parts, Lesson 2 - Question types, Offline activity, Online text, Upload a single file");mygrid.attachHeader("#connector_text_filter,#connector_select_filter,#connector_select_filter,#connector_select_filter,#connector_select_filter,#connector_select_filter,#connector_select_filter,#connector_select_filter,#connector_select_filter,#connector_select_filter,#connector_select_filter,#connector_select_filter,#connector_select_filter,#connector_select_filter,#connector_select_filter");mygrid.setInitWidths("100,60,60,60,60,60,60,60,60,60,60,60,60,60,60");mygrid.setColTypes("txttxt,txttxt,txttxt,txttxt,txttxt,txttxt,txttxt,txttxt,txttxt,txttxt,txttxt,txttxt,txttxt,txttxt,txttxt");mygrid.setColSorting("connector,connector,connector,connector,connector,connector,connector,connector,connector,connector,connector,connector,connector,connector,connector");
    mygrid.enableSmartRendering(true);
    mygrid.enableMultiselect(true);
    mygrid.setSkin("dhx_skyblue");
    mygrid.enableAlterCss("even","uneven");
    mygrid.init();

    mygrid.loadXML("allgrades.php");
    var dp = new dataProcessor("allgrades.php");
    dp.init(mygrid);
</script>
My php file looks like this:

    $grid = new GridConnector($res);
    $grid->enable_log("temp.log",true);
    $grid->dynamic_loading(50);
    $filter1 = new OptionsConnector($res);

$filter1->render_table("my_crosstab_view", "student", "student,`A database of web links`, `A listening quiz` , `A quiz with a password` , `A standard forum for general use` , `Advanced uploading of files` , `An FAQ-style glossary` , `Attendance`, `Barcode Scannig Attendance Module Support Forum` , `eXe SCORM package` , `Lesson 1 - Basic parts` , `Lesson 2 - Question types` , `Offline activity` , `Online text` , `Upload a single file`");

$grid->set_options("student", $filter1);

$grid->render_sql("SELECT student, `A database of web links`, `A listening quiz` , `A quiz with a password` , `A standard forum for general use` , `Advanced uploading of files` , `An FAQ-style glossary` , `Attendance`, `Barcode Scannig Attendance Module Support Forum` , `eXe SCORM package` , `Lesson 1 - Basic parts` , `Lesson 2 - Question types` , `Offline activity` , `Online text` , `Upload a single file` FROM my_crosstab_view", "sutdent","`student`,`A database of web links`, `A listening quiz` , `A quiz with a password` , `A standard forum for general use` , `Advanced uploading of files` , `An FAQ-style glossary` , `Attendance`, `Barcode Scannig Attendance Module Support Forum` , `eXe SCORM package` , `Lesson 1 - Basic parts` , `Lesson 2 - Question types` , `Offline activity` , `Online text` , `Upload a single file`","id","student, `A database of web links`, `A listening quiz` , `A quiz with a password` , `A standard forum for general use` , `Advanced uploading of files` , `An FAQ-style glossary` , `Attendance`, `Barcode Scannig Attendance Module Support Forum` , `eXe SCORM package` , `Lesson 1 - Basic parts` , `Lesson 2 - Question types` , `Offline activity` , `Online text` , `Upload a single file` FROM my_crosstab_view", "id","`student`,`A database of web links`, `A listening quiz` , `A quiz with a password` , `A standard forum for general use` , `Advanced uploading of files` , `An FAQ-style glossary` , `Attendance`, `Barcode Scannig Attendance Module Support Forum` , `eXe SCORM package` , `Lesson 1 - Basic parts` , `Lesson 2 - Question types` , `Offline activity` , `Online text` , `Upload a single file`");

I must be missing something, hope you can help
Answer posted by Alex (support) on Nov 12, 2009 03:08

Hello, 

try to replace  `Upload a single file`","id","student, `A database of web links` with  Upload a single file,id,student,A database of web links

The problem can be caused by "`" in the field names, it works correctly for SQL, but field fetching fails because of this char

Answer posted by Barry on Nov 12, 2009 03:20
Thanks, I just tried it.  It made all my rows disappear and I get an SQL error in the log file:

!!!Uncaught Exception
Code: 0
Message: MySQL operation failed
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'single file as value FROM my_crosstab_view' at line 1

After changing it back I get the 'Undefined Index:' errors again.

I think it might be easier if I give you an SQL dump of the table I'm trying to use.  Please see the attached file (it has three rows and 15 columns).

Thanks a lot for your help.


Answer posted by Alex (support) on Nov 12, 2009 03:33

try to check that the following (the query that you use) is correct:

SELECT student, `A database of web links`, `A listening quiz` , `A quiz with a password` , `A standard forum for general use` , `Advanced uploading of files` , `An FAQ-style glossary` , `Attendance`, `Barcode Scannig Attendance Module Support Forum` , `eXe SCORM package` , `Lesson 1 - Basic parts` , `Lesson 2 - Question types` , `Offline activity` , `Online text` , `Upload a single file` FROM my_crosstab_view", "sutdent","`student`,`A database of web links`, `A listening quiz` , `A quiz with a password` , `A standard forum for general use` , `Advanced uploading of files` , `An FAQ-style glossary` , `Attendance`, `Barcode Scannig Attendance Module Support Forum` , `eXe SCORM package` , `Lesson 1 - Basic parts` , `Lesson 2 - Question types` , `Offline activity` , `Online text` , `Upload a single file`","id","student, `A database of web links`, `A listening quiz` , `A quiz with a password` , `A standard forum for general use` , `Advanced uploading of files` , `An FAQ-style glossary` , `Attendance`, `Barcode Scannig Attendance Module Support Forum` , `eXe SCORM package` , `Lesson 1 - Basic parts` , `Lesson 2 - Question types` , `Offline activity` , `Online text` , `Upload a single file` FROM my_crosstab_view

Try to execute this query seperately and check if it is correct.

Also please delete all ` occurences from the 3rd parameter of the render_sql method:

$grid->render_sql("....", "id","student,A database of web links,A listening quiz,A quiz with a password,A standard forum for general use,Advanced uploading of files,An FAQ-style glossary,Attendance,Barcode Scannig Attendance Module Support Forum,eXe SCORM package,Lesson 1 - Basic parts,Lesson 2 - Question types,Offline activity,Online text,Upload a single file");

Answer posted by Barry on Nov 12, 2009 04:22
Thanks,

The query I'm using works perfectly in phpmyadmin.

If I remove all ` occurences from the 3rd parameter of the render_sql method now rows appear in my grid and I get the following error:

====================================
Log started, 12/11/2009 06:11:08
====================================

SELECT DISTINCT A database of web links as value FROM test

!!!Uncaught Exception
Code: 0
Message: MySQL operation failed
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database of web links as value FROM test' at line 1

So I think the ` occurences are actually necessary for MySQL to accept it.

Here is the php as I have it now (I previously pasted the sql in twice by mistake in my other post):

$filter1->render_table("test", "id", "student,`A database of web links`, `A listening quiz` , `A quiz with a password` , `A standard forum for general use` , `Advanced uploading of files` , `An FAQ-style glossary` , `Attendance`, `Barcode Scannig Attendance Module Support Forum` , `eXe SCORM package` , `Lesson 1 - Basic parts` , `Lesson 2 - Question types` , `Offline activity` , `Online text` , `Upload a single file`");

$grid->set_options("id", $filter1);

$grid->render_sql("SELECT id, student, `A database of web links`, `A listening quiz` , `A quiz with a password` , `A standard forum for general use` , `Advanced uploading of files` , `An FAQ-style glossary` , `Attendance`, `Barcode Scannig Attendance Module Support Forum` , `eXe SCORM package` , `Lesson 1 - Basic parts` , `Lesson 2 - Question types` , `Offline activity` , `Online text` , `Upload a single file` FROM test", "id","`student`,`A database of web links`, `A listening quiz` , `A quiz with a password` , `A standard forum for general use` , `Advanced uploading of files` , `An FAQ-style glossary` , `Attendance`, `Barcode Scannig Attendance Module Support Forum` , `eXe SCORM package` , `Lesson 1 - Basic parts` , `Lesson 2 - Question types` , `Offline activity` , `Online text` , `Upload a single file`");

Now I have three rows in my grid again but they are still empty.  The headers names and filter values are fine.

I still get the 'Undefined Index' error in my log file for each row * each column.  (Copy attached)

All the select queries listed in the log file work fine if I paste them into phpmyadmin.

I'm using a real table now (dumped from my crosstab view as per previous post with attachment) but still the same results

Maybe it's problematic to have spaces in field names?

Attachments (1)
log.txt8.77 Kb
Answer posted by Alex (support) on Nov 12, 2009 08:57
please try delete all ` occurences from the 3rd parameter of the render_sql or render_table method:

$grid->render_table("test", "id","student,A database of web links,A listening quiz,A quiz with a password,A standard forum for general use,Advanced uploading of files,An FAQ-style glossary,Attendance,Barcode Scannig Attendance Module Support Forum,eXe SCORM package,Lesson 1 - Basic parts,Lesson 2 - Question types,Offline activity,Online text,Upload a single file");

Answer posted by Barry on Nov 12, 2009 10:15
I did delete all ` occurences as I mentioned above.

This is what happens when I delete them:
http://luxcreate.com/quickfilter/index.php
http://luxcreate.com/quickfilter/temp.log

And when I put them back:
http://luxcreate.com/index.php
http://luxcreate.com/temp.log

At least when I put them back I have some rows and filters that appear.  It's just that the rows are empty.  The correct values appear in the select filters though.  You can see exactly what I mean if you visit the links.
Answer posted by Barry on Nov 12, 2009 15:13
Hi there,

I finally got it right.

It is not acceptable by MySQL to have spaces in field names (hence my use of ` ), neither do the render_sql and/or render_table methods accept the ` (which is needed by MySQL) if the field names contain spaces.

So I replaced all spaces with underscores like this:
   
$filter1->render_table("test","id","student,A_database_of_web_links,A_listening_quiz,A_quiz_password,A_standard_forum_for_general_use,Advanced_uploading_of_files,An_FAQ_style_glossary,Attendance,Barcode_Scannig_Attendance_Module_Support_Forum,eXe_SCORM_package,Lesson_1_Basic_parts,Lesson_2_Question_types,Offline_activity,Online_text,Upload_a_single_file");
   
$grid->set_options("id", $filter1);
   
$grid->render_sql("SELECT id,student,A_database_of_web_links,A_listening_quiz,A_quiz_password,A_standard_forum_for_general_use,Advanced_uploading_of_files,An_FAQ_style_glossary,Attendance,Barcode_Scannig_Attendance_Module_Support_Forum,eXe_SCORM_package,Lesson_1_Basic_parts,Lesson_2_Question_types,Offline_activity,Online_text,Upload_a_single_file FROM test","id","student,A_database_of_web_links,A_listening_quiz,A_quiz_password,A_standard_forum_for_general_use,Advanced_uploading_of_files,An_FAQ_style_glossary,Attendance,Barcode_Scannig_Attendance_Module_Support_Forum,eXe_SCORM_package,Lesson_1_Basic_parts,Lesson_2_Question_types,Offline_activity,Online_text,Upload_a_single_file");


Now everything works fine.

I find this quite strange though.  Is there any reason for it not accepting the  `  character whereas MySQL does accept it?  Maybe it's a problem for other databases?

Anyway, thanks for your help and patience to get this resolved.  I'll just sort out the table names dynamically when my crosstab query generates them.