Display One Table with data from 2 sources?

RZelazny

What I'm trying to do is display a single datatable with data from two SQL tables (which contain identical column setups) using PHP server-side processing, and I want to add a column to the table to distinguish which SQL table that row is from. This is basically what I am looking to do:

SQL Table A
-----------------
Name Phone
Joe 555-5555
Blair 666-6666
Jake 777-7777

SQL Table B
-----------------
Name Phone
Al 444-4444
Jim 333-3333
Dean 222-2222

Data Table
-------------------------
From Name Phone
B Al 444-4444
A Blair 666-6666
B Dean 222-2222
A Jake 777-7777
B Jim 333-3333
A Joe 555-5555

I searched on google and through the forums and found some stuff referencing "inner joining" not sure if that's what I need to do this or not? If someone could point me in the right direction I would appreciate it!.

(Sorry for un-aligned tables in the example... i tried adding spaces to show the difference in columns, but they are removed when posting).

psharp

select 'A' as `from`, Name, Phone from table_a
union
select 'B' as `from`, Name, Phone from table_b;

// I don't think I'd call a column name "from", but if you really need to, use the backtick

RZelazny

Thank you, I've resolved to just add a column to the SQL database with the "Type" I need. What It's basically for is keeping track of customer accounts, we have a table for "billing" accounts and one for "shipping". Now the issue I'm having is the union isn't working.

I'm using the template for the server-side scripting and so my query I'm trying to do is this:
[code]
$sQuery = "
SELECT SQL_CALC_FOUND_ROWS `".str_replace(" , ", " ", implode("`, `", $aColumns))."`
FROM $sTableB
$sWhere
UNION
SELECT SQL_CALC_FOUND_ROWS `".str_replace(" , ", " ", implode("`, `", $aColumns))."`
FROM $sTableS
$sWhere
$sOrder
$sLimit
";
[/code]

The page with the datatable just says it's loading, if I try and run the PHP page by itself, it gives me a MySql 1234 Error.

// UPDATE

I've actually fixed one part, the SQL_CALC_FOUND_ROWS can only be on the first SELECT statement, and I have to change UNION to UNION ALL. That however gets it to be a MySQL 1064 Error. Now I tried running a query without any variables in it

[code]
SELECT SQL_CALC_FOUND_ROWS `type` , `id` , `name` , `address_1` , `city` , `phone_1`
FROM `customer_ship`
UNION ALL
SELECT `type` , `id` , `name` , `address_1` , `city` , `phone_1`
FROM `customer_bill`
LIMIT 0 , 30
[/code]

Which returns the data I want. So I believe the issue is in the $sWhere or $sOrder, $sLimit portions, which is DataTable specific, not just an SQL bug.

psharp

You cannot use the limit,order or where clause on a union statement unless you put parentheses around each select statement.

(select from where )
union
(select from where )
order by
limit;

Union selects return the natural ordering of the tables, so it does no good to put the order by with each specific select statement.

RZelazny

Thanks psharp.

I've added parentheses around each of the select statements and I'm still recieving a 1064 error. This is what I've got so far:

$sQuery = "
(SELECT SQL_CALC_FOUND_ROWS `".str_replace(" , ", " ", implode("`, `", $aColumns))."`
FROM $sTableB
$sWhere)
UNION ALL
(SELECT `".str_replace(" , ", " ", implode("`, `", $aColumns))."`
FROM $sTableS
$sWhere)
$sOrder
$sLimit
";

psharp

What is the contents of $sQuery after this?
UNION ALL is probably better to use over just UNION, as the latter strips duplicates. I'm assuming you need duplicate records if there are any.

The error you are getting is a "SQL Parsing Error" .

RZelazny

Contents of $sQuery is: (SELECT SQL_CALC_FOUND_ROWS `type`, `id`, `name`, `address_1`, `city`, `phone_1` FROM customer_bill ) UNION ALL (SELECT `type`, `id`, `name`, `address_1`, `city`, `phone_1` FROM customer_ship )

RZelazny

I ran the contents of the string outputted by the PHP in PHPMyAdmin and it runs properly without any issues. So I'm not sure why it's faulting when I try and run it through the PHP.

psharp

For the UNION to work correctly, you must have the same fields listed in both of the queries.

your second select statement is missing SQL_CALC_ROUND_ROWS.

I guess that could be a cut and paste error.

I'm assuming the weird display of your post is due to the code formatting of the forum. Try wrapping it in [ CODE ] [ /CODE ] (minus the spaces).

RZelazny

That's what I had originally when I was getting the SQL 1234 error. Doing some digging I found this:

from the FOUND_ROWS() documentation:

[quote]
The use of SQL_CALC_FOUND_ROWS and FOUND_ROWS() is more complex for UNION statements than for simple SELECT statements, because LIMIT may occur at multiple places in a UNION. It may be applied to individual SELECT statements in the UNION, or global to the UNION result as a whole.

The intent of SQL_CALC_FOUND_ROWS for UNION is that it should return the row count that would be returned without a global LIMIT. The conditions for use of SQL_CALC_FOUND_ROWS with UNION are:

The SQL_CALC_FOUND_ROWS keyword must appear in the first SELECT of the UNION.

The value of FOUND_ROWS() is exact only if UNION ALL is used. If UNION without ALL is used, duplicate removal occurs and the value of FOUND_ROWS() is only approximate.

If no LIMIT is present in the UNION, SQL_CALC_FOUND_ROWS is ignored and returns the number of rows in the temporary table that is created to process the UNION.
[/quote]

So that's why I removed it from the second SELECT, adding it back in just gives me the 1234 Errror again.

RZelazny

I have a feeling the issue is after the original sQuery that I have modified from the original document. The rest of this is from the generic server-side example from this site.

$sQuery = "
(SELECT SQL_CALC_FOUND_ROWS `".str_replace(" , ", " ", implode("`, `", $aColumns))."`
FROM $sTableB
$sWhere)
UNION ALL
(SELECT `".str_replace(" , ", " ", implode("`, `", $aColumns))."`
FROM $sTableS)
$sWhere
$sOrder
$sLimit
";

$rResult = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );

/* Data set length after filtering */
$sQuery = "
SELECT FOUND_ROWS()
";
$rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
$aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
$iFilteredTotal = $aResultFilterTotal[0];

/* Total data set length */
$sQuery = "
SELECT COUNT(`".$sIndexColumn."`)
FROM $sTable
";
$rResultTotal = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
$aResultTotal = mysql_fetch_array($rResultTotal);
$iTotal = $aResultTotal[0];

psharp

Unfortunately, I'm doing this from memory. The database we are using here now is informix and it does not support the LIMIT clause. I'll try and cook something up at home tonight and give it a test.

Also, I use PDO and there are a few differences in getting the record counts. Normally I just get the record count like this:

[code]
$sth = $this->PDODB->prepare($sql);
$sth->execute();
$dataset = $sth->fetchAll();

$return['datalength'] = count($dataset);
[/code]

And because I don't have the abilities of LIMIT, I normally wrap my select with a select and iterate through the requested page /record counts from datatables.

something like:

[code]
select * from (
(select * from tab_a) union all (select * from tab_b)
)


[/code]

RZelazny

I've figured out where the issue is. It's in the /*Total data set length */ portion. I changed the mySQL error's to print different messages to see where it's faulting. So the query it's using is:

$sQuery = "
SELECT COUNT(`".$sIndexColumn."`)
FROM $sTable
";

Which won't work because it's only counting the first table, so I'll have to construct a query to count both tables. Possibly two queries and just adding them will be easier. I'm working on that right now.

RZelazny

Okay... so the solution was to create two queries for the Total data set length and add them together.
It now works 100%!

Thank you psharp for all your help.