php - SQL MAX() in multiple query string -

i have string of 3 queries designed

1. find messages have other messages same id represents replies
2. find messages of results first query have specified user entering first message of string of messages (min timestamp)
3. find latest message of string of messages (max timestamp)

the problem comes third query. expected results second query, when third executed, without max(timestamp) max, expected results. when add that, first message each string of messages when should last, regardless of whether use min or max , row count says 1 row returned when there 2 rows shown. got ideas on went wrong?

$sql="select reply_chunk_id          messages          group reply_chunk_id          having count(reply_chunk_id) > 1 "; $stmt16 = $conn->prepare($sql); $result=$stmt16->execute(array('specified_user')); while($row = $stmt16->fetch(pdo::fetch_assoc)){     $sql="select user,reply_chunk_id, min(timestamp) grp_timestamp             messages reply_chunk_id=?             group reply_chunk_id having user=?";     $stmt17 = $conn->prepare($sql);     $result=$stmt17->execute(array($row['reply_chunk_id'],'specified_user'));     while($row2 = $stmt17->fetch(pdo::fetch_assoc)){         $sql="select message, max(timestamp) max messages reply_chunk_id=?";         $stmt18 = $conn->prepare($sql);         $result=$stmt18->execute(array($row2['reply_chunk_id']));         while($row3 = $stmt18->fetch(pdo::fetch_assoc)){             echo '<p>'.$row3['message'];         }     } } echo ' '.$stmt18->rowcount(); 

create table view of messages, requested:

create table if not exists `messages` (   `id` int(5) not null auto_increment,   `timestamp` int(11) not null,   `user` varchar(25) character set utf8 collate utf8_unicode_ci not null default 'anonimous',   `message` varchar(2000) character set utf8 collate utf8_unicode_ci not null,   `topic_id` varchar(35) not null,   `reply_chunk_id` varchar(35) not null,   primary key (`id`) ) engine=myisam  default charset=latin1 auto_increment=7 ; 

since message isn't grouped, message group you'll isn't defined. if want message maximum timestamp, you'll need explicitly select it:

select message, timestamp max     messages      reply_chunk_id=:rcid       , timestamp=(select max(timestamp)                           messages                           reply_chunk_id=:rcid) 

or:

select message, timestamp max     messages      reply_chunk_id=?     order timestamp desc, id     limit 1 

the second query breaks ties (in unlikely possible situation more 1 person posts @ same time) selecting message highest id.

general feedback

many of variables set within loops invariant, , should moved outside loop.

$stmt17 return @ 1 result. moreover, $stmt18 return return 1 result. rewriting second inner while loop (for $stmt17) if statement, , fetching result $stmt18 equivalent , clearer purpose.

try {   $threadsql="select reply_chunk_id            messages            group reply_chunk_id            having count(reply_chunk_id) > 1 ";   $firstusersql="select user, min(timestamp) grp_timestamp           messages reply_chunk_id=?           group reply_chunk_id having user=?";   $lastmsgsql="select message, max(timestamp) max messages reply_chunk_id=?";   $threadquery = $conn->prepare($threadsql);   $threadquery->setfetchmode(pdo::fetch_assoc);   $firstuserquery = $conn->prepare($firstusersql);   $lastmsgquery = $conn->prepare($lastmsgsql);    $result=$threadquery->execute(array('specified_user'));   foreach ($threadquery $thread){       $result=$firstuserquery->execute(array($thread['reply_chunk_id'],'specified_user'));       if (false !== ($firstuser = $firstuserquery->fetch(pdo::fetch_assoc))) {           $result=$lastmsgquery->execute(array($thread['reply_chunk_id']));           $lastmsg = $lastmsgquery->fetch(pdo::fetch_assoc);           echo '<p>'.$lastmsg['message'].'</p>';       }   }   echo ' ' . $lastmsgquery->rowcount(); } catch (pdoexception $exc) {   ... } 

lastly, single sql statement can replace of php code:

select mchunk.reply_chunk_id,         muser.user, min(muser.`timestamp`) grp_timestamp,         mmax.message, mmax.`timestamp` max   messages mchunk   join messages muser     on mchunk.reply_chunk_id = muser.reply_chunk_id   join messages mmax     on mchunk.reply_chunk_id = mmax.reply_chunk_id   mmax.timestamp=(select max(timestamp) messages m m.reply_chunk_id=mchunk.reply_chunk_id)   group mchunk.reply_chunk_id, muser.user   having count(mchunk.reply_chunk_id) > 1     , muser.user in ('steve', '0010') ; 

this selects threads started specified user have responses, along recent response.