SQL - Join two tables

scoohh

How to join 2 SQL tables and display it in one datatable?

Sample SQL Query:

SELECT * FROM users JOIN users ON users.id = users_info.id

allan

How would you do do it without the join? Are you using client-side or server-side processing? Also, doesn't _users_info need to be in the FROM clause?

Allan

scoohh

I am using server-side processing. The users_info doesn't have to be in the FROM clause. The sample query I've posted was correct I think.

allan

With server-side processing you need to reply to the request from DataTables in the required manner: http://datatables.net/usage/server-side . Although none of these scripts directly support a join as they currently are, they should be modifiable to do so: http://datatables.net/development/server-side/

Allan

scoohh

I already figured out how. Here's what I did

I changed this statement:

$sQuery = "
SELECT SQL_CALC_FOUND_ROWS ".implode(", ", $aColumns)."
FROM $tName
$sWhere
$sOrder
$sLimit
";

to:

$sQuery = "
SELECT SQL_CALC_FOUND_ROWS u.id, u.username, m.first_name, m.last_name, u.email, u.created_on, u.active
FROM tbl_users u LEFT JOIN tbl_meta m ON u.id = m.user_id
$sWhere
$sOrder
$sLimit
";

scoohh

By the way, thanks to you Allan for your replies.

evan

Thank you for this scoohh. I was also attempting to get joins to work with the server side scripts and your addition works great!

bgordon

Ok... can you explain how to modify the rest of the php example file, since using search and sort also requires modifications and I am having a hard time figuring it out... for some reason, even without a join, I cannot seem to even use a table alias .... mytable t1 with 't1.id', 't1.whatever' in the array... no sure why that won't work either.

alex3493

scoohh, your solution has one problem: live filtering stops working.

If you join 2 or more tables in your query you have to use fully qualified field names (table.field) in WHERE clause.
By default (in server-side code example) $sWhere is constructed based on $aColumns array that contains only field names, so when you begin live search you get SQL error (and the page stops responding)

A quick solution in your case could be:

$sQuery = "
SELECT SQL_CALC_FOUND_ROWS * FROM
(SELECT u.id, u.username, m.first_name, m.last_name, u.email, u.created_on, u.active
FROM tbl_users u LEFT JOIN tbl_meta m ON u.id = m.user_id) sel
$sWhere
$sOrder
$sLimit
";

Hope that helps.

jansportw

This works when I pull in INT fields, but when I try to join in CHAR fields I get a JSON formating error warning. I'm sure it's a simple fix, but my brain isn't working.
Any solution?

chenci

Hi, i'm interested in joining two tables too, but can it be done using the example "DataTables server-side script for PHP and MySQL" by Allan modifing the script in a way so the arrays at the begining works?
Does anyone know how to do it??

beginner_

Create a database view and use that as $sTable for server side processing.

chenci

Hey, i don't think that would be a very good idea, for a instance, i have to duplicate both tables with a lot of date to another db, and also, when those tables have to update, this new db have to update as well. It would be much easier to just use the client side datatable and use php to populate html

dafitoff

Hi All!

I'm trying to get the following statement to work with the php server-side script:
[code]
$sQuery = "SELECT SQL_CALC_FOUND_ROWS tab1.discount, tab1.username, tab2.name ".
"FROM tab1 ".
"LEFT JOIN tab2 on tab1.username = tab2.name ".
"$sWhere $sOrder $sLimit ";
[/code]
Directly under the myslq database the above query returnts excatly what i want, but the php script returns tables with null items :
[code]
{"sEcho":0,"iTotalRecords":"62","iTotalDisplayRecords":"62","aaData":[[null,null,null,null,null,null,null,null,null,null],[null,null,null,null,null,null,null,null,null,null],[null,null,null,null,null,null,null,null,null,null], .......
[/code]
with 10 items per row.
What is wrong in this kind of querys?
regards

dafitoff

Found extended php script here: http://www.datatables.net/forums/discussion/2651/alternative-server-side-php-script/

which seems to be a good solution