left join a column with array in php/mysql

Question

I have several tables 1 contacts and multiple data.

Contacts has an ID field and a Name field; Data has a field called Contacts (as well as other fields).

e.g Contacts

ID - Name
1 - James
2 - Mark
3 - Doug

e.g. data

ID - Contacts - Data
1 - 1,3 - more data
2 - 2 - more data
3 - 2,3,1 - more data

Obviously, the comma-separated numbers link to several people in the contacts table. How can I convert from the comma-separated list to the people's names? A standard left join won't work.

Is there a way I can do it in the original query or would I have to add a while inside a foreach inside my original while loop? Such as (the following doesn't work either but it's as far as I've got):

//original query while {
        $contacts_array = array(implode(",",$db['Contacts'])); 
        foreach ($contacts_array as $contacts_id) 
        { 
            $contacts_query = "SELECT Name FROM data_contacts WHERE ID='$contacts_id'";
            $contacts_result = mysql_query ($contacts_query);
            $contactslist="";
            while($contacts=mysql_fetch_array($contacts_result)){
                $contactslist .= $contacts['Name'].", ";
            } 
        } 
echo $contactslist;
}

Read up on 'normalization' and 'normal forms' (such as Boyce-Code Normal Form or BCNF). Properly normalized tables, as recommended in the accepted answer, avoid the problems that your thoroughly denormalized Data 'table' (it isn't even in 1NF, so calling it a 'table' is a serious misnomer) shows up. — Jonathan Leffler
– Jonathan Leffler, Commented Jun 11, 2022 at 16:27

jeffjenx · Accepted Answer · 2012-06-19 20:35:39Z

5

I've always stored many-to-many relationships with individual rows instead of comma-separated values:

ID - Contacts
1 - 1
1 - 3
2 - 2
3 - 2
3 - 3
3 - 1

answered Jun 19, 2012 at 20:35

jeffjenx

17.6k6 gold badges63 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

barsju Over a year ago

You should change your statement to: You must always... Otherwise I fully agree.

user122642 Over a year ago

There is other data in the table so i dont think having separate rows is not an option at the moment.

jeffjenx Over a year ago

@user122642 That is where an additional intermediary table would come into play. A data_contacts table, with data_id and contact_id as the fields.

barsju Over a year ago

Then you should extract this in a separate table! Any other solution will be a mistake and come back to haunt you in the future!

user122642 Over a year ago

I crated the extra tables. Thanks for your help.

bfavaretto · Accepted Answer · 2012-06-19 20:36:29Z

1

It is possible by using FIND_IN_SET. However, you should really rethink your table structure as MrSlayer suggested. That's the proper, relational, way to store a many to many relationship.

answered Jun 19, 2012 at 20:36

bfavaretto

72k18 gold badges118 silver badges162 bronze badges

Collectives™ on Stack Overflow

left join a column with array in php/mysql

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related