SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    724
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    add listing to mutiple headings

    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. #2
    SitePoint Addict Mal Curtis's Avatar
    Join Date
    Jul 2009
    Location
    New Zealand
    Posts
    327
    Mentioned
    13 Post(s)
    Tagged
    0 Thread(s)
    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
    Code SQL:
    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
    Code SQL:
    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. #3
    SitePoint Wizard frank1's Avatar
    Join Date
    Oct 2005
    Posts
    1,392
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yup but i think inner joins are better than cross joins

  4. #4
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    724
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #5
    SitePoint Addict Mal Curtis's Avatar
    Join Date
    Jul 2009
    Location
    New Zealand
    Posts
    327
    Mentioned
    13 Post(s)
    Tagged
    0 Thread(s)
    Correct

  6. #6
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    724
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I got it, thanks alot..

  7. #7
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    724
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh I assume that I dont need a field using auto_increment in the new table?

  8. #8
    SitePoint Wizard frank1's Avatar
    Join Date
    Oct 2005
    Posts
    1,392
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by _matrix_ View Post
    Oh I assume that I dont need a field using auto_increment in the new table?
    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. #9
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    724
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ahh yes good point..

  10. #10
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    724
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    	$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. #11
    SitePoint Wizard frank1's Avatar
    Join Date
    Oct 2005
    Posts
    1,392
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by _matrix_ View Post
    Code:
    	$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=""
    PHP Code:

    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. #12
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    724
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh yer thanks.. Overthinking it again

  13. #13
    SitePoint Wizard frank1's Avatar
    Join Date
    Oct 2005
    Posts
    1,392
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by _matrix_ View Post
    Oh yer thanks.. Overthinking it again
    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
    http://www.phpfreaks.com/tutorial/wo...and-a-database
    http://www.daniweb.com/forums/thread106081.html
    or google it

  14. #14
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    724
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •