random selection excluding most recent

[dachshund]

I tried posting on here, but to no avail so far.

 

I'm basically trying display 4 random 'feature' articles, apart from the most recent one (the one with the highest id number out of all of the features).

 

so far I had

 

$sql = "SELECT * FROM content WHERE `section` LIKE '%feature%' ORDER BY RAND() LIMIT 1,4";

 

but that fails to exclude the newest feature.

 

someone else suggested

 

$sql = "SELECT * FROM (SELECT * FROM content WHERE `section` LIKE '%feature%' ORDER BY id desc limit 1,4) as id ORDER BY rand() limit 0,4";

 

which is closer, but then the page is blank.

 

any help would be great.

 

thanks

 

 

[Mark Baker]

SELECT * 
  FROM content 
WHERE `section` LIKE '%feature%' 
   AND `id` < ( SELECT MAX(`id`)
                  FROM content 
                 WHERE `section` LIKE '%feature%'
              ) 
ORDER BY RAND() LIMIT 0,4

[dachshund]

this seems pretty perfect but the page still comes up blank. hmmm. am i missing something?

[Mark Baker]

If the page comes up blank, then the problem may not be in your query, but elsewhere in your php

[dachshund]

can't spot anything.

 

this is the whole thing.

 

<?php
$today = date('Y-m-d');
$sql = "SELECT * FROM content WHERE `section` LIKE '%feature%' AND `id` < ( SELECT MAX(`id`) FROM content WHERE `section` LIKE '%feature%' ) ORDER BY RAND() LIMIT 0,4";
$result=mysql_query($sql);
while($rows=mysql_fetch_array($result)){
$text = $rows['information'];

if (strlen($text) > 200) {
$text = substr($text, 0, 197) . '...';
}
?>

 

[Philip]

Maybe it is because you're not printing anything to the output?

 

You need to echo/print what you want the user to see.

[dachshund]

well yeah i have all this too

 

<table width="100%" class="columnpad" cellpadding="2" cellspacing="0">
    <tr>
    	<td width="100%" align="center" valign="top">
        <a href="<? echo $rows['section']; ?>/view_<? echo $rows['section']; ?>.php?id=<? echo $rows['id']; ?>"><img src="<? echo $rows['indeximage']; ?>" width="280" border="0"></a>
        </td>
    </tr>
    <tr>
    	<td width="100%" align="left" valign="top" class="greydotted">
        <span class="smallitalicscapitalised">
        <? echo $rows['type']; ?> <? echo $rows['section']; ?>
        </span>
        <br />
        <span class="h4">
        <a href="<? echo $rows['section']; ?>/view_<? echo $rows['section']; ?>.php?id=<? echo $rows['id']; ?>"><? echo $rows['title']; ?></a>
        </span>
        <br />
        <span class="indexcontent">
	<? echo $text; ?>
        </span>
        <br />
        <span class="readmore">
        <a href="<? echo $rows['section']; ?>/view_<? echo $rows['section']; ?>.php?id=<? echo $rows['id']; ?>">
        Read More...
        </a>
        </span>
        </td>
    </tr>
</table>

<?php
}
mysql_close();
?>

 

but i don't think the problems there. it all works fine when I don't try to exclude the highest id, so I think it's in the new code.

 

[dachshund]

any one spot anything?

[phpSensei]

Add this right after the $result variable

 

echo mysql_num_rows($result);

 

and tell me what the output is.

 

 

 

[dachshund]

still comes up blank. this is so annoying.

[dachshund]

but if i do it with

$sql = "SELECT * FROM content WHERE `section` LIKE '%feature%' ORDER BY RAND() DESC LIMIT 1,4";

 

then the output is 4.

 

[phpSensei]

Comes up blank cause the query is wrong

 

try

 

SELECT * FROM `content` WHERE `section` 'feature%' AND `id` < (MAX(`id)) ORDER BY RAND() Limit 4

[dachshund]

still comes up blank.

 

tried this too.

 

$sql = "SELECT * FROM `content` WHERE `section` LIKE '%feature%' AND `id` < (MAX(`id`)) ORDER BY RAND() LIMIT 4";

[Mark Baker]

Have you tried executing the SQL query in phpMyAdmin?

[phpSensei]

Have you tried executing the SQL query in phpMyAdmin?

 

 

I suggest you do that OP.

 

PM me your database table for content, a screenshot so i can take a look at it

[dachshund]

Here's a screenshot.

 

 

[dachshund]

any ideas anyone? i'd love to sort out this problem.

 

it's giving me the itch!

[dachshund]

ok, seems not, for now is there a way i can choose an id number i want to be excluded?

 

$sql = "SELECT * FROM content WHERE `section` LIKE '%feature%' ORDER BY RAND() LIMIT 1,4";

 

 

[Philip]

Simply add a condition in the where clause:

AND id != #