SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot
    Join Date
    Jan 2004
    Location
    Cape Cod
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question about formating a date

    Hi All,
    I have written the following query to pull the most recently uploaded date and picture from the database it works great but I can not figure out how to format the date. Anty help would be great.

    Code:
    	$dailyUsersSQL = "SELECT p.user_id, 
                                     MAX(p.date) AS maxDate,   
                                     MAX(p.photo_name) AS photo_name, 
                                     u.f_name, 
                                     u.l_name, 
                                     u.username, 
                                     u.page_title, 
                                     u.account_id
    			FROM photos as p
    				INNER JOIN
    					users AS u
    					     ON u.id = p.user_id
    						WHERE u.account_id = 4
    							GROUP BY p.user_id
    							ORDER BY maxDate DESC ";
    I thought I could do it with the MAX(p.date) but when I do this it messes up the order and does not pull the last date anymore.

    Thanks for any help
    Don
    Last edited by Donniep; May 31, 2008 at 17:34. Reason: Added another question

  2. #2
    SitePoint Zealot
    Join Date
    Jan 2004
    Location
    Cape Cod
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm sorry I figured it out right after I posted this. If anyone has any comments or better ways to go about this please let me know.
    thanks
    Don

  3. #3
    SitePoint Zealot
    Join Date
    Jan 2004
    Location
    Cape Cod
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK so I have another question.

    What I want to do is pull the newest photo and the newest date and from a photos table and join the user table on that to get the other info I need to build the page. This query works as far as getting one user and one photo. the problem seems to be that if I select the MAX(p.photo_name) it is select the two digit number like 69 as the highest number instead of 561 Which is actually the newest photo. Is there a way to fix this?

    Or is there a way with the query to pull the row with the newest date something like select all the stuff I need and then say where date = the newest date?

    Any help is greatly appreciated.

    Thanks
    Don

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    yeah, you can do a lot of stuff with the right sql

    what do you actually want? the latest picture?

    or the latest picture for a particular user?

    or for all users with a specific account?

    the exact query depends on the requirement
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Jan 2004
    Location
    Cape Cod
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for offering to help. What I am looking to do is join the users table to the photos table. I want the most recent row in the photo table for each user with a certain account type. Let me know if you need any more info.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT p.user_id
         , p.date 
         , p.photo_name
         , u.f_name
         , u.l_name
         , u.username
         , u.page_title
         , u.account_id
      FROM users AS u
    INNER
      JOIN ( SELECT user_id
                  , MAX(date) AS max_date
               FROM photos 
             GROUP
                 BY user_id ) AS m
        ON m.user_id = u.id
    INNER 
      JOIN photos AS p
        ON p.user_id = u.id
       AND p.date = m.max_date
     WHERE u.account_id = 4
    ORDER 
        BY p.date DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Jan 2004
    Location
    Cape Cod
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks so much for your help! That works perfectly. Just when I think that I am starting to get this stuff a problem like this comes up and I realize how little I know. I really appreciate all the help this forum has given me.


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
  •