A tale of two views, or another MySQL parsing conundrum...

[mike.jones]

I have two views, which are being displayed as tables - no problems on that front.

I am trying to "merge" the two views to a single view.

The first view is showing the top five contestants based on the load ratio. The second view is showing all of the contestants with the newest entries on top.

I tried implementing a mysql query to join the two tables - no problem running the query, but the query can't create a view because...

#1349 - View's SELECT contains a subquery in the FROM clause

So...I tried using the query

Code: Select all

select * from (select * from TopFive as tf union all select * from FullReport as fr ) as Report

in the fields.ini in one of my table folders, specifically the table for the "TopFive" view.

No luck, as I receive the parsing error...

Fatal error: Failed parsing SQL query on select: select * from (select * from TopFive as x union all select * from FullReport as y) as z . The Error was Parse error: Unexpected clause on line 1 select * from (select * from TopFive as x union all select * from FullReport as y) as z ^ found: "all" in /home/swmobridge/swmobridgecontest.org/cms/contest/dataface/lib/SQL/Parser.php on line 1765

I'm also wondering if it would it be possible to place a View table in a custom header?

Website: http://www.swmobridgecontest.org/cms/contest/

[shannah]

The parser is having trouble with the union statement.

What do you mean by a custom header?

-Steve

[mike.jones]

The parser is having trouble with the union statement.

What do you mean by a custom header?

-Steve

I was wondering how difficult it would be possible to place a MYSQL view next to the logo at the top of the screen.

Regards,

Mike

[shannah]

OK.. I guess I'm not sure what you mean by "MySQL View" then. Can you describe exactly what you want to appear in that header?

[mike.jones]

OK.. I guess I'm not sure what you mean by "MySQL View" then. Can you describe exactly what you want to appear in that header?

Well, the TopFive Table is created from a MYSQL View, and I'd like to if possible, display the TopFive table in the header above the main body.

[shannah]

OK. You just need to implement a block or slot in your delegate class. Then just query your view (using mysql_query) and print the results as part of the block.

E.g. In the application delegate class (or table delegate):

Code: Select all

function block__after_header(){
    $res = mysql_query("select * from bar... etc...", df_db());
    //etc ...
 
   echo "<span> Content to output here...</span>';
}


-Steve

[mike.jones]

I appreciate the direction - I'll see if I can get it to work.

Thank you very much.

Mike

[mike.jones]

Created a FullReport.php file in the FullReport folder.

Code: Select all

<?
class tables_FullReport
{
   function block__after_header()
   {
   echo "<div id=\"Top Five\">
      <h4>Top Five (HS Division)</h4>";
      $sql = "select * from TopFive"
      $res = mysql_query($sql, df_db());
    if ( !$res ){
                trigger_error(mysql_error(df_db()), E_USER_ERROR);
      }
      
   }
}
?>

Gives the following message...

Code: Select all

Parse error: syntax error, unexpected T_VARIABLE in /home/swmobridge/swmobridgecontest.org/cms/contest/tables/FullReport/FullReport.php on line 9

Revise the FullReport.php file to...

Code: Select all

<?
class tables_FullReport
{
   function block__after_header()
   {
   echo "<div id=\"Top Five\">
      <h4>Top Five (HS Division)</h4>";
      $res = mysql_query("select * from TopFive", df_db());
    if ( !$res ){
                trigger_error(mysql_error(df_db()), E_USER_ERROR);
      }
      
   }
}
?>


No more error message - but no table being displayed. Progress is being made - research will continue on this evening. I'm getting close, right?

[shannah]

Check out the PHP docs for mysql_query() After doing the query you have to loop through the results and print them.
http://php.net/manual/en/function.mysql-query.php

[mike.jones]

Excellent.

I decided to simply have a result page (using PHP) all by itself to display on the projector for the event, and use xataface interface just for data entry at the event. Seems to be working out.

http://www.swmobridgecontest.org/contestresults.php

Thanks again for a most excellent system.

Mike

[shannah]

Looks good. Whatever code you used for your result page, you could have used inside any block in Xataface if you wanted to include it inside the Xataface interface