Avatar of EasyToHelp
EasyToHelp
Flag for South Africa asked on

Query MySQL Date Range Via PHP

Hi,

I have a StartDate & EndDate column in the table products my DB
I have a Search Filter on my PHP website which has a Date picker called dpStartDate & dpEndDate.

I need to query my MySQL DB to select records that have
StartDate = or > than dpStartDate
AND
EndDate < or = dpEndDate

I need the PHP code to get the values from the Datepicker Textboxes, Parse the date to the correct format and then create the query based on the values.

Thanks so much!
PHPMySQL Server

Avatar of undefined
Last Comment
Ray Paseur

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Ray Paseur

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
EasyToHelp

ASKER
Does between act in the same way as
>= and <=
EG:
SELECT product_id FROM jos_vm_product_type_1
WHERE start_date >= '$DEPARTONORAFTER'
AND  end_date <= '$RETURNBY'

How would multiple record values be selected from the above query ?

How would i loop through these results in PHP ?
Basically running the below query nested in the loop returning only the DISTINC "parent_prodcut_id" ?

Loop through SelectedValues until EOF
SELECT parent_product_id
from jos_vm_product
WHERE product_id = $CHILDPRODUCTID
end loop

RETURN Distinct "parent_product_id"

EasyToHelp

ASKER
Here is a better explaination:

$myStartDate = date('c', strtotime($_REQUEST["dpStartDate"]));
$myEndDate = date('c', strtotime($_REQUEST["dpEndDate"]));

$query = "SELECT product_id FROM jos_vm_product_type_1 WHERE ( start_date BETWEEN '$myStartDate' AND '$myEndDate' ) AND ( end_date BETWEEN '$myStartDate' AND '$myEndDate' )";

Execute the $query

Loop through returned $query records

$query2 = "SELECT distinct parent_product_id from jos_vm_product WHERE product_id = '$query->productid'";

End Loop

RETURN parent_product_id ARRAY
SOLUTION
Ray Paseur

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Ray Paseur

Thanks for the points.  Since this question was written, there is a new edition of the book, available here:
http://www.sitepoint.com/books/phpmysql5/
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck