Recent Articles For Center Block

[IchBin]

This for a php block. I just hashed out the code with some of the SMF guru's if this doesn't work out for you. Here's the code Jay, Daniel, and I came up with.

Code Select Expand


global $db_prefix, $scripturl;

$articles = db_query("
        SELECT art.date, art.author, art.subject, art.id, var.value1 AS category_name
        FROM {$db_prefix}tp_articles AS art
                LEFT JOIN {$db_prefix}tp_variables AS var ON (var.id = art.category)
        LIMIT 5", __FILE__,__LINE__);

echo '
<table cellspacing="0" cellpadding="0" width="100%" style="margin-top: 4px; margin-bottom: 4px;" border="0" class="tborder">';

if (mysql_fetch_assoc($articles) == 0)
        echo '
<tr>
<td>No Reviews Available</td>
</tr>';
else
{
        while ($row = mysql_fetch_assoc($articles))
                echo '
<tr>
<td>
<a href="', $scripturl ,'?page=', $row['id'], '"><b>', $row['subject'], '</b></a>
<div style="padding: 8px; padding-top: 0px;">
By : <b>', $row['author'], '</b><br />
Created on: ', date("m/d/y", $row['date']), '<br />
Category: ', $row['category_name'], '
</div>
</td>
</tr>';


}
echo '
</table>';

mysql_free_result($articles);

[JPDeni]

Right. You'll need to edit the URLs for the images, and add other images. You'll also need to add formatting to make things look the way you want them to... the "divs" and such. And you'll need to add your introductory text to the block. (I just had to edit my code because I forgot to put the img tags in. Minor detail. )

This would go into a php block.

IchBin has done an exceptional job on this, especially for someone without much programming background.

[IchBin]

Well, I had to have Jay and Daniel explain the whole "JOIN" thing lol. They also schooled me in the "proper" way of indent and layout of programming. haha

[othersi]

Thanks IchBin... your code seems the closest to working right out of the gate.  It's a different layout than our mockup, but it does bring in all the relevant info.

(I can't get JPDeni's to work for some reason... it just brings in an empty "table", except that the date column is all filled in with 12.31.69)

I've tried IchBin's code in a PHP block, without editing anything, and it's only bringing in 4 articles (I assume the LIMIT 5 is supposed to bring in 5 articles?)... all from a single category ... and it's bringing them in from oldest first.

ie.  The 4 articles that get brought in are all from the 'music' category (category=27), which contains 8 articles... the code 'as is' is bringing in the 4 oldest articles from that category, listed oldest first.

[othersi]

Okay... I added an ORDER BY date DESC which fixed which articles were being called.

And for some reason, the LIMIT has to be set one higher than what is called.  ie.  to bring in 5 articles, it needs to be set to LIMIT 6.

Having done that, it is now bringing in all of the relevant info, though the categories are text, not the associated images.  I imaging mingling JPDenis changing thing where he changes the category integer to an image would work... if I can figure it out.

[othersi]

We are very, very close here.

I've got the code almost right where we want it.

Here's a screenie:




Two things I could still use a hand on:

ONE:

There is still a problem with how it's grabbing articles.

Here is the code as we've got it now:

Code Select Expand


global $db_prefix, $scripturl;

$articles = db_query("
        SELECT art.date, art.author, art.subject, art.id, var.value1 AS category_name
        FROM {$db_prefix}tp_articles AS art
                LEFT JOIN {$db_prefix}tp_variables AS var ON (var.id = art.category)
      ORDER BY date DESC LIMIT 5", __FILE__,__LINE__);


echo '
<div align="center">
      <center>

<table cellpadding="0" cellspacing="0" border="0" width="519" style="border-collapse: collapse" bordercolor="#111111">
<tr valign="top">
<td bgcolor="#101018" colspan="4">
    <img src="http://www.othersi.com/Themes/default/assets/images/header-reviewpost.jpg" alt="Review Post" width="519" height="24"></td></tr>

<tr valign="top">
<td class="lineleft2">
    <img src="http://www.othersi.com/Themes/default/assets/images/spacer.gif" alt="spacer" width="11" height="1"></td>
<td class="mainbg">
    <img src="http://www.othersi.com/Themes/default/assets/images/spacer.gif" alt="spacer" width="386" height="12"><br>

<img src="http://www.othersi.com/Themes/default/assets/images/title-newestreviews.gif" alt="Newest Reviews" width="390" height="22" align="left"><br><br>


<div align="left">
The 5 most recent reviews are listed below. For our complete listing of reviews,
please visit the <a href="http://www.othersi.com/index.php?cat=15">Review archives</a>.</p>

        <table cellpadding="2" cellspacing="0" border="0">
        <tr valign="top">
        <td width=100%>
        <img src="http://www.othersi.com/Themes/default/assets/images/post-title.gif" alt="Title" width="190" height="13"></td>
        <td>
        <img src="http://www.othersi.com/Themes/default/assets/images/post-date.gif" alt="Date" width="54" height="13"></td>
<td>
    <img src="http://www.othersi.com/Themes/default/assets/images/post-format.gif" alt="Format" width="89" height="13"></td></tr>';


if (mysql_fetch_assoc($articles) == 0)
        echo '
<tr>
<td><b>No Reviews Available</b></td>
</tr>';

else
{
        while ($row = mysql_fetch_assoc($articles))
                echo '

                   <tr>
                          <td>
<a href="', $scripturl ,'?page=', $row['id'], '"><b>', $row['subject'], '</b></a></td>
         <td> ', date("m/d/y", $row['date']), '</td>
<td align="right">', $row ['category_name'], '</td>
</tr>';

}

echo '
</table>

<img src="http://www.othersi.com/Themes/default/assets/images/spacer.gif" alt="spacer" width="1" height="10"><br></td>
       
<td class="mainbg">
        <img src="http://www.othersi.com/Themes/default/assets/images/spacer.gif" alt="spacer" width="10" height="1"></td>
        <td class="linerightm">
        <img src="http://www.othersi.com/Themes/default/assets/images/spacer.gif" alt="spacer" width="3" height="1"></td></tr>

        <tr valign="top">
        <td colspan="4">
        <img src="http://www.othersi.com/Themes/default/assets/images/footer-home.gif" alt="spacer" width="100%" height="3"></td></tr>
        </table>

        </center>
</div>';



mysql_free_result($articles);


(most of the code is just setting up the formatting... the meat of the code is a modified version of what IchBin posted up above)

As you can see, in the code, the LIMIT statement is set to 5.  As you can see in the screenshot, it's only grabbing 4 articles.

If I change the LIMIT to 6, then 5 articles show.

BUT... what it's doing is ignoring the most recent article.

So, in the case of the screenshot above , the Zelda review is actually the *second* newest review.  The newest one happens to be a Battlestar Galactica article.

If I submit *another* article/review, suddenly Battlestar Galactica shows up, the other reviews all get bumped down 1... but the new review I just submitted/approved does not show.

As a test, I tried the code exactly as IchBin posted it, just adding an ORDER BY date DESC, right before the LIMIT 5" statement... and it behaves the same way... it pulls 4 articles, missing the most recent, listing the next 4.


What I discovered is the 'if (mysql_fetch_assoc($articles) == 0)' part was causing the problem.  If I take that bit (and the associated 'else' coding) out... and just use the code assuming there always will be at least 1 article, it works properly.

This isn't a HUGE deal... as there should always be an article present... but if someone can suggest a fix so that the "if (mysql_fetch_assoc($articles) == 0)" part will work, I would use it.



And TWO:

We're wanting to give some separation between each subsequent row in the review listing.

My initial idea, as seen in the initial screenshot I posted a while ago (http://www.othersi.com/storage/reviewblock.jpg) was to use alternating backgrounds in alternating rows.  This was setup in the mockup by a co-admin using tr class=row1 and tr class=row2 statements (calling on a custom stylesheet).

Deciding simpler might be better, I figured I could create a small line image... just a 1 pixel-high line that fits nicely across the table, and have that insert in between each row of the review listings.  Problem is... I can't figure out how to get the image in between the rows... seems no matter where I try to put the image tag, it ends up putting 5 copies of the image ABOVE the review listings, rather than in between the listings.



Any help on either of these two issues would be greatly appreciated!

[Byakko89]

alguien que hable estañol y sepa de este tema me podria ayudar con esto ??

[G6Cad]

You have to write in english in this board.

[gffb]

I would really like to use this block snippet on my board has a fix for the block not showing the most recent artice been found and does is show articles in date order?

[edit] I used the code posted on page 2 this worked for me

Code Select Expand

$articles=db_query("SELECT id,subject,intro,author FROM smf_tp_articles WHERE approved=1 AND off=0 ORDER BY date DESC", __FILE__,__LINE__);
$cnt=0;
While ($row=mysql_fetch_row($articles))

{
   // The number of articles you want displayed
   if ($cnt < 5) {
    echo '<table cellspacing="0" cellpadding="0" width="100%" style="margin-top: 4px;
             margin-bottom: 4px;" border="0" class="tborder"><tr>';
    echo '
    <a href="http://www.othersi.com/index.php?page=' . $row[0] . '"><b>' . $row[1] . '</b></a>
     <div style="padding: 8px; padding-top: 0px;">
By : <b>' . $row[3] . '</b></div>

</td>';
     echo '</tr></table>';
    }
   $cnt++;
}
If  ($cnt=0) {
    echo '<td>No Reviews Available';
    }

[planetricki]

How would you use this code outside of TP/SMF? I'd like include recent articles or call a specific article to include on a mostly static php page using the php include code.

Any ideas? Thanks!