Personalised query with dates

meanster99

Well-known member
Hi all,

Does anyone know how if it's possible to have a URL query (similar to the ones talked about here: personalised query ) that will return all records with a date field (date_of_event) containing today's date? In other words I want to query all the current days events, everyday, whenever I click the link. Going one step further, I would like the URL query to return records for the next 5 days. Are either of these even possible?

I found this mySQL where clause: SELECT * FROM 'myTable' WHERE DATE('myDate') = DATE(NOW())
...but I'm not sure Dadabik would recognise the NOW() function (I also read I could use CURDATE() ).

Please can anyone help me get a where clause with URL encoding that would do what I'm after?|

Thanks,

Matt
 

DebbieS

DaDaBIK Guru
It is possible and pretty easy.

/path/to/dadabik/?table_name=yourtablename&where_clause=yourwhereclause&order=fieldnameforordering&order_type=ASC/DESC

The order parts are optional. In the url, you can then setup your where clause to match anything that is today's date or anything that is greater than or equal to today and less than or equal to 5 days from now. If you're not that familiar with PHP's date functions, look it up at www.php.net - great resource.

$today = date("Y-m-d H:i:s", mktime(0,0,0,date("m"),date("d"),date("Y"));
$fivedays = date("Y-m-d H:i:s", mktime(0,0,0,date("m"),date("d")+5,date("Y"));

How you get PHP to setup the dates depends on how your dates are written in your database. If you wanted to write a URL for today plus 5 days, the where_clause part would be (with the appropriate escaping, etc and you could surround it with urlencode):

/path/to/dadabik/?table_name=yourtablename&where_clause=datefield >= $today and datefield <= $fivedays

I've set up links using LIKE, NOT LIKE, >, <, etc based on what the users seem to need and request.
 

meanster99

Well-known member
Hi Debbie,

That's great - thanks for your help. Unfortunately, I can't quite get this to work. So far I have included the 2 variables you provided, in my header.php :

<?php
$today = date("Y-m-d H:i:s", mktime(0,0,0,date("m"),date("d"),date("Y")));
$fivedays = date("Y-m-d H:i:s", mktime(0,0,0,date("m"),date("d")+5,date("Y")));
<?

I have then created the following a tag where I want my link to go in header.php:

[pre]
5 Day View
[/pre]

However, I get the error: DBMS server said: Unknown column '$today' in 'where clause'

Full Error:
[08] Error: during query execution.
SELECT `customer_booking_form`.`Date_Of_Event`, `customer_booking_form`.`venue_town`, `customer_booking_form`.`hr`, `customer_booking_form`.`mn`, `customer_booking_form`.`ampm`, `customer_booking_form`.`ID`, `customer_booking_form`.`firstname`, `customer_booking_form`.`surname`, `customer_booking_form`.`telephone`, `customer_booking_form`.`email`, `customer_booking_form`.`service_required`, `customer_booking_form`.`occasion`, `customer_booking_form`.`recip_name`, `customer_booking_form`.`venue_name`, `customer_booking_form`.`venue_address`, `customer_booking_form`.`venue_address2`, `customer_booking_form`.`venue_county`, `customer_booking_form`.`venue_postcode`, `customer_booking_form`.`venue_telephone`, `customer_booking_form`.`costume_required`, `customer_booking_form`.`act_name`, `customer_booking_form`.`notes`, `customer_booking_form`.`terms_conditions`, `customer_booking_form`.`total_price`, `customer_booking_form`.`booking_fee`, `customer_booking_form`.`balance`, `customer_booking_form`.`payment_type`, `customer_booking_form`.`voucher_code`, `customer_booking_form`.`status`, `customer_booking_form`.`_sfm_form_submision_date_`, `customer_booking_form`.`_sfm_visitor_ip_`, `customer_booking_form`.`user_ID`, `customer_booking_form`.`contact_type`, `customer_booking_form`.`details_sent`, `artiste_contacts__1`.`Stage_Name` AS `artiste_contacts__Stage_Name__1`, `customer_booking_form`.`act_gender`, `customer_booking_form`.`completed` FROM `customer_booking_form` LEFT JOIN `artiste_contacts` AS `artiste_contacts__1` ON `customer_booking_form`.`act_sent` = `artiste_contacts__1`.`act_ID` WHERE `customer_booking_form`.`Date_Of_Event`>=$today AND `customer_booking_form`.`Date_Of_Event`<=$fivedays
DBMS server said: Unknown column '$today' in 'where clause'


I think I'm probably missing some escaping or urlencode but I'm not sure what to do. Where am I going wrong?

Also, is my urlencoding for the >= and <= OK??? I'm still a hopeless noob...
 

DebbieS

DaDaBIK Guru
You need to surround the PHP variables with PHP start/end braces:

Put the date stuff and where clause into a variable:
[pre]
$today = date("Y-m-d H:i:s", mktime(0,0,0,date("m" ),date("d" ),date("Y" )));
$fivedays = date("Y-m-d H:i:s", mktime(0,0,0,date("m" ),date("d" )+5,date("Y" )));
$fivewhere = urlencode($quote.'customer_booking_form'.$quote.'.'.$quote.'Date_Of_Event'.$quote.' >= '.$today.'+AND+'.$quote.'customer_booking_form'.$quote.'.'.$quote.'Date_Of_Event'.$quote.' <= '.$fivedays);
[/pre]

Then echo the full URL to the page:
[pre]
echo '5 Day View';
[/pre]

Which will create the link in your page:
[pre]
5 Day View
[/pre]

All you need to do is confirm the EXACT format of the date entries in your database (eg: using 24 hour clock, 12 hour clock with AM/PM, order of year, month, day, etc). If the date format does not match, you will get no results. You may be able to get by without the hours/min/sec part - you'll have to play around with it in your own installation to see.
 

meanster99

Well-known member
Hi Debbie,

As always, you come to the rescue! However, this still isn't working for me. I now get the following error:

[08] Error: during query execution.
SELECT `customer_booking_form`.`Date_Of_Event`, `customer_booking_form`.`venue_town`, `customer_booking_form`.`hr`, `customer_booking_form`.`mn`, `customer_booking_form`.`ampm`, `customer_booking_form`.`ID`, `customer_booking_form`.`firstname`, `customer_booking_form`.`surname`, `customer_booking_form`.`telephone`, `customer_booking_form`.`email`, `customer_booking_form`.`service_required`, `customer_booking_form`.`occasion`, `customer_booking_form`.`recip_name`, `customer_booking_form`.`venue_name`, `customer_booking_form`.`venue_address`, `customer_booking_form`.`venue_address2`, `customer_booking_form`.`venue_county`, `customer_booking_form`.`venue_postcode`, `customer_booking_form`.`venue_telephone`, `customer_booking_form`.`costume_required`, `customer_booking_form`.`act_name`, `customer_booking_form`.`notes`, `customer_booking_form`.`terms_conditions`, `customer_booking_form`.`total_price`, `customer_booking_form`.`booking_fee`, `customer_booking_form`.`balance`, `customer_booking_form`.`payment_type`, `customer_booking_form`.`voucher_code`, `customer_booking_form`.`status`, `customer_booking_form`.`_sfm_form_submision_date_`, `customer_booking_form`.`_sfm_visitor_ip_`, `customer_booking_form`.`user_ID`, `customer_booking_form`.`contact_type`, `customer_booking_form`.`details_sent`, `artiste_contacts__1`.`Stage_Name` AS `artiste_contacts__Stage_Name__1`, `customer_booking_form`.`act_gender`, `customer_booking_form`.`completed` FROM `customer_booking_form` LEFT JOIN `artiste_contacts` AS `artiste_contacts__1` ON `customer_booking_form`.`act_sent` = `artiste_contacts__1`.`act_ID` WHERE `customer_booking_form`.`Date_Of_Event` >= 2012-02-12+AND+`customer_booking_form`.`Date_Of_Event` <=
DBMS server said: 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+`customer_booking_form`.`Date_Of_Event` <=' at line 1

My code is (I've changed the 5 days to 7 days now, and removed the time part of the variables):
[pre]
<?php //date variables for use in date query in nav menu
$today = date("Y-m-d", mktime(0,0,0,date("m"),date("d"),date("Y")));
$sevendays = date("Y-m-d", mktime(0,0,0,date("m"),date("d")+7,date("Y")));
$sevenwhere = urlencode($quote.'customer_booking_form'.$quote.'.'.$quote.'Date_Of_Event'.$quote.' >= '.$today.'+AND+'.$quote.'customer_booking_form'.$quote.'.'.$quote.'Date_Of_Event'.$quote.' <= '.$sevendays);
?>
[/pre]

and my link is:

[pre]
<li><?php echo '7 Day View';?></li>
[/pre]

Also, as far as the date formats being exactly the same, Date_Of_Event is a date field only (set up in phpmyadmin as DATE) but is set to literal_english in Dadabik. Not sure what else I need to do?

Don't know why this is being so difficult with me! Any ideas?
 

DebbieS

DaDaBIK Guru
It's not being difficult with you - it is just a matter of trial and error to find the sweet spot that works as a URL.

You could try surrounding the dates with single quotes and removing the + symbols from the $sevenwhere variable line.
If you have phpMyAdmin (great, fantastic back-end database management tool for MySQL), you can then build the query there and then take that query and build your URL where_clause. I use phpMyAdmin all the time to do this.

This query worked in one of my installations:
SELECT * FROM `master` WHERE `lastupdate` >= '2012-01-01' and `lastupdate` <= '2012-02-01'

You'll have to play around with it to find exactly what works.
 

meanster99

Well-known member
Thanks for the pointers Debbie. I played around with it quite a bit before posting previously and did try removing the + symbols from the AND part of the $sevenwhere variable. The URL being generated was looking like it should work but it took me a while to figure out it was missing inverted commas around the output of the variables $today and $sevendays. I couldn't figure out how to add them to the where clause variable, so I just removed this variable and added the 2 date variables enclosed by %22 (urlencode for inverted commas) to the echoed a-tag below:

[pre]
<?php //date variables for use in date query in nav menu
$today = date("Y-m-d", mktime(0,0,0,date("m"),date("d"),date("Y")));
$sevendays = date("Y-m-d", mktime(0,0,0,date("m"),date("d")+7,date("Y")));
?>

<?php echo '7 Day View';?>
[/pre]

Thanks for all your time and help again Debbie. I will now have a look at your reply to my other post re the quick search and see if I can figure it out myself before troubling you again!!
 
Top