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?
 
mkiredjianConnect With a Mentor Commented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
 
sweetfa2Connect With a Mentor Commented:
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.