Jump to content

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


bschultz

Recommended Posts

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!

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>";
}  

?> 

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.

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.