...

View Full Version : Retrieving only last "publishdatetime" set



CrazyCoder
03-07-2007, 07:24 PM
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
$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 . . . :o

guelphdad
03-07-2007, 08:02 PM
shall we guess at the error?

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

CrazyCoder
03-07-2007, 08:08 PM
:o 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.

Fumigator
03-07-2007, 08:12 PM
Add error checking to your query.

CrazyCoder
03-07-2007, 08:32 PM
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 -

Nightfire
03-07-2007, 08:47 PM
$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

CrazyCoder
03-07-2007, 10:15 PM
Hey, I think I just learned something. I didn't even think of that.

I rewrote my php. Is this more correct?



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

Nightfire
03-07-2007, 11:23 PM
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

Fumigator
03-07-2007, 11:33 PM
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:



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



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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum