Answers

• mkleinosky Posts: 46Questions: 6Answers: 5

  April 2015

  2 ways:
  one:

      <?php
  error_reporting(E_ALL);
  ini_set('display_errors', '1');
  require "./php/DataTables.php"  ; 
  $result = $db->sql( "select a.first_name, a.last_name, b.date from users a, birthdays b where a.id = b.user_id order by b.date desc");
  $response = array(
    'data' => $result->fetchAll(),
    'options' => array()
    );
  print_r( json_encode( $response ));
  
  
• mkleinosky Posts: 46Questions: 6Answers: 5

  April 2015

  another way

      <?php
  error_reporting(E_ALL);
  ini_set('display_errors', '1');
  
  // DataTables PHP library and database connection
   
  require "DataTables.php"  ; 
   
  // Alias Editor classes so they are easy to use
  use
      DataTables\Editor,
      DataTables\Editor\Field, 
      DataTables\Editor\Format,
      DataTables\Editor\Join,
      DataTables\Editor\Validate;
  
  // Build our Editor instance and process the data coming from _POST
  Editor::inst( $db, 'users', 'id' )
      ->fields(
    Field::inst( 'users.first_name'     ),
    Field::inst( 'users.last_name'     ),
    Field::inst( 'birthdays.birthdays'     )
      )
      ->leftJoin("birthdays","birthdays.user_id","=","users.id")
      ->process( $_POST )
      ->json();
  
  
• kevin2012 Posts: 7Questions: 1Answers: 0

  April 2015

  Thanks, mkleinosky. I like the first solution better, way easier. Is there any added benefit with the second approach ?
• allan Posts: 63,364Questions: 1Answers: 10,449 Site admin

  April 2015

  Second one works nicely if you are using Editor. In fact, it looks like the first also uses the Editor database libraries.

  Other options exist such as using PHP's PDO interface to query the database, mysqli, etc. I would suggest referring to the PHP documentation and tutorials available on the web if you aren't sure how to query the database in PHP.

  Allan
• orkneywill Posts: 3Questions: 2Answers: 0

  April 2015 edited April 2015

  I've been trying to get my head around adding a Where clause for the last 2 days. I'm using Editor and hoping someone can help me out.

  I'd like the table to only show results based on the GET URL, eg ?order_number=9

  Demo URL:- http://tinyurl.com/ppqgw99
  HTML/JS:- http://tinyurl.com/kx99ofy
  PHP: http://tinyurl.com/lammcj4

  I've read the documentation at https://editor.datatables.net/manual/php/conditions and I've made some progress...

  When I hardcode for example:

  ->where( 'order_number', 9 )
  ->process( $_POST )
  ->json();

  to my "table.order_inventory.php" it correctly shows only the rows from order 9.

  I don't understand how to dynamically change the order number though. I think it needs to be sent through the Javascript/Ajax request but all of my attempts (not saved) haven't worked. Guessing I need something like this though:-

      "ajax": "php/table.order_inventory.php",
      "table": "#order_inventory",
      "get_order_number": "<?php echo $_GET['order_number']; ?>"
      "fields": [
          {
              "label": "Line Type",
              "name": "line_type"
          },
          etc. etc.
  

  If someone could show me an example I'd appreciate it =)
  Cheers
• allan Posts: 63,364Questions: 1Answers: 10,449 Site admin

  April 2015

  Use the ajax.data option to send the order option to the server, which you can then get using $_REQUEST['order_number'] (or whatever variable name you select) to apply to the condition.

  Allan
• kevin2012 Posts: 7Questions: 1Answers: 0

  April 2015

  where can i find Datatables.php ?
• allan Posts: 63,364Questions: 1Answers: 10,449 Site admin

  April 2015

  It is in the php directory of the Editor package.

  Allan
• kevin2012 Posts: 7Questions: 1Answers: 0

  April 2015

  I'm not really looking for editor capabilities. Just want to display the table from a php/mysql datasource. I also want to ability to add checkboxes and radio buttons as columns so that I can make some selections and capture them when a submit button is clicked on the page.

  What is the best way to achieve this ?
• allan Posts: 63,364Questions: 1Answers: 10,449 Site admin

  April 2015

  Simply get the data from the database using an SQL query (there are lots of tutorials about how to do this in PHP available on the web) and then JSON encode the results so DataTables can read it by Ajax.

  Allan