Question 8120 in PHP Versions Question posted in
You can find a list of all versions here.

How do I select unique values from multiple columns (each value being unique not the total row) – PHP Versions

I have an existing table that has many rows something like this:

id Product Tel1 Tel2 Tel3 Tel4
1 A 1234 1234 1234 1234
2 B 1234 1234 1234 3750
3 C 1234 1235 1234 1234
4 D 1234 1234 1234 1234
5 E 9876 1234 3654 6583
6 F 1234 4567 1234 1234
6 G 9876 1234 1234 2785
7 etc… 1234 1234 1234 1234

There are other columns, but this demonstrates the import ones for this question.

It is used as a kind of telephone directory in a php front end, Many of the numbers are the same, espcially for similar products. I need to create a new table that I can show some additional info about each number on the front end, and the current table is very long and has many phone numbers in it in total so it’s not practical for me to manually look at it and find all the numbers I weant to put into a new table.

I need to run some kind of select query to get a list of all the unique numbers accross any and all phone number columns the current table has. This is just a one off query to get me a list I can then create a new table with, so I don’t care about query efficency.

The result should look something like this:

numbers
1234
0987
3654
9876
etc…

Just listing all unique numbers. I can then take this data and put it into this new table and start adding other columns myself for the additional information I mentioned, then just use a typical query to show this extra data on my front end when one of the numbers from the orignal table is selected by a user, kind of using the phone number as an ID in my new table.

I have tried to search for a solution and I already know about the DISTINCT command, but my understanding of this is that it will only produce a list of results where every column is not identical, which is not what I want, and this is the search results I seem to get from my keywords.

Once I have a unique list and I have not had to manually look through the orignal large table to get them, I’m fine to manally create the new table and manually insert all the unique numbers by hand, but bonus points if I can also somehow combine the query to insert into a new table I have created. (If this is even possible?)

EDIT: For those mentioning normalisation, While I don’t disagree in principle, I cannot alter the existing table in any way, as it is kept in sync with a 3rd party propratory piece of software I have no direct control over, so it must remain in it’s current schema. I can do whatever I like with other tables, but I must not touch the orignal table, so I can still use phpmyadmin to overwrite it with new versions taken from this other software in future.

Tags:

3

Answers


  1. 0

    Based on you sample you could use a select union with one select for each column you need

    select tel1 
from my_table
union 
select tel2
from my_table
union
select tel3
from my_table
union 
select tel4
from my_table

    then you could create the table using a create select

    create table my_new_table 
select tel1 tel
from my_table
union 
select tel2
from my_table
union
select tel3
from my_table
union 
select tel4
from my_table
    Login or Signup to reply.
  2. 0

    Assuming the table name is products, you can use the following query

    select tel1 as numbers from products
union
select tel2 from products
union
select tel3 from products
union
select tel4 from products;

    It will output the following:

    numbers
    1234
    9876
    1235
    1001
    1111
    3750
    2785

    View on DB Fiddle

    Mandatory PSA:

    • Please, when you create a new table, please normalize the table to make life easier.
    Login or Signup to reply.
  3. 0

    I would do a union of queries to get all distinct telephones by column then do a query on this union of telephones to get them only once:

    SELECT DISTINCT `Tel` FROM
( 
  SELECT DISTINCT `Tel1` AS `Tel` FROM products
  UNION
  SELECT DISTINCT `Tel2` AS `Tel` FROM products
  UNION
  SELECT DISTINCT `Tel3` AS `Tel` FROM products
  UNION
  SELECT DISTINCT `Tel4` AS `Tel` FROM products
) all_telephones
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search