SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Didn't design it properly...

    OK, I wasn't thinking when I designed the tables. Currently, when a record is added it contains the user ID that added it. However, if a record is deleted, so is the credit that goes to that user for adding it. I've decided to create an 'added' table that contains a record for every addition to the database.

    What I want to do now is get the user ID for each record out of the 'books' table and insert it into the 'books_added' table. Can I do that with mysql? Or do I need to loop it in php instead?

    Code MySQL:
    CREATE TABLE `books` (
      `id` bigint(20) unsigned NOT NULL auto_increment,
      `added_uid` bigint(20) unsigned NOT NULL default '0',
      `dev_id` int(10) unsigned NOT NULL,
      `date_added` datetime NOT NULL default '0000-00-00 00:00:00',
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
     
    CREATE TABLE `books_added` (
      `ad_id` bigint(20) unsigned NOT NULL auto_increment,
      `ad_book_id` bigint(20) unsigned NOT NULL,
      `ad_uid` bigint(20) unsigned NOT NULL,
      `ad_dev_id` bigint(20) unsigned NOT NULL,
      `ad_date_added` datetime NOT NULL,
      PRIMARY KEY  (`ad_id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
     
    SOMETHING LIKE
     
    INSERT INTO books_added (ad_book_id,ad_uid,ad_dev_id,ad_date_added)
    SELECT id,added_uid,dev_id,date_added
    FROM books

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    i fail to see why splitting this off to another table solves the issue.

    a better solution would be to not allow users to be deleted. simply disable them.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  3. #3
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    i fail to see why splitting this off to another table solves the issue.

    a better solution would be to not allow users to be deleted. simply disable them.
    Well, that's not really why the record was being deleted. However, your post made me think about this in a different light. I'm going to keep it as it is.

    Thanks!


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
  •