CODEWITHSUNDEEP
phpmysqldatabase
Baker Johnson
Baker Johnson

Reputation: 253

How to insert a variable number of values into a database using php

I have a form where users can select the number of questions (no more than 10) they would like to enter marks into.

Enter Number of Questions : ____

Another form pops up with the requested number of questions where the user can enter marks.

If 3 was inputted :

Question1 : ____

Question 2: ____

Question 3: ____

I have a table called questions with 11 rows where ID is auto-incremented and the other questions can be null if there is no marks to be entered in that question

| ID | Q1 | Q2 | . . . | Q10 |

This leads to my question which is, since the number of questions that the user selects can vary how can I input these values into a database using PHP.

Upvotes: 1

Views: 527

Answers (2)

user1032531
user1032531

Reputation: 26281

First, be sure to use a Many-to-many table (called "yourPrimaryTable_has_questions" in my below schema) between your questions table and the primary table which represents the page.

Then cycle through the questions, and add a row for each in your many-to-many table. If you use a name with brackets but no value in the brackets, it will auto-increment when your PHP server receives the data.

if (isset($_POST) && $_POST  && $_POST['questions'])
{
    echo("<form name='xxx'>");
    foreach($_POST['questions'] as $question)
    {
        //Insert record with answer into your many to many table
    }
    echo("</form>");
}
else
{
    for ($i = 1; $i <= $count; $i++) {
        echo("Question {i} <input name='question[] type='text' /><br>");
    }
}


SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`questions`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`questions` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `otherData` VARCHAR(45) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`yourPrimaryTable`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`yourPrimaryTable` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `otherData` VARCHAR(45) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`yourPrimaryTable_has_questions`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`yourPrimaryTable_has_questions` (
  `yourPrimaryTable_id` INT NOT NULL ,
  `questions_id` INT NOT NULL ,
  `answer` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`yourPrimaryTable_id`, `questions_id`) ,
  INDEX `fk_yourPrimaryTable_has_questions_questions1_idx` (`questions_id` ASC) ,
  INDEX `fk_yourPrimaryTable_has_questions_yourPrimaryTable_idx` (`yourPrimaryTable_id` ASC) ,
  CONSTRAINT `fk_yourPrimaryTable_has_questions_yourPrimaryTable`
    FOREIGN KEY (`yourPrimaryTable_id` )
    REFERENCES `mydb`.`yourPrimaryTable` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_yourPrimaryTable_has_questions_questions1`
    FOREIGN KEY (`questions_id` )
    REFERENCES `mydb`.`questions` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;



SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Upvotes: 0

Fabien Warniez
Fabien Warniez

Reputation: 2741

You can set the "name" property of your answer fields to "answer[0]" "answer[1]" and so on. Then in PHP you can do $_GET['answer'] which will return an array that contains all your answers.

Upvotes: 1

Related Questions