On March 26, 2019 we launched new MODX Forums. Please join us at the new MODX Community Forums.
Subscribe: RSS
  • Hey Garry, long time smiley

    The code is the same as it is in the snippet in the repistory and as you know I have no code skills, more a cut paste n hack person, so not too sure.

    I set $ds=0; near the top to ensure it was empty then I returned $ds on the page and I got a value of Resource id #18 not a number huh I was expecting the number of rows in the table.

     if ($ds= $modx->db->query($query)) {
    	// if the query was successful, build our table array from the rows
    	while ($row= $modx->db->getRow($ds)) {
    		$industries[]= array(


    That would appear to check if $ds equals the same number as what was in $query (which is "SELECT COUNT(id) FROM modx_courses" and that returns the correct value), if its not it gets that row number from the database and puts in the array?

    This seems to be where it all falls down, it gets a positive value for the IF statement so tries to build an array, however something isn’t right at this point.
      [img]http://www.emanz.ac.nz/assets/images/logo/emanz-icon_16x16.gif[/img] Emergency Management Academy of New Zealand [br] http://www.emanz.ac.nz[br][br]MODx Sandbox Login: sandbox Password: castle [br]
      Admin Sandbox Login: sandbox Password: castle
    • Hey Garry, long time smiley
      Certainly is, hope you’re keeping well.

      I set $ds=0; near the top to ensure it was empty then I returned $ds on the page and I got a value of Resource id #18 not a number Huh I was expecting the number of rows in the table.
      That’s to be expected, by returning $ds you are returning the handle on the recordset. You would need to use $modx->db->getValue() statement first and then return the results of that to the page to get the record count.

      That would appear to check if $ds equals the same number as what was in $query (which is "SELECT COUNT(id) FROM modx_courses" and that returns the correct value), if its not it gets that row number from the database and puts in the array?
      No, the single ’=’ sign means it is assigning the results of the executed query to $ds. Because the query returns a record then the IF statement evaluates to true.

      Just entertain me and try adjusting the code at that point to:

      if ($ds= $modx->db->select('*','modx_courses')) {
      


      Let me know if that returns anything to the array,
      Garry
        Garry Nutting
        Senior Developer
        MODX, LLC

        Email: support@modx.com
        Twitter: @garryn
        Web: modx.com
      • laugh Yup, that now returns the results! Thanks Garry.

        Ok, new issue, I can’t sort the results, haven’t looked at why yet... it is creating the header with links like http://www.emanz.ac.nz/dev-coursestable.html?orderby=Course&orderdir=desc (requries sandbox login as per my signature)

        just excited its got some results

          [img]http://www.emanz.ac.nz/assets/images/logo/emanz-icon_16x16.gif[/img] Emergency Management Academy of New Zealand [br] http://www.emanz.ac.nz[br][br]MODx Sandbox Login: sandbox Password: castle [br]
          Admin Sandbox Login: sandbox Password: castle
        • Try adding the sort field and direction to your query; the function definition looks like this
          function select($fields = "*", $from = "", $where = "", $orderby = "", $limit = "")

          so your query would look like this
          ($ds= $modx->db->select('*','modx_courses','',$orderby))

          where $orderby would be whatever field you want to sort by and the direction, ASC or DESC.
            Studying MODX in the desert - http://sottwell.com
            Tips and Tricks from the MODX Forums and Slack Channels - http://modxcookbook.com
            Join the Slack Community - http://modx.org
          • Thanks everyone for your help, I’m getting there

            The snippet is meant to have "sortable coloums" which I am guessing is performed by the section below? However it doesn’t work, maybe its missing something?

            Has anyone else used MakeTable? Or am I just being special?

            // create the table header definition with each header providing a link to sort by that field
            	$industryTableHeader= array(
            		'Code'=> $objTable->prepareOrderByLink('Code', 'Code'),
            		'Start'=> $objTable->prepareOrderByLink('Start', 'Start'),
            		'End'=> $objTable->prepareOrderByLink('End', 'End'),
            		'Course'=> $objTable->prepareOrderByLink('Course', 'Course'),
            		'Location'=> $objTable->prepareOrderByLink('Location', 'Location'),
            	);
              [img]http://www.emanz.ac.nz/assets/images/logo/emanz-icon_16x16.gif[/img] Emergency Management Academy of New Zealand [br] http://www.emanz.ac.nz[br][br]MODx Sandbox Login: sandbox Password: castle [br]
              Admin Sandbox Login: sandbox Password: castle
            • Okay, I’ve had another look at the example and think I can see the problem with the sorting ...

              Set the query string to this:

              if ($ds= $modx->db->select('*','modx_courses','',$objTable->handleSorting(),$objTable->handlePaging())) {

              That should fix the problem with the sortable columns.
                Garry Nutting
                Senior Developer
                MODX, LLC

                Email: support@modx.com
                Twitter: @garryn
                Web: modx.com
              • That generates an error:

                MODx encountered the following error while attempting to parse the requested resource:
                « Execution of a query to the database 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 'ORDER BY id DESC LIMIT LIMIT 0, 25' at line 1 »
                      SQL: SELECT * FROM modx_courses ORDER BY ORDER BY id DESC LIMIT LIMIT 0, 25;
                      [Copy SQL to ClipBoard]
                  [img]http://www.emanz.ac.nz/assets/images/logo/emanz-icon_16x16.gif[/img] Emergency Management Academy of New Zealand [br] http://www.emanz.ac.nz[br][br]MODx Sandbox Login: sandbox Password: castle [br]
                  Admin Sandbox Login: sandbox Password: castle
                • Right, I see. Easily fixed, replace with:
                  $sql = 'SELECT * FROM modx_courses '.$objTable->handleSorting().' '.$objTable->handlePaging();
                  if ($ds= $modx->db->query($sql)) {
                  


                  I didn’t realise that the handleSorting() and handlePaging() methods outputted the ’ORDER BY’ and ’LIMIT’ as well.
                    Garry Nutting
                    Senior Developer
                    MODX, LLC

                    Email: support@modx.com
                    Twitter: @garryn
                    Web: modx.com
                  • The header still won’t allow sorting. It displays the records in a different order when it was refreshed, but the table headers, which are links, still doesn’t change the sort order when they are clicked on like the snippet documentation says it should.

                    Once this is done maybe we can give it to Open Geek so he can modify the original Snippet as it doesn’t seem to work as is.

                    The headers are created here:

                    	// create the table header definition with each header providing a link to sort by that field
                    	$industryTableHeader= array(
                    		'Code'=> $objTable->prepareOrderByLink('Code', 'Code'),
                    		'Start'=> $objTable->prepareOrderByLink('Start', 'Start'),
                    		'End'=> $objTable->prepareOrderByLink('End', 'End'),
                    		'Course'=> $objTable->prepareOrderByLink('Course', 'Course'),
                    		'Location'=> $objTable->prepareOrderByLink('Location', 'Location'),
                      [img]http://www.emanz.ac.nz/assets/images/logo/emanz-icon_16x16.gif[/img] Emergency Management Academy of New Zealand [br] http://www.emanz.ac.nz[br][br]MODx Sandbox Login: sandbox Password: castle [br]
                      Admin Sandbox Login: sandbox Password: castle
                    • Briggsy, in all honesty, that was simply an example snippet, and I have since rewritten this class as part of my upcoming xPDO release. The sorting and paging do work, but it really takes the ability to write SQL, test and debug the snippet to take advantage of the tool I’m providing, which is the class itself, not the snippet. In any case, I’ll soon be announcing the new versions of this and MakeForm, which will be much easier to use, and not so limited to developer types...