homepage Welcome to WebmasterWorld Guest from 54.205.144.54
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
dates & strtotime problem
Pollos




msg:4091448
 3:25 pm on Mar 4, 2010 (gmt 0)

This problem has me stumped I'm trying to select entries in the MYSQL database between two dates.

Database tables
ID - date -------- strtotime ---- cost
01 - 01-01-2010 - 1262300400 - 10

I'm working out the 'strtotime' using the snippit below. I fill the 'strtotime' table with the output, is this giving me the correct timestrings?
<?php

$seconds_in_day = (60*60*24);
$timestring = (strtotime('01-01-2010'));
for ($i = 1; $i <= 365; $i++) {
//echo 'Day ' . $i . ' = ';
//echo date('d-m-Y', $timestring).' ';
echo ($timestring . '<br />');
$timestring = ($timestring + $seconds_in_day) ;
}
?>


I have a form for filling the 'Cost' table between selected dates, E.G between 01-01-2010 to 01-04-2010
The form send the dates in 'dd-mm-yyyy' format.

I convert to strtotime using:
<?php
$formdate_start = ($_POST['start_date'])
$formdate_end = ($_POST['end_date'])

$date_string_start = (strtotime($formdate_start));
$date_string_end = (strtotime($formdate_end));
?>

I select the ID's of the two dates and use these in my sql_update
<?php
$query_Update_prices = "SELECT id FROM `database_name` WHERE date_strtotime BETWEEN $formdate_start AND $formdate_end";
?>
Everthing looks OK until I select dates pairs which include 29-03-2010


$formdate_start = 01-01-2010, $formdate_end = 28-03-2010, selects 1-87 the correct number
$formdate_start = 01-01-2010, $formdate_end = 29-03-2010, selects 1-87 the incorrect number it should be 1-88
$formdate_start = 01-01-2010, $formdate_end = 30-03-2010, selects 1-88 the incorrect number it should be 1-89

Selecting date pairs which don't include 29-03-2010 work fine.

Where am I going wrong? Any help would be much appreciated.

 

Matthew1980




msg:4091463
 3:46 pm on Mar 4, 2010 (gmt 0)

Hi there pollos,

Just to point out:-

<?php
$formdate_start = mysql_real_escape_string(strip_tags($_POST['start_date']));
$formdate_end = mysql_real_escape_string(strip_tags($_POST['end_date']));


Was missing the semi-colon on the ends, and to keep the query safe, add the mysql_real_escape_string(); function & strip_tags(); to stop any malicous code injection, as the data comes from the users form input...


EDIT:

Just noticed that because you are using double quotes you will need to concatonate the vars too, I could be wrong, but thats how I do it, I'm not saying that it will fix the error either.. ;-p

$query_Update_prices = "SELECT id FROM `database_name` WHERE `date_strtotime` BETWEEN ".$formdate_start." AND ".$formdate_end."";


Cheers,

MRb

Readie




msg:4091585
 6:02 pm on Mar 4, 2010 (gmt 0)

It's single quotes where variables need to be concatonated :)

With regards to the problem, have you tried saying something like:

$query_Update_prices = 'SELECT id FROM `database_name` WHERE date_strtotime>="' . $formdate_start . '" AND WHERE date_strtotime<="' . $formdate_end . '"';
Pollos




msg:4091628
 7:10 pm on Mar 4, 2010 (gmt 0)

Cheers for the reply's. I've tried every combination I could think of for the suggested Query's, but they all give a parse error or baulk at the 'date_strtotime <='part.

I can't understand why it works perfectly for any combination of dates except when they include 29-03-2010

In the first draft I used two sql_selects one for the first date and one for the second. Then I had a for loop adding (60*60*24) to the $formdate_start then the sql_update until it = $formdate_end

I may have to revert to that.

Thanx again.

Readie




msg:4091642
 7:26 pm on Mar 4, 2010 (gmt 0)

Have you checked your table to make sure there is an entry next to 29-03-2010? or that the date for that column is in the correct format?

Matthew1980




msg:4091675
 8:34 pm on Mar 4, 2010 (gmt 0)

Hi there Pollos,

Before the query gets sent, have you echoed the $var to screen to actually see how the query is populated, then if it is as you think, manually putting that into phpmyadmin to see how that reacts?

Also have you deleted any entries from the database, because if you have altered the ID column, and if it auto increments this may also produce unexpected results.

If all else fails try entering the query directly into phpmyadmin via the sql tab (making sure that you have selected the relevant table ;-p) and seeing what results that gives you... You have more than likely done this, but I just though I should point out places to check.

Hope that gives you a few Ideas anyway,

Cheers,
MRb

Pollos




msg:4091678
 8:36 pm on Mar 4, 2010 (gmt 0)

After adding a few 'echos' it seems that the problem is with the way I originally got the time stamp:
$seconds_in_day = (60*60*24);
$timestring = (strtotime('01-01-2010'));
for ($i = 1; $i <= 365; $i++) {
//echo 'Day ' . $i . ' = ';
//echo date('d-m-Y', $timestring).' ';
echo ($timestring . '<br />');
$timestring = ($timestring + $seconds_in_day) ;
}


After the 28-03-2010 the above script gives a different result to the timestamp given to the sql_update, which is a direct conversion e.g.

strtotime(date('29-03-2010'))

So can anyone suggest a script for working out the timestamp for a complete year, preferably is a way that I can add a sql_insert statement to populate the database.

Thanx in advance

Pollos




msg:4091999
 10:14 am on Mar 5, 2010 (gmt 0)

Thanks for the replys. I believe I've found the problem

I used the following to work out the timstamp for teh data base.

$date = "31-12-2009";
for ($i = 1; $i <= 365; $i++) {
$newdate = strtotime ( '+1 day' , strtotime ( $date ) ) ;
$newdate = date ( 'd-m-Y' , $newdate );
//echo $newdate,' ';
$timestamp = strtotime($newdate);
//echo $timestamp,'<br>';
echo "UPDATE poppycars_pricecalc SET date_strtotime ='".$timestamp."' WHERE id ='".$i."';<br>";
$date = $newdate ;
}

Matthew1980




msg:4092012
 11:45 am on Mar 5, 2010 (gmt 0)

Excellent news pollos,

GLad as you got there in the end, and good luck with the rest of your project.

Cheers,
MRb

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved