intensivex Posted March 12, 2010 Author Share Posted March 12, 2010 Your SQL query was select 0 SELECT ci.... Where did that "select 0" come from? Was there some other piece of code trying to build a query, starting with "select $value"? Is $referrals_query_raw what's fed to mysql_query(), or is there further processing that adds more stuff? Sorry to not get back to you before, but today is the first time that the forum has let me see your replies! And just when I thought the thread was dead ... thanks alot for coming back to this. I think it may be more efficient for me to paste the entire script here: <?php require('includes/application_top.php'); $action = (isset($HTTP_GET_VARS['action']) ? $HTTP_GET_VARS['action'] : ''); if ($action == 'display_other') { $referrals_query_raw = "select count(ci.customers_info_source_id) as no_referrals, so.sources_other_name as sources_name from " . TABLE_CUSTOMERS_INFO . " ci, " . TABLE_SOURCES_OTHER . " so where ci.customers_info_source_id = '9999' and so.customers_id = ci.customers_info_id group by so.sources_other_name order by so.sources_other_name DESC"; } else { /* $referrals_query_raw = "SELECT ci.customers_info_date_account_created AS date_created, COUNT( ci.customers_info_source_id ) AS no_referrals, s.sources_name, s.sources_id FROM customers_info ci LEFT JOIN sources s ON s.sources_id = ci.customers_info_source_id WHERE DATE( ci.customers_info_date_account_created ) >= $date_from AND DATE( ci.customers_info_date_account_created ) >= DATE_ADD( $date_to, INTERVAL 1 DAY ) GROUP BY s.sources_id ORDER BY no_referrals DESC";*/ $referrals_query_raw = "select count(ci.customers_info_source_id) as no_referrals, s.sources_name, s.sources_id from " . TABLE_CUSTOMERS_INFO . " ci LEFT JOIN " . TABLE_SOURCES . " s ON s.sources_id = ci.customers_info_source_id group by s.sources_id order by no_referrals DESC"; } ?> <!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN"> <html <?php echo HTML_PARAMS; ?>> <head> <meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>"> <title><?php echo HEADING_TITLE; ?></title> <link rel="stylesheet" type="text/css" href="includes/stylesheet.css"> <script language="javascript" src="includes/general.js"></script> <script type="text/javascript" src="js/popcalendar.js"></script> </head> <body style="background-color:transparent; margin:0;"> <?php require(DIR_WS_INCLUDES . 'header.php'); ?> <table border="0" width="100%" cellspacing="2" cellpadding="2"> <tr> <td width="100%" valign="top" colspan="2"><table border="0" width="100%" cellspacing="0" cellpadding="0"> <tr> <td style="padding:10px 0 15px 0"><table border="0" width="100%" cellspacing="0" cellpadding="0"> <tr> <td class="pageHeading"><?php echo HEADING_TITLE; ?></td> <td class="pageHeading" align="right"> <table border="0" cellpadding="0" cellspacing="0"> <? $date_from=isset($_GET['date_from'])?$_GET['date_from']:date('m-d-Y'); $date_to=isset($_GET['date_to'])?$_GET['date_to']:date('m-d-Y'); ?> <?php echo tep_draw_form('date_range', 'stats_referral_sources.php', '', 'get'); ?> <tr> <td align="right" style="padding-top:2px;"> <input type="text" name="date_from" style="font:bold 9px arial;" onClick="self.popUpCalendar(this,this,'mm/dd/yyyy',document);" value="<?=$date_from?>" size="12" maxlength="11" textfield></td> <td><img src="images/calander2.gif" width="16" height="16" hspace="3" border="0" onClick="self.popUpCalendar(document.date_range.date_from,document.date_range.date_from,'mm/dd/yyyy',document);" style="cursor:pointer"></td> <td align="center" style="padding-top:1px; padding-left:3px; padding-right:3px;"> - </td> <td align="right" style="padding-top:2px;"><input type="text" name="date_to" onClick="self.popUpCalendar(document.date_range.date_from,this,'mm/dd/yyyy',document);" style="font:bold 9px arial;" value="<?=$date_to?>" size="12" maxlength="11" textfield></td> <td><img src="images/calander2.gif" width="16" height="16" hspace="3" border="0" onClick="self.popUpCalendar(document.date_range.date_from,document.date_range.date_to,'mm/dd/yyyy',document);" style="cursor:pointer"></td> <td style="padding-right:7px; padding-top:1px;"> <a href="javascript:document.date_range.submit();"><font style="font:bold 11px arial; background-color:#6295FD; color:#FFFFFF;"> GO </font></a> </td> </tr> </form> </table> </td> </tr> </table></td> </tr> <tr> <td><table border="0" width="100%" cellspacing="0" cellpadding="0"> <tr> <td valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2"> <tr class="dataTableHeadingRow"> <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_NUMBER; ?></td> <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_REFERRALS; ?></td> <td class="dataTableHeadingContent" align="center"><?php echo TABLE_HEADING_VIEWED; ?> </td> </tr> <?php if (isset($HTTP_GET_VARS['page']) && ($HTTP_GET_VARS['page'] > 1)) $rows = $HTTP_GET_VARS['page'] * MAX_DISPLAY_SEARCH_RESULTS - MAX_DISPLAY_SEARCH_RESULTS; $rows = 0; $presplit_query = tep_db_query($referrals_query_raw); $presplit_query_numrows = tep_db_num_rows($presplit_query); $referrals_split = new splitPageResults($HTTP_GET_VARS['page'], MAX_DISPLAY_SEARCH_RESULTS, $referrals_query_raw, $referrals_query_numrows); $referrals_query_numrows = $presplit_query_numrows; $referrals_query = tep_db_query($referrals_query_raw); while ($referrals = tep_db_fetch_array($referrals_query)) { $rows++; if (strlen($rows) < 2) { $rows = '0' . $rows; } if ( tep_not_null($referrals['sources_name']) ) { ?> <tr class="dataTableRow" onmouseover="rowOverEffect(this)" onmouseout="rowOutEffect(this)"> <?php } else { ?> <tr class="dataTableRow" onmouseover="rowOverEffect(this)" onmouseout="rowOutEffect(this)" onclick="document.location.href='<?php echo tep_href_link(FILENAME_STATS_REFERRAL_SOURCES, 'action=display_other'); ?>'"> <?php } ?> <td class="dataTableContent"><?php echo $rows; ?>.</td> <td class="dataTableContent"><?php echo (tep_not_null($referrals['sources_name']) ? $referrals['sources_name'] : '<b style="cursor:pointer">' . TEXT_OTHER . '</b>');?> </td> <td class="dataTableContent" align="center"><?php echo $referrals['no_referrals']; ?> </td> </tr> <?php } ?> </table></td> </tr> <tr> <td colspan="3"><table border="0" width="100%" cellspacing="0" cellpadding="2"> <tr> <td class="smallText" valign="top"><?php echo $referrals_split->display_count($referrals_query_numrows, MAX_DISPLAY_SEARCH_RESULTS, $HTTP_GET_VARS['page'], TEXT_DISPLAY_NUMBER_OF_REFERRALS); ?></td> <td class="smallText" align="right"><?php echo $referrals_split->display_links($referrals_query_numrows, MAX_DISPLAY_SEARCH_RESULTS, MAX_DISPLAY_PAGE_LINKS, $HTTP_GET_VARS['page'], tep_get_all_get_params(array('page')) ); ?></td> </tr> </table></td> </tr> </table></td> </tr> </table></td> </tr> </table> </body> </html> <?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?> You'll see I commented out the non-working query. I'm simply trying to make my date range form (date_range) work. Your help would be greatly appreciated. Quote Link to comment Share on other sites More sharing options...
MrPhil Posted March 19, 2010 Share Posted March 19, 2010 Are you still getting the "select 0 SELECT..." error, or is it something else now? I can't read your code listing... please "Preview" it and "wrap" long lines like your query statements (insert hard carriage returns) so I don't have to scroll way down to the bottom of the listing, scroll right, scroll the page back up, and try to figure out which line I should be looking at. I just keep losing my position in a long code listing (spanning several screens). Are you using the split_page_results() constructor correctly? The definition for it is function splitPageResults($query, $max_rows, $count_key = '*', $page_holder = 'page') { where the query string comes first (you have a page number?). That might account for the "select 0". Quote Link to comment Share on other sites More sharing options...
intensivex Posted March 19, 2010 Author Share Posted March 19, 2010 Are you still getting the "select 0 SELECT..." error, or is it something else now? I can't read your code listing... please "Preview" it and "wrap" long lines like your query statements (insert hard carriage returns) so I don't have to scroll way down to the bottom of the listing, scroll right, scroll the page back up, and try to figure out which line I should be looking at. I just keep losing my position in a long code listing (spanning several screens). Are you using the split_page_results() constructor correctly? The definition for it is function splitPageResults($query, $max_rows, $count_key = '*', $page_holder = 'page') { where the query string comes first (you have a page number?). That might account for the "select 0". Sorry about that, but i can't exactly control where it's spanning and not breaking line in those tags. Could you copy and paste it into something like notepad? My errors are as follow (when enabling that commented-out query): 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT ci.customers_info_date_account_created AS date_created, C select 0 SELECT ci.customers_info_date_account_created AS date_created, COUNT( ci.customers_info_source_id ) AS no_referrals, s.sources_name, s.sources_id FROM customers_info ci LEFT JOIN sources s ON s.sources_id = ci.customers_info_source_id WHERE DATE( ci.customers_info_date_account_created ) >= 2010-03-19 AND DATE( ci.customers_info_date_account_created ) >= DATE_ADD( 2010-03-19, INTERVAL 1 DAY ) GROUP BY s.sources_id ORDER BY no_referrals DESC The splitPageReults line is line #86: $referrals_split = new splitPageResults($HTTP_GET_VARS['page'], MAX_DISPLAY_SEARCH_RESULTS, $referrals_query_raw, $referrals_query_numrows); Quote Link to comment Share on other sites More sharing options...
MrPhil Posted March 19, 2010 Share Posted March 19, 2010 You have to look at the page in Preview, and manually hit the Enter key where you want a "hard carriage return" inserted, so that it doesn't extend beyond the box. My point about splitPageResults is that I think you're calling it wrong. If this is the catalog version of the class you're using, the SQL query needs to be first. If you're using the admin version, I think it's correct. Make sure of which one you're using -- they have the same name, but different calls! Quote Link to comment Share on other sites More sharing options...
intensivex Posted March 19, 2010 Author Share Posted March 19, 2010 I did my best wraping the code. Depending on your resolution, it may still scroll horizontally. This is the admin version of the report. <?php require('includes/application_top.php'); $action = (isset($HTTP_GET_VARS['action']) ? $HTTP_GET_VARS['action'] : ''); if ($action == 'display_other') { $referrals_query_raw = "select count(ci.customers_info_source_id) as no_referrals, so.sources_other_name as sources_name from " . TABLE_CUSTOMERS_INFO . " ci, " . TABLE_SOURCES_OTHER . " so where ci.customers_info_source_id = '9999' and so.customers_id = ci.customers_info_id group by so.sources_other_name order by so.sources_other_name DESC"; } else { /* $referrals_query_raw = "SELECT ci.customers_info_date_account_created AS date_created, COUNT(ci.customers_info_source_id) AS no_referrals, s.sources_name, s.sources_id FROM customers_info ci LEFT JOIN sources s ON s.sources_id = ci.customers_info_source_id WHERE DATE( ci.customers_info_date_account_created ) >= $date_from AND DATE( ci.customers_info_date_account_created ) >= DATE_ADD( $date_to, INTERVAL 1 DAY ) GROUP BY s.sources_id ORDER BY no_referrals DESC";*/ $referrals_query_raw = "select count(ci.customers_info_source_id) as no_referrals, s.sources_name, s.sources_id from " . TABLE_CUSTOMERS_INFO . " ci LEFT JOIN " . TABLE_SOURCES . " s ON s.source s_id = ci.customers_info_source_id group by s.sources_id order by no_referrals DESC";} ?> <!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN"> <html <?php echo HTML_PARAMS; ?>> <head> <meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>"> <title><?php echo HEADING_TITLE; ?></title> <link rel="stylesheet" type="text/css" href="includes/stylesheet.css"> <script language="javascript" src="includes/general.js"></script> <script type="text/javascript" src="js/popcalendar.js"></script> </head> <body style="background-color:transparent; margin:0;"> <?php require(DIR_WS_INCLUDES . 'header.php'); ?> <table border="0" width="100%" cellspacing="2" cellpadding="2"> <tr> <td width="100%" valign="top" colspan="2"><table border="0" width="100%" cellspacing="0" cellpadding="0"> <tr> <td style="padding:10px 0 15px 0"><table border="0" width="100%" cellspacing="0" cellpadding="0"> <tr> <td class="pageHeading"><?php echo HEADING_TITLE; ?></td> <td class="pageHeading" align="right"> <table border="0" cellpadding="0" cellspacing="0"> <? $date_from=isset($_GET['date_from'])?$_GET['date_from']:date('m-d-Y'); $date_to=isset($_GET['date_to'])?$_GET['date_to']:date('m-d-Y'); ?> <?php echo tep_draw_form('date_range', 'stats_referral_sources.php', '', 'get'); ?> <tr> <td align="right"> <input type="text" name="date_from" onclick="self.popUpCalendar(this,this,'mm/dd/yyyy',document);" value="<?=$date_from?>" size="12" maxlength="11" textfield> </td> <td> <img src="images/calander2.gif" width="16" height="16" hspace="3" border="0" onclick="self.popUpCalendar(document.date_range.date_from,document.date_range.date_from,'mm/dd/yyyy',document);"> </td> <td> - </td> <td> <input type="text" name="date_to" onclick="self.popUpCalendar(document.date_range.date_from,this,'mm/dd/yyyy',document);" value="<?=$date_to?>" size="12" textfield> </td> <td> <img src="images/calander2.gif" width="16" height="16" onclick="self.popUpCalendar(document.date_range.date_from,document.date_range.date_to,'mm/dd/yyyy',document);"> </td> <td> <a href="javascript:document.date_range.submit();"><font style="font:bold 11px arial; background-color:#6295FD; color:#FFFFFF;"> GO </font></a> </td> </tr> </form> </table> </td> </tr> </table></td> </tr> <tr> <td><table border="0" width="100%" cellspacing="0" cellpadding="0"> <tr> <td valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2"> <tr class="dataTableHeadingRow"> <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_NUMBER; ?></td> <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_REFERRALS; ?></td> <td align="center"><?php echo TABLE_HEADING_VIEWED; ?> </td> </tr> <?php if (isset($HTTP_GET_VARS['page']) && ($HTTP_GET_VARS['page'] > 1)) $rows = $HTTP_GET_VARS['page'] * MAX_DISPLAY_SEARCH_RESULTS - MAX_DISPLAY_SEARCH_RESULTS; $rows = 0; $presplit_query = tep_db_query($referrals_query_raw); $presplit_query_numrows = tep_db_num_rows($presplit_query); $referrals_split = new splitPageResults($HTTP_GET_VARS['page'], MAX_DISPLAY_SEARCH_RESULTS, $referrals_query_raw, $referrals_query_numrows); $referrals_query_numrows = $presplit_query_numrows; $referrals_query = tep_db_query($referrals_query_raw); while ($referrals = tep_db_fetch_array($referrals_query)) { $rows++; if (strlen($rows) < 2) { $rows = '0' . $rows; } if ( tep_not_null($referrals['sources_name']) ) { ?> <tr class="dataTableRow" onmouseover="rowOverEffect(this)" onmouseout="rowOutEffect(this)"> <?php } else { ?> <tr onclick="document.location.href='<?php echo tep_href_link(FILENAME_STATS_REFERRAL_SOURCES, ' action=display_other'); ?>'"> <?php } ?> <td class="dataTableContent"><?php echo $rows; ?>.</td> <td> <?php echo (tep_not_null($referrals['sources_name']) ? $referrals['sources_name'] : '<b style="cursor:pointer">' . TEXT_OTHER . '</b>');?> </td> <td class="dataTableContent" align="center"><?php echo $referrals['no_referrals'];?> </td> </tr> <?php } ?> </table></td> </tr> <tr> <td colspan="3"><table border="0" width="100%" cellspacing="0" cellpadding="2"> <tr> <td class="smallText" valign="top"> <?php echo $referrals_split->display_count($referrals_query_numrows, MAX_DISPLAY_SEARCH_RESULTS, $HTTP_GET_VARS['page'], TEXT_DISPLAY_NUMBER_OF_REFERRALS); ?> </td> <td class="smallText" align="right"> <?php echo $referrals_split->display_links($referrals_query_numrows, MAX_DISPLAY_SEARCH_RESULTS, MAX_DISPLAY_PAGE_LINKS, $HTTP_GET_VARS['page'], tep_get_all_get_params(array('page')) ); ?> </td> </tr> </table></td> </tr> </table></td> </tr> </table></td> </tr> </table> </body> </html> <?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?> Quote Link to comment Share on other sites More sharing options...
MrPhil Posted March 20, 2010 Share Posted March 20, 2010 You're still getting the "select 0 SELECT ..." error? I dunno. About all I can think of at this point is that you're pulling in the wrong "include" branch -- the "catalog" side rather than the "admin" side. What PHP version? Do you have an "include_path" setting in your php.ini that specifies your store root (e.g., ..../catalog)? If so, that might be picking up the includes/... rather than admin/includes/... You could check this by explicitly adding admin/ to the "require" or "include" paths: require('admin/includes/application_top.php'); etc. and see if the behavior changes. If it then works, that was the problem. If no difference, at this point I have no idea. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.