Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

intensivex

Pioneers
  • Posts

    67
  • Joined

  • Last visited

Profile Information

  • Real Name
    Intensivex
  • Gender
    Male
  • Location
    East Coast
  • Website

intensivex's Achievements

  1. missed a colon in the referrer url: Should be: if($_SERVER['HTTP_REFERER'] != 'http://map.google.com/'.$_SERVER['QUERY_STRING']) header("Location: /");
  2. Seems i fixed my issues with the map. It now populates with icons. The problem was the visitors_georss.php needed to be in the root/catalog, public directory so the google maps api can access it (to populate it with icon locations). Fair enough, but my original assumption was this file not being in a publicly accessible directory. Even though its only basic information, someone can easily mine your traffic stats - exposing them like this probably violates some privacy rules and or laws as well. As a solution, I added this to visitors_georss.php: // Redirect me back to home page if im NOT maps.google.com if($_SERVER['HTTP_REFERER'] != 'http//map.google.com/'.$_SERVER['QUERY_STRING']) header("Location: /"); Also ... the v3 of the Map API code posted by @@pixclinic, didn't actually work for me. I reverted to v2 and all seems well again. Has anyone else gotten his v3 map update to work? Also ... For those of you still having issues with your visitors cart contents not displaying properly or at all, the issue stems from your get_products() and calculate() Methods that live inside your /admin/includes/classes/shopping_cart.php class. Namley to due with the differences in OOP between php 4 and php 5.3+ Apparently the while loops need to be converted into foreach's, and your queries should be updated to ensure they are returning results. The products_id wrapped in that tep_ function inside the queries was our issue. Here are the get_products() and calculate() functions, both /admin/includes/classes/shopping_cart.php http://pastebin.com/SgzpUVDj These work for us. Use a code comparison tool to see what we did. Good Luck!
  3. Hello folks, Install was a success. API key works (the map throws an alert dialog with an error if it doesn't). I'm digging the newer icons and the updated selects for control. I would love the map, if it actually did anything. Does the map rely on the ipinfo api feed at all? I've also applied pixclinic's v3 update, and the map still seems to work. My questions are: 1) Is the map designed to do anything? Am i supposed to see icons over the map? if so, from what local source? catalog/images/flags? 2) @pixclinic - in your update to v3, you're code seems like it expects "<HERE WE NEED THE LATITUDE>" and "<HERE WE NEED THE LONGITUDE>" - how are these populated and from where? Thanks much for your time.
  4. 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'); ?>
  5. 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);
  6. 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.
  7. Hi All ... I started another forum thread, not knowing this one existed. Atleast now my problem is a bit more refined and loads of you are familiar with the contrib (opposed to the gentlemen that where helping me with basic SQL syntax etc). I'll start by explaining. I wanted to improve on the default contribution by adding a date range feature which should spit out rows based on any date range you select. The latest contributions, although work in the same kind of way, are still a little rough around the edges. In the past I've had someone help me add a date range function to another report, so I thought I'd emulate it on this referal report. The guys helped me construct a working query which does exactly what Im looking to do, but I believe the syntax of the actual usage of the query is wrong. Perhaps some of you can chime in and tell me where I'm wrong and propose a solution: Here is my working query: 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-01-01' AND DATE( ci.customers_info_date_account_created ) >= DATE_ADD( '2010-01-26', INTERVAL 1 DAY ) GROUP BY s.sources_id ORDER BY no_referrals DESC Now, keep something in mind, I have a $date_from and $date_to var which I'm passing (on my other report). When a user selects a date range from the report, the query executes based on the range the user selected. The above query has dates in-lined to eliminate other possible causes of the thing not working. Once i get the query and the output correct, I'll re-add the GET vars for $date_from and $date_to. Here is the query with my vars (doesn't seem to work): $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" With so many moving peices I'm not sure my version of the query with the included vars is correct. Here are the definitions for the $_GET vars: <?php $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'); ?> Here is my current usage: <table border="0" width="100%" cellspacing="0" cellpadding="0"> <?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> Here is the form which ranges the referral report: <?php echo tep_draw_form('date_range', 'stats_referral_sources.php', '', 'get'); ?> <table><tr><td> <input type="text" name="date_from" onClick="self.popUpCalendar(this,this,'mm/dd/yyyy',document);" value="<?=$date_from?>" textfield> </td> <td> <input type="text" name="date_to" onClick="self.popUpCalendar(document.date_range.date_from,this,'mm/dd/yyyy',document);" value="<?=$date_to?>" textfield></td> <td><input type="submit" value="search"> </td> </tr> </table> </form> Since im not including this calendar date selector (which simply populates the inputs with the selected dates) just inline your dates in the form if your going to test it. I believe my problem is with my usage, although my query syntax with my $_GET vars could be wrong also. Can someone please chime in and assist me getting this working. Once it works, I'll release it into the contribs area for everyone to enjoy. Thanks in advance!
  8. Just a correction, this is whats being used to convert dates to the format the calendar needs: $date_from=date('Y-m-d',strtotime($date_from)); $date_to=date('Y-m-d',strtotime($date_to)); (Used right above the query) So I've changed my code to: $date_from=date('Y-m-d',strtotime($date_from)); $date_to=date('Y-m-d',strtotime($date_to)); $referrals_query_raw = "SELECT ci.customers_info_date_account_created, 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 WHERE ".($date_from?" ci.customers_info_date_account_created>='$date_from' ":"").($date_to?"AND ci.customers_info_date_account_created<'$date_to' ":"")." GROUP BY s.sources_id ORDER BY no_referrals DESC"; Notice i added: WHERE ".($date_from?" ci.customers_info_date_account_created>='$date_from' ":"").($date_to?"AND ci.customers_info_date_account_created<'$date_to' ":"")." Still same error! 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... I'm all over the place here. This can't be as difficult as it's becoming ... just an FYI, I used phpMyAdmin to generate me a snippet of php from that working query and i STILL recieve the same error when i use it: $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 ) >= \'2009-12-25\'' . ' AND DATE( ci.customers_info_date_account_created ) >= DATE_ADD( \'2009-01-25\', INTERVAL 1' . ' DAY )' . ' GROUP BY s.sources_id' . ' ORDER BY no_referrals DESC';
  9. I'm not entirely positive about the date format. I believe we added some tweak to the reports in the past to reflect a more western/US format of dates and for compatibility with a calendar script we're using. I believe this is the snippet which does the date conversion on the fly: return sprintf("%04d-%02d-%02d",$y,$m,$d); Regarding the logic, you make sense. I tested inside phpMyAdmin and it runs perfectly. You da man! ... My problem still remains though - my stinkin script doesn't work. Even using your exact query with no date range function (with those dates in-lined), I'm still getting sql syntax errors. To help my confidence that it isn't something outside of the query conflicting with the query, can you tell me if the following snippet would be the cause of the error (where the query is actually used): <table border="0" width="100%" cellspacing="0" cellpadding="2"> <?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> <?php } else { ?> <tr class="dataTableRow" 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> If the above doesn't conflict, and I've commented out the other variables like the $_GET vars, then the script should spit out the same results i receive in phpMyAdmin (in theory). Just as described (everything in-lined and other variables commented out), here is the error: 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 ) >= '2009-12-25' AND DATE( ci.customers_info_date_account_created ) >= DATE_ADD( '2009-01-25', INTERVAL 1 DAY ) GROUP BY s.sources_id ORDER BY no_referrals DESC Here is what phpMyAdmin spits out: Showing rows 0 - 21 (22 total, Query took 0.1792 sec) SQL query: 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 ) >= '2009-12-25' AND DATE( ci.customers_info_date_account_created ) >= DATE_ADD( '2009-01-25', INTERVAL 1 DAY ) GROUP BY s.sources_id ORDER BY no_referrals DESC LIMIT 0 , 30
  10. sorry didnt notice the forums pagination ... let me digest this for a minute ... thanks FWR
  11. I know I've resulted to pasting my entire script like some newb crying for free scripting advice, but I'm willing to barter my services if that's the case. I'm simply at a point of limited skill level and have resulted to kicking my way through what was supposed to be a simple enhancement. I thought emulating what works (or whats already in place) was going to be easier then this, but there are so many moving pieces I get lost quickly. If someone would please chime in and help me through this further, I'd be indebted to you. Thanks guys for all your valuable time!
  12. Thanks alot ... the question that comes up is, what's wrong with the logic? Surely the date_range_query shouldnt produce zero results. My logic is: Grab the date the account was created as recorded in the customers_info_date_account_created column of the customers_info table, to segment results by date range. Since this default contrib logs all referrals and Groups By source_id, while displaying everything in lump numbers since the life of the db table, I wanted to add a date ranging mechanism as i have on other reports in my admin. This should allow a greater degree of detail analyzing some of our marketing efforts. Yes it's not foolproof, and a customer could simply lie about "how they heard about us", but it's an important dimension and allows us to place sources like magazine referral sources among other non-traceable referral sources. I assume the logic is wrong in the following snippet if my query is correct: <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> Actually, here is the entire script from head to tail: <?php require('includes/application_top.php'); $action = (isset($HTTP_GET_VARS['action']) ? $HTTP_GET_VARS['action'] : ''); function fmt_date($date) { list($m,$d,$y)=preg_split('|[\-\-]|',$date); if ($d<=0 || $m<=0) return NULL; if ($y=='') $y=date('Y'); if ($y<50) $y+=2000; return sprintf("%02d-%02d-%04d",$m,$d,$y); } $date_range_query = ''; if (isset($_GET['date_from']) && $date_from=fmt_date($_GET['date_from'])) { if (strlen($date_range_query) > 0) $date_range_query .= 'and '; $date_range_query .= "ci.customers_info_date_account_created>='$date_from' "; } if (isset($_GET['date_to']) && $date_to=fmt_date($_GET['date_to'])) { if (strlen($date_range_query) > 0) $date_range_query .= 'and '; $date_range_query .= "ci.customers_info_date_account_created>='$date_to'+INTERVAL 1 DAY "; } 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 " . TABLE_CUSTOMERS_INFO . " ci LEFT JOIN " . TABLE_SOURCES . " s ON s.sources_id = ci.customers_info_source_id WHERE $date_range_query 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 type="text/javascript" src="includes/general.js"></script> <script type="text/javascript" src="js/popcalendar.js"></script> </head> <body> <?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"> <?php $date_from=date('m-d-Y',time()-47840*7*8); $date_to=date('m-d-Y',time()); echo tep_draw_form('date_range', 'stats_referral_sources.php', '', 'get'); ?> <table border="0" cellpadding="0" cellspacing="0"> <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> </table> </form> </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'); ?> Process of elimination is like throwing crap on the wall and seeing if it sticks i guess ...
  13. MySQL returned an empty result set (i.e. zero rows). (Query took 0.0081 sec) SQL query: 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 ci.customers_info_date_account_created >= '12-25-2009' AND ci.customers_info_date_account_created >= '01-25-2010' + INTERVAL 1 DAY GROUP BY s.sources_id ORDER BY no_referrals DESC LIMIT 0 , 30
  14. Here is the output: 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 ci.customers_info_date_account_created>='12-25-2009' and ci.customers_info_date_account_created>='01-25-2010'+INTERVAL 1 DAY GROUP BY s.sources_id ORDER BY no_referrals DESC Here is $date_range_query with only the change you suggested: $date_range_query = ''; if (isset($_GET['date_from']) && $date_from=fmt_date($_GET['date_from'])) { if (strlen($date_range_query) > 0) $date_range_query .= 'and '; $date_range_query .= "ci.customers_info_date_account_created>='$date_from' "; } if (isset($_GET['date_to']) && $date_to=fmt_date($_GET['date_to'])) { if (strlen($date_range_query) > 0) $date_range_query .= 'and '; $date_range_query .= "ci.customers_info_date_account_created>='$date_to'+INTERVAL 1 DAY "; } Here is the query again unchanged: $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 " . TABLE_CUSTOMERS_INFO . " ci LEFT JOIN " . TABLE_SOURCES . " s ON s.sources_id = ci.customers_info_source_id WHERE $date_range_query GROUP BY s.sources_id ORDER BY no_referrals DESC"; Here is the error: 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 ...
×
×
  • Create New...