Third last post from MySQL

[tomarild]

Hello!

I really hope that someone in here can help. Ive googled a whole evening and asked in my local forum, but nobody knows what to do.

First let me say what Ive got:
A MySQL database with a table called articles.
In the table articles i have these fields:
id (autonum), day, month, year, category, title, picurl, text

I have made a form from which I can insert data into these fields, and I group the categories into 5 different categories (lets cal them A,B,C,D and E)

What I want:
I want to pull the data from f.ex only the third last post in the catgory C.
Or the eight last post in the category E.
Or the fifth last post no matter which category.

You see what I want?

I already know that this code will get me the last three post in a category

SELECT * FROM articles WHERE category=´A´ ORDER BY id DESC LIMIT 3

..... or the very last post if using LIMIT 1

But how to get only the sixth (or some other number) last post?

[onicsoft]

8th last post
ORDER BY id DESC LIMIT 8,1
Start at the 8th data row and only give me 1 row.

5th last post
ORDER BY id DESC LIMIT 5,1
Start at the 5th data row and only give me 1 row.

The 5 or 8 tells mysql where to start in the returned data array. The 1 tells it how many to return after that.

For more reading check out this: http://php.about.com/od/mysqlcommands/g/Limit_sql.htm

Sidenote:
This is also how you do paging normally, ie, 10 articles per a page, your on the 3rd page. 10*3=30.. So, you would do limit 30, 10 at the end of the sql statement. Start at the 30th row and give me the next 10. Then if they went to page 4, it would be 40, 10. (40-49=10 rows). Lots more checks would go into it.

I hope this helps.

[djjjozsi]

WHERE category='A' ORDER BY id

use apostrofe and not `

if you want to list with differend blocks, but selections from categories,

use the UNION to add the categories into one result.

SELECT *
FROM articles
WHERE articles.category='A'
 LIMIT 10,8
UNION
SELECT *
FROM articles
WHERE articles.category='B'
 LIMIT 5,2

[tomarild]

Oh thank you guys for helping! I appreciate it a lot.

It really looks simple, and I am so glad that it obviously works. However in my case it returns a blank page. Maybe I have something wrong in my code? Would you mind take a quick look?

<?php
$con = mysql_connect("localhost","username","pwd");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("my_db", $con);

$result = mysql_query("SELECT * FROM articles ORDER BY id DESC LIMIT 3,1");

while($row = mysql_fetch_array($result))

{

echo "<font color=#919191>" . $row['day'] . "." . $row['month'] . "." . $row['year'] . "</font>";

echo "<br />";
echo "<b><font color=B9720E size=2>" . $row['title'] . "</font></font></b>";
echo "<br />";
echo "<br />";
echo $row['text'];
echo "<br /><br /><br /><br /><br />";

}

mysql_close($con);
?>

[djjjozsi]

could your code selected the table ?

$con = mysql_connect( "localhost", "username", "pwd" ) or die( 'Could not connect: ' . mysql_error() );
mysql_select_db( "my_db", $con ) or die( mysql_error() );

$result = mysql_query( "SELECT * FROM articles ORDER BY id DESC LIMIT 3,1" ) or die( mysql_error() );

[tomarild]

It still returns a blank page.

If I remove the

,1 from LIMIT 3,1

it works, but I get all posts. So the problem is this ,1-thing. Hm.... More advices?

[djjjozsi]

print the query and test in phpmyadmin, and see teh results. if its telling that no results, then count the results before you use them.

[tomarild]

Thank you so much everybody!

Your last reply lead me to the answear.

The code

"SELECT * FROM artikler LIMIT 3, 1 ; "

shows the fourth post (i dont know why), and as long as there were only three posts in the database, the result was blank.

THis works great now. Thank you so much. I really appreciate it a lot!!

[djjjozsi]

becouse the firs element is called 0

LIMIT 0,5 means select the 'first row' and the following 5

LIMIT 1,5 means select the second and the following 4

[tomarild]

Thanks 🙂

Another question while I am sitting here.

How can i get only the 200 first words from the "text"-field in the database, and where in my code should it be inserted?

[djjjozsi]

ORDER BY id LIMIT 0,200

if you need to have another order, for example by date, you can insert date-tiome field to track the date of registration 4 example.