SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Stupid Query Blues

    I know there are some SQL experts here, ie freakysid, Skunk and others. Please help. I am doing a playlist for a radio station and I want to show the last 24 hours of music. Here are my tables.

    playlist
    | SongDate | date | | | 0000-00-00 | |
    | StartTime | time | | | 00:00:00 | |
    | SongLength | varchar(50) | YES | | NULL | |
    | SongID | varchar(50) | | | | |


    master_playlist
    | SongID | varchar(50) | | | | |
    | SongName | varchar(100) | | | | |
    | SongArtist | varchar(100) | | | | |


    So basically right now I am saying grab all records from playlist where the date and time are earlier than now and newer than 24 hours ago. Here is my query:

    PHP Code:
    $s_date date("Y-m-d"time());
    $p_date date("Y-m-d"time() - 86400);
    $s_time date("H:i:s"time());
    $p_time date("H:i:s"time() - 86400);

    $sql sprintf("SELECT p.SongID as pSongID, 
                           DATE_FORMAT(p.SongDate, '%%Y-%%m-%%d') as pSongDate, 
                           TIME_FORMAT(p.StartTime, '%%l:%%i:%%s %%p') as pStartTime,
                           mp.SongName as mpSongName,
                           mp.SongArtist as mpSongArtist 
                    FROM 
                           playlist p,
                           master_playlist mp
                    WHERE 
                           ((p.SongDate = '%s' AND p.StartTime < '%s') 
                    OR
                           (p.SongDate = '%s' AND p.StartTime > '%s'))
                    AND 
                           (p.SongID = mp.SongID)
                    ORDER by 
                           p.SongDate DESC, 
                           p.StartTime DESC"

                           
    $s_date$s_time$p_date$p_time); 
    But, for instance it will show the first three songs then it will repeat one a bunch of times in a row and then continue on here is a sample

    PHP Code:
    9084 2001-06-26 11:18:05 AM Artist289 SongName289
    BREAK23 
    2001-06-26 11:17:58 AM Artist287 SongName287
    BREAK23 
    2001-06-26 11:17:58 AM Artist312 SongName312
    BREAK23 
    2001-06-26 11:17:58 AM Artist86 SongName86
    BREAK23 
    2001-06-26 11:17:58 AM Artist136 SongName136
    BREAK23 
    2001-06-26 11:17:58 AM Artist187 SongName187
    BREAK23 
    2001-06-26 11:17:58 AM Artist391 SongName391
    BREAK23 
    2001-06-26 11:17:58 AM Artist237 SongName237
    BREAK23 
    2001-06-26 11:17:58 AM Artist515 SongName515
    BREAK23 
    2001-06-26 11:17:58 AM Artist37 SongName37
    BREAK23 
    2001-06-26 11:17:58 AM Artist339 SongName339
    8904 
    2001-06-26 11:17:58 AM Artist288 SongName288
    BREAK23 
    2001-06-26 11:17:58 AM Artist563 SongName563
    BREAK23 
    2001-06-26 11:17:58 AM Artist443 SongName443
    BREAK23 
    2001-06-26 11:17:58 AM Artist9 SongName9
    BREAK23 
    2001-06-26 11:17:58 AM Artist161 SongName161
    BREAK23 
    2001-06-26 11:17:58 AM Artist212 SongName212
    BREAK23 
    2001-06-26 11:17:58 AM Artist112 SongName112
    BREAK23 
    2001-06-26 11:17:58 AM Artist365 SongName365
    BREAK23 
    2001-06-26 11:17:58 AM Artist62 SongName62
    BREAK23 
    2001-06-26 11:17:58 AM Artist262 SongName262
    BREAK23 
    2001-06-26 11:17:58 AM Artist470 SongName470
    BREAK23 
    2001-06-26 11:17:58 AM Artist417 SongName417
    8904 
    2001-06-26 11:17:58 AM Artist24 SongName24
    BREAK23 
    2001-06-26 11:17:58 AM Artist587 SongName587
    BREAK23 
    2001-06-26 11:17:58 AM Artist539 SongName539
    BREAK23 
    2001-06-26 11:17:58 AM Artist496 SongName496
    9781 
    2001-06-26 11:17:48 AM Artist286 SongName286
    9781 
    2001-06-26 11:17:48 AM Artist17 SongName17
    5443 
    2001-06-26 11:14:11 AM Artist285 SongName285
    3408 
    2001-06-26 11:09:21 AM Artist284 SongName284 
    I tried DISTINCT but it didn't ehlp the above should look like
    PHP Code:
    9084 2001-06-26 11:18:05 AM Artist289 SongName289
    BREAK23 
    2001-06-26 11:17:58 AM Artist287 SongName287
    8904 
    2001-06-26 11:17:58 AM Artist288 SongName288
    9781 
    2001-06-26 11:17:48 AM Artist286 SongName286
    5443 
    2001-06-26 11:14:11 AM Artist285 SongName285
    3408 
    2001-06-26 11:09:21 AM Artist284 SongName284 
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  2. #2
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I figured it out, yes I am a dumba*s, my table had duplicates in it.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.


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
  •