MYSQL Limit by id?

[Mortenjan]

Hi. I am trying to limit rows in mysql based on id.

Im using a left join.

 

If i have this setup:

id

175

175

185

196

198

 

And i want mysql to limit to the first 2 id numbers so the output becomes this:

 

175

175

185

 

How can i achieve this?

 

my mysql query looks like this:

SELECT
c2.blog_id,
c2.blog_title_en,
c2.blog_title_no,
c2.blog_image,
c2.blog_content_en,
c2.blog_content_no,
c2.blog_date,
c2.mms,

c1.id,
c1.mmsid,
c1.datalen,
c1.content_type,
c1.data

FROM parts AS c1
RIGHT JOIN blog AS c2

ON c1.mmsid = c2.blog_id
WHERE `content_type` != 'application/smil' OR `content_type` IS NULL

ORDER BY  c2.blog_date DESC, c1.id ASC

 

Thanks in advance:)

Regards Morten

[fenway]

Easier to handle this in php -- but meaningless without an order by clause.

[Mortenjan]

I use this command so it wont display the same id twise:

 

if ($lastblog != $blog_array['blog_id']) {

// CODE

$lastblog = $blog_array['blog_id'];
}

 

But i still have an issue.

If i have this result from my query:

 

id:

125

125

196

196

 

It only displays this far:

125

125

196

 

How can i get it to display the last row?

 

Thank you so far

 

Regards Morten

 

[brianlange]

You need a variable to indicate when the number of unique iterations have been meant.

$keepDisplayingRows = true;

 

Set this to false when a new id is displayed. But keep looping through the result set to display the remaining matching ids.

 

The logic is pretty simple. Post your loop code if it is not working.

[Mortenjan]

$keepDisplayingRows = true;
while ($blog_array = mysql_fetch_assoc($blog_result)){ 
if($keepDisplayingRows == true){
	if($lastblog != $blog_array['blog_id']){
	$lastblog = $blog_array['blog_id'];
	$i++;	
	if($i == $limit){
		$keepDisplayingRows = false;
		}	
	}
           // REST OF THE CODE GOES HERE
           }
}

 

Hm, i understand what you mean, but im not sure how keep looping through the rest of the ids. Do you think you could help me?

That would be much appreciated.

 

Regards Morten

[Mortenjan]

Never mind, i think i got it:)

 

I put this:

if($i == $limit){
$keepDisplayingRows = false;
}

In an if statment which were made for the last row on each id.

 

Thanks for the help!