Filter records between 2 dates

[liamloveslearning]

Hi everyone, I have a list containing all records in a table. Upon inserting these records a the date is also inserted into the record. I need to create a filter where I can pick 2 dates and filter the table showing only those records added within the 2 dates.

Ive no idea how or how to start working on this so could anybody provide me ideas?

[Daniel0]

SELECT something FROM stuff WHERE created_at BETWEEN '2010-02-05' AND '2010-03-13';

[liamloveslearning]

Thanks Daniel, The filter needs to be defined by the user, so the dates wont be static they will be varied, Is this still possible?

[Daniel0]

Of course...

 

$stmt = $db->prepare('SELECT something FROM stuff WHERE created_at BETWEEN ? AND ?');
$stmt->execute(array($_POST['start'], $_POST['end']));

$stuff = $stmt->fetchAll();

 

(Assuming a PDO interface and that the values have already been validated)

[liamloveslearning]

Could you explain it please Daniel, Im unsure what parts Id need to swop out with my code etc? Sorry to be so naive

[liamloveslearning]

Is it not possible to variables within' a query such as SELECT * FROM worksorders WHERE worksorders.intradateadd BETWEEN '<?php $POST_['date1'] ?>' AND '<?php $POST_['date2'] ?>'

 

[cags]

As it's unlikely you are using a PDO interface (since you don't have a clue what Daniel is talking about), your code would look something more like...

 

mysql_query("SELECT * FROM worksorders WHERE worksorders.intradateadd BETWEEN '{$_POST['date1']}' AND '{$_POST['date2']}'");

Though as Daniel said you would want to validate/sanitize the $_POST variables first.

[liamloveslearning]

haha I did have to google it    brilliant, thanks cags

[liamloveslearning]

Okay I now have my dates worked, I just need to put them into my query, my date variables are

<?php 
$startdate = date('Y-m-d', strtotime ("last Monday"));  
$enddate = date('Y-m-d', strtotime ("next Friday"));

echo $startdate;
echo $enddate;
?>

 

and my query is

SELECT worksorders.intrajobno, worksorders.intratype, worksorders.intrastatus, worksorders.intracustomer, worksorders.intradescription, worksorders.intraorder, worksorders.intraartapproval, worksorders.intraestimatedinstall, worksorders.intraprio, worksorders.intrajobvalue, worksorders.intraterms, worksorders.intrajobref, worksorders.id, worksorders.intradelete FROM worksorders WHERE {$NXTFilter_rsworksorders1} AND worksorders.intradelete != 0 ORDER BY {$NXTSort_rsworksorders1}";

 

Ive tried...

SELECT worksorders.intrajobno, worksorders.intratype, worksorders.intrastatus, worksorders.intracustomer, worksorders.intradescription, worksorders.intraorder, worksorders.intraartapproval, worksorders.intraestimatedinstall, worksorders.intraprio, worksorders.intrajobvalue, worksorders.intraterms, worksorders.intrajobref, worksorders.id, worksorders.intradelete, worksorders.intradateadd FROM worksorders WHERE {$NXTFilter_rsworksorders1} AND worksorders.intradelete != 1 AND worksorders.intradateadd BETWEEN '{$startdate}' AND '{$enddate'); ORDER BY {$NXTSort_rsworksorders1}"

To no avail, Can anybody see why? are my variables in the query incorrect?

[liamloveslearning]

Ive also just tried

$query_rsworksorders1 = "SELECT worksorders.intrajobno, worksorders.intratype, worksorders.intrastatus, worksorders.intracustomer, worksorders.intradescription, worksorders.intraorder, worksorders.intraartapproval, worksorders.intraestimatedinstall, worksorders.intraprio, worksorders.intrajobvalue, worksorders.intraterms, worksorders.intrajobref, worksorders.id, worksorders.intradelete FROM worksorders WHERE {$NXTFilter_rsworksorders1} AND worksorders.intradelete != 1 AND worksorders.intradateadd BETWEEN "$startdate" AND "$enddate" ORDER BY {$NXTSort_rsworksorders1}";

I read variables in queries need double quotes, I tried this, again without success

[cags]

Even a basic syntax highlighting software package should point out the various issues with that. Variables will only be evaluated when they are contained within a string that is delimited by double quotes this doesn't mean the variables themselves should be delimited by them. Any value which is string based, which is being passed to MySQL need to be delimited by quotes (single or double, but single makes most sense because the string object in PHP is itself delimited by double quotes, so if you use double quotes you'll have to escape them). When placing variables in strings I find it useful to delimit them with curly braces as it helps to differentiate the difference between the variable name and part of the string eg

 

$var = "like;
echo "{$var}able";
echo "$varable"; 

[liamloveslearning]

So in my query I should have my variables as {'$myvar'} ? Thanks for being so helpful

[cags]

No, the value needs to be delimited by quotes and the variable by curly brackets (which are entirely optional in most circumstances). Switch them over.

[liamloveslearning]

Sorry cags im new to php and now im completely lost, by the value needs to be delimited do you mean..

<?php 
$startdate = 'date('Y-m-d', strtotime ("last Monday"));' 
$enddate = 'date('Y-m-d', strtotime ("next Friday"));'
?>

 

I know this is wrong as the highlighting suggests so but im completely dumbfounded

[Daniel0]

That's funny. Your profile suggests that you started with PHP at least two years ago

 

You seem to be struggling a lot with syntax. I would suggest that you check out the Langauge Reference in the manual before you try to do anything further.

[liamloveslearning]

haha i did join then, I didnt touch it for a long time and i started using it again last month. Its just so confusing

[liamloveslearning]

SORTED! Thanks for being so patient