I have not visited PHP/MYSQLI for some time now, and, need some guidance on the following problem:

I'm trying to retrive all the data from the given week and just in that week. My table looks like the following:

id int(11)
type varchar(255)
week datetime

And as a test data I have the following:

1 Sport 2013-05-28 00:00:00

This is within this week, and, therefore should be returned, however, it does not return. Again, I am just getting back into PHP/MYSQLI so there may be a problem with my query, I just cannot seem to figure it out. Here is it below:

<?php

$mysqli = new mysqli("localhost", "root", "root", "Site");
if($mysqli->connect_errorno) {

    die("Cannot connect to the database");

}

$getWeeks = "SELECT * FROM FlipCards WHERE week => currdate - INTERVAL DAYOFWEEK(currdate())+6 DAY
            AND date < currdate() - INTERVAL DAYOFWEEK(currdate())-1 DAY";

$result = mysqli_query($getWeeks);

if(mysqli_affected_rows($result, $mysqli) >= 1)
{
    echo 'There has been a row matched!'
}
?>

Could anyone please suggest where I am going wrong? I believe it has something to do with the query.

Member Avatar for diafol
week >= ...

Hey,

I've changed the type to just "date" and tried the following:

    $getWeeks = "SELECT * FROM FlipCards WHERE week >= DATE_SUB(NOW(), INTERVAL 1 WEEK)";

But still get no results shown, even though the date is actually: '2013-06-28' "28" is in this week. Argh, any suggestions?

In essence, isn't your query trying to get rows within the week starting from today, and not the week prior?

E: nevermind, it works for me >.< BTW in your first post it said May, and your most recent one is June. Which is it?

Member Avatar for diafol
SELECT DATE_SUB(NOW(), INTERVAL 1 WEEK)

gives me (in my TZ) - which is 1 week ago exactly:

2013-06-23 16:26:06

So any value in a date field equal to or later than this should be returned:

SELECT * FROM `tablename` WHERE `fieldname` >= DATE_SUB(NOW(), INTERVAL 1 WEEK)

Notice the backticks. I don't think that WEEK is a reserved word, even though it's used as an unit and a function. So backticking it shouldn't make a diff.

Sorry guys, fixed the error. I believe it was to do with my code. But it's sorted :)! Thanks!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.