Display record that relies on many to many

General PHP Help
#1

Hello all,

Hopefully I can get some guidance on this. I’m looking to display an entry that relies on a many to many relationship in the DB. I have a list of contacts, each contact has one or many tags (for organization). I have a contacts table. I have a tags table. I have a intermediary contacts_tags table to manage the many to many relationship.

So let’s assume I want one contact record. I query the DB with some JOIN in my select statement and what happens when I need to list one or many of these tags - its like I need to loop through some subset of the result. I don’t get it. If the results provide one row (one contact) how do I extract multiple tag results that relate to this one contact?

#2

You are on the right track. The contact will be repeated but you will only use that data once.

#3

Thanks for the reply. I see. So even though I’m only interested in 1 contact the results don’t quite come that way. If 1 contact has 5 tags there will essentially be 5 rows of data in my result. So how would I just list the contact details once and split out say a comma separated list of tag names?

#4

There are some ways to solve your problem but i will show you one.
I assume that your tables (except the join table) have an unique id column.

$last_id = 0;

foreach($contacts as $contact) {
    if($contact['id'] != $last_id) { // ahh we start with another contact now
        // show contact details just once
        echo $row['contactname'];

        // save contacts id so that we will notice a new contact again
        $last_id = $contact['id'] ;
    }

    // now show every tag
    echo $row['tagname'];
}
#5

Or see php’s array_column() function.

#6

Post an SQL dump of your DB with a few sample records.

#7

Oh hell. You’ll be disappointed to know that I’ve never done that and I’m not entirely sure this is what you mean, but here she is… note: this is all dummy data…

--
-- Table structure for table `contacts`
--

CREATE TABLE `contacts` (
  `id` int(10) UNSIGNED NOT NULL,
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `user_id` int(11) NOT NULL,
  `email` varchar(60) DEFAULT NULL,
  `first_name` varchar(60) NOT NULL,
  `last_name` varchar(60) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `contacts`
--

INSERT INTO `contacts` (`id`, `created_on`, `user_id`, `email`, `first_name`, `last_name`) VALUES
(2712, '2019-08-05 17:40:39', 0, '[email protected]', 'Chris', ''),
(2713, '2019-08-05 17:57:12', 0, '[email protected]', 'Gary', ''),
(2714, '2019-08-05 19:09:59', 0, '[email protected]', 'Chris', 'Falk'),
(2715, '2019-08-05 20:09:50', 0, '[email protected]', 'tom', ''),
(2719, '2019-08-05 21:22:27', 0, '[email protected]', 'Tom', 'River'),
(2720, '2019-08-05 21:29:10', 0, '[email protected]', 'Zod', ''),
(2721, '2019-08-05 21:32:44', 0, '[email protected]', 'James', 'Butt'),
(2722, '2019-08-05 21:32:44', 0, '[email protected]', 'Josephine', 'Darakjy'),
(2723, '2019-08-05 21:32:44', 0, '[email protected]', 'Art', 'Venere'),
(2724, '2019-08-05 21:32:44', 0, '[email protected]', 'Lenna', 'Paprocki'),
(2725, '2019-08-05 21:32:44', 0, '[email protected]', 'Donette', 'Foller'),
(2726, '2019-08-05 21:32:44', 0, '[email protected]', 'Simona', 'Morasca'),
(2727, '2019-08-05 21:32:44', 0, '[email protected]', 'Mitsue', 'Tollner'),
(2741, '2019-08-05 21:32:44', 0, '[email protected]', 'Bette', 'Nicka'),
(2742, '2019-08-05 21:32:44', 0, '[email protected]', 'Veronika', 'Inouye'),
(2743, '2019-08-05 21:32:44', 0, '[email protected]', 'Willard', 'Kolmetz'),
(2744, '2019-08-05 21:32:44', 0, '[email protected]', 'Maryann', 'Royster'),

-- --------------------------------------------------------

--
-- Table structure for table `tags`
--

CREATE TABLE `tags` (
  `id` int(10) UNSIGNED NOT NULL,
  `user_id` int(11) NOT NULL,
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `name` varchar(60) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tags`
--

INSERT INTO `tags` (`id`, `user_id`, `created_on`, `name`) VALUES
(17, 6, '2019-08-19 19:40:38', 'Democrats'),
(19, 6, '2019-08-19 19:41:43', 'Republicans'),
(27, 0, '2019-08-19 19:51:10', 'Wonderful People'),
(28, 0, '2019-08-19 20:24:43', 'Boiling Springs'),
(29, 0, '2019-08-19 20:24:53', 'Mechanicsburg');

-- --------------------------------------------------------

--
-- Table structure for table `tags_contacts`
--

CREATE TABLE `tags_contacts` (
  `contact_id` int(11) NOT NULL,
  `tag_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `created_on` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `username` varchar(50) NOT NULL,
  `password` varchar(60) NOT NULL,
  `email` varchar(60) DEFAULT NULL,
  `company_name` varchar(60) DEFAULT NULL,
  `address` varchar(60) DEFAULT NULL,
  `state` varchar(60) DEFAULT NULL,
  `city` varchar(60) DEFAULT NULL,
  `zip_code` varchar(20) DEFAULT NULL,
  `time_zone` varchar(60) DEFAULT NULL,
  `first_name` varchar(60) DEFAULT NULL,
  `last_name` varchar(60) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `created_on`, `username`, `password`, `email`, `company_name`, `address`, `state`, `city`, `zip_code`, `time_zone`, `first_name`, `last_name`) VALUES
(0, '2019-06-05 16:40:43', 'thewebprojects', '$2y$10$fKe5GtZtjsfFnz5NSsD.iOaifme6BFSP2l8EZm6YpO8qHHNKYbjUK', '[email protected]', 'theWebProjects', '589 W 1st Street', 'PA', 'Boiling Springs', '17007', 'America/New_York', 'Christoper', 'Falkenstein'),
(6, '2019-08-14 17:14:24', 'sendsible', '$2y$10$2DcqgT16FiBkTJFNfqX9E.jUUlYzBQe3XYqGFUYUk.SoP6fJFSR86', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `contacts`
--
ALTER TABLE `contacts`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `idx` (`user_id`,`email`);

--
-- Indexes for table `tags`
--
ALTER TABLE `tags`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `user_id_2` (`user_id`,`name`),
  ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `tags_contacts`
--
ALTER TABLE `tags_contacts`
  ADD UNIQUE KEY `u_idx` (`contact_id`,`tag_id`),
  ADD UNIQUE KEY `contact_id` (`contact_id`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `username` (`username`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `contacts`
--
ALTER TABLE `contacts`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4239;

--
-- AUTO_INCREMENT for table `tags`
--
ALTER TABLE `tags`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=31;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `contacts`
--
ALTER TABLE `contacts`
  ADD CONSTRAINT `contacts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

--
-- Constraints for table `tags`
--
ALTER TABLE `tags`
  ADD CONSTRAINT `user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
#8

Yeah, that’s exactly what I meant, but an attachment to a single file to download would have been better.

#9

Are users something different then contacts? If so, what is the difference? If there is no difference, get rid of the contact_id stuff and just use user_id.

Is the user_id some different than the contacts.id auto-increment number? If you dont have some custom user id reference, then just use the auto-increment id and rename it to user_id.

#10

Gotcha… I went back and preformatted that mess. That cleaned it up a bit.

#11

Users are different than contacts. This is an app I’m building to learn. I’ll refactor things as I learn more and more. That app’s goal is a very simple email marketing tool (think Sendy). So users are the folks that log into this app. Contacts are their subscribers.

#12

Here is something to think about. Who are users? Who are contacts? They are people, right? What kind of people? People with roles, ie, contact, user, employee, vender, etc…

So then you should have a person table, a roles table, and a joiner table to store the relations. You are now infinitely scalable. Unlimited people with unlimited roles.

#13

Whoa. I see. And yeah… many of the columns of the users table are duplicates of the columns found in my contacts table (e.g. first_name, last_name, etc.). I just didn’t think of them similar in any way because their roles are very different, but yeah… you could certainly do that with a roles relation.

So I appreciate the database design help. Like most things in programming it makes sense now that I see it (or think about it a new way).

Back to actually displaying this stuff on a page though. I can’t quite seem to get my head around the query using JOIN (let alone loopin’ though those tags). This isn’t a call to give me code samples to cut and paste. That won’t help me learn, but … I think there are some fundamental things missing in my mind (like the fact that the query mentioned above splits out 5 rows of data).

So we create a person table, a role table, a person_role table (intermediary), a tags table, a person_tags table (intermediary). Imagine a page where the person (with the proper role) can edit their contacts (which now reside in the persons table). I’d need to display the person’s details and roll through all available tags and mark/check which tags the current person is assigned to. What the heck would that look like? Every time I look at a JOIN statement I get all dizzy and want to give up and drink a beer. That’s on me though. I know gotta work that out myself.

But it’d be a JOIN statement of some kind which brings that data together, then I’d need to use the above mentioned tactics to spit out that data. It seems like working with many to many relations requires some hacking - doesn’t seem very elegant considering how very common the task must be.

#14

Make sure you learn up on Database Normalization. As for the joins, set aside your project and create a new test project with two simple tables to practice doing joins on.

#15

Thanks for the input. I’ll be back to ask more questions later. :grinning:

Sponsor our Newsletter | Privacy Policy | Terms of Service