passing date via form for mysql date type query

[scott]

Hello,

I'm new to mysql and php, I've been playing around with them for around six months. But it's on and off in my spare time. (that's the heads up) :)

I've created a database for tracking our paper inventory. Basically when an individual takes paper, or envelopes the quantity is entered into the database, along with some other items. This all works great.

I also have two fields that use the "Date" and "Time" types for holding the date and time of the initial transaction.

I've created a seperate php script that we will use for our "end of month" reports. The script goes through and adds up the cost of each "purchase" between a specified time frame (1 may thru 31 may for example).

This script works for me as long as I perform my query with my condition formated as such:

WHERE 1 AND `costcode` = '.$costcode_num.' AND `date` >= \'2003-07-13\' AND `date` <= \'2003-07-14\'

(I left out the begining and ending of the query)

But I need to be able to pass the start date and end date (my search parameter) via a form using the POST action.

When I do this it results in my date being a string...which does not produce any results for me.

I've been testing within my script by setting a variable to some sample dates.

$startdate = '2003-07-13';
$enddate = '2003-07-14';

I've tried a trim() on them, and addslashes(), etc.

I can't seem to get the "string" to give me any results with my query.

I've tried formatting my query as such:

`date` >= '.$startdate.'

I've also tried the following:

`date` >= \"$startdate\"

I just can't seem to figure out how to get this working. I'm guessing it's because I am using the "Date" type for my field.

Is there a way to get this to work...or do I have to change my field type?

I really need to be able to use >= and <= to limit my search queries to be within the dates specified.

I'd appreciate the help if someone could point me in the correct direction for solving my problem.

Thanks,

Scott

[raf]

Welcome here.

The correct syntawx would be something like

$sql_select = "select * from tabel where date >='".$_POST
['startdate']."' AND date <='".$_POST['enddate']."'";

Where $_POST['startdate'] is the formelement -> so formfield is named 'startdate' . Idem for enddate.
So you maybe need to change the names in the query + include the other conditions etc.

So its "string" . $variable . "string2" . ...

In your statement i see
where 1 and ...
Is that a typo ?

By inserting
echo $sql_select ;
die();

you will see the sql-statement that is actually executed (with datavariabels in it)

[scott]

Thank you for the reply. I think I should have saved myself a few hours of searching and just posted here in the first place. :-)

I had to change a few things, as you can see from my code below. I was passing my dates in more than two variables, my form had drop down lists for the Month, Day, Year...for both a start and end on the search criteria.

$sql_query = "SELECT * FROM inventory WHERE costcode ='".$costcode_num."' AND
date >='".$_POST['SYear']."-".$_POST['SMonth']."-".$_POST['SDay']."' AND
date <='".$_POST['EYear']."-".$_POST['EMonth']."-".$_POST['EDay']."'";

I also took your suggestion in cleaning things up a bit.

I got the "where 1 and" from some code in a book, I just used it and modified after the "and". (my mistake)

Yes, the above code is _much_ cleaner than what it used to be.

Plus...It works the way I wanted it to! After I cleaned it up and changed it to use the "string".$variable."string2" example you gave me.

At first it wouldn't work because I still had a '.$costcode.' in there, was giving me a printable result of: ".123456." instead of just "123456"

Thank you for the help!

My problem has been solved. :-)


Scott

[raf]

Glad i could help.

Last note on the "... where 1 and ...".
I sometimes use "... where 1=1 and ..." because it is quite handy when you dynamically build your sql string. Because the 1=1 is alway a true condition, you don't need to make sure that the first record you process doesn't have the "and" at the start, and you can do somethin like:
$sql="select * from table where 1=1";
while(...) {
$sql=$sql . " and ID=" . $ID
}