Grid, Connector & MySQL Views

Barry · November 11, 2009, 12:17am

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:

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?

Support1 · November 11, 2009, 9:43am

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)”);

Barry · November 11, 2009, 5:45pm

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?

Barry · November 11, 2009, 5:53pm

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

Alex_V_Tech_Support · November 12, 2009, 11:08am

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

Barry · November 12, 2009, 11:20am

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.

Alex_V_Tech_Support · November 12, 2009, 11:33am

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”);

Barry · November 12, 2009, 12:22pm

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?

log.txt (8.57 KB)

Alex_V_Tech_Support · November 12, 2009, 4:57pm

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”);

Barry · November 12, 2009, 6:15pm

I did delete all ` occurences as I mentioned above.

This is what happens when I delete them:
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.

Barry · November 12, 2009, 11:13pm

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.