Select statement help.

Question

Hi, there

I am coding a blog post kind of thing, where the author will post the article and it will be displayed in the front end, my problem starts for selecting the posts as i have to meet certain conditions for posting the news in the front end,

I have 4 fields in the database namely

title
pic_title
pic_brief
pic_detail

you guessed it right apart from the title table the rest of three will hold the path to the images in varchar datatype, which will be used to display as the post, the format of the front end is such that

a) there will be total of eight post displaying in the front end (eight entries from the database)

b) there will be three post on the top which will include the value from the table title, pic_title and pic_brief (total of 3 values)

c) and the rest five will contain just the title and pic_title (excluding the three entries of top) Please NOTE: i want the second query to exclude the top 3 record which already exist in the top i.e (first query = 3 post in descending order, second query = 8 - first 3 = 5 post)

The Order of the Post i want is by id DESC

EDIT: I took the first query as

SELECT * FROM news ORDER BY id DESC LIMIT 3

Now if i take the same second query and try populating the values by desc order again the same records will be accessed

In simple words i want a query that will skip the last three records order by id DESC

How do i achieve this feat in PHP?

Answer 1

If you just want the SQL, here it is:

First query

SELECT * FROM `table` LIMIT 3

Second query

SELECT * FROM `table` LIMIT 3,5

(where table is the name of your table of course. Of course you may want to add some ORDER BY clause. To execute these queries in PHP, I suggest reading the manual. If you have any specific problems after doing so, then you can post a new question.

Answer 2

first query will be like this

"select title, pic_title , pic_brief from table_name order by post_id desc limit 0 , 3"

and rest of five will be

"select title, pic_title from table_name order by post_id desc limit 3 , 5"

second query will exclude the three results returned by first query...

If you want more perfection you can collect all three Ids returned by first query and can add NOT IN in second query.

"select title, pic_title from table_name where post_id not in (1,2,3) order by post_id desc limit 0 , 5";

Answer 3

This is a situation where I'd likely opt to select all eight records at once - the less trips to the database, the better.

  SELECT t.title,
         t.pic_title,
         t.pic_brief
    FROM TABLE t
ORDER BY t.id DESC
   LIMIT 8

...because the rest is just presentation:

$query = sprintf("SELECT t.title,
                         t.pic_title,
                         t.pic_brief
                    FROM TABLE t
                ORDER BY t.id DESC
                   LIMIT 8");

// Perform Query
$result = mysql_query($query) or die( mysql_error() );

$rowcount = 1;

// Use result
while ($row = mysql_fetch_assoc($result)) {

  if(rowcount <= 3) {
     echo $row['title'] 
     echo $row['pic_title'] 
     echo $row['pic_brief'] 
  } else {
     echo $row['title'] 
     echo $row['pic_title'] 
  }

  ++$rowcount;
}