Page 1 of 1

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

PostPosted: Wed Feb 27, 2013 8:35 am
by 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/

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

PostPosted: Wed Feb 27, 2013 10:05 am
by shannah
The parser is having trouble with the union statement.

What do you mean by a custom header?

-Steve

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

PostPosted: Wed Feb 27, 2013 10:27 am
by mike.jones
shannah wrote: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

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

PostPosted: Wed Feb 27, 2013 2:00 pm
by 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?

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

PostPosted: Wed Feb 27, 2013 2:37 pm
by mike.jones
shannah wrote: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.

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

PostPosted: Wed Feb 27, 2013 3:32 pm
by 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

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

PostPosted: Wed Feb 27, 2013 4:17 pm
by mike.jones
I appreciate the direction - I'll see if I can get it to work.

Thank you very much.

Mike

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

PostPosted: Wed Feb 27, 2013 5:21 pm
by 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? :D

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

PostPosted: Thu Feb 28, 2013 10:30 am
by 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

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

PostPosted: Sat Mar 02, 2013 8:55 am
by 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

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

PostPosted: Sat Mar 02, 2013 1:13 pm
by 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