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

A place for users and developers of the Xataface to discuss and receive support.

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

Postby mike.jones » Wed Feb 27, 2013 8:35 am

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/
mike.jones
 
Posts: 12
Joined: Mon Feb 11, 2013 9:17 pm

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

Postby shannah » Wed Feb 27, 2013 10:05 am

The parser is having trouble with the union statement.

What do you mean by a custom header?

-Steve
--
Steve Hannah
@shannah78 (on twitter)
sjhannah.com blog
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

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

Postby mike.jones » Wed Feb 27, 2013 10:27 am

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
Attachments
small-header-screenshot-for-forum.JPG
screenshot
small-header-screenshot-for-forum.JPG (100.44 KiB) Viewed 45079 times
mike.jones
 
Posts: 12
Joined: Mon Feb 11, 2013 9:17 pm

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

Postby shannah » Wed Feb 27, 2013 2:00 pm

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?
--
Steve Hannah
@shannah78 (on twitter)
sjhannah.com blog
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

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

Postby mike.jones » Wed Feb 27, 2013 2:37 pm

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.
mike.jones
 
Posts: 12
Joined: Mon Feb 11, 2013 9:17 pm

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

Postby shannah » Wed Feb 27, 2013 3:32 pm

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
--
Steve Hannah
@shannah78 (on twitter)
sjhannah.com blog
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

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

Postby mike.jones » Wed Feb 27, 2013 4:17 pm

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

Thank you very much.

Mike
mike.jones
 
Posts: 12
Joined: Mon Feb 11, 2013 9:17 pm

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

Postby mike.jones » Wed Feb 27, 2013 5:21 pm

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
mike.jones
 
Posts: 12
Joined: Mon Feb 11, 2013 9:17 pm

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

Postby shannah » Thu Feb 28, 2013 10:30 am

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
--
Steve Hannah
@shannah78 (on twitter)
sjhannah.com blog
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

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

Postby mike.jones » Sat Mar 02, 2013 8:55 am

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
mike.jones
 
Posts: 12
Joined: Mon Feb 11, 2013 9:17 pm

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

Postby shannah » Sat Mar 02, 2013 1:13 pm

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
--
Steve Hannah
@shannah78 (on twitter)
sjhannah.com blog
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm


Return to Xataface Users

Who is online

Users browsing this forum: No registered users and 1 guest

cron
Powered by Dataface
© 2005-2007 Steve Hannah All rights reserved