Replies

• compcentral Posts: 40Questions: 0Answers: 0

  March 2011 edited March 2011

  I see what might be a solution here: http://datatables.net/plug-ins/filtering#html_column
  
  I'm just not sure how to implement it. Where should the code listed go? I tried adding it to the multiple locations in the section of the webpage, but nothing seemed to make a difference.
  
  I also noticed that filtering is broken too... If you type "hidden" into the search box, nothing should be displayed, but since that is part of the html tags, everything matches and is shown.
  
  HELP?
• compcentral Posts: 40Questions: 0Answers: 0

  March 2011

  anyone help? still can't seem to get this figured out. I'm sure it's something simple.
• compcentral Posts: 40Questions: 0Answers: 0

  March 2011

  HELLO?
  
  Is anyone out there?
  
  I sure wish I could get an answer or at least an acknowledgement. It feels like the forums are meant to suck on purpose to force people into "donating" to get help. I love open source software and I'm all for donations, but come on. If I would receive adequate and prompt support for simple questions THEN I would gladly donate... but not the other way around.
  
  Anyway, sorry about that. Anyone out there have any ideas?
  
  Thanks.
• allan Posts: 63,214Questions: 1Answers: 10,415 Site admin

  March 2011

  Hi compcentral,
  
  Sorry you haven't had an answer to your question yet - I'm just one person and simply can't answer the 30+ questions that I get every single day unfortunately while also trying to continue development of DataTables. I do try my best to get through as many as possible though...
  
  From your initialisation you are using server-side processing, which means that the sorting is being done on the server-side, and not on the client-side by DataTables. So the answer to your question lies in PHP and SQL (note that the sType property that you've set has no effect on that, since sType is client-side only).
  
  So... how is the data stored in your database? I'm going to guess that the HTML is in there since you are saying that it sorted by that, and the SQL engine will be doing that sorting. Is that right, or are you rendering in the HTML somewhere else? Ideally it would be the latter since then the sort is trivial! If you could elaborate on that, perhaps I'll be able to offer a solution (hopefully promptly :-) ).
  
  Regards,
  Allan
• compcentral Posts: 40Questions: 0Answers: 0

  March 2011 edited March 2011

  Thank you Allan for responding. Right now, I'm using the SQL concat() function to add the HTML directly to the SELECT query, so I end up with something like this:
  
  [code]
  SELECT concat ("", c.name, "" ), c.courseType, concat( "", t.firstName, " ", t.lastName, "" ) fullname, concat( "", d.shortName, "" ) district, c.startDate, c.endDate
  FROM courses c, teachers t, districts d
  WHERE t.teacherID = c.teacherID
  AND d.districtID = c.districtID
  [/code]
  
  This is done using all the variables in the standard example php file like this:
  
  [code]
  $q1 = "'";
  $q2 = '"';
  
  $courseName = "concat ( ".$q2."
• allan Posts: 63,214Questions: 1Answers: 10,415 Site admin

  March 2011

  Hi compcentral,
  
  The client-side code _could_ sort it, but since the client-side only has the information for each individual page that is shown and not the full dataset (when using server-side processing) then you would only be sorting that single page, and not the full data set - which would result in incorrect behaviour.
  
  Is there a reason why you are doing the concat in SQL and not in PHP? It seems to me that you are really doing the operation twice at the moment (building the query and then having the SQL engine do the concat). Can you do the rendering of the HTML return to DataTables after you've done the SQL queries needed? That should also result in the ordering being correct (since at the moment I would guess the resultant query is actually doing ORDER BY contact(...).
  
  Allan
• compcentral Posts: 40Questions: 0Answers: 0

  March 2011

  Hmm.. okay. Yeah. I see your point. I never thought of modifying the $output array after pulling the data from the database. That would actually be a bit easier to code that anyway. Let me give that a try and get back to you.
• allan Posts: 63,214Questions: 1Answers: 10,415 Site admin

  March 2011

  If you have a look at line 164 in this code: http://www.datatables.net/development/server-side/php_mysql - you can see one why of putting in custom output for the individual columns. Alternatively you don't need to do the loop at all - just output a fixed array if you know what it's going to be!
  
  Allan
• compcentral Posts: 40Questions: 0Answers: 0

  March 2011 edited March 2011

  Still does the same thing. You can view the new code in action here: http://blogs.wiueacademy.org/courseenroll/test.php
  
  New server-side code follows:
  
  [code]
  <?php
  $aColumns = array( 'c.courseID', 'c.name', 'c.courseType', 't.email', 't.firstName', 't.lastName', 'd.websiteURL', 'd.shortName', 'c.enrollmentCount', 'c.maxEnrollment', 'c.startDate', 'c.endDate');
  
  $sIndexColumn = "c.courseID";
  $sIndexTable = "courses c";
  $sTable = "courses c, teachers t, districts d";
  $sWhere = "WHERE c.teacherID = t.teacherID AND c.districtID = d.districtID";
  
  /* Database connection information removed*/
  
  $gaSql['link'] = mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password'] ) or
  die( 'Could not open connection to server' );
  
  mysql_select_db( $gaSql['db'], $gaSql['link'] ) or
  die( 'Could not select database '. $gaSql['db'] );
  
  $sLimit = "";
  if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
  {
  $sLimit = "LIMIT ".mysql_real_escape_string( $_GET['iDisplayStart'] ).", ".
  mysql_real_escape_string( $_GET['iDisplayLength'] );
  }
  
  if ( isset( $_GET['iSortCol_0'] ) )
  {
  $sOrder = "ORDER BY ";
  for ( $i=0 ; $i $iFilteredTotal,
  "aaData" => array()
  );
  
  while ( $aRow = mysql_fetch_array( $rResult ) )
  {
  $row = array();
  for ( $i=0 ; $i
  [/code]
• allan Posts: 63,214Questions: 1Answers: 10,415 Site admin

  March 2011

  I think it might be an idea to print out the master query to check that it is what you be expected and also a print_r of the result from the master query. That would confirm what the order coming from the query is and make sure that nothing else is interfering with it.
  
  Allan
• compcentral Posts: 40Questions: 0Answers: 0

  March 2011

  Using the new server-side code, the contents of $sQuery when I click to sort by Course Name is:
  
  [code]
  SELECT SQL_CALC_FOUND_ROWS c.courseID, c.name, c.courseType, t.email, t.firstName, t.lastName, d.websiteURL, d.shortName, c.enrollmentCount, c.maxEnrollment, c.startDate, c.endDate
  FROM courses c, teachers t, districts d
  WHERE c.teacherID = t.teacherID AND c.districtID = d.districtID
  ORDER BY c.courseID asc
  [/code]
  
  With the results array being:
  
  [code]
  Array
  (
  [0] => 1
  [courseID] => 1
  [1] => Algebra II
  [name] => Algebra II
  [2] => Mathematics
  [courseType] => Mathematics
  [3] => asdasd@asdasd.com
  [email] => asdasd@asdasd.com
  [4] => Joe
  [firstName] => Joe
  [5] => Smith
  [lastName] => Smith
  [6] => http://www.bellevernonarea.net/bvasd/site/default.asp
  [websiteURL] => http://www.bellevernonarea.net/bvasd/site/default.asp
  [7] => BVASD
  [shortName] => BVASD
  [8] => 5
  [enrollmentCount] => 5
  [9] => 20
  [maxEnrollment] => 20
  [10] => 02/02/2011
  [startDate] => 02/02/2011
  [11] => 06/03/2011
  [endDate] => 06/03/2011
  )
  [/code]
  
  
  
  Using the SQL concat() function gives me this query:
  
  [code]
  SELECT SQL_CALC_FOUND_ROWS concat ( "  ", c.name ), c.courseType, concat( "", t.firstName, " ", t.lastName, "" ), concat( "", d.shortName, "" ), concat( c.enrollmentCount, " of ", c.maxEnrollment ), c.startDate, c.endDate
  FROM courses c, teachers t, districts d
  WHERE c.teacherID = t.teacherID AND c.districtID = d.districtID
  ORDER BY concat ( "  ", c.name
  [/code]
  
  With these results:
  
  [code]
  Array
  (
  [0] =>  Algebra II
  [concat ( "  ", c.name )] =>  Algebra II
  [1] => Mathematics
  [courseType] => Mathematics
  [2] => Joe Smith
  [concat( "", t.firstName, " ", t.lastName, "" )] => Joe Smith
  [3] => BVASD
  [concat( "", d.shortName, "" )] => BVASD
  [4] => 5 of 20
  [concat( c.enrollmentCount, " of ", c.maxEnrollment )] => 5 of 20
  [5] => 02/02/2011
  [startDate] => 02/02/2011
  [6] => 06/03/2011
  [endDate] => 06/03/2011
  )
  [/code]
• compcentral Posts: 40Questions: 0Answers: 0

  March 2011

  Of course neither of these are correct, but my initial thoughts were that the concat method would work best since the number of fields matched the table. I thought this would be sorted correctly if html data would be stripped away from the ORDER BY part of the query, which I thought DataTables was doing. This method at least has the correct field (c.name) in the ORDER BY statement.
  
  I guess I'll have to modify the received ORDER BY string to sort by the correct field... I was just hoping DataTables would save me the trouble of hard-coding this for each "combined" column.
• Jibi Abraham Posts: 11Questions: 0Answers: 0

  March 2011

  compcentral - see if you can use the post on this page http://datatables.net/forums/comments.php?DiscussionID=4512&page=1#Item_8
  I have described a method to use all of datatables default features with any data source and no extra coding in the back-end as shown here. Its still experimental, but I managed to put in a full fledged CRUD today.
• Jibi Abraham Posts: 11Questions: 0Answers: 0

  March 2011

  Also, forgive me if the answer is obvious - I just don't see it, why do you need server side processing for this page? can't you simply load the data from the back-end and let data tables do the rest? I see no other requirement here.
• compcentral Posts: 40Questions: 0Answers: 0

  March 2011

  I could do that, but I don't want to have to load thousands of rows of data into a table structure and process it client side. This is just a small sampling of data right now in a test environment. Our existing database is massive. I want to be able to sort and filter all data as well.
• Jibi Abraham Posts: 11Questions: 0Answers: 0

  March 2011

  Ahh, then this won't be of much use to you. Best of luck though.
• compcentral Posts: 40Questions: 0Answers: 0

  March 2011 edited March 2011

  thanks for the suggestions.
  
  I think I've found a solution however... not really ideal, but I'm testing it now. once complete I will post back.
• compcentral Posts: 40Questions: 0Answers: 0

  March 2011 edited March 2011

  Ok.. by modifying the Ordering section of the server side script, I got it to work correctly. It's just a bit of a pain to do it this way and I didn't think it was necessary, but now I know what I need to do.
  
  I went back to the concat() method and I modified the the ordering section to this:
  
  [code]
  /*
  * Ordering
  */
  if ( isset( $_GET['iSortCol_0'] ) )
  {
  $sOrder = "ORDER BY ";
  for ( $i=0 ; $i= 1){
  $sOrder .= "c.name ".mysql_real_escape_string( $_GET['sSortDir_'.$i] ).", ";
  }else{
  $sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."
  ".mysql_real_escape_string( $_GET['sSortDir_'.$i] ) .", ";
  }
  }
  }
  
  $sOrder = substr_replace( $sOrder, "", -2 );
  if ( $sOrder == "ORDER BY" )
  {
  $sOrder = "";
  }
  }
  [/code]
  
  I just look for part of the column string's data that would be unique to the first columns contents ("
• compcentral Posts: 40Questions: 0Answers: 0

  March 2011 edited March 2011

  I also had to fix the filtering code in a similar manner, but that is working now too. Thanks for all the help and for pointing me in the right direction.
• allan Posts: 63,214Questions: 1Answers: 10,415 Site admin

  March 2011

  Very cool - good to hear you got it working :-). Thanks for posting your solution - I'm sure others will find it useful.
  
  Regards,
  Allan
• Zyy Posts: 2Questions: 0Answers: 0

  February 2012

  I have these numbers on my 1st column of datatable:
  1%, 5%, 30%.
  It would sort like,
  1%, 30%, 5%.
  it is in link href tag and has a % sign. it does not sort properly. what should i do?
• allan Posts: 63,214Questions: 1Answers: 10,415 Site admin

  February 2012

  This plug-in will do it: http://datatables.net/plug-ins/sorting#percentage . Having said that, you note about an href "tag" (do you man an A tag with an href attribute?). You might need to strip the HTML as well. There are also plug-ins for that which you could combine with the percentage plug-in.
  
  Allan
• dianar Posts: 5Questions: 0Answers: 0

  April 2012

  Hi guys!
  
  I´m new using this plug-in. I need to use a big query taht contains like 8 INNER JOIN. The case is that when I try to use the same structure as compcentral on one March post, I'm getting the "Unknown column 'u.id' in 'field list'" error...which I haven't been able to figure out.
  
  My values for those variables are:
  
  $aColumns = array('u.firstname', 'u.lastname', 'u.email', 'u.city', 'u.country',
  'd.data', 'u.institution', 'c.fullname', 'p.finalgrade',
  'ci.code', 'qt.Quizes_tomados', 'cu.Quizes_del_curso', 'FROM_UNIXTIME(u.lastaccess)' );
  
  /* Indexed column (used for fast and accurate table cardinality) */
  $sIndexColumn = "u.id";
  $sIndexTable = "mdl_user u";
  
  /* DB table to use */
  $sTable = "((((((mdl_user u LEFT JOIN mdl_user_info_data d ON u.id = d.userid AND d.fieldid=1)
  LEFT JOIN (SELECT mdl_grade_grades_history.userid, mdl_grade_grades_history.itemid, mdl_grade_grades_history.finalgrade
  FROM mdl_grade_grades_history INNER JOIN (SELECT userid, itemid, max(timemodified) UltimaFechaQuiz
  FROM mdl_grade_grades_history
  GROUP BY userid, itemid) UltimosQuicesUsuarios
  ON mdl_grade_grades_history.userid = UltimosQuicesUsuarios.userid
  AND mdl_grade_grades_history.itemid = UltimosQuicesUsuarios.itemid
  AND mdl_grade_grades_history.timemodified = UltimosQuicesUsuarios.UltimaFechaQuiz
  WHERE mdl_grade_grades_history.rawgrademax=100 AND mdl_grade_grades_history.finalgrade is not null) p ON u.id = p.userid)
  LEFT JOIN mdl_grade_items g ON p.itemid = g.id)
  LEFT JOIN mdl_course c ON g.courseid = c.id)
  LEFT JOIN mdl_certificate_issues ci ON ci.userid = u.id AND ci.classname = c.fullname)
  LEFT JOIN (Select Usuarios_Cursos.userid, Usuarios_Cursos.course, Cursos.Quizes_del_curso
  From (Select distinct mdl_quiz_grades.userid, mdl_quiz.course
  From mdl_quiz_grades inner join mdl_quiz on mdl_quiz_grades.quiz = mdl_quiz.id) Usuarios_Cursos
  inner join (Select mdl_quiz.Course, Count(1) Quizes_del_curso
  From mdl_quiz
  Group by mdl_quiz.Course) Cursos on Usuarios_Cursos.Course = Cursos.Course
  ) cu ON u.id = cu.userid AND c.id = cu.course)
  LEFT JOIN (Select mdl_quiz_grades.userid, mdl_quiz.course, Count(1) Quizes_tomados
  From mdl_quiz_grades inner join mdl_quiz on mdl_quiz_grades.quiz = mdl_quiz.id
  Group by mdl_quiz_grades.userid, mdl_quiz.course
  ) qt ON u.id = qt.userid AND c.id = qt.Course";
  
  Please someone help to fix these error. I know it may be a stupid error but still all I've tried isn't enough to fix it. Thanks!
• allan Posts: 63,214Questions: 1Answers: 10,415 Site admin

  April 2012

  > I'm getting the "Unknown column 'u.id' in 'field list'" error...which I haven't been able to figure out.
  
  That sounds like an SQL error to me. You might be best asking on an SQL forum or on SO where people more knowledgable in SQL than me will be.
  
  Allan
• dianar Posts: 5Questions: 0Answers: 0

  April 2012

  Hey Allan!
  
  thanks for your quick answer, I did find some answer to this.
  
  Now I'm having the same as problem as you did, I mean I'm getting JSON code when I called the script, but unfortunately the "DataTables warning: JSON data from server could not be parsed. This is caused by a JSON formatting error." dialog box error still appears.
  May you tell me what I can do to fix that problem???? or can anyone help me?
  
  Thanks!
• allan Posts: 63,214Questions: 1Answers: 10,415 Site admin

  April 2012

  > May you tell me what I can do to fix that problem????
  
  Yes - return valid JSON from the server :-). Your JSON can be validated using http://jsonlint.com .
  
  Allan
• dianar Posts: 5Questions: 0Answers: 0

  April 2012

  I already validate my JSON code and both pages I used tell me it is valid! But still got the error...so...that's why I'm asking