Solved

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

Posted on 2010-11-25
24
400 Views
Last Modified: 2012-05-10
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%"
0
Comment
Question by:Tyler Laczko
  • 13
  • 5
  • 5
  • +1
24 Comments
 
LVL 142

Expert Comment

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

Open in new window

0
 
LVL 10

Author Comment

by:Tyler Laczko
Comment Utility
close but i need them displayed as 1 per row
0
 
LVL 17

Expert Comment

by:sweetfa2
Comment Utility
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
 
LVL 3

Accepted Solution

by:
mkiredjian earned 400 total points
Comment Utility
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
 
LVL 10

Author Comment

by:Tyler Laczko
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
have you tried sweetfa2's suggestion?
should be better ...
0
 
LVL 10

Author Comment

by:Tyler Laczko
Comment Utility
did not work as good. and takes way to long to run.

embedding select statements is very slow.
0
 
LVL 17

Expert Comment

by:sweetfa2
Comment Utility
create index idx1 on phone_number;

it will be much quicker then.
0
 
LVL 10

Author Comment

by:Tyler Laczko
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 
LVL 10

Author Comment

by:Tyler Laczko
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
so, you want the ID column to be generated based on the "group" of the phone number information?

0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 10

Author Comment

by:Tyler Laczko
Comment Utility
yes
0
 
LVL 10

Author Comment

by:Tyler Laczko
Comment Utility
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
 
LVL 17

Expert Comment

by:sweetfa2
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>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
 
LVL 10

Author Comment

by:Tyler Laczko
Comment Utility
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
 
LVL 10

Author Comment

by:Tyler Laczko
Comment Utility
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
 
LVL 10

Author Comment

by:Tyler Laczko
Comment Utility
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
 
LVL 17

Assisted Solution

by:sweetfa2
sweetfa2 earned 100 total points
Comment Utility
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
 
LVL 10

Author Comment

by:Tyler Laczko
Comment Utility
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
 
LVL 17

Expert Comment

by:sweetfa2
Comment Utility
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
 
LVL 10

Author Comment

by:Tyler Laczko
Comment Utility
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
 
LVL 10

Author Closing Comment

by:Tyler Laczko
Comment Utility
Awarded full points to expert. Just accepting my final comment for future viewers.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

In Solr 4.0 it is possible to atomically (or partially) update individual fields in a document. This article will show the operations possible for atomic updating as well as setting up your Solr instance to be able to perform the actions. One major …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now