inserting data into multiple tables

[CORT0619]

Hello,

 

I am trying to insert data into multiple tables from one form and the customers table has a foreign key on the user_id colum that relates it to the customers table. Since the user_id field is the primary key on the users table and is auto incremented i'm getting the error message below and i'm trying to figure out how I can fix the problem.

 

Error message:

Cannot add or update a child row: a foreign key constraint fails

 

below is mysql:

 

$q1 = "INSERT INTO users(login_name, password, security_level) VALUES('$login_name', '$password', 1)";

 

$q = "INSERT INTO customers(first_name, last_name, address, city, zip_code, phone_number, alt_phone, email) VALUES('$first_name', '$last_name', '$address', '$city', '$zip_code', '$phone_number', '$alt_phone', '$email')";

 

$r1 = @mysqli_query($dbc, $q1);

 

$r = @mysqli_query($dbc, $q);

 

if($r1 && $r) {

[fenway]

Don't use @ -- that's just masking any errors.

 

Also, without seeing the table structure, it's hard to guess what field you missed.  But I don't' see you getting back a UID to use for a subsequent insert.

[CORT0619]

This is the structure for both tables:

 

CREATE TABLE IF NOT EXISTS `customers` (

  `customer_id` mediumint( unsigned NOT NULL AUTO_INCREMENT,

  `first_name` varchar(30) NOT NULL,

  `last_name` varchar(70) NOT NULL,

  `address` varchar(70) NOT NULL,

  `city` varchar(30) NOT NULL,

  `zip_code` char(5) NOT NULL,

  `phone_number` varchar(15) NOT NULL,

  `alt_phone` varchar(15) DEFAULT NULL,

  `email` varchar(50) NOT NULL,

  `user_id` mediumint( unsigned NOT NULL,

  PRIMARY KEY (`customer_id`),

  KEY `user_id` (`user_id`)

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;

 

-- --------------------------------------------------------

 

--

-- Table structure for table `users`

--

 

CREATE TABLE IF NOT EXISTS `users` (

  `user_id` mediumint( unsigned NOT NULL AUTO_INCREMENT,

  `login_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,

  `password` char(40) COLLATE utf8_unicode_ci NOT NULL,

  `security_level` smallint(6) NOT NULL DEFAULT '1' COMMENT '0 is for admins 1 is customers',

  PRIMARY KEY (`user_id`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=12 ;

[The Little Guy]

Off topic..

 

If your going to hard code a value, in your query, might as well make it a MySQL default value.

 

On topic..

 

I looks to me that your second query needs the primary key from your first query.

[fenway]

I don't see any FK constraints.???