SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Jun 2003
    Location
    London
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Error message when using SELECT DATE_SUB with PHP-MySQL function

    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:
    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.

  2. #2
    SitePoint Member
    Join Date
    Jun 2003
    Location
    London
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I couldn't get this working. I wrote the following function:
    PHP Code:
    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

  3. #3
    SitePoint Wizard Sillysoft's Avatar
    Join Date
    May 2002
    Location
    United States :)
    Posts
    1,691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SitePoint Member
    Join Date
    Jun 2003
    Location
    London
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 ?

  5. #5
    SitePoint Wizard Sillysoft's Avatar
    Join Date
    May 2002
    Location
    United States :)
    Posts
    1,691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 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 ?
    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

  6. #6
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    What field type is your publishDate field?
    Ian Anderson
    www.siteguru.co.uk

  7. #7
    SitePoint Member
    Join Date
    Jun 2003
    Location
    London
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thats it

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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •