MYSQL PHP: Inserting into database many to many relationships

Question

I have two questions :

I have 3 tables : EMTS , levels, and EMTS_has_levels. It is a many to many relationship between EMTS AND levels

My first question:

In levels table, I have 2 columns, idlevels(primary key) and I have a column called levelname :

CREATE TABLE IF NOT EXISTS `mydb`.`levels` (
  `idlevels` INT NOT NULL AUTO_INCREMENT,
  `levelname` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`idlevels`))
ENGINE = InnoDB;

I want to insert multiple values to levelname. AN EMT might have multiple levels. SO what I did is add this to the html form:

<input type="checkbox" name="level" value="level1">Level1</input>

<input type="checkbox" name="level" value="level2">Level2</input>

ANd in the register.php I have:

if(isset($_POST['level']))
{
    $name=$_POST['level'];
}
$stmt4=$mysqli->prepare("INSERT INTO levels(levelsname) VALUES(?)");
$stmt4->bind_param("s",$name);
$stmt4->execute();

When I try the above php code I get an error. Idlevels isn't the reason as it should autoincrements, but why isn't the insertion to levelsname working?

My second question: This is EMTS table:

CREATE TABLE IF NOT EXISTS `mydb`.`EMTS` (
  `idEMTS` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `nickname` VARCHAR(45) NOT NULL,
  `age` VARCHAR(45) NOT NULL,
  `nuber` VARCHAR(45) NOT NULL,
  `email` VARCHAR(45) NULL,
  `city` VARCHAR(255) NOT NULL,
  `password` VARCHAR(45) NOT NULL,
  `shifts_idshifts` INT NULL,
  `bloodtype_id` INT NULL,
  `street` VARCHAR(45) NOT NULL,
  `building` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`idEMTS`),
  INDEX `fk_EMTS_shifts1_idx` (`shifts_idshifts` ASC),
  INDEX `fk_EMTS_bloodtype1_idx` (`bloodtype_id` ASC),
  UNIQUE INDEX `nickname_UNIQUE` (`nickname` ASC),
  CONSTRAINT `fk_EMTS_shifts1`
    FOREIGN KEY (`shifts_idshifts`)
    REFERENCES `mydb`.`shifts` (`idshifts`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_EMTS_bloodtype1`
    FOREIGN KEY (`bloodtype_id`)
    REFERENCES `mydb`.`bloodtype` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

This is my EMTS_has_levels table:

CREATE TABLE IF NOT EXISTS `mydb`.`EMTS_has_levels` (
  `levels_idlevels` INT NULL,
  `EMTS_idEMTS` INT NULL,
  PRIMARY KEY (`levels_idlevels`, `EMTS_idEMTS`),
  INDEX `fk_levels_has_EMTS_EMTS1_idx` (`EMTS_idEMTS` ASC),
  INDEX `fk_levels_has_EMTS_levels1_idx` (`levels_idlevels` ASC),
  CONSTRAINT `fk_levels_has_EMTS_levels1`
    FOREIGN KEY (`levels_idlevels`)
    REFERENCES `mydb`.`levels` (`idlevels`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_levels_has_EMTS_EMTS1`
    FOREIGN KEY (`EMTS_idEMTS`)
    REFERENCES `mydb`.`EMTS` (`idEMTS`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

EMTS_has_levels has only two columns that are foriegn keys . First one is levels_idlevels referencing levels table, and the second one is EMTS_idEMTS referencing idEMTS from EMTS table.

My question is say I filled EMTS and I filled levels how can I fill the two foreign keys in EMTS_has_levels?

I tried this but it gives me an error:

 $stmt10=$mysqli->prepare("INSERT INTO belongs(levels_idlevels) SELECT idlevels FROM levels WHERE levelname=$_POST['levelname'] " );
    $stmt10->execute();
$stmt11=$mysqli->prepare("INSERT INTO belongs(EMTS_idEMTS) SELECT idEMTS FROM EMTS WHERE nickname=$_POST['nickname'] " );
$stmt11->execute();

How can I fill the third table that results from the many to many relationship?

You have a typo: The name of the column is `levelname`, but you're inserting into `levelsname`. But why is this form inserting into the `levels` table? If you're linking an EMT to a level, you should only be inserting into `belongs`. — Barmar, Nov 20 '17 at 16:55
This looks wrong too `$stmt4->bind_param("s",$name);` as you have a question mark as a placeholder. — Pete, Nov 20 '17 at 16:58
`password VARCHAR(45)` - Right away, this suggests an unsafe password hashing method. One such as `password_hash()`, stores a 60 length char. If you plan on using this or are using it already, it already failed you, *silently*. — Funk Forty Niner, Nov 20 '17 at 17:06
you also need to treat your checkbox inputs as arrays, given the same name attributes. If you only require one to be checked, don't use those but radios instead. — Funk Forty Niner, Nov 20 '17 at 17:06

score 1 · Answer 1 · answered Nov 20 '17 at 17:07

If you want to allow multiple levels, you need to use an array-style name so you get all of them.

<input type="checkbox" name="level[]" value="level1">Level1</input>
<input type="checkbox" name="level[]" value="level2">Level2</input>

Then you can loop over all the values. And you need to insert just once into EMTS_has_levels, getting level_idlevel from the levels table, and EMTS_idEMTS from the EMTS table

$stmt4=$mysqli->prepare("INSERT INTO EMTS_has_levels (levels_idlevels, EMTS_idEMTS)
    SELECT l.idlevel, e.idEMTS
    FROM levels AS l
    CROSS JOIN EMTS AS e
    WHERE l.levelname = ?
    AND e.nickname = ?");
$stmt4->bind_param("s",$name, $_POST['nickname']);
foreach ($_POST['level'] as $name) {
    $stmt4->execute();
}

MYSQL PHP: Inserting into database many to many relationships

1 Answers1