Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New Coder
    Join Date
    Sep 2006
    Location
    USA
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Retrieving only last "publishdatetime" set

    I am building an article database which is updated [usually] on a weekly basis. Records are given a specific 'publishdatetime' when entered into the database.

    I need to find the latest 'publishdatetime' and then pull all the articles with only that 'publishdatetime'.

    So far I can pull EVERY article -or- break the querry so that all I get is an error.

    Here is my latest code [which throws an error]:

    PHP Code:
    <?php
    $sql 
    "SELECT * FROM article_data WHERE publishdatetime=max('$publishdatetime') AND siteID='7' ORDER BY artdataID asc";
    $result mysql_query($sql);
    $row 1//row counter 
    if ($myrow mysql_fetch_array($result)) {
    do{
    $publishdatetime=$myrow["publishdatetime"];
    $artdataID=$myrow["artdataID"];
    $headline=$myrow["headline"];
    $subhead=$myrow["subhead"];
    $categoryID=$myrow["categoryID"];
    $categoryname=$myrow["categoryname"];
    $siteID=$myrow["siteID"];
    echo 
    "<b>$categoryname</b> <span style='font-size:7pt;'>[$publishdatetime]</span>
    <ul style='margin-left:25px;'>
    <li><a href='javascript:void(0);' onclick='document.getElementById(\"$artdataID\").style.display = \"block\"; document.getElementById(\"artNav\").style.display = \"none\";' onmouseover='window.status=\"Read Article: $headline\"; return true;' onmouseout='window.status=\"\"; return true;'>$headline $subhead</a></li>
    </ul><br>"
    ;
    $row++;
    }
    while (
    $myrow mysql_fetch_array($result));
    }
    ?>
    Please help . . .
    • Jesus called. He says He misses you.

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    shall we guess at the error?

    have you tested directly in mysql (or phpmyadmin) and not via php?

  • #3
    New Coder
    Join Date
    Sep 2006
    Location
    USA
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry.

    Here is the error: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in Index.php on line 135

    I found some info on the max() and I think that is part of the problem [I think I'm using it incorrectly].

    Also, I don't know if this will help or not, but the publishdatetime is in this format: 2007-03-07 00:00:00.
    • Jesus called. He says He misses you.

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Add error checking to your query.

  • #5
    New Coder
    Join Date
    Sep 2006
    Location
    USA
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry 'bout all my omissions I'm a bit of a php newbie.

    Okay, I found/added echo mysql_errno() . ": " . mysql_error() . "\n"; to the php and it returned "1111: Invalid use of group function ".

    If there is other/better error checking I should use could someone please show me or drop me a link. I'm going through a bunch of web search results but what I am trying/finding doesn't seem to work [or is over my head].

    Thanks -
    • Jesus called. He says He misses you.

  • #6
    Senior Coder Nightfire's Avatar
    Join Date
    Jun 2002
    Posts
    4,265
    Thanks
    6
    Thanked 48 Times in 48 Posts
    $sql = "SELECT * FROM article_data WHERE publishdatetime=max('$publishdatetime') AND siteID='7' ORDER BY artdataID asc";

    Where are you getting $publishdatetime from in the above line? You don't set it to anything until further down in the script

  • #7
    New Coder
    Join Date
    Sep 2006
    Location
    USA
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hey, I think I just learned something. I didn't even think of that.

    I rewrote my php. Is this more correct?

    PHP Code:
    <?php
    $publishdatetime
    =$myrow["publishdatetime"];
    $artdataID=$myrow["artdataID"];
    $headline=$myrow["headline"];
    $subhead=$myrow["subhead"];
    $categoryID=$myrow["categoryID"];
    $categoryname=$myrow["categoryname"];
    $siteID=$myrow["siteID"];
     
    $sql "SELECT * FROM article_data WHERE publishdatetime=max('$publishdatetime') AND siteID='7' ORDER BY artdataID asc";
    $result mysql_query($sql);
    $row 1//row counter 
    if ($myrow mysql_fetch_array($result)) {
    do{
    echo 
    "<b>$categoryname</b> <span style='font-size:7pt;'>[$publishdatetime]</span>
    <ul style='margin-left:25px;'>
    <li><a href='javascript:void(0);' onclick='document.getElementById(\"$artdataID\").style.display = \"block\"; document.getElementById(\"artNav\").style.display = \"none\";' onmouseover='window.status=\"Read Article: $headline\"; return true;' onmouseout='window.status=\"\"; return true;'>$headline $subhead</a></li>
    </ul><br>"
    ;
    $row++;
    }
    while (
    $myrow mysql_fetch_array($result));
    }
    echo 
    mysql_errno() . ": " mysql_error() . "\n";
    ?>
    It is still broke though. I think I am way on the wrong track.

    How would the professionals out there query for the same result [a batch of records with the same publish date] and, uh oh, I just thought of something. They would need to display only from the publish date to the next set's [future] publish date. Ohhhhhhhhhhh . . . . this just got a lot harder.
    • Jesus called. He says He misses you.

  • #8
    Senior Coder Nightfire's Avatar
    Join Date
    Jun 2002
    Posts
    4,265
    Thanks
    6
    Thanked 48 Times in 48 Posts
    That won't work either as you're assigning values to the variables, but they've not been retrieved from the db yet.

    Not sure how to get around your problem as I've not got my coding head on at the moment lol So I'm as blank as you. Been a long day

  • #9
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    You did great with adding the mysql_error() display. That's exactly what was needed. However you will only want to display it if the query fails, so do it like this:

    PHP Code:
    $sql "SELECT * FROM article_data blah blah blah";
    $result mysql_query($sql);
    //if $result is false, that means the query failed
    if (!$result) {
        die(
    'QUERY ERROR! query text: $sql<br />query error: ' mysql_error());

    Now as for your query... you can't use the max() function like you are doing-- that is what is causing the problem. If your version of MySQL is recent enough you can use a subquery to achieve the same thing, just make sure you use the max() function on a field name, not a variable:

    PHP Code:
    $sql "SELECT *
    FROM article_data as A
    WHERE A.publishdatetime = 
        (SELECT max(B.publishdatetime)
        FROM article_data as B)
    AND A.siteID='7'
    ORDER BY A.artdataID asc"

    But even more efficient would be to just sort the query results by publishdatetime DESCENDING first, then by artdataID Ascending. You could then just fetch through the result set until the publishdatetime changes.... sorry if I'm losing you, the query above should work ok.


  •  

    Posting Permissions

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