Getting all mysql results...and making one the "featured" story

[bschultz]

I'm trying to get a "featured" story working.  What I want is this:

 

go get all mysql records...make one the featured story (randomly selected) ...and make all others "sub stories".

 

I currently have this code...

 

<?php 
$link = mysql_pconnect($host, $username, $password);  
mysql_select_db('content',$link);
$sql = "SELECT * FROM stories WHERE NOW() BETWEEN startdate AND enddate AND (station = 'mix' OR station = 'all') ORDER by RAND() LIMIT 1";
$rs = mysql_query($sql,$link);  
$matches = 0; 
while ($row = mysql_fetch_assoc($rs))  {
$matches++;
echo "<div class='box_subtitle'>";
echo "<div class='left_box'><h3>".str_replace('COMPANYNAMEHERE',COMP_NAME,stripslashes($row['headline']))."</h3>";
echo "</div>";
echo "<div class='middle_box_content'><div class='middle_box_text'><p class='middle_text'><div class='regular'>";
$code = str_replace('COMPANYNAMEHERE',COMP_NAME,$row['long_story']);
eval('?>' . $code . '<?');
echo "</p></div></div></div></div>";
}  
if (! $matches) { 
echo (""); 
}  
echo "";  
?> 
          
            </div>
            
        </div>
        
        <div class="bottom_content">



<?php 
///////////////////////  SHORT STORIES HERE   ///////////////////////
$link = mysql_pconnect($host, $username, $password);  
mysql_select_db('content',$link);
$sql = "SELECT * FROM stories WHERE NOW() BETWEEN startdate AND enddate AND (station = 'mix' OR station = 'all') ORDER by RAND()";
$rs = mysql_query($sql,$link);  
$matches = 0; 
while ($row = mysql_fetch_assoc($rs))  {
$matches++;
echo "<div class='left_box'>";
$headline = str_replace('COMPANYNAMEHERE',COMP_NAME,$row['headline']);
echo "<h3>$headline</h3>";
echo "<div class='left_img_box'><a href='/pages/$row[keyword]/'><img src='$row[short_story_image]' alt='Image' title='' height='75' width='75' border='0' /></a></div>";
echo "<div class='left_text_content'><p>";
$code = str_replace('COMPANYNAMEHERE',COMP_NAME,$row['short_story']);
eval('?>' . $code . '<?');
echo "</p></div></div>";
}  
if (! $matches) { 
echo (""); 
}  
echo "";  
?> 

 

 

What this code is doing is get 1 random story in the first query...then get all the stories for the second query.  I'm applying separate styles to these...as the featured story is much larger than the sub stories.

 

The problem here is that the featured story will also show up in the sub story query.  In the database, the column "keyword" is a unique value.

 

How can I accomplish this?  Thanks!

[Pikachu2000]

Store the id of the randomly selected story in a variable, then specifically exclude it from the second result set.

[wildteen88]

The two queries are identical, except the first storey returned is labeled as the featured story. You can do this with just one query. Like so

 

<?php 
$link = mysql_pconnect($host, $username, $password);  
mysql_select_db('content',$link);


$sql = "SELECT * FROM stories WHERE NOW() BETWEEN startdate AND enddate AND (station = 'mix' OR station = 'all') ORDER by RAND()";
$rs = mysql_query($sql,$link);  

// grab the first featured storey
$featured = mysql_fetch_assoc($rs)

echo "<div class='box_subtitle'>";
echo "<div class='left_box'><h3>".str_replace('COMPANYNAMEHERE',COMP_NAME,stripslashes($featured['headline']))."</h3>";
echo "</div>";
echo "<div class='middle_box_content'><div class='middle_box_text'><p class='middle_text'><div class='regular'>";
$code = str_replace('COMPANYNAMEHERE',COMP_NAME,$featured['long_story']);
eval('?>' . $code . '<?');
echo "</p></div></div></div></div>";

?>
</div>
            
        </div>
        
        <div class="bottom_content">

<?php
// now display the rest of the stores
$matches = 0; 
while ($row = mysql_fetch_assoc($rs)) 
{
$matches++;
echo "<div class='left_box'>";
$headline = str_replace('COMPANYNAMEHERE',COMP_NAME,$row['headline']);
echo "<h3>$headline</h3>";
echo "<div class='left_img_box'><a href='/pages/$row[keyword]/'><img src='$row[short_story_image]' alt='Image' title='' height='75' width='75' border='0' /></a></div>";
echo "<div class='left_text_content'><p>";
$code = str_replace('COMPANYNAMEHERE',COMP_NAME,$row['short_story']);
eval('?>' . $code . '<?');
echo "</p></div></div>";
}  

?> 

[Pikachu2000]

Also, be careful with using ORDER BY RAND(). On a large table ( > 10,000 records or so ), it can be prohibitively slow. Google "why order by rand() is bad" for more info.

[bschultz]

Thanks everyone!