MySQL PHP - Need to display similar records in a table.

I have a large database with:

company_name, username, fax_num, phone_num

I want to select rows that have similar records.


EG

Big Co., bigcomp, 3069335656, 3069335657
Big Co Inc., big, 3069335656, 3069335657
Big Co Inc South., bigco, 3069335650, 3069335657


If these 3 rows were in my table i would want all of them showing. In the instance all 3 have the same phone number. I would like this solution.

I think a group by phone_num where count > 2 or something.



Once I have that solution I think I can modify it to do me similar company_name

`company_name` LIKE "%name%"
LVL 10
Tyler LaczkoAsked:
Who is Participating?
 
mkiredjianCommented:
I recomend you to do this
select t1.company_name,t1.phone_num ,t2.company_name,t2.phone_num
from yourtable t1,yourtable t2
where t1.yourtableId<> t2.yourtableId AND t1.phone_num=t2.phone_num


0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about this:
select group_concat(company_name) , phone_num 
  from yourtable
 group by phone_num
having count(*) > 1

Open in new window

0
 
Tyler LaczkoAuthor Commented:
close but i need them displayed as 1 per row
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
sweetfa2Commented:
select company_name, phone_num from yourtable
   where phone_num in (select phone_num from yourtable group by phone_num having count(1) > 1)
   order by 2
0
 
Tyler LaczkoAuthor Commented:
This is what i am using and it is working pretty good.

i need to get rid of all the doubles tho.

EG it shows

company1, company2
company2, company1



select t1.company_name,t2.company_name,t2.phone_num
from project_users t1,project_users t2
where t1.id <> t2.id AND t1.phone_num=t2.phone_num and t1.phone_num <> "3069990440" and t1.phone_num <> ""
order by t2.phone_num
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
have you tried sweetfa2's suggestion?
should be better ...
0
 
Tyler LaczkoAuthor Commented:
did not work as good. and takes way to long to run.

embedding select statements is very slow.
0
 
sweetfa2Commented:
create index idx1 on phone_number;

it will be much quicker then.
0
 
Tyler LaczkoAuthor Commented:
i still need to return company1 and company2.

as i need to show the details of each company.

like i said the other solution is close but i do not want them showing up twice.

company1, company2
company2, company1

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
again, what is wrong about this syntax:
select company_name, phone_num from yourtable
   where phone_num in (select phone_num from yourtable group by phone_num having count(1) > 1)
   order by 2

Open in new window

it will not show the same company_name twice as you claim ...
0
 
Tyler LaczkoAuthor Commented:
the issue is with that statement is that is does not group them together.

it gives a list of companies but does not say which ones match.

in the current situation i can just use php to compare the phone number for an exact match and group the same phone numbers together.

but i need to know when they match. the other solution matched the 2 companies on 1 line.

can we ad an id for matched companies?

company    number     id
comp1        123            1
comp2        123            1
comp3        333            2
comp4        333            2
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so, you want the ID column to be generated based on the "group" of the phone number information?

0
 
Tyler LaczkoAuthor Commented:
yes
0
 
Tyler LaczkoAuthor Commented:
or some other way to know how these are being grouped together.

I need to select all of these groups and then display them separately.
0
 
sweetfa2Commented:
The order by 2 groups them together in matching phone number.

select company_name, t2.phone_num, (select rownum 
                                                          from (select phone_num from yourtable group by phone_num having count(1) > 1) t1
                                                          where t1.phone_num = t2.phone_num)
   from yourtable t2
   where t2.phone_num in (select phone_num from yourtable group by phone_num having count(1) > 1)
   order by 2

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>display them separately.

I presume, with some PHP code or the like?
then, I would solve that part in php ... you can loop the rows, and compare the value of phonenumber with the value of the previous row, and do the "break" eventually.
0
 
Tyler LaczkoAuthor Commented:
Yes that would work but I'm looking for the mysql solution.

I may as well return all the records and compare them in php.


You are getting stuck on the fact that in the example i provided the phone numbers match exactly.
I will be taking this solution further to return results where the fax number or company name are close.

such as

Big Company
Big Co

I cannot just do an exact comparison.

So I am still looking for the mysql solution.
0
 
Tyler LaczkoAuthor Commented:
sweetfa2 these are the results i get from your query:

ssman Canada Inc.          2049997863       76
ssmann Canada Inc.        2049997863       1469
City Roofing Ltd                  2049992498       1466
City Roofing                        2049992498       2456





select company_name, t2.phone_num, (select id
                                                          from (select phone_num from project_users group by phone_num having count(1) > 1) t1
                                                          where t1.phone_num = t2.phone_num
AND t1.phone_num <> "3069990440"
AND t1.phone_num <> "")
   from project_users t2
   where t2.phone_num in (select phone_num from project_users group by phone_num having count(1) > 1)
AND t2.phone_num <> "3069990440"
AND t2.phone_num <> ""
   order by 2



* i changed rownum to id.. i got a sql error when using rownum... maybe i used it wrong?
0
 
Tyler LaczkoAuthor Commented:
FYI: I was originally using the following sql until i posted this question:

SELECT `company_name` , `fax_num` , count( `fax_num` ) AS cnt
FROM `project_users`
GROUP BY `fax_num`
HAVING cnt >1
ORDER BY cnt

I got the first record that matched but i need to see both per line.
0
 
sweetfa2Commented:
select company_name, t2.phone_num, (select @rownum := @rownum +1 rownum 
                                                          from (select @rownum := 0) r, (select phone_num from yourtable group by phone_num having count(1) > 1) t1
                                                          where t1.phone_num = t2.phone_num)
   from yourtable t2
   where t2.phone_num in (select phone_num from yourtable group by phone_num having count(1) > 1)
   order by 2

Open in new window


Try this
0
 
Tyler LaczkoAuthor Commented:
When making future edits can you please use variations of:





select company_name, t2.phone_num, (select @rownum := @rownum +1 rownum
                                                          from (select @rownum := 0) r, (select phone_num from project_users  group by phone_num having count(1) > 1) t1
                                                          where t1.phone_num = t2.phone_num)
   from project_users  t2
   where t2.phone_num in (select phone_num from project_users  group by phone_num having count(1) > 1)
AND t2.phone_num <> "3069990440"
AND t2.phone_num <> ""
   order by 2




this returns:


ssman Canada Inc.          2049997863       1
ssmann Canada Inc.        2049997863       2
City Roofing Ltd                  2049992498       3
City Roofing                        2049992498        4


looking for

ssman Canada Inc.          2049997863       1
ssmann Canada Inc.        2049997863       1
City Roofing Ltd                  2049992498       2
City Roofing                        2049992498        2
0
 
sweetfa2Commented:
It sounds like what you really need is two separate loops within your php.

The first loop executes select phone_num from project_users  group by phone_num having count(1) > 1
and maintains  a count of elements.

Then within that loop have a loop that does the second query
select ... from project_users where phone_num = <loop variable>

In that way you keep your sql simple, and easy to modify to deal with similarities later.
0
 
Tyler LaczkoAuthor Commented:
I decided to keep my sql simple and use php to do most of the work.

      $sql = "SELECT
                        t1.id as 't1id',
                        t2.id as 't2id',
                        t1.company_name as 't1company_name',
                        t2.company_name as 't2company_name',
                        t1.user_name as 't1user_name',
                        t2.user_name as 't2user_name',
                        t1.date_created as 't1date_created',
                        t2.date_created as 't2date_created',
                        t1.phone_num,
                        t1.fax_num
                  FROM project_users t1, project_users t2
                  WHERE t1.id <> t2.id
                        AND t1.phone_num = t2.phone_num
                        AND t1.phone_num <> '3069990440'
                        AND t1.phone_num <> ''
                  ORDER BY t1.phone_num";

this is what i use to pull the data then i display it to the user in groups.
0
 
Tyler LaczkoAuthor Commented:
Awarded full points to expert. Just accepting my final comment for future viewers.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.