How to join two tables with ssp.class.php

by

As PaulF pointed out, you need to use JOIN or sub-query to retrieve father’s name from the same table.

I assume you’re using ssp.class.php to process your data on the server-side based on the example you’ve mentioned.

Class ssp.class.php doesn’t support joins and sub-queries, but there is a workaround. The trick is to use sub-query as shown below in $table definition. Replace table with your actual table name in the sub-query.

$table = <<<EOT
 (
    SELECT 
      a.id, 
      a.name, 
      a.father_id, 
      b.name AS father_name
    FROM table a
    LEFT JOIN table b ON a.father_id = b.id
 ) temp
EOT;

$primaryKey = 'id';

$columns = array(
   array( 'db' => 'id',          'dt' => 0 ),
   array( 'db' => 'name',        'dt' => 1 ),
   array( 'db' => 'father_id',   'dt' => 2 ),
   array( 'db' => 'father_name', 'dt' => 3 )
);

$sql_details = array(
   'user' => '',
   'pass' => '',
   'db'   => '',
   'host' => ''
);

require( 'ssp.class.php' );
echo json_encode(
   SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);

You also need to edit ssp.class.php and replace all instances of FROM `$table` with FROM $table to remove backticks.

Make sure all column names are unique otherwise use AS to assign an alias.

NOTES

There is also github.com/emran/ssp repository that contains enhanced ssp.class.php supporting JOINs.

LINKS

See jQuery DataTables: Using WHERE, JOIN and GROUP BY with ssp.class.php for more information.

More Related Contents:

  1. Laravel – only a zero is saved to the database
  2. storing user_id in session variable
  3. Convert from MySQL datetime to another format with PHP
  4. MySQL vs MySQLi when using PHP [closed]
  5. How do I set ORDER BY params using prepared PDO statement?
  6. Passing an array to a query using a WHERE clause
  7. How can I store and retrieve images from a MySQL database using PHP?
  8. PHP/MySQL Insert null values
  9. mysqli_stmt::bind_param(): Number of elements in type definition string doesn’t match number of bind variables
  10. Laravel: PDOException: could not find driver
  11. Can you add an if statement in ORDER BY?
  12. PDO Connection Test
  13. Grouping WHERE clauses in Codeigniter
  14. Using if(isset($_POST[‘submit’])) to not display echo when script is open is not working
  15. MySQL (or PHP?) group results by field data
  16. SELECT COUNT() vs mysql_num_rows();
  17. Store procedures in phpMyAdmin
  18. Check if a row exists using old mysql_* API
  19. Unique IPs in a voting system
  20. MySQL get a random value between two values
  21. MySQL error when inserting data containing apostrophes (single quotes)?
  22. mysql – INSERT date range into date columns IF dates don’t overlap with existing ones
  23. php password_verify() hash and pass won’t match
  24. MySQL query using an array [duplicate]
  25. PHP mySQL – Insert new record into table with auto-increment on primary key
  26. Using MySQL’s TIMESTAMP vs storing timestamps directly
  27. How do you make good use of multicore CPUs in your PHP/MySQL applications? [closed]
  28. Update query PHP MySQL [duplicate]
  29. Do I have to use mysql_real_escape_string if I bind parameters?
  30. How to sort MYSQL fulltext search results by relevancy

Leave a Comment