使用过去2个月（日期）

I'm trying to delete the past 2 months in my database.

I'm saving things in my database like this format date("n-Y"); zo the output would be 8-2013

Now i'm trying to delete the past 2 months like this:

Code:

echo "<h2>Opruimen.</h2>";

$AFGELOPENMAAND = date("n-Y",strtotime("-1 Months"));
$AFGELOPENMAAND2 = date("n-Y",strtotime("-2 Months"));

$result = $pdo->prepare("SELECT * FROM photos WHERE geupload = $AFGELOPENMAAND OR geupload = $AFGELOPENMAAND2");
$result->execute();
$hoeveel = $result->rowCount();

if($hoeveel != 0){
    foreach($result as $row){
        unlink($row["thumb"]);
        unlink($row["location"]);

        $resultDEL = $pdo->prepare('DELETE FROM photos WHERE id = :id');
        $resultDEL->execute(array(':id' => $row["id"]));

        echo 'De vorige 2 maanden zijn verwijdert.';
        echo '<meta http-equiv="refresh" content="2; URL=admin.php">';  
    }
}else{
    echo 'Deze actie is al uitgevoerd of het is niet nodig!!';
}

But i can see with my else, if he found something or not. And it doesn't find rows in my database, but there are some with the past 2 months in it.

Am i doing something wrong with my query ?

Grz

Replace this:

$result = $pdo->prepare("SELECT * FROM photos WHERE geupload = $AFGELOPENMAAND OR geupload = $AFGELOPENMAAND2");
$result->execute();

with this

$result = $pdo->prepare("SELECT * FROM photos WHERE geupload = '".$AFGELOPENMAAND."' OR  geupload = '".$AFGELOPENMAAND2."'");
$result->execute();

You have some issues...

Prepared statements don't have to be prepared for each iteration when the bound data changes:

if($hoeveel != 0){
    $resultDEL = $pdo->prepare('DELETE FROM photos WHERE id = :id'); //put it here
    foreach($result as $row){
        unlink($row["thumb"]);
        unlink($row["location"]);

        $resultDEL->execute(array(':id' => $row["id"]));

        echo 'De vorige 2 maanden zijn verwijdert.';
        echo '<meta http-equiv="refresh" content="2; URL=admin.php">';  
    }
}else{
   echo 'Deze actie is al uitgevoerd of het is niet nodig!!';
}

However You could also delete them directly:

$result = $pdo->prepare("DELETE FROM photos WHERE geupload = $AFGELOPENMAAND OR geupload = $AFGELOPENMAAND2");
$result->execute();

Rule of thumb: When you read something from DB, and have to process each line so that there is a resulting DB query, 99 out of 100 cases you could have done it entirely in the DB, saving a lot of bandwidth and time.

EDIT AdrianBR found the problem... Don't store dates as strings. DB systems are good at handling their own defined datatypes, use them.

you have to quote the string you search for in mysql, currently you are searching for month_value-year_value, a negative int

My advice, change your "geupload" field to a DATETIME (or DATE) field instead of VARCHAR, it's much much easier to work with and the database can handle this field like a real date instead of a string (like mentioned before).

You can remove the rows, older then two months like this then:

DELETE FROM photos WHERE TIMESTAMPDIFF(MONTH, geupload, NOW()) > 2

You can easily insert or update the current time into this field like this:

INSERT INTO photos (.., .., geupload) VALUES (.., .., NOW())

UPDATE photos SET geupload = NOW()