SQLite Views instead of Tables?

SQLite Views instead of Tables?

LalatoLalato Posts: 5Questions: 1Answers: 0

Anyone have success using SQLite Views instead of Tables?

I can get tables to work without issue, but when I try to do something with a view, the resulting datatable output is empty. It says it's returning records, but nothing appears on the page. I don't mind using tables, just wondering if anyone had resolved this kind of issue before.

Answers

  • allanallan Posts: 63,381Questions: 1Answers: 10,449 Site admin

    What is the JSON that your view code is returning? Assuming that valid JSON is being returned, DataTables does really care where the data comes from.

    Allan

  • LalatoLalato Posts: 5Questions: 1Answers: 0

    Thanks for taking the time to help, Allan. I especially appreciate since I'm a novice.

    I'm at my day job right now. I'll check how to do that (validate JSON) when I get a free moment today or maybe when I get home tonight.

    If it helps, here's where I've been playing with datatables...

    http://lalato.com/dl/dtables/leagues.php

  • LalatoLalato Posts: 5Questions: 1Answers: 0

    OK. I had a chance to look at the JSON output when using the view. According JSONlit's valid. However, the values returned all NULL. Here's a small sample of the JSON...

    {"sEcho":2,"iTotalRecords":"1586","iTime":0.0775589942932,"iTotalDisplayRecords":"1586","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],

    And it's nulls all the way down.

    It's returning the correct number of rows (1586), but it doesn't have any data.

  • allanallan Posts: 63,381Questions: 1Answers: 10,449 Site admin

    And it's nulls all the way down.

    Nulls! I thought it was turtles all the way down! ;-)

    I guess you are using PHP to get the data from a database? How are you doing that? That looks like where the problem is, not with DataTables.

    Allan

  • LalatoLalato Posts: 5Questions: 1Answers: 0

    Yes, it's pulling from a SQLite database via PHP. When I run the query directly against SQLite it runs just fine. And when I put the same exact data into a table, it displays via datatables just fine. I'm not advanced enough to know what's going on there. Just seems odd that a view would act so weirdly different than a table.

    Here's a version with the view... (aka "nulls/turtles all the way down")
    http://lalato.com/dl/dtables/leaguesv.php

    and a version with the table... (aka "I can haz data")
    http://lalato.com/dl/dtables/leagues.php

    The view and the table both have the same columns with the same exact data. The only difference in the query is instead of pointing to dl_leagues_t (the table) it points to dl_leagues_v (the view). Adding to the weirdness is that when pulling from the view, it returns the correct number of rows... it's just that those rows are all filled with null values.

    You're right that this isn't so much a datatables issue. It works fine with a table... I was just hoping someone might have run into the view issue and had a workaround.

  • tangerinetangerine Posts: 3,365Questions: 39Answers: 395

    So "serverdatapdo.php" gives two different responses where the only difference is table vs. view.

    Could the answer be in your PDO connection params?

  • LalatoLalato Posts: 5Questions: 1Answers: 0

    Upon further internet searching and tinkering I have stumbled upon a solution. Apparently Views require that you explicitly name the columns. So I named the columns what would appear in my datatable (COUNTRY, REGION, CITY, etc. instead of leaving the original table names... league_country, league_region, league_city, etc.)

    That seemed to do the trick, but I still find it strange.

  • allanallan Posts: 63,381Questions: 1Answers: 10,449 Site admin

    Good to hear you got it sorted out. Not sure why SQLite works like that, but I'm sure they have a reason.

    Allan

This discussion has been closed.