Error message when using SELECT DATE_SUB with PHP-MySQL function

[heidi]

The actual PHP program shall list all articles from a database that were published between the current date and 7 days ago.
The MySQL request for this is:
SELECT articleText,articleTitle FROM articleDB WHERE publishDate>='YYYY-MM-DD'
The problem is to determine this date:
PHP code:

$currentDate=date("Y-m-d");
$sql="SELECT DATE_SUB ('$currentDate',INTERVAL 7 DAY)";
$getDate=mysql_query($sql); 

The result of this is an error message I don't understand:
mysql_error(): supplied argument is not a valid MySQL-Link resource in ...
Can anybody help ?
thankyou *heidi

BTW: The SQL request SELECT DATE_SUB('YYYY-MM-DD,INTERVAL 7 DAY'); works fine at the MySQL DB software - its just not working with PHP.

[heidi]

I couldn't get this working. I wrote the following function:

function getPublishDate()
{
    $Y=date("Y");
    $M=date("n");
    $D=date("j");
    
    $dayCount=7;
    $tempDay=0;
    $w=settype($tempDay,"integer");
    $dateArray[]="";
    $r=0;

    while($dayCount>=0)
    {
        $tempDate="";
        if($D<=$dayCount)
        {
            $tempMonth=$M-1;
            if($tempMonth=='2'||$tempMonth=='4'||$tempMonth=='6'||$tempMonth=='9'||$tempMonth=='11')
            {
                if($tempMonth=='2')
                {
                    if($Y=="2004"||$Y=="2008") //leap years
                    {
                        $tempDay=$D-$dayCount;
                        $tempDay=29+$tempDay;
                    }
                    else
                    {
                        $tempDay=$D-$dayCount;
                        $tempDay=28+$tempDay;
                    }
                }
                else
                {
                    $tempDay=$D-$dayCount;
                    $tempDay=30+$tempDay;
                }
                $tempYear=$Y;
            }
            else
            {
                $tempDay=$D-$dayCount;
                $tempDay=31+$tempDay;
                if($tempMonth=='0')
                {
                    $tempMonth=12;
                    $tempYear=$Y-1;
                }
                else
                {
                    $tempYear=$Y;
                }
            }
        }
        else
        {
            $tempDay=$D-$dayCount;
            $tempMonth=$M;
            $tempYear=$Y;
        }
        $tempDate=$tempYear;
        if($tempMonth<10)
        {
            $tempDate=$tempDate."-0".$tempMonth."-";
        }
        else
        {
            $tempDate=$tempDate."-".$tempMonth."-";
        }
        if($tempDay<10)
        {
            $tempDate=$tempDate."0".$tempDay;
        }
        else
        {
            $tempDate=$tempDate.$tempDay;
        }
        $dateArray[$r]=$tempDate;
        $r++;
        $dayCount--;
    }
    $publishDates="published='$dateArray[0]' OR 
                    published='$dateArray[1]' OR
                    published='$dateArray[2]' OR
                    published='$dateArray[3]' OR
                    published='$dateArray[4]' OR
                    published='$dateArray[5]' OR
                    published='$dateArray[6]' OR
                    published='$dateArray[7]'";
                    
    return($publishDates);
} 

"$publishDates" will be used at the SELECT * FROM * WHERE ($publishDates) query.
I'm sure there is a better way to define a time period for SQL querys.
I would be interested in other solutions.
thanks
bye *heidi

[Sillysoft]

So your trying to get the date from 7 days ago? If this is correct then try this:

http://us2.php.net/manual/en/function.mktime.php

Silly

EDIT:

BTW I noticed in your SQL query:
$sql="SELECT DATE_SUB ('$currentDate',INTERVAL 7 DAY)";

Is there suppose to be a FROM in there? Could be wrong

[heidi]

No, not just the date 7 days ago.
Example: If today's date was 2003-08-03 the PHP programme shall search all articles that were published during the time from 2003-07-27 until today (2003-08-03).
That means the publish date could be the 27|07, 28|7, 29|7, 30|7, 31|7, 1|8, 2|8 or 3|8.
The query: SELECT * FROM table WHERE publishDate>=2003-07-27 did not work. I dont know about possible where clauses for date or time types. I reckon its not possible to use <,>,<= or >= in a where clause for date queries ?

[Sillysoft]

No, not just the date 7 days ago.
Example: If today's date was 2003-08-03 the PHP programme shall search all articles that were published during the time from 2003-07-27 until today (2003-08-03).
That means the publish date could be the 27|07, 28|7, 29|7, 30|7, 31|7, 1|8, 2|8 or 3|8.
The query: SELECT * FROM table WHERE publishDate>=2003-07-27 did not work. I dont know about possible where clauses for date or time types. I reckon its not possible to use <,>,<= or >= in a where clause for date queries ?

Right. I might not be understanding this, but mktime can get the date 7 days ago. So you could create a beginning date and a end date and find all articles within that date range. Maybe using the BETWEEN method in your sql statement. Or the >= and <=

Silly

[siteguru]

What field type is your publishDate field?

[heidi]

Ah, I was looking for this: the SQL clause "BETWEEN".
I forgot about this one.
Thanks