CODEWITHSUNDEEP
phpmysqlsqldatabase
Adagoon
Adagoon

Reputation: 23

Multiple value insertion into mysql database table

I will use images to explain what i am trying to accomplish. I am trying to insert Values in a table inputted in a box in any order.

Lets say the name of your website is your Website.com

The website lets you input values so that they can be summed up for data analysis like the image below.

enter image description here

Your PHP code looking like this:-

    <input  type="number"  name="Box1"></input>
    <input  type="number"  name="Box2"></input>
    <input  type="number"  name="Box3"></input>
    <input  type="number"  name="Box4"></input>

Lets further say that your MySQL table in your database looks something like the image below.

enter image description here

The names in the database are 1,2,3...8 as showed above.

Lets say for Example , the numbers 4,5,6,1 are inputted into the boxes on your site like the image below:

enter image description here

Let also assume that it does not matter how the numbers are entered. The results should be like the image below in your database table

enter image description here

Lets add more numbers for Example 8,5,2,7 and it does not matter how they are inputted enter image description here

After the numbers are added, your MySQL database table should look as follows

enter image description here

This is what i am trying to achieve. Having the table auto increment the entered values could also be nice but i figured it might be harder.

If the table could auto increment, the the MYSQL table will look something like the image below

enter image description here

I need help knowing how to achieve any of the end results. Any help will be greatly appreciated .

Sorry for the long story, i just wanted to explain what i wanted to do as basic as i could.

Upvotes: 0

Views: 75

Answers (1)

TimWolla
TimWolla

Reputation: 32731

I recommend a different database layout that allows for easier growth of allowed values, easier inserting and easier selection:

CREATE TABLE results (
  number INT(10) NOT NULL PRIMARY KEY,
  amount INT(10) NOT NULL DEFAULT 1
);

You can easily insert and update the amounts then using the MySQL specific ON DUPLICATE KEY UPDATE:

INSERT INTO results (number)
VALUES (1), (3), (3), (7)
ON DUPLICATE KEY UPDATE amount = amount + 1;

Demo on SQLFiddle: http://sqlfiddle.com/#!2/382cd7/1

Upvotes: 1

Related Questions