SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: Distinct

  1. #1
    SitePoint Evangelist Deano's Avatar
    Join Date
    Mar 2003
    Location
    Derbyshire, UK
    Posts
    487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Distinct

    Hi all,

    I've run into a few hickups and wonder if anyone else has ever encounted this problem before and if so how did you over come it.

    Im simply pulling data from a news table. However, there are many rows that contain same entries, Example theres around 10 rows that contain the word ORANGE and another 5 rows that contains the word APPLES.

    What Im trying to do is pull 1 row that contain APPLES then another row that contains ORANGE :

    - ORANGE
    - APPLES

    OK, Ive managed to sort through them all and pull rows using DISTINCT.

    PHP Code:
    ////////////////////////////////////////////////////
    //* Table Rows for gmn_news: id, gid, gametitle *///
    ////////////////////////////////////////////////////

    $game = @mysql_query('
    SELECT DISTINCT gametitle FROM gmn_news ORDER BY gametitle
    '
    );
     
    if (!
    $game) {          
    die(
    'Error retrieving Game Titles from the database!<br />'
    'Error: ' mysql_error() . ''); 


    echo (
    "Games List");

    while (
    $title mysql_fetch_array($game)) { 
           
    $id $title['id'];
           
    $gid $title['gid'];
           
    $gametitle $title['gametitle'];
            
     echo     (
    "$id - $gametitle");    

    However, the problem im facing is that it will ONLY pull the $gametitle and nothing else. Ive been trying for many hours now to also try and pull the $id into the echo statement.

    Hope someone knows whats happening here becuase its surely causing me some problems.

    Kind regards
    Deano
    Regards
    Deano

  2. #2
    SitePoint Zealot
    Join Date
    Aug 2006
    Location
    Newcastle, England
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    in your sql query you need to pull out the id so change

    $game = @mysql_query('
    SELECT DISTINCT gametitle FROM gmn_news ORDER BY gametitle
    ');

    to

    $game = @mysql_query('
    SELECT DISTINCT gametitle, id FROM gmn_news ORDER BY gametitle
    ');

    I think thats it.

  3. #3
    Who turned the lights out !! Mandes's Avatar
    Join Date
    May 2005
    Location
    S.W. France
    Posts
    2,496
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    As thejackel has correctly said you need to list all the columns that your script uses in your SQL query.
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming

  4. #4
    SitePoint Evangelist Deano's Avatar
    Join Date
    Mar 2003
    Location
    Derbyshire, UK
    Posts
    487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi thx for your suggestions,

    Ive tryed that also. What it does is spit out ALL rows from the table, so in most cases I'll have the same row output 3 or 4 times, which eliminates me using DISTICT for the $gametitle.

    Regards
    Deano
    Regards
    Deano

  5. #5
    Who turned the lights out !! Mandes's Avatar
    Join Date
    May 2005
    Location
    S.W. France
    Posts
    2,496
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Have you tried

    SELECT gametitle, id
    FROM gmn_news
    GROUP BY gametitle
    ORDER BY gametitle;
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming

  6. #6
    SitePoint Evangelist Deano's Avatar
    Join Date
    Mar 2003
    Location
    Derbyshire, UK
    Posts
    487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Mandes,

    of cause, how did I over look the GROUP BY

    thanks so much for helping me out, works perfectly.

    Regards
    Deano
    Regards
    Deano

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yeah, except the sql in post #5 is technically invalid (even though mysql will execute it!)

    the value of id in the results is unpredictable

    see GROUP BY and HAVING with Hidden Fields

    the question you need to ask youself is: if i want only one row per gametitle, which id should it have? the highest? the lowest? the average?

    then use the appropriate aggregate function in the sql, e.g.

    SELECT gametitle, max(id) as max_id
    FROM gmn_news
    GROUP BY gametitle
    ORDER BY gametitle;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •