inserting indefinite numbers of set of fields into mysql

@rubenhanNov 20.2008

Hi,

I am a beginner PHP student trying to tackle a personal project right now.

My initial problem was this. Let’s say there is a set of field for the user to fill in.

Field1 field1 field3 field4 field5

I first wanted that if those fields are filled or if the user wanted an additional set of field by clicking a button, a new blank set of field would pop. I found out with the incorporation of AJAX and HTML DOM I can accomplish that.

My problem is how do I insert this indefinite numbers of set of fields into mysql?
By the time the user clicks ‘submit’ and all the forms are validated, I do not know how many sets of field were made. So the conventional way of
‘INSERT INTO tables () values ();’
wouldn’t work since it will only INSERT the first set of values.

So how do I make sure that no matter how many sets of fields are filled in, I will be able to INSERT all of them into MYSQL?

My vague solution would be

1. For each set of fields, give them a unique ID added to their name such as 1, 2, 3, 4, 5 or insert each set of fields into an array such as array1, array2, array3.

2. Then later loop them and insert them into mysql.

Am I in the right direction? If I’m not could someone tell me at least some keywords that I could google to help me out? If you could give me some short snippets, that would even more amazing.

ANyways, thank you so much for reading this rather long post and have a great evening guys.

to post a comment

PHP

5 Comments(s) _↴

@MindzaiNov 20.2008 — #I would normally handle this by making my form fields arrays:

[code=html]
 <input type="text" name="myfield[]" value="field1" />
 <input type="text" name="myfield[]" value="field2" />
 <input type="text" name="myfield[]" value="field3" />
 [/code]

Then when you submit your form you will have an array of fields:

[code=php]
 echo $_POST['myfield'][0]; // field1
 echo $_POST['myfield'][1]; // field2
 echo $_POST['myfield'][2]; // field3
 [/code]

And you can loop these building an SQL query as you go:

[code=php]
 $sql = 'INSERT INTO mytable (myfield) VALUES ';
 foreach ($_POST['myfield'] as $key => $value) {
 $sql .= '('' . $value . ''),';
 }
 $sql = rtrim($sql, ',');
 
 echo $sql; // INSERT INTO mytable (myfield) VALUES ('field1'),('field2'),('field3')
 [/code]

Bear in mind that the above is just an example, you dont want to insert submitted form data directly like this without sanitizing it first otherwise you leave yourself open to SQL injection amongst other things.

If you have more than one field you need to insert at a time you can loop the (sanitized) $_POST array directly and build a more complex query that way.

@rubenhanauthorNov 20.2008 — #@mindzai

Thank you so much. Now I have some ideas on how to tackle this issue. Of course I'll validate and sanitize all inputs before I Insert them into MYSQL.

Anyways, I really appreciated your help and I'll come back if I get stuck.

@SyCoNov 20.2008 — #But what is the user add more fields than there are columns currently in the table?

Have you learned how to join relational tables yet? That's the next thing you'll need to study. Then you can have a user table with the users info and a userid unique to them. The next table is joined to the first using that unique userid (or whatever common id). The data is stored in rows in the seconds table, rather than columns so your ability to store fields is limited only by the system hardware.

So when your user clicks the 'new input' link, you generate the input with a name presumably dyn_field1, dyn_field2 or whatever. Something that 1, makes each input name/id unique and 2, identifies it as a dynamically generated input.

No you can save the info in the second table as a row rather than as a column so the data in the 2 tables would look something like this. I'm skipping over how to store the user info.

[CODE]Users Table
 userid	username	age
 1	bob		15
 2	sam		25
 2	jon		43
 
 FormFields Table
 formfiledid	userid	inputname	value
 1		1	dyn_field1	eggs
 2		1	dyn_field2	bacon
 3		1	dyn_field3	beans
 4		2	dyn_field1	blue
 5		2	dyn_field2	yellow
 6		2	dyn_field3	green
 7		3	dyn_field1	Saturday
 [/CODE]

So read up on joins. Wikipedia has a good article.

The query could be something like this

[CODE]select * from FormFields
 INNER JOIN users ON FormFields.userid=users.userid
 WHERE userid=1[/CODE]

Which would return a result set like this.

[CODE]
 userid	username	age formfiledid	userid	inputname	value
 1	bob		15	1	1	dyn_field1	eggs
 1	bob		15	2	1	dyn_field2	bacon
 1	bob		15	3	1	dyn_field3	beans
 [/CODE]

and you can pick all the data out the you need with an unlimited number of input fields that can be added by the user

To go one step further the dyn_ prefix allows you to identify the dynamic POSTed elements in the POST array so you can build the query in the way Mindzai suggests but also taking the key and value. eg

[code=php]
 $q="INSERT into FormFields (userid,inputname,value) VALUES ";
 foreach($_POST as $key => $value){
 if(substr($key,'dyn_')){ //if true it's dynamic
 $q_parts[]='('.$userid.','.$key.','.$value.')';
 
  }
 }
 $q.=implode(',',$q_parts);
 
 //run query once[/code]

This builds a multiple insert query in this structure

[CODE]INSERT INTO x (a,b)
 VALUES 
 ('1', 'one'),
 ('2', 'two'),
 ('3', 'three')
 [/CODE]

Remember to sanitize and validate all user inputted data!!

If you need to add the user info at this time as well then either do it as a 2 step process (over 2 pages) or use a SQL TRANSACTION. Transactions are cool but lets not get ahead of ourselves here ?

BTW this is entirely 'pseudo code' I've not tested anything so don't expect it to work right off the bat. This is more about the concept than handing you code to run.

@rubenhanauthorNov 21.2008 — #@Syco

Thank you for your help! Yes, I'm currently in the process of learning the JOIN function. It really looks like it's a powerful feature. With your code and Mindzai's codes, I am trying to apply them in my project.

I have one more questions though.

How do I read them back and echo them in the application. What I'm trying to pull off is, if there are the following sets of input fields INSERTED into MySQL

USERNAME, field1[A], field2[A], field3[A]

USERNAME, field1[B], field2[B], field3[B]

When I read them back and echo them on the browser, I want to display in this way.

USERNAME, field1[A], field2[A], field3[A], field1[B], field2[B], field3[B]

Since the username is same, I don't want to show them twice or more. Instead show the data pertaining to the username next to a single username.

Do I have to use php and code if there are arrays with the same username, I would merge those two arrays with one username?

@SyCoNov 21.2008 — #You're going to loop through the result set so set a comparison value outside the loop. That value is updated each time with the current value and compared to the next eg

[code=php]
 $compval='';//called anything with any value
 
 while ($row=mysql_fetch_assoc($r)){
 if($compval!=$row['username']) //if the comparision value doesnt = the username
 echo $row['username'];//echo the username
 }
 echo $row['field1'].' '.$row['field2'].' '.$row['field3'];//echo the values of each row
 $compval=$row['username'];//reset the comp val to compare next iteration
 }[/code]

So in your example it will be the same the second time so it wont echo. If the next username is different, it again meets the condition to echo.

Also in #PHP _↴

Creating from a while loop send email through exchange server with php sendmail adding information to a database

Success!

Help @rubenhan spread the word by sharing this article on Twitter...

Tweet This

inserting indefinite numbers of set of fields into mysql

5 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

inserting indefinite numbers of set of fields into mysql

5 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

5 Comments(s) _↴

Also in #PHP _↴