Recent Articles For Center Block

[othersi]

Hi all.

This code is sooo close to what I'm looking for, but could use some tweaks to get me exactly where I want it... and I can't figure it out.  Hoping someone can help me out here.

I took the first code posted, by opschf, altered the database table name and url's to match my setup, stripped it down a bit, and put it in a php block.

In the block I turned off title/frame, as we want to build our own frame around it.

I've got it to show the most recent Article titles, and the author, but can't figure out how to get it to display the article category or date along with it.  I tried fitting the 2nd code posted by opschf into the mix to show the category, but couldn't get it to work... kept getting a parse error.

To show what we're trying to accomplish, and were we are right now, here is a screenshot.



The lower portion is a "mockup" block that was created, in a script block.  This is what we are trying to accomplish.  This mockup block has the article info entered manually, but of course, we want to use a modified version of the code in this thread to do the work for us automatically.  The mockup block is, as I said, in a script block, not a php block as the code in this thread is... and it (the mockup block) has title/frame disabled, calling on a custom style sheet, custom images, etc to achieve the look it has.

The upper block of the screenshot is the result of the code I have so far, modified from this thread, running in a php block, with title/frame disabled.  As you can see... not quite there.  lol

I tried copying the calls to the stylesheet from the mockup block into this php block, but it caused errors... so obviously php blocks and script blocks don't work the same way... so what I may need is for this code to be altered so it will work in a script block... in order to allow us to then setup the style the way we want it.

The code used in the PHP block (modified from this thread) is:

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

So, could someone help me out with what I need to do to get from where I am now to where I want to be?  I don't necessarily need the article author to appear in the block (not sure whether I want that or not)... but if it is included in the code, I can easily remove it later.

What we do want is for the code to call up the article title of the latest X number of articles, along with the date posted, and the category ... it would be cool if it would call up the category icon as in the screenshot mockup block, but if that's not possible, just the text description of the category, possibly as a link to that category, would be fine.  And if the article author is also called, that's cool too... I can decide later if I want to keep that or not.

All this needs to work in a script block (I think) so that we can then add in our calls to our own style-sheet to format the block as seen in the screenshot mockup block.

If someone could post the script code we need to pull the information in the right way, we can take it from there and build the formatted look around it.

Thank you very much in advance!

[IchBin]

Well I don't know javascript at all so sorry about that. But to add the date you can just add 'date' to your database call.

SELECT id,subject,intro,author,date

You could then call it from the array with ', date($row[4], date_format_here),'

Refer to http://us2.php.net/date for the date format of your choice.

To add the Article category is a little beyond what I'm able to figure out without getting deep into it. You'd probably have to join the table on ID of articles and Category of the variables table if possible and pull the article category name from the tp_variables table, if you didn't want to create a seperate call. I don't know SQL commands well at all or I'd give it a shot.

[othersi]

Thanks, that's one step closer.

Could you explain how the connection is made between the # in the $row statement and the actual data?

eg.  Why does row[4] pull the date from the tp_article table?  When I view the table in phpmyadmin, DATE is not the 4th row.

EDIT:  Ahh... I think I figured it out... maybe?  The # in the $row call is referring to what is being called in the SELECT statement?  So in this case, there are 5 things called in the SELECT statement, with them being 'assigned' 0,1,2,3,4 ?

2nd EDIT:  I can't seem to get the date formatting to work.

I'm trying to get in the form of month.day.year (eg. today would be 01-05-07).

I've used:  ', date($row[4], "m.d.y"),' , but it returns the dates as long strings of numbers... eg. 1168029360

[IchBin]

Yes that long string of numbers is what is called Unix time. It has to do with the total amount of seconds since 1979 or something like that. I can't remember exactly. lol

Nice job on figuring out how the array works. :up:

That looks like it should be working, but I'm not a php guru yet. I'll take a look when I get home here in a bit cuz I'm still at work.

[othersi]

I appreciate your time and help.

[IchBin]

Oops... I had it backwards. You put the time integer first, and then the date format like this:

date('m.d.y', $row[4])

I'll see what I can come up with for a Category call. I don't want to make another database call. But as I said, I'm no guru so I'm going to see if I can figure it out.

[JPDeni]

Can I butt in?

You can add the category to the list of columns returned from the search:

Code Select Expand


SELECT id,subject,intro,author,date, category


If you know the number of the category that corresponds to the image you want to display for Playstation, DVD, CD, etc, you can just use an array to display it.

[IchBin]

But category only holds an integer, the name of the category is stored in the variables table. Please enlighten me more, because I'm working on a very complex SQL call right now lol.

[JPDeni]

But category only holds an integer

Right. But it's pretty easy to define the category name or icon based on the integer without pulling it from the database. The example othersi gave shows an image for the category, which would have to be defined anyway.

Here's how I would create what he has in his example:

Code Select Expand


global $db_prefix, $scripturl;

$cnt = 5; // the number of articles you want to display

$format[1] = '<img src="http://url/to/playstation.jpg">'; // change number to playstation category
$format[2] = '<img src="http://url/to/dvd.jpg">'; // change number to dvd category
$format[3] = '<img src="http://url/to/cd/jpg">'; // change number to cd category

$articles=db_query(
    "SELECT id, subject, date, category
     FROM {$db_prefix}tp_articles
     WHERE approved=1
     AND off=0
     ORDER BY date DESC
     LIMIT $cnt", __FILE__,__LINE__);

echo '<table><tr><td>TITLE</td><td>DATE</td><td>FORMAT</td></tr>';
while ($row=mysql_fetch_row($articles))
{
  echo '<tr><td><a href="', $scripturl , '?page=' , $row['id'] , '"><b>' , $row['subject'] , '</b></a></td>';
  echo '<td>' , date('m.d.y', $row['date']) , '</td>';
  echo '<td>' , $format[$row['category']] , '</td></tr>';
}
echo '</table>';


Of course, I didn't add formatting and intros and that sort of stuff in there, but this would print out the information needed.

I find it preferable to use the column names rather than the numbers because there's more flexibility. If you change the code, you don't have to remember to change all the references to numbers. Also, it's easier to see what you're referring to.

[othersi]

You guys are awesome.  You have no idea how cool I find this that there are gurus like you out there willing to put time into other peoples problems.

JPDeni... if your idea ends up being the best solution... just wondering what parts of it I need to edit. 

Obviously I need to edit the URL's to the appropriate category image urls.  I also would need to add more $format[_] lines, one for each category (the example in my earlier screenshot only shows three categories, while there will be probably 6 or so when we're done).

Does anything else need to be edited?

Also... is this for a php block, or a script block?