Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2010-11-25
24
Medium Priority
?
448 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 5
  • 5
  • +1
24 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34213782
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
ID: 34214519
close but i need them displayed as 1 per row
0
 
LVL 17

Expert Comment

by:sweetfa2
ID: 34214563
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 3

Accepted Solution

by:
mkiredjian earned 1600 total points
ID: 34216501
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
ID: 34218428
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 143

Expert Comment

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

Author Comment

by:Tyler Laczko
ID: 34219137
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
ID: 34219806
create index idx1 on phone_number;

it will be much quicker then.
0
 
LVL 10

Author Comment

by:Tyler Laczko
ID: 34222581
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34223002
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
ID: 34223446
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 143

Expert Comment

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

0
 
LVL 10

Author Comment

by:Tyler Laczko
ID: 34226530
yes
0
 
LVL 10

Author Comment

by:Tyler Laczko
ID: 34226534
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
ID: 34227412
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34227988
>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
ID: 34231625
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
ID: 34231654
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
ID: 34231698
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 400 total points
ID: 34231948
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
ID: 34233945
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
ID: 34234149
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
ID: 34234721
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
ID: 34234825
Awarded full points to expert. Just accepting my final comment for future viewers.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
The viewer will learn how to count occurrences of each item in an array.
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 …
Suggested Courses

618 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