mySQL Columns Query

[(Shovel)]

Hello, so far I'm doing alright at this mySQL business, but I'm having one niggling problem with the current module I'm working on.

I'm writing a "block" (phpnuke speak for "a bit of menu") to display the 10 most recent column entires.

Columns are kept in just one table, so it's a nice simple query:

$sql = "SELECT `column_id`, `author_id`, `title`, `date`, `time` FROM `column` GROUP BY `author_id` ORDER BY `column_id` DESC LIMIT -10;

This means that I get the last 10 column entires. However, the GROUP BY, to eliminate duplicate entries (I only want one per author), causes it to display the *FIRST* entry by that author, not the most recent.

Can anyone offer some guidence on getting it to give me the latest entry rather than the first?

Thanks

 

[Rederovski]

maybe you could use a date field:

$sql="SELECT * FROM column GROUP BY author_id ORDER BY column_date DESC LIMIT -10";

When they post a column just auto insert the date (more info http://www.php.net/date)

 

[(Shovel)]

That didn't work I'm afraid. However, I figured it out

The problem relates to writing output for a pre-provided module in phpNuke, which obviously adds to the fun. Everything was coming out fine, apart from the id number it provided for the "most recent" column entry.

Fixed it like this:

		 	 	SELECT MAX(column_id) as id, author_id, title, date, time
		 	 	FROM column
				GROUP BY author_id
				ORDER BY column_id DESC
				LIMIT -10

 

[(Shovel)]

Rats - not fixed as I thought.

What I've got is that it will now identify the correct journal ID number, but the rest of the info - title and date etc. will be for the first entry.

I have a feeling I'm getting there, albeit slowly.

 

[wyrd_fish]

you could just use multiple querys

inefficient i know, but...

 

[(Shovel)]

Checking the mySQL manual multiple queries appears to be the way to go - subqueries aren't introduced until version 4.1

So, here's the exact PHP/mySQL (with the actual phpNuke variable names).

The problem I'm having is that it will not return any results.

/* mySQL Queries */
/* Create temp table */
$sql = "CREATE TEMPORARY TABLE tmp (
		 	  jid INT(11) UNSIGNED NOT NULL,
        aid VARCHAR(30) NOT NULL);";
$result = mysql_query($sql);

$sql = "LOCK TABLES nuke_journal READ;
		 	 	INSERT INTO tmp SELECT MAX(jid), aid FROM nuke_journal GROUP BY aid;";
$result = mysql_query($sql);

$sql = "SELECT nuke_journal.jid, nuke_journal.aid, title, mdate, mtime 
		 	  FROM nuke_journal, tmp
				WHERE nuke_journal.jid=tmp.jid
				ORDER BY nuke_journal.jid DESC
				LIMIT -10;";
$result = mysql_query($sql);
				
/* Return Journals */
while ($row = mysql_fetch_assoc($result)) {
	$content .= "<a href=\"modules.php?name=Journal&file=display&jid=$row[jid]\" title=\"$row[title] ($row[mdate] $row[mtime])\" class=\"content\">$row[aid]</a>
";
}
  $content .= "<a href=\"modules.php?name=Journal\" title=\"Full Columns Listing\">[size="1"]More...[/size]</a></div>";

/* SQL Clean up */
$sql = "UNLOCK TABLES;
				DROP TABLE tmp;";
$result = mysql_query($sql);

But, if I open up the mySQL Control Centre and run each query step by step in a query box there, it returns the exact results I want. Which leads me to be rather confused by what on earth is going wrong.

As I say, it's just coming out blank, no errors or anything like that. I'm hoping it's just a silly mistake somewhere, cause it maketh no sense now!

 

[wyrd_fish]

$sql = "SELECT nuke_journal.jid, nuke_journal.aid, title, mdate, mtime 
		 	  FROM nuke_journal, tmp
				WHERE nuke_journal.jid=tmp.jid
				ORDER BY nuke_journal.jid DESC
				LIMIT -10;";

you don't want to have the extra ; at the end of the sql... just at the end of the php.