Check checkbox on many-to-many relation

General PHP Help
#1

Hi all,

I am fighting with the following issue and was hoping you can give me some insights, as I am totally stuck.
What’s going on? I have 3 tables, created as follows:

table category_p
column id
column name
column type

table home
column id

table link_category_p_home
column category_p_id
column home_id

These tables are linked with eachother, where link_category_p_home is the many-to-many relation table. Now, I want to display for a selected home ID all connected category_p_id’s as a checked checkbox. All category_p_id’s which are not findable in the link_category_p_home table have to be displayed too, but remain unchecked. I am really trying everything but I just can’t figure it out. For most of you it’s probably easy stuff but for me it’s just like aaahhh. Anyway, here is the code:

<table cellpadding="0" cellspacing="0" border="0"> <?php $query1=mysqli_query($conn,"SELECT category_p.id, category_p.name, category_p.type, home.id FROM home, category_p,link_category_p_home WHERE link_category_p_home.home_id = home.id AND link_category_p_home.category_p_id = category_p.id AND home.id='$id[$i]' ORDER BY home.name ASC"); while($row=mysqli_fetch_array($query1)){ $category_p_id=$row['id']; ?> <tr> <td width="20" style="padding-bottom: 4px"><input name="selector[]" type="checkbox" value="<?php echo $category_p_id; ?>" checked></td> <td width="100" style="padding-top:3px; padding-bottom: 1px"><?php echo $row['type'] ?></td> <td width="265" style="padding-top:3px; padding-bottom: 1px"><?php echo $row['name'] ?></td> </tr> <?php } ?> </table>

If somebody could get me on the way, that would be really wonderfull!
Many thanks in advance.

#2

You should never have a many to many relationship?

#3

Can you tell me what you mean? You say something without any arguments. On this same forum all posters suggested to use a many to many relation and now you say I may never use them? Why? And if I shouldnt use them, then what should I use and how?

#4
You should never have a many to many relationship?

Where do you get this from? There are countless situations for a Many to Many relationship

Consider two tables, One Authors, One Books.

An Author can author many books and a book can have many authors. In a Relational Database you would use a third Junction/Cross Reference table with the appropriate ID references to pull your data.

It is not exactly the way a Many to Many relationship is, in say, Access using two tables only. I suppose you may have been referring to something like that.

This quote I found explains how to deal with it in more detail:

To get around the problem of having a many-to-many relationship you need to break apart the many-to-many relationship into two one-to-many relationships. Using a third table, commonly called a “join table”, does this. Each record in the “join table” would have the foreign key fields of the two tables it is joining together. Nothing special needs to be done with the foreign key fields in the join table as they will get populated with data from the other two tables as records are created. It is not uncommon for a join table to have a lot of records in it – since records are created in the join table as records are created in the two tables it joins.
#5
An Author can author many books and a book can have many authors. In a Relational Database you would use a third Junction/Cross Reference table with the appropriate ID references to pull your data.

Ha, I just wrote out a big post explaining why many-many relationships are bad but decided to do a little research. Turns out that I studied another garbage module in advanced databases in uni!

I was thinking about scalability and unneeded data but yeah many-many is ok.

I stand corrected.

#6

There is a lot of bad/mis information out there. Luckily we have a board like this where we can learn from each other.

I am picking up new knowledge almost every day as a result of this board, either from researching someones problem or someone posting alternative methods.

(Shhhh! I actually had to research this one myself. I only knew it didn’t sound right.)

#7

I am happy I am on the right track and I can see you all know where you are talking about. Anyone would like to help me a bit here? Appreciate it!

#8

Anybody please?

#9

Today I 've tried some more but I just cannot get it to work.
I can show all categories that belong to one of the selected ID’s in home, but I can’t add the categories that are not selected for this home ID. With some help I now I need to use left join as follows :

      [code]  <table cellpadding="0" cellspacing="0" border="0">
                <?php 
                //$query1=mysqli_query($conn, "select * from categorie_p")or die(mysqli_error($conn));
                $query1=mysqli_query($conn,"SELECT
c.id AS cat_id,
c.namr,
h.id AS home_id,
IF(l.category_p_id IS NULL, 0, 1) AS checked

FROM
category_p AS c
LEFT JOIN
link_category_p_home AS l
ON c.id = l.category_p_id AND l.home_id = 1234
LEFT JOIN
home AS h
ON l.home_id = h.id ");
while($row=mysqli_fetch_array($query1)){
$categorie_p_id=$row[‘cat_id’];
?>



<?php echo $row['type'] ?>
<?php echo $row['cbr_naam'] ?>

<?php } ?>
[/code]

Unfortunately this still does not work. Let’s say there are 10 categories and 1 home ID has 3 categories of it. Then I want to display all categories, but only those 3 checked. Rest remains unchecked.

Any help would be appreciated very much!!

#10

Unfortunately I’m having the following problem with this:

// PART 1 $insert_id = $id; $result = mysqli_query($conn, "UPDATE home SET name='$name', active='$active' where id='$id'")or die(mysqli_error($conn)); $result = mysqli_query($conn, "DELETE FROM link_cloud_home WHERE home_id='$id'")or die(mysqli_error($conn)); if(isset($_POST['selector_cloud'])){ $id = $_POST['selector_cloud']; $N = count($id); for($i=0; $i < $N; $i++) { $result = mysqli_query($conn, "SELECT * FROM cloud where id='$id[$i]'"); while($row = mysqli_fetch_array($result)) { echo "<br>"; echo $insert_id; echo "<br>"; echo $row['id']; $sql = "INSERT INTO link_cloud_home (home_id, cloud_id) VALUES ('{$insert_id}', '{$row['id']}');"; $retval = mysqli_query($conn, $sql); if(! $retval ) { die('Could not enter data: ' . mysqli_error($conn)); } } } } // PART 2 $result = mysqli_query($conn, "DELETE FROM link_category_p_home WHERE home_id='$id'")or die(mysqli_error($conn)); if(isset($_POST['selector_cat_p'])){ $id = $_POST['selector_cat_p']; $N = count($id); for($i=0; $i < $N; $i++) { $result = mysqli_query($conn, "SELECT * FROM category_p where id='$id[$i]'"); while($row = mysqli_fetch_array($result)) { echo "<br>"; echo $insert_id; echo "<br>"; echo $row['id']; $sql = "INSERT INTO link_category_p_home (home_id, category_p_id) VALUES ('{$insert_id}', '{$row['id']}');"; $retval = mysqli_query($conn, $sql); if(! $retval ) { die('Could not enter data: ' . mysqli_error($conn)); } } } }

The previous code works when I use one of the parts, so for example PART 1 or PART 2. But when I use them together, I receive the following error:

Notice: Array to string conversion in C:\xampp\htdocs\admin\edit\home_edit_save.php on line 44

Line 44 is as follows:

$result = mysqli_query($conn, "DELETE FROM link_category_p_home WHERE home_id='$id'")or die(mysqli_error($conn));

Am I missing something here? Why does it work with one of those 2 parts and why doesn’t it work for those 2 parts combined? Any idea? Thanks!

#11

[php]$result = mysqli_query($conn, “DELETE FROM link_category_p_home WHERE home_id=’$id’”)or die(mysqli_error($conn));[/php]

$id at this point is an array and it can’t covert it to a string in your query…

#12

Ok understood. How can I solve this the best way? Rename id?
And how about the rest of my code since I am still learning. Does it look ok / logical?

Thank you very much!!

Sponsor our Newsletter | Privacy Policy | Terms of Service