Forums

hello

i am trying to interrogate the database to count the number of visitors between 2 specific dates each year 08 Dec YYYY until 07 Dec YYYY. each year it will change ( hence the variable )

ijust cant figure out the rest of the code.

<?php 
mysql_connect("localhost", "user", "password") or die(mysql_error());
mysql_select_db("database") or die(mysql_error());
$thisyr = DATE("08/12/Y");
$lastyr = strtotime ( '-366 day' , strtotime ( $thisyr ) ) ;
$lastyr = date ( 'd/m/Y' , $lastyr );
$result = mysql_query("SELECT * FROM table WHERE field BETWEEN '<?php date($thisyr) ?>' AND '<?php date($lastyr) ?>'");
$num_rows = mysql_num_rows($result); 
echo $num_rows . ' visitors'; 
?>
</p>
<?php
echo date($thisyr);
?>
</p>
<?php
echo date($lastyr);
?>

the field is a date input in varchar eg 25-12-2011. the end product does not give a result that compares dates.
the echoes both produce the right format,

where am i going wrong?

thanks ever so much.

Hi tgribble,

First off - it's a really bad idea to store dates in VARCHAR columns (and, yes, I know that ChronoForms encourages this). You have to convert the VARCHAR strings back into usable date formats somehow. Note: You can't reliably use the PHP strtotime unless you are certain that your dates are in mm-dd-yyyy format and it looks as though yours are dd-mm-yyyy.

This MySQL snippet recasts the VARCHAR dd-mm-yyyy as a yyyymmdd date that MySQL can work with. (Though in this case a straight string comparison without the CAST would probably work OK.

$db =& JFactory::getDBO();
$date1 = '20111208';
$date2 = '20121207';
$query = "
    SELECT *
        FROM `#__some_table`
        WHERE CAST( CONCAT(SUBSTRING(`field`, 7, 4),SUBSTRING(`field`, 4, 2), SUBSTRING(`field`, 1, 2)) AS DATE ) BETWEEN '{$date1}' AND '{date2}' ;
";
$db->setQuery($query);
$data = $db->loadObjectList();
?>

Not tested and may well need debugging!

Bob



Bob

hi again bob.

thanks for the reply.
firstly, to clarify the problem(s)

1. the variable (year) would have to be able to change - you have fixed it as a date. i am trying to have the same period each year eg 2010/12/08 until 2011/12/07 and then the next year it would be 2011/12/08 until 2012/12/07. hence the need for the $thisyr = DATE("08/12/Y"); - ( which i shall make yyyymmdd )

2. is the syntax of the $result = mysql_query("SELECT * FROM table WHERE field BETWEEN '<?php date($thisyr) ?>' AND '<?php date($lastyr) ?>'"); correct?

3. how come as it delivers the correct echo echo date($thisyr); , it does not do the query properly? is this the whole intent of the snippet you sent.

thanks

Hi trgribble,

Well yes, you'll need to modify the code to deal with the year variable. I think this will do it

<?php
$db =& JFactory::getDBO();
$year = date('Y');
$date1 = $year.'1208';
$date2 = ($year + 1).'1207';
. . .

Apart from not using the Joomla! DB Classes your query is so seriously broken that it's not worth fixing :-( Please check the PHP Manual for date() to start with.

Bob

hi bob.

just for completeness this was the final coding that worked.
it may not be pretty, but i am a novice and it works well.

thanks ever so much for the help.

$year = date('Y');
$lastyr = ($year - 1).'-12-08';
$thisyr = $year.'-12-07';
 

$query = "SELECT *,COUNT(name) FROM table WHERE Date(field) BETWEEN '$lastyr' AND '$thisyr'" ; 

$result = mysql_query($query) or die(mysql_error());

// Print out numbers
while($row = mysql_fetch_array($result)){
echo "There are ". $row['COUNT(name)'] ." visitors ";
}

Hi tgribble,

Looks fine - and if it works that's great.

@for other readers:

Hers's the Joomla! version of tgribble's code:

<?php
$year = date('Y');
$lastyr = ($year - 1).'-12-08';
$thisyr = $year.'-12-07';
$db =& JFactory::getDBO();
$query = "
  SELECT COUNT(name) 
    FROM `#__some_table` 
    WHERE DATE(`some_column`) BETWEEN '{$lastyr}' AND '{$thisyr}'
";
$db->setQuery($query);
$count = $db->loadResult();
// Print out numbers
echo "There are {$count} visitors ";
?>

This will work provided that your dates are in yyy-mm-dd format in the database. If they are saved in dd-mm-yyyy or mm-dd-yyyy format then you will need something like my earlier post.

Bob