Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

How'd you hear about is report.


intensivex

Recommended Posts

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.

Link to comment
Share on other sites

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".

Link to comment
Share on other sites

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);

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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'); ?>

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...