multiple query comparison

[jojest]

Hi All,

I am looking for help from experts, eventhough the solution for my query might be simple.

I have 3 queries to display latest news

Query 1. Select latest 1 record to show the bigger thumbnail.
Query 2 Select the second and third record in the query to show smaller than image in the query 1.

Third query is to show 10 results from the rest of the date from db. But since the second query returns 2 results, I am not able to pass the id to a variable and compare that in the query like I am able to do for the second query where I need to compare only one record, but second query return return 2 results.

So I like to write a query which omit first 3 records in the database and return the remining 10 results from the news list database.

Could some one help me on this...

Thanks in advance

[r937]

first, which database system is this?

second, could we see the queries please?

[jojest]

Thanks for your reply.

It is mysql and following are the queries I have used for first 2

For displaying the first news with Big Thumb Image
-------------------------------------------------
$sql = "SELECT id, title, LEFT(before_src, InSTR(before_src, '.')) as Content, date, thumb FROM newsArticles WHERE published='yes' AND thumb != ''
ORDER BY id DESC LIMIT 1";


For displaying the latest news other than first one with small Thumb Image
-----------------------------------------------------------------------

SELECT id, title, LEFT(before_src, InSTR(before_src, '.')) as Content, date, thumb FROM newsArticles WHERE id != $id AND published='yes' AND
thumb != '' ORDER BY id DESC LIMIT 2";

Here $id get the value from the first query. Also check that if no thumb image available, omit the news.

Third query should avoid the first query result of 1 and second query result of 2. So next five news (also include the omitted news item due to empty image) should be fetched in third.

Hope my explanation is clear....

Thanks in advance

[r937]

use LIMIT 3,10

[jojest]

Hi
Thank you for your reply. But actually I am omitting records in the first and second query where there is no thumbnail images even if the record is the latest one. So like to include those records as well in the third query where there are no images displayed. So Limit will not work in this scenario. Is there any other way?

Thanks in advance

[guido2004]

If the rows selected by the first two queries aren't the first three (ordering by id DESC), because among the first rows there are some that have an empty image, in the 3rd query you might exclude them using

WHERE id NOT IN ($id1, $id2, $id3)

[jojest]

Thanks Guido 2004,

The fact is the second query got 2 results and how could we pass the values to variables ($id2 and $id3) ? Since the result is an array . I am a learner and sorry for the silly queries.....

[guido2004]

How do you pass the first id to $id?

[jojest]

Since first query fetch only one result it is passing as

$sql = "SELECT id, title, LEFT(before_src, InSTR(before_src, '.')) as Content, date, thumb FROM newsArticles WHERE published='yes' AND thumb != ''
ORDER BY id DESC
LIMIT 1";

$result = mysql_query($sql);
$row = mysql_fetch_assoc($result);
extract($row);

$id= $row['id']; // *** here passes the value to variable ***

But second one got 2 results and i am stuck there.....

[guido2004]

But I'm sure your doing something with those 2 results? A while loop?
Inside that loop, put the two ids in an array, or construct part of the IN condition.