Add listing to mutiple headings

PHP
1

Whats the best table format to enable listings to be added to mutiple headings?

There are 2 tables so far “headings” and “Listings”. The listings table holds the heading id for the query as it stands now…

Thanks

2

Hey,

You’ll need a third table, listing_headings (for example) which just has two columns: listing_id and heading_id. Then you don’t have a heading id in the listing table any more. This is a many to many relationship.

If you wanted all the listings under a header

SELECT l.*, h.* FROM listings l, heading h, listing_headings lh 
WHERE l.id = lh.listing_id AND lh.heading_id = h.id
AND h.id = xx

All headings for a listing

SELECT h.* FROM listings l, heading h, listing_headings lh 
WHERE l.id = lh.listing_id AND lh.heading_id = h.id
AND l.id = xx
3

yup but i think inner joins are better than cross joins

4

So if one listing went under three headings the listing_headings table would have 3 new entries…

headingid listingid
1 2
3 2
7 2

?

Thanks

5

Correct

6

I got it, thanks alot…

7

Oh I assume that I dont need a field using auto_increment in the new table?

8

well it depends upon your design as well
have you checked there are no double entries…

if there can be…having auto_increment primary key in that table helps in editing and deleting
(delete from table where id=xx not where key1=xxx and key2=xxx)

9

Ahh yes good point…

10 
	$sql = mysql_query("SELECT headingid,  heading FROM heading");
	while ($row = mysql_fetch_array($sql)) {
	$headings[] = $row['heading'];
	}

	foreach($headings as $heading) { 

	echo '<td><input type="checkbox" name="'.$heading.'" value="'.$headingid.'" '.$checked.'></td>';

How do you go about it to get headingid into value=“”

11 


while ($row = mysql_fetch_array($sql)) {
$heading = $row['heading'];
$headingid= $row[headingid];
echo '<td><input type="checkbox" name="'.$heading.'" value="'.$headingid.'" '.$checked.'>'.$heading.'</td>';
	}

can be made better but this should work

12

Oh yer thanks… Overthinking it again

13

i guess you will fall into problem again in later while taking value before inserting in database as
echo ‘<td><input type=“checkbox” name="’.$heading.‘" value="’.$headingid.‘" ‘.$checked.’>’.$heading.‘</td>’;
is not 100 right way of doing it
better may be
input type=“checkbox” name=“something”…//this should be array
now you can check that array and make , separated value or serialize it or do something other
check these

or google it

14

I have the inserting of the info sorted… Not really sure how to when it comes to editing the record and the mutiple headings for the listings are changed?

As the UPDATE is about to happen, delete all the records in the table equal to the listingid then INSERT the updated POST data?