How'd you hear about is report.

intensivex · January 25, 2010

Hello ... pardon me if this isn't categorized properly. I couldn't find a forum category or thread which covered this.

I installed the "How'd you hear about us" contrib some time ago.

http://www.oscommerce.com/community/contributions,2159

I recently decided to add a date ranging function since the default admin side report tallied totals from the life of the install.

My problem starts with adding an existing date range function (on some other reports of mine) to this "How'd you hear about us" report. The problem is my query syntax, and no matter what i come up with, it errors out.

I would greatly appreciate someone with a higher level of mysql coding to chime in and point me in the right direction.

Here is my code:

Query before my tinkering:

//  $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";

Query After my tinkering:

$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 WHERE '". ($date_from?"AND 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";

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 '01-01-2010' AND ci.customers_info_date_account_created<'01-24-20

SELECT 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 'AND ci.customers_info_date_account_created>='01-01-2010' AND ci.customers_info_date_account_created<'01-24-2010' ' GROUP BY s.sources_id ORDER BY no_referrals DESC

[TEP STOP]

This is the snippet of code I added to the query:


WHERE '". ($date_from?"AND ci.customers_info_date_account_created>='$date_from' ":"").($date_to?"AND ci.customers_info_date_account_created<'$date_to' ":"")."'

I'm grabbing the actual date from the customers_info_date_account_created column since it seems the straightest way (without digging through other tables or creating other queries outside of my programming level) to grab dates.

Here is the form on the admin side report which does the ranging:


<?php 
$date_from=isset($_GET['date_from'])?$_GET['date_from']:date('m/d/Y',time()-86400*7*8);
$date_to=isset($_GET['date_to'])?$_GET['date_to']:date('m/d/Y',time());
echo tep_draw_form('date_range', 'stats_referral_sources.php', '', 'get'); ?>
<table>
<tr>
<td><input type="text" name="date_from" value="<?=$date_from?>" size="12" maxlength="11" textfield></td>
<td> - </td>
<td><input type="text" name="date_to" value="<?=$date_to?>" size="12" maxlength="11" textfield></td>
<td><a href="javascript:document.date_range.submit();">GO</a>
</td>
</tr>
</table>
</form>

I hope my problem is simple and someone can shed some light on the my issue and teach me a little something ...

Thanks alot for your valuable time!

Edited January 25, 2010 by intensivex

intensivex · January 25, 2010

So to eliminate syntax issues (or i thought i was eliminating), I consolidated that date function to an actual function like so:

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("%04d-%02d-%02d",$y,$m,$d);
 }

 $date_range_query='';
 if (isset($_GET['date_from']) && $date_from=fmt_date($_GET['date_from'])) $date_range_query.="and date_account_created>='$date_from' ";
 if (isset($_GET['date_to']) && $date_to=fmt_date($_GET['date_to'])) $date_range_query.="and date_account_created<'$date_to'+INTERVAL 1 DAY ";

Which works in different reports, so I'm sure the syntax is fine.

I also noticed i missed grabbing the actual column for the data i needed in my query, so i updated things:

$referrals_query_raw = "SELECT ci.customers_info_date_account_created AS 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_range_query 
GROUP BY s.sources_id 
ORDER BY no_referrals DESC";

You'll notice, I used the $date_range_query function i created, in my query. Luck has it, it produces the same stinkin 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 'and date_account_created>='2009-12-31' and date_account_created<......

SELECT ci.customers_info_date_account_created AS date_account_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 and date_account_created>='2009-12-31' and date_account_created<'2010-01-24'+INTERVAL 1 DAY GROUP BY s.sources_id ORDER BY no_referrals DESC

[TEP STOP]

Please help!

MrPhil · January 25, 2010

Your variable $date_range_query is improperly constructed. It is 'and date_account_created>=..., leading to a bad SQL query ...WHERE and date... . You want to put that leading 'and' on it only if there is already something in the clause:

$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 .= "date_account_created>='$date_from' ";
}
... etc. ...

intensivex · January 25, 2010

Hi Phil ... thanks a ton for your input ...

Ok, I edited my code to include your revision but I think I'm still missing something (now an undefined column error).

1054 - Unknown column 'date_account_created' in 'where clause'

As i mentioned earlier, I found one of my problems was not declaring the customers_info_date_account_created column, so i added it to the query (or atleast i thought i did).

$referrals_query_raw = "SELECT ci.customers_info_date_account_created AS 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_range_query 
GROUP BY s.sources_id 
ORDER BY no_referrals DESC";

SELECT ci.customers_info_date_account_created AS date_account_created, ....

Here is the whole code including your revision:


$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 .= "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 .= "date_account_created>='$date_to'+INTERVAL 1 DAY ";

}

$referrals_query_raw = "SELECT ci.customers_info_date_account_created AS 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_range_query 
GROUP BY s.sources_id 
ORDER BY no_referrals DESC";

What did i miss?

Thanks alot once again!

MrPhil · January 25, 2010

All I can figure is that there is no customers_info_date_account_created field in table customers_info. Check that -- a missing field would probably mean that the alias (date_account_created) wouldn't be created, leading to the error.

intensivex · January 25, 2010

Hiya Phil ...

That's weird ... The customers_info_date_account_created column exists inside the customers_info table of osc's default 2.2 db install (and exists in my db) ... The table itself is being used in the query as " . TABLE_CUSTOMERS_INFO . "

Inside database_tables.php it is defined as:

define('TABLE_CUSTOMERS_INFO', 'customers_info');

Just as a reference, here is a partial sql dump of my customers_info table structure:

...TABLE customers_info (

customers_info_id int(11) NOT NULL default '0',

customers_info_date_of_last_logon datetime default NULL,

customers_info_number_of_logons int(5) default NULL,

customers_info_date_account_created datetime default NULL,

customers_info_date_account_last_modified datetime default NULL,

customers_info_source_id int(11) NOT NULL default '0',

global_product_notifications int(1) default '0',

PRIMARY KEY (customers_info_id)

) ...

Could you kindly give my query a once over and let me know what I did wrong?

You've already shed some light on my first problem, which I've absorbed, so these are valuable lessons that should stick with me for a long time to come. Teach a man to fish and he eats for life ;).

Thanks alot once again ... and I owe ya one!

♥FWR Media · January 25, 2010

In your $date_range_query you have ..

date_account_created

this should be ..

ci.customers_info_date_account_created

intensivex · January 25, 2010

In your $date_range_query you have ..
date_account_created
this should be ..
ci.customers_info_date_account_created

Thanks FWR, but I've tried several variations of that already to no avail.

Using ci.customers_info_date_account_created in place of date_account_created produces the following 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...

If you notice my query, I'm trying to alias ci.customers_info_date_account_created AS date_account_created


$referrals_query_raw = "SELECT ci.customers_info_date_account_created AS 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_range_query 
GROUP BY s.sources_id 
ORDER BY no_referrals DESC";

My problem seems that my query doesn't return the column customers_info_date_account_created from the customers_info table of my database.

1054 - Unknown column 'date_account_created' in 'where clause'

Here is the entire snippet:

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 .= "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 .= "date_account_created>='$date_to'+INTERVAL 1 DAY ";

}

$referrals_query_raw = "SELECT ci.customers_info_date_account_created AS 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_range_query 
GROUP BY s.sources_id 
ORDER BY no_referrals DESC";

Thanks again guys ...

Edited January 25, 2010 by intensivex

intensivex · January 25, 2010

Removing the AS alias "date_account_created" from the query and using strictly ci.customers_info_date_account_created produces the 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, COUNT(ci.customer

select 0 SELECT ci.customers_info_date_account_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

♥FWR Media · January 25, 2010

Removing the AS alias "date_account_created" from the query and using strictly ci.customers_info_date_account_created produces the 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, COUNT(ci.customer

select 0 SELECT ci.customers_info_date_account_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

Just below the query and before it is actually ran add the following temporary code: -

// REMOVE ME
if ( array_key_exists( 'fwrtest', $_GET ) ) {
 echo $referrals_query_raw;
}
// REMOVE ME

Browse to the page that generates this query but append the querystring ?fwrtest

Post here the query it outputs.

intensivex · January 25, 2010

Just below the query and before it is actually ran add the following temporary code: -
// REMOVE ME
if ( array_key_exists( 'fwrtest', $_GET ) ) {
 echo $referrals_query_raw;
}
// REMOVE ME
Browse to the page that generates this query but append the querystring ?fwrtest

Post here the query it outputs.

Ok, added the code, 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 date_created>='12/25/2009' and date_created>='01/25/2010'+INTERVAL 1 DAY GROUP BY s.sources_id ORDER BY no_referrals DESC

♥FWR Media · January 25, 2010

Ok, added the code, 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 date_created>='12/25/2009' and date_created>='01/25/2010'+INTERVAL 1 DAY GROUP BY s.sources_id ORDER BY no_referrals DESC

Well .. same again .. the only problem with the query is that you can't use date_created you have to use the version referencing the table as in the SELECT so ci.customers_info_date_account_created

intensivex · January 25, 2010

Well .. same again .. the only problem with the query is that you can't use date_created you have to use the version referencing the table as in the SELECT so ci.customers_info_date_account_created

I'm just confused ... call it "writers block" ...

If i remove the alias from the query like so:

$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_range_query 
GROUP BY s.sources_id 
ORDER BY no_referrals DESC";

and change my $date_range_query to

$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 ";

}

I get the following 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, COUNT(ci.customer

select 0 SELECT ci.customers_info_date_account_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

[TEP STOP]

....

My forehead hurts from banging it on my desk ... :P

♥FWR Media · January 25, 2010

I'm just confused ... call it "writers block" ...

If i remove the alias from the query like so:
$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_range_query 
GROUP BY s.sources_id 
ORDER BY no_referrals DESC";
and change my $date_range_query to
$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 ";

}
I get the following 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, COUNT(ci.customer

select 0 SELECT ci.customers_info_date_account_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

[TEP STOP]

....

My forehead hurts from banging it on my desk ... :P

you don't need to remove the alias, but the alias is only used as the returned key on a successful query it can't be used as a part of the query itself.

What outputs with JUST the change I suggested when you run my output code?

intensivex · January 25, 2010

you don't need to remove the alias, but the alias is only used as the returned key on a successful query it can't be used as a part of the query itself.

What outputs with JUST the change I suggested when you run my output code?

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

♥FWR Media · January 25, 2010

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

Run the query that was output in phpMyAdmin ( or your chosen method ).

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

intensivex · January 25, 2010

Run the query that was output in phpMyAdmin ( or your chosen method ).

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

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

♥FWR Media · January 25, 2010

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

Well there you go .. the query is now fine there must be something in the code where the query is applied.

This is of course assuming that the query date range should have produced zero results .. if not the logic is wrong .. BUT .. the query works as is with no MySQL errors.

Edited January 25, 2010 by FWR Media

intensivex · January 26, 2010

Well there you go .. the query is now fine there must be something in the code where the query is applied.

This is of course assuming that the query date range should have produced zero results .. if not the logic is wrong .. BUT .. the query works as is with no MySQL errors.

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

intensivex · January 26, 2010

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!

♥FWR Media · January 26, 2010

I'm not going through all that .. but having looked at the query again there's more wrong so I'll just give you pointers.

1) The dates are wrong, you supplay a date as 12-25-2009 ( mm-dd-yyyy ) osCommerce requires ( yyyy-mm-dd ).

2) The usage of date functions looks wrong to me . . the query itself should look more like ..

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

3) The logic for the dates we are looking for here is ..

Larger or equal to 25th December 2009

And

Larger than or equal to 26th January 2009

Does that make sense given what your script is trying to achieve??

Edited January 26, 2010 by FWR Media

intensivex · January 26, 2010

sorry didnt notice the forums pagination ... let me digest this for a minute ... thanks FWR

Edited January 26, 2010 by intensivex

intensivex · January 26, 2010

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

Edited January 26, 2010 by intensivex

intensivex · January 26, 2010

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

MrPhil · February 25, 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!

How'd you hear about is report.

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation