Php Insert Lots of data to mysql

Question

Hi I have an evaluation form with more than 300 fields, where there are several parts. i don't want to make 300 rows in mytable.Can someone give me a method how to save my data as it need to be easy to retrive to make some stats.

Thanks.

Edit: Something like that with lots of differnt parts

<form action="" method="post" id="OKF" name="OKF" autocomplete="off">
<table width="100%" border="0" cellpadding="0" cellspacing="0">
        <tr>
          <td width="25%" class="tableft"><strong>Date </strong></td>
          <td width="25%"><input name="controldate" type="number" id="controldate" /></td>
          <td width="25%"><strong>Start Time</strong></td>
          <td><input type="text" name="startime" id="startime" class="required"/></td>
        </tr>
        <tr>
          <td class="tableft"><strong>Controller</strong></td>
          <td><input type="text" name="controller" id="controller" /></td>
          <td><strong>End Time</strong></td>
          <td><input type="text" name="endtime" id="endtime" class="required"/></td>
        </tr>
        <tr>
          <td class="tableft"><strong>Hotel Name</strong></td>
          <td><input type="text" name="otelname" id="otelname" class="required"/></td>
          <td><strong>Company Name</strong></td>
          <td><input type="text" name="compres" id="compres" class="required"/></td>
        </tr>
        <tr>
          <td class="tableft"><strong>No Stars</strong></td>
          <td><input type="text" name="stars" id="stars" class="required"/></td>
          <td><strong>No Room</strong></td>
          <td><input type="text" name="roomnum" id="roomnum" class="required"/></td>
        </tr>
      </table>
      <br>
      <h1>Part 1</h1>
<table width="100%" border="0" cellpadding="0" cellspacing="0">
  <tr>
    <td width="40%" class="tabtopmid">Question</td>
    <td width="4%" class="tabtopmid"></td>
    <td width="17%" class="tabtopmid"><strong>Point</strong></td>
    <td width="37%" class="tabtopmid"><strong>Comment</strong></td>
  </tr>
  <tr>
    <td>Question 1</td>
    <td>20</td>
    <td><input name="puan_1" id="puan_1" type="text"></td>
    <td><textarea name="cmt_1" id="cmt_1"></textarea></td>
  </tr>
  <tr>
    <td>Question 2</td>
    <td>20</td>
    <td><input name="puan_2" id="puan_2" type="text"/></td>
    <td><textarea name="cmt_2" id="cmt_2"></textarea></td>
  </tr>
  <tr>
    <td>Question 3</td>
    <td>20</td>
    <td><input name="puan_3" id="puan_3" type="text"/></td>
    <td><textarea name="cmt_3" id="cmt_3"></textarea></td>
  </tr>
  <tr>
    <td>Question 4</td>
    <td>20</td>
    <td><input name="puan_4" id="puan_4" type="text"/></td>
    <td><textarea name="cmt_4" id="cmt_4"></textarea></td>
  </tr>
  <tr>
    <td>Question 5</td>
    <td>20</td>
    <td><input name="puan_5" id="puan_5" type="text"/></td>
    <td><textarea name="cmt_5" id="cmt_5"></textarea></td>
  </tr>
</table>
      <input name="submit" type="submit" id="submit" value="Submit" alt="Submit" title="Submit">

If I understood well, you just need **1 row** with **300 columns(fields)** in your table, so just **one insert**. — pconcepcion, Apr 28 '11 at 13:52
you will have to elaborate on what sort of fields there are .. 300 column table is **not** a solution. Maybe you could [pastebin](http://pastie.org/) the html fragment with your form. — tereško, Apr 28 '11 at 13:55
well some info fields: name, surname etc... and the others are part1 : 24 fields 12 points Fields, 12 comments fields there are like 10 parts like that. — Shad, Apr 28 '11 at 13:58
You must give us **SOME** information about the form, or you will be better of [here](http://www.mysticalball.com/). — tereško, Apr 28 '11 at 14:00
I've asked the [magical ball](http://www.mysticalball.com/) for `Can someone give me a method how to save my data as it need to be easy to retrive to make some stats ?` and the result is `yes`, so hold on :) — , Apr 28 '11 at 14:27

Flask · Answer 1 · 2011-04-28T14:44:47.130

3

depending on your fields would recommend you to make more than one table

form - id 1

n questions - id - type - value - label - form_id

and then you could store the answer of the users in a third table

easier to you would be names like formname[foobar], formname[foobar_1] and so on

then you are able to do something like:

$sql = "INSERT INTO answers (name, value) VALUES ";
foreach ($_POST['formname'] as $key => $value) {
  $sql .= "('".$key."', '".$value."'),";
}
$sql = substr($sql, 0, -1).";";
mysql_query($sql);

please note, this is only an example.. no security, no valiation, nothing

edited Apr 28 '11 at 14:44

answered Apr 28 '11 at 13:59

Flask

4,966
1
20
39

is serialization a solution? i can use only one table – Shad Apr 28 '11 at 14:15
could be a solution. but keep in mind that selecting out of serialized data is pain in the a** (e.g. you want to select what all users anwered to question x) – Flask Apr 28 '11 at 14:18
2

serialization is __never__ a good solution for storing information in a database... SO is littered with requests for help from people who have done exactly that, and then discovered that they can't search the database for the information they need. The answer is always to normalize your database, even if that does mean you end up writing several rows rather than just one. – Mark Baker Apr 28 '11 at 14:19
So how do i insert question by question with a form of 150 questions? – Shad Apr 28 '11 at 14:21
that should be easy, you could loop trough your fields an create one insert string with all values… – Flask Apr 28 '11 at 14:32
Your form is probably too long if you have 150 questions on the one form, you should probably page it (30 questions per form perhaps); but if you have 150 questions and answers, then do 150 inserts in your user_answers table in total. That might seem stupid, but in database terms, it's the most sensible approach. If you page your form, you can insert 30 answers at a time into a temporary table, then do a mass insert from that (INSERT INTO table SELECT * from temp_table WHERE...) in a single statement, followed by a single statement to delete from the temporary table. – Mark Baker Apr 28 '11 at 14:34
1

See SO answers to http://stackoverflow.com/questions/779986/insert-multiple-rows-via-a-php-array-into-mysql for an example of efficient bulk insert – Mark Baker Apr 28 '11 at 14:35

score 1 · Answer 2 · answered Apr 28 '11 at 14:22

So this is a hotel registration form , i take it.

Well, the Questions should definitely be a separate table. Then, i guess, there should be a table for Reservations ( containing hotel_id, room_id, customer_id, date, additional info about reservation ) .. then a Customers table and Hotels table, and Rooms table ... and maybe few more.

It's impossible to tell, without knowing the the entire list of fields.

score 0 · Answer 3 · answered Apr 28 '11 at 14:19

I never worked with 300 columns in one table. My suggestion is use JSON or someother serialized data technic for columns. Grouping related fields can save time in any case.

Recently used;

column = value (variable1:value1-variable2:value2-var3:val3)

adv_room_numbers(text) = "Room:2-Bedroom:3-Kitchen:1-Dining Room:1,etc..room:1,etc..room:1,etc..room:1" // I can use explode() to extract fields
adv_online(enum) = "active"
adv_contact(text) = "Name:Name Surname-Tel:65465-City:Cityname-Address:address details"

score 0 · Answer 4 · answered Apr 29 '11 at 04:28

Is it all coming from one big form? If so, it might just make sense to put all the data into one array, serialise it, and log it to a file. Then analyse the data after with a more specialist tool.

If you do want to put it into a database, you need to spend some time breaking out the schema - I'm sure people would help but at the moment, the definition is too vague to say anything meaningful.

It might help you to use a framework to map between the front end form and the database. Symfony is excellent at form generation - you just write the files into a YAML file and run a command.

Php Insert Lots of data to mysql

4 Answers4