php script where clause

kevin2012

In the server side scripting using PHP, if I have multiple tables, how can i specify a query like this :

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.

Thanks for your help !

mkleinosky

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

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

Thanks, mkleinosky. I like the first solution better, way easier. Is there any added benefit with the second approach ?

allan

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

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

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

where can i find Datatables.php ?

allan

It is in the php directory of the Editor package.

Allan

kevin2012

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

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