Using a MySQL view instead of a MySQL table shows only NULLs

hurrells

Hello Allan.

I have used datatables to successfully get data from a MySQL table.
See Success at: http://www.chester.open-sourced.com/tracking1.html

Now I want to use a MySQL view as I need to concat some fields. But a view does not appear to display data values only NULLs. Works fine if I simply change back to a table from a view.
See problem at: http://www.chester.open-sourced.com/tracking2.html

I'm guessing it's a PHP/JSON issue because if you run the underlying PHP you just get NULLs too.
See NULLS at: http://www.chester.open-sourced.com/scripts/tracking2.php

There are links in ./scripts dir so you can see the PHP code too. It's basically your server-side example with my VIEW and the same fieldnames as tracking1.html above. I have even tried a simple view made by "CREATE VIEW vw_bols select * from bols ;".

I have also switched to the non-min versions of jquery and datatables. I should be running the latest versions. You can view phpinfo() at: http://www.chester.open-sourced.com/

Any Ideas?

Thank You
Stephen

hurrells

Hello Allan.

I am able to see the data from the view if I run a SELECT * from vw_bols from mysql command line.
If I run "php5 tracking2.php" I see the right number of rows (720) and fields (6) but everything is NULL as follows:

php ./tracking2.php
{"sEcho":0,"iTotalRecords":"720","iTotalDisplayRecords":"720","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],[null,null,null,null,null,null],[null,null,null,null,null,null],...and so on....

If I do some debugging with some "print_r($output)" in various spots. I see good SQL and data everywhere until the loop over the data rows at the end.

$ php5 ./tracking2.php | head -30

SELECT SQL_CALC_FOUND_ROWS bill_of_lading, manifest_date, location_name, load_number, dtlsta_code, proof_of_delivery_url
FROM vw_bols



Array
(
[0] => bill_of_lading
[1] => manifest_date
[2] => location_name
[3] => load_number
[4] => dtlsta_code
[5] => proof_of_delivery_url
)
Array
(
[sEcho] => 0
[iTotalRecords] => 720
[iTotalDisplayRecords] => 720
[aaData] => Array
(
[0] => Array
(
[0] =>
[1] =>
[2] =>
[3] =>
[4] =>
[5] =>

hurrells

Hello Allan

Solved!

What the problem turned out to be was that in the ./script/tracking2.php the $aColumns variable is case sensitive when a view is used. It is not case sensitive when a table is used. Very weird eh?

Thanks
Steve

allan

Hi Steve,

Sorry I wasn't able to help with this one, but good to hear you get it sorted! Useful knowledge - thanks for sharing the feedback with us! It certainly is a curious one - perhaps a quirk of the SQL engine.

Regards,
Allan