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

Question

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.

simple for loop? for($i = 0; $i < $numQuestions; $i++) { $query = "INSERT INTO....} — dmikester1
– dmikester1, Commented Jan 7, 2014 at 20:00

Fabien Warniez · Accepted Answer · 2014-01-07 20:01:11Z

1

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.

answered Jan 7, 2014 at 20:01

Fabien Warniez

2,7411 gold badge21 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user1032531 · Accepted Answer · 2014-01-07 20:28:54Z

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;

I think OP was asking about how to send the data from HTML to PHP knowing that the number of fields is variable.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related