SitePoint Sponsor

User Tag List

Results 1 to 22 of 22
  1. #1
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Separating resutls from the same record

    Kind of tough to put into words, so I'll do my best.

    I have a 'members' table:
    Code HTML4Strict:
    member_id     name           spouse_name     birth_date     spouse_birth_date     email                spouse_email
    --------------------------------------------------------------------------------------------------------------------
    12345         John Doe       Jane Doe        1950-10-10     1951-12-10            [email]sample@email.com[/email]     [email]spouse@email.com[/email]
    12346         Jeff Smith     Peggy Smith     1975-05-21     1975-03-14            [email]sample@email.com[/email]     [email]spouse@email.com[/email]
    12347         Glenn Geiger                   1975-05-31                           [email]glenn@geiger.com[/email]

    I need to SELECT a LIKE search for the 'name' or the 'spouse_name', and display the results in a HTML table. The tricky part (for me) is to list each match (name or spouse) on it's own row, and order them together:

    Code HTML4Strict:
    member_id     name           birth_date      email
    ----------------------------------------------------------------
    12345         John Doe       1950-10-10      [email]sample@email.com[/email]
    12345         Jane Doe       1951-12-10      [email]spouse@email.com[/email]
    12346         Jeff Smith     1975-05-21      [email]sample@email.com[/email]
    12346         Peggy Smith    1975-03-14      [email]spouse@email.com[/email]
    12347         Glenn Geiger   1975-05-31      [email]glenn@geiger.com[/email]

    I haven't a clue how to do this in one query, but if need be I could do it in 2.
    Any suggestions are appreciated as always.

  2. #2
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    64 Post(s)
    Tagged
    2 Thread(s)
    Very simply done through one query :-) Do you know how to run your query and assign the recordset to an array in PHP yet?

  3. #3
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I can do it this way:
    Code PHP:
    $results = array();
     
    while ($rows = mysqli_fetch_assoc($r)) { // fill the array
        $results[] = $rows;
    }
     
    foreach ($results as $value) {   
        // display results
    }

    But I've also done it this way:
    Code PHP:
    while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
        // display results
    }

  4. #4
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    64 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by ggeiger View Post
    I can do it this way:
    Code PHP:
    $results = array();
     
    while ($rows = mysqli_fetch_assoc($r)) { // fill the array
        $results[] = $rows;
    }
     
    foreach ($results as $value) {   
        // display results
    }

    But I've also done it this way:
    Code PHP:
    while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
        // display results
    }
    PHP Code:
    $results = array();   while ($rows mysqli_fetch_assoc($r)) { // fill the array     
    $results[] = $rows
    }   
    echo 
    "<table><tr>member id</tr><tr>member name</tr>"//headers
    foreach ($results as $value) {        
    echo 
    "<td>$value[member_id]</td><td>$value[name]</td>"//display each field for each record
    }
    echo 
    "</table>"//end the table 

  5. #5
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I can output the results without a problem. What I can't do is separate the results from 1 record and display in 2 rows of the table.

    The data examples above should make more sense. The first is a database table. The second is how I would like to display that data.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT member_id
         , name           
         , birth_date     
         , email                
      FROM members
    UNION ALL  
    SELECT member_id
         , spouse_name     
         , spouse_birth_date     
         , spouse_email
      FROM members
     WHERE COALESCE(spouse_name,'') = ''
    ORDER
        BY member_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    64 Post(s)
    Tagged
    2 Thread(s)
    Ah, I was way off on what you wanted, sorry.

  8. #8
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    SELECT member_id
         , name           
         , birth_date     
         , email                
      FROM members
    UNION ALL  
    SELECT member_id
         , spouse_name     
         , spouse_birth_date     
         , spouse_email
      FROM members
     WHERE COALESCE(spouse_name,'') = ''
    ORDER
        BY member_id
    Wow, I have so much to learn, just from this. I'll be looking into UNION and COALESCE in the manual. Thanks much for this! Really.

  9. #9
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    SELECT member_id
         , name           
         , birth_date     
         , email                
      FROM members
    UNION ALL  
    SELECT member_id
         , spouse_name     
         , spouse_birth_date     
         , spouse_email
      FROM members
     WHERE COALESCE(spouse_name,'') = ''
    ORDER
        BY member_id
    This is giving me really unexpected results. I can't figure out what this query is doing, and what I should expect. The manual is short on the COALESCE comparison. Any chance you want to explain this some?

  10. #10
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    This is not working because r937 was probably in a hurry and made a mistake, the COALESCE comparison should be != (not =). Try this:
    Code:
    (SELECT member_id
         , name
         , birth_date
         , email
         , 1 AS pos
      FROM members)
    
    UNION ALL
    
    (SELECT member_id
         , spouse_name
         , spouse_birth_date
         , spouse_email
         , 2 AS pos
      FROM members
     WHERE COALESCE(spouse_name,'') != '')
    
    ORDER
        BY member_id, pos
    Moreover, I added the field 'pos' in the result sets so that you can have consistent sorting - that is member data first, spouse data second.

    UNION simply joins two separate queries and returns results of both in a single result set - instead of running 2 separate queries and then merging the results in another language (like php) you are doing it all in sql.

    COALESCE(spouse_name,'') != '' means that the result should be returned only if spouse_name is defined (if it's other than NULL or empty string). In fact when no spouse data is present you should have NULL values in the table, then change WHERE COALESCE(spouse_name,'') != '' to:

    Code:
    WHERE spouse_name IS NOT NULL
    because it's shorter and faster.

  11. #11
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    64 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Lemon Juice View Post
    UNION simply joins two separate queries and returns results of both in a single result set - instead of running 2 separate queries and then merging the results in another language (like php) you are doing it all in sql.
    And UNION ALL will not check for unique value, and is much faster to use. With that being said.. I'm not sure why we are not using a UNION ALL here.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Lemon Juice View Post
    Code:
    WHERE spouse_name IS NOT NULL
    because it's shorter and faster.
    and often wrong

    the whole reason i used COALESCE (and thanks for picking up my booboo regarding not equal) was to catch instances where spouse name is equal to an empty string

    if you look at post #1, there's nothing there for the spouse on the last table row, which is consistent with an empty string, whereas if those columns had actually been NULL, i would've expected to see NULL printed out

    this is all phpmyadmin's fault, which has this nasty habit of assigning DEFAULT '' for VARCHARs if you don't specify a default
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    and often wrong

    the whole reason i used COALESCE (and thanks for picking up my booboo regarding not equal) was to catch instances where spouse name is equal to an empty string

    if you look at post #1, there's nothing there for the spouse on the last table row, which is consistent with an empty string, whereas if those columns had actually been NULL, i would've expected to see NULL printed out

    this is all phpmyadmin's fault, which has this nasty habit of assigning DEFAULT '' for VARCHARs if you don't specify a default
    I think you misunderstood my intention. I noticed that the last row has empty strings and I know you used COALESCE to be universal so that it catches both NULL and empty string. Otherwise you could just have written WHERE spouse_name != ''.

    I was saying that the OP should change the column definitions to allow NULL values and use NULL instead of empty string for spouse_name, etc. Then he could use
    Code:
    WHERE spouse_name IS NOT NULL
    Of course, using it on the current data as he presented would be wrong .

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Lemon Juice View Post
    I was saying that the OP should change the column definitions to allow NULL values and use NULL instead of empty string ...
    that would just make too much sense, eh

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

  15. #15
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks everyone for the help here. Right now the query returns seemingly the whole database (about 200,000 records). I'm not quote yet sure how to get the data returned the way I need. But I will keep tinkering with these new tools. If I run into a dead end, I shall return.

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ggeiger View Post
    Right now the query returns seemingly the whole database
    you need to add the LIKE condition to your two SELECTs

    (which would mean the COALESCE nonsense is not necessary, since you're searching for actual spouse names)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you need to add the LIKE condition to your two SELECTs

    (which would mean the COALESCE nonsense is not necessary, since you're searching for actual spouse names)
    Thanks! I could not figure out the use of COALESCE in this case. This works:
    Code MySQL:
    SELECT member_id, name, birth_date, email
    FROM members
    WHERE name LIKE '%geiger%'
    UNION ALL
    SELECT member_id, spouse_name, spouse_birth_date, spouse_email
    FROM members
    WHERE spouse_name LIKE '%geiger%'
    ORDER
    BY member_id
    LIMIT 1000

  18. #18
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Had to thank you one more time. That UNION ALL is so great for a database like mine, and the results are working perfectly. Onward!

  19. #19
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    however, your table is not properly normalised...

  20. #20
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    64 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Dr John View Post
    however, your table is not properly normalised...
    All depends on how its being implemented. The way he has it designed or "normalized" could be perfect in numerous situations I can think of.

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    the members table in post #1 satisfies third normal form

    anybody that says it isn't should also explain why you can't have two columns called firstname and lastname in the same table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  22. #22
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    64 Post(s)
    Tagged
    2 Thread(s)
    SP should implement a like or up vote system lol


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
  •