RedBean PHP does not insert into two tables

Question

I have two MySQL tables:

user

email

email table has a foreign key from user. I insert a record in to user table and get the insert id then insert in to email table.

I do this in RedBean like this

$user = R::dispense('user');
$user->name = 'John' ;

$email = R::dispense('email');
$email->email = $this->Email;
$email->user_id = R::store($user);

R::store($email);

But only user table is filled. No record has entered in to the email table.

What could be the issue here?

-- -----------------------------------------------------
-- Table `mydb`.`User`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`User` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `firstname` VARCHAR(45) NULL,
  `middlename` VARCHAR(45) NULL,
  `lastname` VARCHAR(45) NULL,
  `email` VARCHAR(255) NULL,
  `password` VARCHAR(255) NULL,
  `address` VARCHAR(255) NULL,
  `gender` ENUM('m','f') NULL DEFAULT 'm',
  `birthday` VARCHAR(45) NULL,
  `profilepic` VARCHAR(255) NULL,
  `datecreated` DATETIME NULL,
  `lastlogin` DATETIME NULL,
  `lastactive` DATETIME NULL,
  `mobilePhone` VARCHAR(45) NULL,
  `businessPhone` VARCHAR(45) NULL,
  `homePhone` VARCHAR(45) NULL,
  `faxNumber` VARCHAR(45) NULL,
  `drivingLicence` VARCHAR(45) NULL,
  `passportNum` VARCHAR(45) NULL,
  `bloodgrp` VARCHAR(5) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

And the email table

-- -----------------------------------------------------
-- Table `mydb`.`email`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`email` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(255) NOT NULL,
  `verifyCode` VARCHAR(255) NULL,
  `verified` TINYINT(1) NULL,
  `User_id` INT NOT NULL,
  PRIMARY KEY (`id`, `User_id`),
  INDEX `fk_email_User1_idx` (`User_id` ASC),
  CONSTRAINT `fk_email_User1`
    FOREIGN KEY (`User_id`)
    REFERENCES `mydb`.`User` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

besides when I enter raw mysql, the insert works as expected. I think there is something wrong with my redbean usage. I've searched many places but I cannot figure this out.

Answer 1

Change the lines

$email->user_id = R::store($user);
R::store($email);

to

$user->email = $email;
R::store($user);

and the relationship will be created automatically, assuming you are in fluid mode.

The Redbean engine will create the foreign key relationship and set a column in email table called user_id for you. You don't even have to store the $email object, that happens when the $user object is stored.

Answer 2

I'm pretty sure you're interfering with the one-to-many relation functionality that is part of RedBean: http://redbeanphp.com/adding_lists. When you add relations using $obj -> ownChild, you are actually setting child_id in your object. By directly setting child_id, you're creating a field that RedBean chooses to retrieve through an own-list. However, this is just a hunch - I'll test this next time I'm at my development machine and update this answer.