Recent Articles For Center Block

[IchBin]

oops. Try var.value1 instead of category_name

[Chipicao]

Thanks, it works now! One more thing please. The newest article isn't shown, only from the second one till the last. How can I make the firs one appear?

[IchBin]

As far as I can tell it should be getting it. Is it on and approved?

[Chipicao]

Yes, the article is active. It appears on the recent list only after another article is posted, but again, the new article isn't listed.

Here is the exact code I use, if it helps:

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)
WHERE var.value1  = 'Teste si prezentari' AND approved=1 AND off=0
ORDER BY art.date DESC
        LIMIT 15", __FILE__,__LINE__);

echo '
<table cellspacing="0" cellpadding="0" width="100%">';

if (mysql_fetch_assoc($articles) == 0)
        echo '
<tr>
<td>No Reviews Available</td>
</tr>';
else
{
        while ($row = mysql_fetch_assoc($articles))
                echo '
<tr>
<td><font size="1">
<a href="', $scripturl ,'?page=', $row['id'], '"><b>• ', $row['subject'],

'</b></a>
</font></td>
</tr>';


}
echo '
</table>';

mysql_free_result($articles);

You can see it here: http://dev.sayit.ro
I've even tried removing AND approved=1 AND off=0, but it still doesn't work.

[Chipicao]

I have one more issue (unfortunately). I want to show articles from 4-5 categories, not just one. I thought that if I make the categories I want subcategories of one "main" category it will work. It doesn't! How can I work around this?

L.E. Solved it, thanks to a friend.
Just use WHERE var.value1 in ('cat1','cat2','cat3','cat4')

I also have a workaround for the last article issue. Just post an empty article, deactivate it and set it's date to 31 December. Don't forget to change the year on January 1st

[jacortina]

Yes, the article is active. It appears on the recent list only after another article is posted, but again, the new article isn't listed.

Here is the exact code I use, if it helps:

Code Select Expand

if (mysql_fetch_assoc($articles) == 0)

Change that line to:

Code Select Expand

if (mysql_num_rows($articles) == 0)

The original code meant that the first row was fetched for the test. After it came up positive, what was left for the 'while' loop was the 2nd through Xth rows.

[gffb]

I also added on the left hand side a block that lists the categories for articles.  Maybe you would find this useful as well...

Code Select Expand

$categories=db_query("SELECT * FROM tportal_variables", __FILE__, __LINE__);
While ($row=mysql_fetch_row($categories)) {
if ($row[4] == "category" && $row[0] != 46) {  // I exclude one category
echo '<a href=http://www.
mywebsite.com/SMF/index.php?cat=' . $row[0] . '>' . $row[1] . '</a><br>';
}
}
echo'
<br>
<br>
<i>More categories will be added as articles are received.<i>';

Let me know what you think of them...   

I have tried using this code but get a 500 server error is there any other code that can list all my article cats in a block.

[IchBin]

Change this line to reflect the right table for variables:

Code Select Expand

$categories=db_query("SELECT * FROM tportal_variables", __FILE__, __LINE__);

Its usually tp_variables and not tportal_variables.

[gffb]

This code still gives 500 server error

Code Select Expand

$categories=db_query("SELECT * FROM tp_variables", __FILE__, __LINE__);
While ($row=mysql_fetch_row($categories)) {
if ($row[4] == "category" && $row[0] != 46) {  // I exclude one category
echo '<a href=http://www.abcunited.net/site/index.php?cat=' . $row[0] . '>' . $row[1] . '</a><br>';
}
}
echo'
<br>
<br>
<i>More categories will be added as articles are received.<i>';

[gffb]

This code still gives 500 server error

Code Select Expand

$categories=db_query("SELECT * FROM tp_variables", __FILE__, __LINE__);
While ($row=mysql_fetch_row($categories)) {
if ($row[4] == "category" && $row[0] != 46) {  // I exclude one category
echo '<a href=http://www.abcunited.net/site/index.php?cat=' . $row[0] . '>' . $row[1] . '</a><br>';
}
}
echo'
<br>
<br>
<i>More categories will be added as articles are received.<i>';

Resolved by changing

Code Select Expand

$categories=db_query("SELECT * FROM tp_variables", __FILE__, __LINE__);
to

Code Select Expand

$categories=db_query("SELECT * FROM smf_tp_variables", __FILE__, __LINE__);