id | cid | name
1 | 1 | product 1
2 | 1 | product 2
3 | 1 | product 3
4 | 1 | product 4
5 | 1 | product 5
6 | 1 | product 6
7 | 2 | product 7
8 | 2 | product 8
9 | 2 | product 9
10 | 2 | product 10
11 | 2 | product 11
12 | 2 | product 12
13 | 3 | product 13
14 | 3 | product 14
15 | 3 | product 15
16 | 3 | product 16
PHP:
$query = "SELECT cid FROM product GROUP BY cid ORDER by cid DESC";
$result = mysql_query($query) or die('Error : ' . mysql_error());
echo 'id |cid | name <br>';
while($row = mysql_fetch_array($result, MYSQL_NUM))
{
list($cid) = $row;
$query2 = "SELECT id, cid, name FROM product WHERE cid = $cid ORDER by id DESC LIMIT 3";
$result2 = mysql_query($query2) or die('Error : ' . mysql_error());
while($row2 = mysql_fetch_array($result2, MYSQL_NUM)){
list($id2, $cid2, $name2) = $row2;
echo ''.$id2.' | '.$cid2.' | '.$name2.' <br>';
}
}
return ==>>
id | cid | name
16 | 3 | product 16
15 | 3 | product 15
14 | 3 | product 14
12 | 2 | product 12
11 | 2 | product 11
10 | 2 | product 10
6 | 1 | product 6
5 | 1 | product 5
4 | 1 | product 4
Now, i wan't 2 select to 1 select.
example:
$query = "SELECT cid FROM product WHERE (SELECT id, cid, name FROM product WHERE cid = $cid ORDER by id DESC LIMIT 3) GROUP BY cid ORDER by cid DESC";
$result = mysql_query($query) or die('Error : ' . mysql_error());
while($row = mysql_fetch_array($result, MYSQL_NUM)){
list($id, $cid, $name) = $row;
echo ''.$id.' | '.$cid.' | '.$name.' <br>';
}
return ==>>
id | cid | name
16 | 3 | product 16
15 | 3 | product 15
14 | 3 | product 14
12 | 2 | product 12
11 | 2 | product 11
10 | 2 | product 10
6 | 1 | product 6
5 | 1 | product 5
4 | 1 | product 4
help me! thanks for support
Easiest way to achieve it would be as
SELECT *
FROM product p
WHERE (
SELECT COUNT(*)
FROM product p1
WHERE p1.cid = p.cid AND
p.id <= p1.id
) <= 3
order by p.id desc
I don't know if this is the most elegant solution without using procedures...
What is done here is SUBSTRING_INDEX is being used to limit the entries used by GROUP_CONCAT.
And the result of GROUP_CONCAT is used as a set to be searched by FIND_IN_SET.
SELECT
p1.*
FROM
products p1
inner join (
SELECT
SUBSTRING_INDEX(GROUP_CONCAT(p2.id SEPARATOR ','), ',', 3) AS ids
FROM
products p2
GROUP BY
p2.cid
) AS ids
ON FIND_IN_SET(p1.id, ids.ids)
ORDER BY
p1.cid DESC,
p1.id
PS:
You won't u run into this now, but just be aware: GROUP_CONCAT has a maximum length setting (as in characters length of a string) that sets what the GROUP_CONCAT can return. This is 1024 characters by default, and thus is sufficient for 3 INT ids because each INT id with adjending comma is max 12 characters. If you go extreme and want more than 85 records per cid, you will need to adjust your mysql group_concat_max_len
setting.