PHP MySQL query to identify company URL 1

[aalmeida]

There is a table that matches URL to a specific company the problem lies when a company has more than one URL.

Table tbl_company
Comp_Id Comp_Name Comp_URL
10 CompA [URL unfurl="true"]www.compa.com[/URL]
20 compB [URL unfurl="true"]www.compb.com,[/URL] [URL unfurl="true"]www.compb1.net[/URL]

Initially I thought I could identify the company by the URL the user came to the site running a query like this:

SELECT a.Comp_ID, a.Comp_Name, a.Comp_URL 
FROM tbl_company a 
WHERE a.Comp_URL = '[URL unfurl="true"]www.compa.com'[/URL]

Which works fine when the company has only one URL. what would be the best option to solve this so that I would be able to identify the company in both case one or more than one URL?

Thanks




AL Almeida
CIO

http://www.artdigitalonline.com/

May all those that come behind us, find us faithful.

 

[vacunita]

The best option would be not to have comma separated values in a single field. In makes searching difficult as you have seen. and doesn't scale well. With more records and more comma separated values per field this can become very slow.

If you must keep the comma separated list try using the LIKE operator such as:

SELECT a.Comp_ID, a.Comp_Name, a.Comp_URL
FROM tbl_company a
WHERE a.Comp_URL [red]LIKE[/red] '[red]%[/red]www.compa.com[red]%[/red]'

However I'd use a URL table linked to the company table by the company's Id:

id_url id_company URL
1        company1  [URL unfurl="true"]www.urlcompany1.com[/URL]
2         company2 [URL unfurl="true"]www.urlcompnay2.com[/URL]
3         company1 [URL unfurl="true"]www.url2company1.com[/URL]
...

Then its just a matter of performing a regular search and join it to the company table.

For further MYSQL questions:
forum436




----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[aalmeida]

vacunita,

That was really good.

AL Almeida
CIO

http://www.artdigitalonline.com/

May all those that come behind us, find us faithful.