[Conny Westh]

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `ThreadsByLatestActiveDate`()
BEGIN
	SELECT threadid, MAX(LatestActiveDate) as LatestActiveDate
	FROM
	(
		SELECT threadid, MAX(createddate) AS LatestActiveDate from wn.threads
		GROUP BY threadid
		UNION 
		SELECT threadid, MAX(created)  AS LatestActiveDate from wn.posts
		GROUP BY threadid
	) x
	GROUP BY threadid
	ORDER BY LatestActiveDate DESC;
END

Jag döpte om proceduren och fixade lite testkod i PHP om någon vill provköra proceduren i PHP.

<?php

$host="localhost";
$port=3306;
$socket="";
$user="root";
$password="mypassword";
$dbname="wn";

$con = new mysqli($host, $user, $password, $dbname, $port, $socket)
	or die ('Could not connect to the database server' . mysqli_connect_error());

//$con->close();

$query = "call `wn`.`ThreadsByLatestActiveDate`";

if ($stmt = $con->prepare($query)) 
{
    $stmt->execute();
    $stmt->bind_result($threadId, $latestActiveDate);
    while ($stmt->fetch()) 
	{
        printf("%s, %s\n", $threadId, $latestActiveDate);
    }
    $stmt->close();
}
?>