Entries from last 7 days

[adzie]

Hello all 

 

I'm hitting a brick wall here so I'm hoping you can help.

 

I can get my script to count all the entries and give the result, but I have been unable thus far to get it to display the entries from only the last 7 days.  The date format in the db is  as an example

 

20060422081640

 

I'd appreciate any help.

 

many thanks

 

$query7 = "SELECT * FROM records WHERE member =\"".$member."\"";
$result7 = mysql_query($query7);
$number7 = mysql_numrows($result7); 

[akitchin]

SELECT stuff FROM table WHERE datefield >= DATE_SUB(NOW(), INTERVAL 7 DAY)

 

MySQL functions are incredibly helpful for date/time conditions.

[discomatt]

It's worth it to convert all your data over to a datetime field

 

ALTER TABLE `records` ADD `new_date` DATETIME NULL ;

 

<?php

mysql_connect( 'localhost', 'root', '' );
mysql_select_db( 'test' );

$q = "SELECT `member`, `date` FROM `records`";
$r = mysql_query( $q );
$values = array();
while ( $d = mysql_fetch_assoc($r) ) {
$a = $d['date'];
$newDate = substr($a,0,4).'-'.substr($a,4,2).'-'.substr($a,6,2).' '.substr($a,8,2).':'.substr($a,10,2).':'.substr($a,12,2);
$values[] = "`new_date` = '$newDate' WHERE `member` = '{$d['member']}'";
}

foreach( $values as $value ) {
$q = "UPDATE `records` SET $value";
mysql_query( $q );
}

?>

 

 ALTER TABLE `records` DROP `date`  

 ALTER TABLE `records` CHANGE `new_date` `date` DATETIME NOT NULL 

[discomatt]

You can then use akitchin's query.

[akitchin]

It's worth it to convert all your data over to a datetime field

 

ALTER TABLE `records` ADD `new_date` DATETIME NULL ;

 

<?php

mysql_connect( 'localhost', 'root', '' );
mysql_select_db( 'test' );

$q = "SELECT `member`, `date` FROM `records`";
$r = mysql_query( $q );
$values = array();
while ( $d = mysql_fetch_assoc($r) ) {
$a = $d['date'];
$newDate = substr($a,0,4).'-'.substr($a,4,2).'-'.substr($a,6,2).' '.substr($a,8,2).':'.substr($a,10,2).':'.substr($a,12,2);
$values[] = "`new_date` = '$newDate' WHERE `member` = '{$d['member']}'";
}

foreach( $values as $value ) {
$q = "UPDATE `records` SET $value";
mysql_query( $q );
}

?>

 

 ALTER TABLE `records` DROP `date`  

 ALTER TABLE `records` CHANGE `new_date` `date` DATETIME NOT NULL 

 

their current fieldtype is most likely a TIMESTAMP, which can be used equivalently as a DATETIME field; they need not convert it.  you'll notice that timestamp they posted is pretty much a DATETIME entry, minus the dashes, space and colons.

[adzie]

thanks for the support guys,

 

Unfortunatly changing the DB isnt an option as such, too many scripts already in place around that date format

 

I tried the first solution  but I think its pulling all results from the last 7 months as opposed to 7 days,

 

any thoughts?

 

Thanks

[redarrow]

Im lost did this get solved............

 

 

is the database setting a INT and is the date set as a unix timestamp........

 

 

[adzie]

hello

 

Sorry its a varchar field, changing to INT completely messed up the DB entries, and theres thousands!

[DarkWater]

Whoa, it's a VARCHAR?  Then you cannot do this easily.  You REALLY need DATETIME or another MySQL date type column to do this.

[Mchl]

DATETIME and TIMESTAMP have been introduced for a reason... The sooner you convert your database to proper column types, the less problem you'll have later.

[adzie]

thought it might be a problem to do

 

The date field is taken from a unix document and just dumped into the db

 

The script extracts the date ie 20080916192159 from the document and enters it into the DB

 

[Mchl]

20060422081640 <- date like this can be dumped directly into mysql DATETIME or TIMESTAMP field.

 

To convert all rows you'll need three or four queries

 

1. ALTER TABLE

add new DATETIME column

 

2. UPDATE

move data from old column to new column

 

3. ALTER TABLE

drop VARCHAR column

 

There will be more problems with modifying all queries to fetch dates in new format.

 

 

[ngreenwood6]

You can do it with an if statement with the time:

 

//set the time variable
$time = time();

//get the data from the database
$query = "SELECT * FROM records WHERE member =\"".$member."\"";

//get the results
$result = mysql_query($query);

//count the rows
$count = mysql_num_rows($result);

//if an entry exists put it into an array
if($count = 1)
{
$row = mysql_fetch_array($result);
}

//if the time is less than 1 week show all the entries
if($time < ($row['time'] + 604800))
{
display the results
}
else
{
dont display the results
}

 

The reason I am using 604800 is 60 seconds in a min, 60 minutes in an hour, 24 hours in a day, 7 days a week. 60*60*24*7 is how I got that. Hope that helps.

[akitchin]

You can do it with an if statement with the time:

 

//set the time variable
$time = time();

//get the data from the database
$query = "SELECT * FROM records WHERE member =\"".$member."\"";

//get the results
$result = mysql_query($query);

//count the rows
$count = mysql_num_rows($result);

//if an entry exists put it into an array
if($count = 1)
{
$row = mysql_fetch_array($result);
}

//if the time is less than 1 week show all the entries
if($time < ($row['time'] + 604800))
{
display the results
}
else
{
dont display the results
}

 

The reason I am using 604800 is 60 seconds in a min, 60 minutes in an hour, 24 hours in a day, 7 days a week. 60*60*24*7 is how I got that. Hope that helps.

 

this is, frankly, an atrocious way of accomplishing the task. it will pull all records, a waste of resources when you only need those from the last 7 days. on top of that, the time format isn't in seconds, so using a UNIX timestamp for comparison will yield unanticipated results.

[adzie]

20060422081640 <- date like this can be dumped directly into mysql DATETIME or TIMESTAMP field.

 

To convert all rows you'll need three or four queries

 

1. ALTER TABLE

add new DATETIME column

 

2. UPDATE

move data from old column to new column

 

3. ALTER TABLE

drop VARCHAR column

 

There will be more problems with modifying all queries to fetch dates in new format.

 

 

 

Thanks Guys the support and guidance is as good as always

 

Just to throw a spanner in the work the date example I beleive is unix/linux the actual server is a windows server

[akitchin]

the timestamp doesn't look like a UNIX timestamp, though.  i could be wrong by coincidence, but it just looks like a normal timestamp in the format:

 

YYYYMMDDHHMMSS

 

which as Mchl suggested, can be manipulated quite easily into a DATETIME column.