Separating resutls from the same record

**ggeiger** · Sep 25, 2012 12:02

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.

**K. Wolfe** · Sep 25, 2012 13:02

Very simply done through one query :-) Do you know how to run your query and assign the recordset to an array in PHP yet?

**ggeiger** · Sep 25, 2012 13:45

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
}

**K. Wolfe** · Sep 25, 2012 13:57

Originally Posted by ggeiger

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

**ggeiger** · Sep 25, 2012 14:11

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.

**r937** · Sep 25, 2012 16:40

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

**K. Wolfe** · Sep 26, 2012 07:47

Ah, I was way off on what you wanted, sorry.

**ggeiger** · Sep 26, 2012 08:19

Originally Posted by r937

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.

**ggeiger** · Sep 26, 2012 08:30

Originally Posted by r937

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?

**Lemon Juice** · Sep 26, 2012 09:45

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.

**K. Wolfe** · Sep 26, 2012 10:04

Originally Posted by Lemon Juice

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.

**r937** · Sep 26, 2012 10:43

Originally Posted by Lemon Juice

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

**Lemon Juice** · Sep 26, 2012 11:01

Originally Posted by r937

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

.

**r937** · Sep 26, 2012 11:04

Originally Posted by Lemon Juice

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

**ggeiger** · Sep 26, 2012 14:10

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.

**r937** · Sep 26, 2012 14:20

Originally Posted by ggeiger

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)

**ggeiger** · Sep 27, 2012 12:14

Originally Posted by r937

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

**ggeiger** · Sep 27, 2012 14:45

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!

**Dr John** · Sep 28, 2012 11:44

however, your table is not properly normalised...

**K. Wolfe** · Sep 28, 2012 12:09

Originally Posted by Dr John

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.

**r937** · Sep 28, 2012 12:53

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

**K. Wolfe** · Sep 28, 2012 13:29

SP should implement a like or up vote system lol

User Tag List

Thread: Separating resutls from the same record

Thread Tools

Display

Separating resutls from the same record

Bookmarks

Bookmarks

Posting Permissions