SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Location
    Dubai
    Posts
    971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    name the (AS) in IN statement

    Hi,

    This is the original query

    Code MySQL:
    SELECT meaning FROM category_dict WHERE abbreviation IN('{$cat}','{$cat2}')

    I want to name each result like this

    Code MySQL:
    SELECT meaning FROM category_dict WHERE abbreviation IN('{$cat}' AS dirl 1,'{$cat2}' AS dir2)

    i don't think I am doing it correctly, can you help fixing it ?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by runrunforest View Post
    I want to name each result
    please explain more, as this doesn't make sense to me

    suggestion: show a few sample rows of data, and then show what the query is supposed to return
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Sep 2008
    Location
    Dubai
    Posts
    971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I knew that its difficult to make sense as i don't know the technical term for this issue.

    Simplest request, how would you combine these queries?

    Code MySQL:
    SELECT meaning AS a 
    FROM category_dict 
    WHERE abbreviation = '{$cat1}';
     
    SELECT meaning AS b 
    FROM category_dict 
    WHERE abbreviation = '{$cat2}';
     
    SELECT meaning AS c 
    FROM category_dict 
    WHERE abbreviation = '{$cat3}';
    Last edited by runrunforest; Jan 10, 2009 at 03:29.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    like this --
    Code:
    SELECT '{$cat1}', meaning 
      FROM category_dict 
     WHERE abbreviation = '{$cat1}'
    UNION ALL 
    SELECT '{$cat2}', meaning 
      FROM category_dict 
     WHERE abbreviation = '{$cat2}'
    UNION ALL 
    SELECT '{$cat3}', meaning 
      FROM category_dict 
     WHERE abbreviation = '{$cat3}';
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Sep 2008
    Location
    Dubai
    Posts
    971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not quite what I am looking for, but quite what I need to see.

    I change the code a little bit to get one array with 3 values as I wished in the first place.

    Code MySQL:
    SELECT meaning AS a
    				  FROM category_dict 
    				 WHERE abbreviation = '{$cat1}'
    				UNION 
    				SELECT  meaning AS b
    				  FROM category_dict 
    				 WHERE abbreviation = '{$cat2}'
    				UNION 
    				SELECT  meaning  AS c
    				  FROM category_dict 
    				 WHERE abbreviation = '{$cat3}'

    Thank you very much. You the man.
    Last edited by runrunforest; Jan 10, 2009 at 08:34. Reason: Actually Im still wrong, the result is still 3 arrays with the same indexes, maybe I will have to go with yours

  6. #6
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    why don't you show us actual values and display them as you'd like to see them.
    I'm not sure why you didn't just do:

    Code:
    SELECT
    meaning
    FROM category_dict
    WHERE
    abbreviation IN ('{$cat1},{$cat2},{$cat3})
    there must be something you are doing that would prevent you from using this much simpler and more efficient query.

  7. #7
    SitePoint Guru
    Join Date
    Sep 2008
    Location
    Dubai
    Posts
    971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Because the result will be like this
    Code MySQL:
    Array ( [meaning] => Computer ) Array ( [meaning] => Laptop ) Array ( [meaning] => Sony )
    While I like to see it as this

    Code MySQL:
    Array ( [meaning1] => Computer ,   [meaning2] => Laptop , [meaning3] => Sony )

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    sounds like a php issue

    i'm going to move the thread to the php forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    <?php
    $sSQL 
    sprintf("SELECT meaning FROM category_dict WHERE abbreviation IN ('%s', '%s', '%s')",
        
    mysql_real_escape_string($sCat1),
        
    mysql_real_escape_string($sCat2),
        
    mysql_real_escape_string($sCat3)
    );
    $rResult mysql_query($sSQL);
    while (
    $aRow mysql_fetch_assoc($rResult))
    {
        
    $aMeanings[] = $aRow['meaning'];
    }
    print_r($aMeanings);
    /*
    Array
    (
        [0] => Computer
        [1] => Laptop
        [2] => Sony
    )

    */
    ?>
    Would the above suffice?
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  10. #10
    SitePoint Guru
    Join Date
    Sep 2008
    Location
    Dubai
    Posts
    971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you, Silverbullet.

    This is somewhat what i come up with to solve the problem in the first place since I don't like to use while loop ,I seek for maybe an sql solution.Then again this is the only way.

  11. #11
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Yes it is.

    The proper way to do it would be as guelphdad showed you, and that brings out the results in a meaningful way. To put it as you want it causes alot of restrictions, so it wasn't going to be done with just SQL.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona


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
  •