How to query two phone numbers in table with two type (Cellphone and Workphone)?

Question

Need help with adding a phone number based on cell phone or work number. So in the table phone I have type which is cellphone or workphone some have both. Need to allow this query to allow both. I have it working by pulling only one number, but need to pull both based on type.

You'll see in screen-shot a table example of entry_id are the same meaning same person, but need to query and echo the type with the number. The current query is only pulling one number regardless of what it is.

elseif (isset($individual)) {
    $person = <<<SQL
      SELECT w.first_name, w.last_name, w.slug, w.options,
w_phone.number, w_email.address, w_address.line_1, w_address.city, w_address.state, w_address.zipcode
      FROM wp_connections w INNER JOIN wp_connections_address w_address 
      ON w.id = w_address.entry_id LEFT JOIN wp_connections_phone w_phone
      ON w.id = w_phone.entry_id LEFT JOIN wp_connections_email w_email
      ON w.id = w_email.entry_id
      WHERE
        w.first_name = '$individual' OR
        w.last_name = '$individual' OR
        w_address.state = '$individual' OR
        w_address.city = '$individual' OR
        w_address.zipcode = '$individual'
      GROUP BY w.id
      ORDER BY RAND()
SQL;
}

Here is link to drop box with images of table structure: https://www.dropbox.com/sh/ep0z6yn6sd3mqmu/AAAA-OrNq6QPWtISdxwTeGJZa?dl=0

I need to pull this information out: slug, first_name, last_name from wp_connections

line_1, line_2, city, state, zipcode from from wp_connections_address

type, number from wp_connections_phone but need to get get both types(workphone, cellphone) if person has both.

Keep in mind that in wp_connections the id matches order_id in other tables.

So I want to be able to echo out Cell phone: xxx-xxx-xxxx and Workphone: xxx-xxx-xxxx for the people. I am creating contact cards on a web page. — Anthony, Sep 08 '15 at 16:07
You can either use GROUP_CONCAT for pure MySQL or use nested queries if you want to display the results using PHP. — Maximus2012, Sep 08 '15 at 16:09
Can you help with that a little more, I am kinda new to MySQL and such. — Anthony, Sep 08 '15 at 16:10
Please update your question with what your table structure looks like and what is the desired outcome. — Maximus2012, Sep 08 '15 at 16:11
See if this helps: http://stackoverflow.com/questions/13451605/how-to-use-group-concat-in-a-concat-in-mysql — Maximus2012, Sep 08 '15 at 16:14
What would be best for you, cause there are multiple tables that I need to get info from each? — Anthony, Sep 08 '15 at 16:15
All the tables from which you need the information along with the desired outcome. It will probable be better for you if you look at the accepted answer on that other question and try something on your own. — Maximus2012, Sep 08 '15 at 16:16
I have updated my question with dropbox screen shots and desired outcome. — Anthony, Sep 08 '15 at 17:09

Aung Myo Linn · Answer 1 · 2015-09-08T16:55:06.813

Try GROUP_CONCAT([DISTINCT] expr [,expr ...] [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [,col_name ...]] [SEPARATOR str_val]), something like below,

$person = "SELECT w.first_name, w.last_name, w.slug, w.options,
    GROUP_CONCAT(w_phone.number ORDER BY w_phone.type ASC SEPARATOR ', '), w_email.address, w_address.line_1, w_address.city, w_address.state, w_address.zipcode
    FROM wp_connections w INNER JOIN wp_connections_address w_address 
    ON w.id = w_address.entry_id LEFT JOIN
    wp_connections_phone w_phone
    ON w.id = w_phone.entry_id LEFT JOIN
    wp_connections_email w_email
    ON w.id = w_email.entry_id
    WHERE w.first_name = '$individual' OR w.last_name = '$individual' OR w_address.state = '$individual' OR w_address.city = '$individual' OR w_address.zipcode = '$individual' GROUP BY w.id ORDER BY RAND()";

Another example without JOIN,

$person = "w.first_name, w.last_name, w.slug, w.options,
    GROUP_CONCAT(DISTINCT w_phone.type, ': ', w_phone.number ORDER BY w_phone.type ASC SEPARATOR ', ') AS phones, w_email.address, w_address.line_1, w_address.city, w_address.state, w_address.zipcode
    FROM wp_connections w, wp_connections_address w_address, wp_connections_phone w_phone, wp_connections_email w_email
    WHERE
    w.id = w_address.entry_id AND
    w.id = w_phone.entry_id AND
    w.id = w_email.entry_id AND
    (w.first_name = '$individual' OR w.last_name = '$individual' OR w_address.state = '$individual' OR w_address.city = '$individual' OR w_address.zipcode = '$individual')
    GROUP BY w.id ORDER BY RAND()";

And the expected result should be something like below,

+------------+-----------+------+---------+---------------------------------------------+----------+----------------+
| first_name | last_name | slug | options |          phones                             |  address |      ...       |
+------------+-----------+------+---------+---------------------------------------------+----------+----------------+
| xxxxxxxxxx | xxxxxxxxx | xxxx | xxxxxxx | cellphone: xxxxxxxxxx, workphone xxxxxxxxxx |  xxxxxxx |      ...       |
+------------+-----------+------+---------+---------------------------------------------+----------+----------------+
| xxxxxxxxxx | xxxxxxxxx | xxxx | xxxxxxx | cellphone: xxxxxxxxxx                       |  xxxxxxx |      ...       |
+------------+-----------+------+---------+---------------------------------------------+----------+----------------+
| xxxxxxxxxx | xxxxxxxxx | xxxx | xxxxxxx | workphone: xxxxxxxxxx                       |  xxxxxxx |      ...       |
+------------+-----------+------+---------+---------------------------------------------+----------+----------------+

Ref:https://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

Ive tried the first way, it actually doesn't show any results for phone number, and second example just seems like its causing an error. I have updated my question with a link to dropbox of tables and my desired outcome. — Anthony, Sep 08 '15 at 18:29

score 0 · Answer 2 · edited May 23 '17 at 12:03

You need to re-write your query using JOIN statements, to return all kind of phone numbers, a query that would return something like below:

id   |   entry_id    |    order    |    preferred    |    cellphone    |     workphone    |
9    |      13       |      0      |    cellphone    |   xxx-xxx-xxxx  |      NULL        |

You can achieve that by creating a view like suggested in the below post: MySQL - Rows to Columns

by doing something like:

create view wp_connections_phone_pivot_pretty as (
  select 
    entry_id, 
    coalesce(cellphone, NULL) as cellphone, 
    coalesce(workphone, NULL) as workphone
  from wp_connections_phone 
);

Then join your query to that view, like:

SELECT w.first_name, w.last_name, w.slug, w.options,
w_phone.workphone, w_phone.cellphone, w_email.address, w_address.line_1, w_address.city, w_address.state, w_address.zipcode
      FROM wp_connections w INNER JOIN wp_connections_address w_address 
      ON w.id = w_address.entry_id LEFT JOIN wp_connections_phone_pivot_pretty w_phone
      ON w.id = w_phone.entry_id LEFT JOIN wp_connections_email w_email
      ON w.id = w_email.entry_id
      WHERE
        w.first_name = '$individual' OR
        w.last_name = '$individual' OR
        w_address.state = '$individual' OR
        w_address.city = '$individual' OR
        w_address.zipcode = '$individual'
      GROUP BY w.id
      ORDER BY RAND()

But in my opinion you would add all possible phone types as new columns to your wp_connections_phone table, and just pull them from there with no extra efforts (no need to create view in that case).

I think it would be better for the understanding of the poster if you actually show a query by which you can achieve this result. Additionally, it would also be helpful if you could explain why that query works. — Maximus2012, Sep 08 '15 at 19:26
Yes. This is much better. It's up to the OP now to see if this is the solution that they are looking for. — Maximus2012, Sep 08 '15 at 19:55

How to query two phone numbers in table with two type (Cellphone and Workphone)?

2 Answers2