SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Zealot Nate's Avatar
    Join Date
    Sep 2001
    Location
    BC, Canada
    Posts
    109
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    getting everything from one day when time() is used

    I have a mysql table in which I have my news (and other stuff for different tables, we'll just use this as an example), and I want to have the news under heading for each date. I just don't know how to do this with while(). I don't even know where to begin.

    How is this done by most people? Also, how can I do a query and get all the rows from a certain using a 'post_time' field, which contains the time() of the post?
    NATHAN WRIGHT
    PHP Developer, Simple Station

  2. #2
    SitePoint Wizard TWTCommish's Avatar
    Join Date
    Aug 1999
    Location
    Pittsburgh, PA, USA
    Posts
    3,910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do you have a field in the table containing a timestamp for each entry? If so, the loop wouldn't be too tricky...

  3. #3
    SitePoint Zealot Nate's Avatar
    Join Date
    Sep 2001
    Location
    BC, Canada
    Posts
    109
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Its not really a timestamp, its just a VARCHAR field with PHP's time() output in it. Should I be using TIMESTAMP? If so, how does that change how I convert it to a regular date, can I still use date()?
    Last edited by Nate; Mar 22, 2002 at 00:39.
    NATHAN WRIGHT
    PHP Developer, Simple Station

  4. #4
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by Nate
    Its not really a timestamp, its just a VARCHAR field with PHP's time() output in it.
    ouch! if you're storing time()'s output, you shouldn't use VARCHAR, but INT UNSIGNED.

    time() IS a timestamp. however, it's a Unix timestamp, which differs from MySQL's TIMESTAMP (i'd probably use DATETIME) column.

    i would use DATE/DATETIME/TIMESTAMP instead of storing time()'s output, unless you are trying to maintain compatibility with databases other than MySQL. if you use one of MySQL's date types, you can use MySQL's DATE_FORMAT() function instead of PHP's date().


    do you want news displayed like this?:

    March 22
    News 1
    News 2
    etc

    March 23
    News 4
    News 5

    in other words, displaying multiple items under a single date heading? in that case, regardless of your column type (as long as it's NOT VARCHAR), you should be able to do something like the following pseudo "code".

    Code:
    SELECT stuff FROM news ORDER BY time DESC
    
    $current_day = '';
    
    while ($row = mysql_fetch_array)
    {
    	// If the day from the current row does not match $current_day,
    	// we're on a new day and should display the heading
    	if ($current_day != $row[day]) { echo $row[day]; }
    
    	// Update $current_day for the next time through the loop
    	$current_day = $row[day];
    
    	// Display the actual news stuff
    	echo $row[other_stuff];
    }
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  5. #5
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Have no fear though if you already have tons of records and don't want to change the field type, you can use FROM_UNIXTIME(varcharfield) to convert the time() into something MySQL knows how to use in its date/time functions.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  6. #6
    SitePoint Zealot Nate's Avatar
    Join Date
    Sep 2001
    Location
    BC, Canada
    Posts
    109
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't really have any records in the db yet, I'm structuring everything before I go to work on the bulk of the PHP.

    Thanks for that code sample, it's so simple I feel like an idiot now
    NATHAN WRIGHT
    PHP Developer, Simple Station

  7. #7
    SitePoint Zealot Nate's Avatar
    Join Date
    Sep 2001
    Location
    BC, Canada
    Posts
    109
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have done some more reading on TIMESTAMP, DATETIME, and DATE, and I'm going to have to go with INT, because I don't want it to change when the record is modified. What I don't understand is what the difference between an unsigned and a signed INT is. Is there anything different that I have to do with PHPmyAdmin to get an unsigned INT field?
    NATHAN WRIGHT
    PHP Developer, Simple Station

  8. #8
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Uh only timestamp is updated when inserting and updating records. DATETIME and DATE types will not change automatically. Try reading over that part of the manual again.

    http://mysql.he.net/documentation/my....html#DATETIME

    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  9. #9
    SitePoint Zealot Nate's Avatar
    Join Date
    Sep 2001
    Location
    BC, Canada
    Posts
    109
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks. Must have missed that part, I think I read a different bit. I blame it on the very large page, NOT my stupidity
    NATHAN WRIGHT
    PHP Developer, Simple Station

  10. #10
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by Nate
    What I don't understand is what the difference between an unsigned and a signed INT is. Is there anything different that I have to do with PHPmyAdmin to get an unsigned INT field?
    even if you're not going to use INT, i'll explain UNSIGNED. it just doesn't allow negative numbers, which effecitively doubles your postive storage range. for example, the maximum value that TINYINT can hold is 127. however, TINYINT UNSIGNED goes up to 255, with the same storage requirement. i always make INT-family columns UNSIGNED if i don't need negative values. just as you should make all columns NOT NULL, unless you specifically need NULL.

    as far as making a column UNSIGNED in phpMyAdmin, i dunno since i don't use it for creating tables. it should be in the column attributes or something.


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
  •