1 Reply Latest reply on Jan 5, 2009 10:34 AM by Joseph07888

    User Registration With Multiple Insert Transactions, Link Transactions, and Captcha Image...

      Hi,

      I have a user registration page (PHP/MySQL), where i need to insert information into three different tables. The third table I have to insert 3 rows into. I need to have a captcha image on this page also. I have tried a few different methods, all have not worked. I keep getting a USER_ID column cannot be null.

      My tables are :

      users {
      USER_ID INT(11) PRIMARY AUTO INCREMENT NOT NULL
      ACCESS_LEVEL tinyint(1) NOT NULL
      ACTIVE tinyint(1) NOT NULL DEFAULT 0
      PASSWORD binary(32) NOT NULL
      EMAIL varchar(55) NOT NULL
      RANDOM_KEY varchar(255) NULL
      MAILING_LIST tinyint(1) DEFAULT 1
      }

      listeners {
      FIRST_NAME varchar(25) NOT NULL
      LAST_NAME varchar(25) NOT NULL
      CITY varchar(25) NOT NULL
      STATE varchar(25) NULL
      COUNTRY varchar(25) NOT NULL
      USER_ID int(11) NOT NULL PRIMARY
      }

      user_genres {
      ID int(11) AUTO INCREMENT NOT NULL PRIMARY
      USER_ID int(11) NOT NULL
      GENRE_ID int(11) NOT NULL
      }

      My Registration Page:

      5 insert transactions: ins_users, ins_listeners, ins_genre1, ins_genre2, ins_genre3
      4 Link Transactions:
      link_listeners - links ins_users as master with ins_listeners as slave on USER_ID
      link_genre1 - links ins_listeners as master with ins_genre1 as slave on USER_ID
      link_genre2 - links ins_genre1 as master with ins_genre2 as slave on USER_ID
      link_genre2 - links ins_genre2 as master with ins_genre3 as slave on USER_ID

      So far, it gives me a 'USER_ID' column cannot be NULL error, coming from the link_listeners Link Transaction. I thought ADDT is supposed to magically fill that foreign key in for the linked tables? Any ideas??
        • 1. Re: User Registration With Multiple Insert Transactions, Link Transactions, and Captcha Image...
          Joseph07888
          Hi,

          Personally I would not do it that way on the registration page and have your genre options within a members area.

          This is because it could cause issues when registering users.

          captcha image is not linked to the database and is simply a checking system generated by php code in the ins page.

          So you can add the captcha on any ins page at the bottom.

          The USER_ID is this on your users tbl?

          Also I would not have the same USER_ID in all your tbl's for the simple reason of relationships (FK's) as it can cause issues later down the line. I would do something like

          tbl_User = USER_ID

          tbl_listeners = USERIDLIS

          tbl_user_genres = USERIDGEN

          this will uniquly identify each ID in each table with a unique reference.

          Are you new to MYSQL DB or is it me doing it wrong?

          Cheers,
          Joe