Separating resutls from the same record

[ggeiger]

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

I have a ‘members’ table:

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:

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.

[KyleWolfe]

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]

I can do it this way:

$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:

while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
    // display results
}

[KyleWolfe]

@ggeiger

I can do it this way:

$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:

while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
    // display results
}

$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]

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]

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

[KyleWolfe]

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

[ggeiger]

@r937

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]

@r937

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]

This is not working because r937 was probably in a hurry and made a mistake, the COALESCE comparison should be != (not =). Try this:

(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:

WHERE spouse_name IS NOT NULL

because it’s shorter and faster.

[KyleWolfe]

@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]

@Lemon_Juice

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]

@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

WHERE spouse_name IS NOT NULL

Of course, using it on the current data as he presented would be wrong :).

[r937]

@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]

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]

@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]

@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:

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]

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]

however, your table is not properly normalised…

[KyleWolfe]

@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.