SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Wizard silver trophy
    beley's Avatar
    Join Date
    May 2001
    Location
    LaGrange, Georgia
    Posts
    6,117
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    A few PHP questions... Date function, etc.

    I've been working on a script to pull data from an existing MySQL database (a vBulletin database) to display on my site. I have a news forum in vBulletin and want to display the 10 newest headlines and hopefully some text of the post.

    Two issues... how do I get the text from the post (using VB 2.2.8) and can I reformat the DATE.

    Here is the code I have so far:
    PHP Code:
    <?php

    $dbcnx 
    = @mysql_connect('localhost''xxxxx''xxxxx');
    if (!
    $dbcnx) {
        echo( 
    '<p>Unable to connect to the database server at this time.</p>' );
        exit();
        }

    if (! @
    mysql_select_db(cdhost_forums) ) {
        die( 
    '<p>Unable to locate the database at this time.</p>' );
        }
    ?>

    <p><h3>Latest News</h3></p>

    <?php

    $result 
    mysql_query('SELECT forumid, threadid, title, FROM_UNIXTIME(dateline) AS dateline FROM thread WHERE forumid="9" ORDER BY threadid DESC LIMIT 10');

    while ( 
    $row mysql_fetch_array($result) ) {
        echo(
    '<p>' $row['dateline'] . '<br>' '<a href=http://forums.cdhost.com/showthread.php?s=&threadid=' $row['threadid'] . '>' $row['title'] . '</a></p>');
        }

    ?>
    It works great... http://www.cdhost.com/news.php
    But as you can see, the date is displayed in a way that's not very easy to read. And as I said, I'd love it if I could have the first XXX characters of the post.

    Any suggestions? Thanks!

  2. #2
    Non-Member Icheb's Avatar
    Join Date
    Mar 2003
    Location
    Germany
    Posts
    1,474
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can use FROM_UNIXTIME with a format argument, which automatically formats the time according to your needs.

    http://www.mysql.com/doc/en/Date_and...functions.html
    Check for FROM_UNIXTIME for the specific syntax and DATE_FORMAT on which arguments are allowed.

  3. #3
    SitePoint Wizard silver trophy
    beley's Avatar
    Join Date
    May 2001
    Location
    LaGrange, Georgia
    Posts
    6,117
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Thanks... I'll try to figure that out. Anyone have any suggestions about getting some of the post from the VBulletin thread?

  4. #4
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Presumably the column "dateline" is a unix timestamp. If so, I recommend using PHP to format it e.g.

    PHP Code:
    $result mysql_query('SELECT forumid, threadid, title, dateline FROM thread WHERE forumid="9" ORDER BY threadid DESC LIMIT 10'); 

    while ( 
    $row mysql_fetch_array($result) ) { 
        echo(
    '<p>' date('jS m Y H:i:sa,$row['dateline']) . '<br>' . '<a href=http://forums.cdhost.com/showthread.php?s=&threadid=' . $row['threadid'] . '>' . $row['title'] . '</a></p>'); 
        


    ?> 

  5. #5
    Non-Member Icheb's Avatar
    Join Date
    Mar 2003
    Location
    Germany
    Posts
    1,474
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If it can be done by the MySQL server, why not let it do it the time formatting?

  6. #6
    "Of" != "Have" bronze trophy Jeff Lange's Avatar
    Join Date
    Jan 2003
    Location
    Calgary, Canada
    Posts
    2,063
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    as for the text, you can use:

    Code:
    SELECT
         post.pagetext,
         post.postid,
         thread.forumid,
         thread.threadid,
         thread.title,
         thread.dateline
    FROM
         post,thread
    WHERE
         thread.forumid=9 AND
         thread.threadid=post.threadid
    GROUP BY
         post.threadid
    ORDER BY
         post.postid DESC,
         thread.threadid DESC
    LIMIT 0,10
    And then you use

    PHP Code:
    date('jS m Y H:i:sa'$row['dateline']); 
    like HarryF mentioned.

    (I haven't tested this, but something similar to that query should work.)
    Who walks the stairs without a care
    It shoots so high in the sky.
    Bounce up and down just like a clown.
    Everyone knows its Slinky.

  7. #7
    ********* Member website's Avatar
    Join Date
    Oct 2002
    Location
    Iceland
    Posts
    1,238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    HarryF, you missed a ' in the date() function I belive.

    Icheb, Well, I would do as HarryF suggests to, why, you ask, because I want my templates to control how the date is shown, say I want to change the date format the I would hate to locate this single query somewhere in my code to change it.

    To answer beley's second question, I would add 'pagetext' to the SELECT query and then I would check out this thread or to be more precise, this post.

    So you could use this:
    PHP Code:
    echo('<p>' $row['dateline'] . '<br>' '<a href=http://forums.cdhost.com/showthread.php?s=&threadid=' $row['threadid'] . '>' $row['title'] . '</a></p><p>' substr($row['pagetext'], 0strrpos(substr($row['pagetext'], 0100),' ')) . '</p><hr />'); 
    or something like that (also replace 100 with any number you like).

    Hope that helps!

    Edit:

    Edited because of Jeff's reply
    Last edited by website; Jun 1, 2003 at 16:14.
    - website

  8. #8
    "Of" != "Have" bronze trophy Jeff Lange's Avatar
    Join Date
    Jan 2003
    Location
    Calgary, Canada
    Posts
    2,063
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    website, check out my post right above yours, the column name is pagetext.

    I tested my query, and it does what you need, you can use the date() function to format dateline, and use the substr() method website posted, or use the MySQL SUB_STR() function (although, website's is better, because it takes words into account).
    Who walks the stairs without a care
    It shoots so high in the sky.
    Bounce up and down just like a clown.
    Everyone knows its Slinky.

  9. #9
    SitePoint Wizard silver trophy
    beley's Avatar
    Join Date
    May 2001
    Location
    LaGrange, Georgia
    Posts
    6,117
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Thanks for all your help guys... I got most everything working great thanks to your help. Check it out so far:

    http://www.cdhost.com/newstest.php

    One thing though, how do I limit the amount of text that is displayed? Can I limit it to 50 or 100 characters? Thanks! (PS - below is the code I have so far)

    PHP Code:
    <?php

    $result 
    mysql_query('SELECT post.pagetext, post.postid, thread.forumid, thread.threadid, thread.title, thread.dateline FROM post,thread WHERE thread.forumid=9 AND thread.threadid=post.threadid GROUP BY post.threadid ORDER BY post.postid DESC, thread.threadid DESC LIMIT 0,10');

    while ( 
    $row mysql_fetch_array($result) ) {
        echo(
    '<p>' date('F j, Y'$row['dateline']) . '<br>' '<b><a href=http://forums.cdhost.com/showthread.php?s=&threadid=' $row['threadid'] . '>' $row['title'] . '</a></b><br>' $row['pagetext'] . '...' '</p>');
        }

    ?>

  10. #10
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    See my post in this thread Brandon

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  11. #11
    SitePoint Wizard silver trophy
    beley's Avatar
    Join Date
    May 2001
    Location
    LaGrange, Georgia
    Posts
    6,117
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    That worked perfectly, thanks Sean! If you're interested, the final page is here. The code I used is below:

    PHP Code:
    <?php

    $result 
    mysql_query('SELECT post.pagetext, post.postid, thread.forumid, thread.threadid, thread.title, thread.dateline FROM post,thread WHERE thread.forumid=9 AND thread.threadid=post.threadid GROUP BY post.threadid ORDER BY post.postid DESC, thread.threadid DESC LIMIT 0,10');

    while ( 
    $row mysql_fetch_array($result) ) {
        echo(
    '<p>' date('F j, Y'$row['dateline']) . '<br>' '<b><a href=http://forums.cdhost.com/showthread.php?s=&threadid=' $row['threadid'] . '>' $row['title'] . '</a></b><br>' substr($row['pagetext'],0,strrpos(substr($row['pagetext'], 0250),' ')) . '...' '</p>');
        }

    ?>

  12. #12
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Getting all of the text and only using part of it isn't very efficient Also, you may want to parse the text through the vB code function, you've got some vB tags in your news

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  13. #13
    SitePoint Wizard silver trophy
    beley's Avatar
    Join Date
    May 2001
    Location
    LaGrange, Georgia
    Posts
    6,117
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Yeah, I just started another thread about the VB code... any ideas? If you can't tell, I'm a PHP newbie


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •