Professional Web Applications Themes

Displaying entry until specified date problem - MySQL

I have a mysql table (`news`) set up to hold news articles. It has 6 fields: `ID` (smallint - auto increment), `start` (date), `end` (date), `headline` (varchar 255), `newsitem` (text), and `imp` (varchar 255 - can be null). I want to get the headlines pulled from the table and displayed on a page. I want them to display in ascending order by end date and sub-ordered by the imp field until the date after the end date. The imp field is used to push items that are her off than the 8 selected to the top of the list for ...

  1. #1

    Default Displaying entry until specified date problem

    I have a mysql table (`news`) set up to hold news articles. It has 6
    fields: `ID` (smallint - auto increment), `start` (date), `end` (date),
    `headline` (varchar 255), `newsitem` (text), and `imp` (varchar 255 -
    can be null). I want to get the headlines pulled from the table and
    displayed on a page. I want them to display in ascending order by end
    date and sub-ordered by the imp field until the date after the end date.
    The imp field is used to push items that are her off than the 8
    selected to the top of the list for extra exposure.

    Example: headline of "Free coffee Sept 28" carries an end date of
    2006-09-28. I want that to stop displaying when today's date turns to
    2006-09-29.

    I can get the headline to display on the page properly but I seem to
    have a mistake somewhere in getting it to stop displaying as of the
    29th. The code I'm using is:

    <?
    mysql_connect("$dbhost","$dbuser","$dbpass"); //(host, username, password)
    mysql_select_db("$dbase") or die("Unable to select database"); //select
    database

    $now = date("Y-m-d"); //today's date

    $query = "SELECT * FROM `news` WHERE ($now < `end`) ORDER BY `imp`,`end`
    LIMIT 8";

    $result = mysql_query($query) or die(mysql_error());

    //and then process the results.
    ?>

    But the item that should have ceased being displayed on the 29th is
    still displaying. What do I need to do to correct this?
    JackM Guest

  2. #2

    Default Re: Displaying entry until specified date problem


    JackM wrote: 

    Hmm, you know MySQL knows when now is. Try this:

    $query = "SELECT * FROM `news` WHERE (now() < `end`) ORDER BY
    `imp`,`end`
    LIMIT 8";

    Otherwise maybe try sticking $now into inverted commas: 

    strawberry Guest

  3. #3

    Default Re: Displaying entry until specified date problem

    strawberry wrote:
     [/ref]
     

    Well, duh! It never even occured to me that mysql knew when now is.
    <slaps forehead> I'm so used to doing $now = mktime(); php statements
    that I just carried it over to the mysql syntax. Thanks for putting me
    straight on this. Your solution works perfectly and a lesson has been
    learned.
    JackM Guest

Similar Threads

  1. calendar date entry
    By joeny in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 0
    Last Post: March 25th, 10:42 PM
  2. displaying the date
    By cfkerry in forum Macromedia ColdFusion
    Replies: 4
    Last Post: March 4th, 10:01 PM
  3. recordset displaying 'double entry' issue.
    By Miranda johnsen in forum ASP Database
    Replies: 12
    Last Post: February 24th, 01:58 AM
  4. Dependent queries in Access and Entry Date
    By Marco Alting in forum ASP
    Replies: 0
    Last Post: September 4th, 07:58 AM
  5. Update Record only displaying first entry of database in the details page
    By Jack Le webforumsuser@macromedia.com in forum Macromedia Dreamweaver
    Replies: 0
    Last Post: July 10th, 08:35 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139