MYSQL - GROUP BY - case issue

Discussion in 'MySQL' started by webvivre, Sep 22, 2007.

0
  1. #1
    In doing a SELECT statement and using GROUP BY city - I have a problem with the way the city has been inputted in the form.

    So for example, if LONDON has been added in one entry and in another as London, the resulting list - produces

    LONDON
    London

    Ideally I want the GROUP BY to create just one entry

    London

    Help!
     
    webvivre, Sep 22, 2007 IP
  2. xdimension

    xdimension Peon

    Messages:
    180
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    0
    #2
    is this answering your problem?

    SELECT upper(city) AS mycity FROM table GROUP BY mycity
     
    xdimension, Sep 22, 2007 IP
  3. webvivre

    webvivre Peon

    Messages:
    249
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Thanks..

    $q = 'select UPPER(city) AS mycity,mls,department from homes where department = "'.$_GET['department'].'" group by mycity order by mycity asc';
    $r = mysql_query($q);
    while($row = mysql_fetch_array($r)){
    $r2 = mysql_query('select city,mls,department from homes where mls = "'.$row['mls'].'" and city = "'.$row['city'].'"');
    $num = mysql_num_rows($r2);
    etc....

    How do I modify $r2 statement please?
     
    webvivre, Sep 23, 2007 IP