DaDaBIK
This forum is devoted to the discussion about the software DaDaBIK database front-end
the PHP Database application generator for MySQL, PostgreSQL and SQLite; also available for Wordpress.

Welcome! Log In Create A New Profile

Advanced

Personalised query with dates

Posted by meanster99 
Personalised query with dates
February 11, 2012 01:27AM
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
Re: Personalised query with dates
February 11, 2012 02:03AM
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"winking smiley,date("d"winking smiley,date("Y"winking smiley);
$fivedays = date("Y-m-d H:i:s", mktime(0,0,0,date("m"winking smiley,date("d"winking smiley+5,date("Y"winking smiley);

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.

_______________________________
..:: Debbie |\.-./| DaDaBIK Support ::..
Re: Personalised query with dates
February 11, 2012 03:03AM
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"winking smiley,date("d"winking smiley,date("Y"winking smiley));
$fivedays = date("Y-m-d H:i:s", mktime(0,0,0,date("m"winking smiley,date("d"winking smiley+5,date("Y"winking smiley));
<?

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

<a href="http://www.mydomain.com/database/index.php?&table_name=customer_booking_form&where_clause=%60customer_booking_form%60.%60Date_Of_Event%60%3E%3D$today+AND+%60customer_booking_form%60.%60Date_Of_Event%60%3C%3D$fivedays">5 Day View</a>

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...
Re: Personalised query with dates
February 11, 2012 05:24PM
You need to surround the PHP variables with PHP start/end braces:

Put the date stuff and where clause into a variable:
$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);

Then echo the full URL to the page:
echo '<a href="'.$dadabik_main_file.'?&table_name=customer_booking_form&where_clause='.$fivewhere.'">5 Day View</a>';

Which will create the link in your page:
<a href="index.php?&table_name=customer_booking_form&where_clause=%60customer_booking_form%60.%60Date_Of_Event%60+%3E%3D+2012-02-11+00%3A00%3A00%2BAND%2B%60customer_booking_form%60.%60Date_Of_Event%60+%3C%3D+2012-02-16+00%3A00%3A00">5 Day View</a>

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.

_______________________________
..:: Debbie |\.-./| DaDaBIK Support ::..
Re: Personalised query with dates
February 12, 2012 02:57PM
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):
<?php //date variables for use in date query in nav menu
$today = date("Y-m-d", mktime(0,0,0,date("m"winking smiley,date("d"winking smiley,date("Y"winking smiley));
$sevendays = date("Y-m-d", mktime(0,0,0,date("m"winking smiley,date("d"winking smiley+7,date("Y"winking smiley)); 
$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);
?>

and my link is:

<li><?php echo '<a href="'.$dadabik_main_file.'?&table_name=customer_booking_form&where_clause='.$sevenwhere.'">7 Day View</a>';?></li>

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?
Re: Personalised query with dates
February 12, 2012 07:06PM
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.

_______________________________
..:: Debbie |\.-./| DaDaBIK Support ::..
Re: Personalised query with dates
February 12, 2012 10:19PM
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:

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

<?php echo '<a href="http://www.dannyvalentino.co.uk/database/index.php?&table_name=customer_booking_form&where_clause=%60customer_booking_form%60.%60Date_Of_Event%60+%3E%3D+%22'.$today.'%22+AND+%60customer_booking_form%60.%60Date_Of_Event%60+%3C%3D+%22'.$sevendays.'%22">7 Day View</a>';?>

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!!
Sorry, only registered users may post in this forum.

Click here to login