CODEWITHSUNDEEP
phpmysqlarraysgroup-bysql-order-by
Guage
Guage

Reputation: 85

SELECT WHERE group id IN array ORDER BY and Group By

I made a messaging system with a unique id for each message, and a category to keep related threads tied/linked. I have the categories stored in an array, and I can display each message with this mysql query. 

$getid = $link->prepare("SELECT * FROM `table` WHERE 
                                               category IN ('$array') 
                                               ORDER BY id DESC");                   
$getid->execute();
while($msg=$getid->fetch()) {

echo "<br />".$msg['id'];
}

The above successfully outputs every message that is within the category ids listed in the array. I am trying to only display the most recently listed, i.e. the messages in each category with the largest id.

I tried group by category that have failed, it gave me incorrect results.

$getid = $link->prepare("SELECT * FROM `table` WHERE 
                                               category IN ('$array') 
                                               GROUP BY category 
                                               ORDER BY id DESC");                   
$getid->execute();
while($msg=$getid->fetch()) {

echo "<br />".$msg['id'];
}

also failed using MAX(id)

$getid = $link->prepare("SELECT MAX(id) FROM `table` WHERE 
                                               category IN ('$array') 
                                               ORDER BY id DESC");                   
$getid->execute();
while($msg=$getid->fetch()) {

echo "<br />".$msg['id'];
}

I have tried multiple variations, too numerous to elaborate here. I am trying to display only the latest messages (last id) in any given category, most recent on top.

Upvotes: 2

Views: 386

Answers (1)

bitfiddler
bitfiddler

Reputation: 2115

I think a subquery something like this will do it. 

select * from `table` T where T.category in ('$array') and 
  T.id = (select max(id) from `table` where category = T.category)

Upvotes: 2

Related Questions