bschultz Posted July 10, 2010 Share Posted July 10, 2010 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! Link to comment https://forums.phpfreaks.com/topic/207369-getting-all-mysql-resultsand-making-one-the-featured-story/ Share on other sites More sharing options...
Pikachu2000 Posted July 10, 2010 Share Posted July 10, 2010 Store the id of the randomly selected story in a variable, then specifically exclude it from the second result set. Link to comment https://forums.phpfreaks.com/topic/207369-getting-all-mysql-resultsand-making-one-the-featured-story/#findComment-1084151 Share on other sites More sharing options...
wildteen88 Posted July 10, 2010 Share Posted July 10, 2010 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>"; } ?> Link to comment https://forums.phpfreaks.com/topic/207369-getting-all-mysql-resultsand-making-one-the-featured-story/#findComment-1084156 Share on other sites More sharing options...
Pikachu2000 Posted July 10, 2010 Share Posted July 10, 2010 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. Link to comment https://forums.phpfreaks.com/topic/207369-getting-all-mysql-resultsand-making-one-the-featured-story/#findComment-1084163 Share on other sites More sharing options...
bschultz Posted July 10, 2010 Author Share Posted July 10, 2010 Thanks everyone! Link to comment https://forums.phpfreaks.com/topic/207369-getting-all-mysql-resultsand-making-one-the-featured-story/#findComment-1084176 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.