SitePoint Sponsor

User Tag List

Results 1 to 13 of 13

Thread: sql question

  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    london
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    sql question

    Hi this is more of an sql question than a php but i am wrkign with php so i hope u dont mind

    If have more that 1 occurence of a result is there an sql command to retreive only 1 occurence
    eg

    1,1,1,1,2,2,3,3,3,3

    is there a way to get
    1,2,3

    Thanks

  2. #2
    \m/\m/ karinne's Avatar
    Join Date
    Dec 2002
    Location
    Aylmer, QC, Canada
    Posts
    409
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes... use the GROUP BY in sql. for instance

    select * from books group by year;

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    london
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i have tried it and i get this error
    Warning: ociexecute(): OCIStmtExecute: ORA-00979: not a GROUP BY expression in c:\oracle\ora90\apache\apache\htdocs\proj\films\filmsshowing.php on line 62

    i am using a join statement, is that why?

  4. #4
    SitePoint Wizard silver trophy someonewhois's Avatar
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    6,364
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your using Oracle? That's more of a database question than an SQL question. Where's Rudy?

  5. #5
    SitePoint Enthusiast Viper_SB's Avatar
    Join Date
    Aug 2003
    Location
    Canada
    Posts
    76
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    can you post your query?

  6. #6
    SitePoint Member
    Join Date
    Oct 2004
    Location
    San Diego
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    distinct

    Try using distinct:
    select distinct item from table_name;

  7. #7
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    london
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    $cmdstr = "Select title,id, date from Table1 JOIN Table2 ON Table1.id = Table2.id"; WHERE date ='$date';

    there is a one to many relationship b/n Table1 and Table2...basically its title i want which is in table1 and date is in Table2 so i get multiple displays of the same title.


    I tried distinct which doesnot work.


    thanks.

  8. #8
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    try

    Code:
    $cmdstr = "SELECT t1.title, t1.id, MAX(t2.date) 
    FROM Table1 t1 JOIN Table2 t2 ON t1.id = t2.id
    GROUP BY t1.title, t1.id";
    or

    Code:
    $cmdstr = "SELECT DISTINCT t1.title, t1.id 
    FROM Table1 t1 JOIN Table2 t2 ON t1.id = t2.id
    WHERE t2.date = '$date'";
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.

  9. #9
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    london
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks alot it works, can i have a bit of an explanation though


    Thanks.

  10. #10
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    london
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    By the way i used the first statement

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by someonewhois
    Your using Oracle? That's more of a database question than an SQL question. Where's Rudy?
    sorry, busy last night, plus, i don't always scan the "Today's Topics" search results for threads that might involve sql, and i would never just jump into the php forum, because, well, i don't do php at all, in fact, i can barely spell it (heh) -- although i do know what the letters stand for

    if you have an sql question, post it in database or mysql forum and i'll be sure to see it

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    london
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    dates question

    hi if i have the current date as

    $currentdate = date("j-M-y");

    How do i get yesterdays date in the same format.

    thanks.

  13. #13
    SitePoint Wizard silver trophy someonewhois's Avatar
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    6,364
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You use PHP's mktime function:

    $yesterday = date('j-M-y', mktime(0, 0, 0, date('n'), (date('j') - 1), date('Y')));

    Should do it.


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
  •