Not sure how to insert dynamically added text fields into my DB

Hi,
I'm a bit perplexed on how I should be adding a set of fields that the user can dynamically add additional sets to and insert into the DB.
I've attached a jpg to show you what I'm talking about.
Screenshot of admin area
The user can add additional managers by clicking on the 'Add Another' link and it creates a new set of fields for a manager (DB fields are: fname, lname, phone, email and table is called 'managers')

I was thinking you would set the 'name' attribute for all of them to: name="managers[]"

I'm slow at the coding end of things so I'm trying to ensure I'm on the right track before spending hours trying to figure out how to do an insert based on a managers[] array?

Is that the right way to do it or should each of the fields have their own array? Something like: fname[], lname[], phone[] and email[]?

Any direction, help suggestion would be much appreciated. Last part of my app to try and figure out so am hoping to finally get this put to bed.

Thanks!
LVL 1
tjyoungAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
It's a frequent question, so I wrote an article about it.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_12335-PHP-and-MySQLi-Table-Maintenance.html

Of course there are many different ways to approach this kind of thing, and if you ask two programmers how to do it you'll get six answers, but hopefully that will give you a start on the design principles.  

Best of luck with it, ~Ray
0
Mark BradyPrincipal Data EngineerCommented:
Well I think you have the correct approach there. That field is dynamically created so you could make an array unless you want to append each one with a number (a counter) like 'manager_1', 'manager_2' etc..

That is easy to parse out in php.
0
Marco GasiFreelancerCommented:
Use array for form fields: fname[], lname[], phone[] and email[]. Then in your action script do something like this:

for ($i = 0; $i > count($_POST['fname']); $i++)
{
  $fname = (isset($_POST['fname'][$i])) ? $_POST['fname'][$i] : '';
  $lname = (isset($_POST['lname'][$i])) ? $_POST['lname'][$i] : '';
  $phone = (isset($_POST['phone'][$i])) ? $_POST['phone'][$i] : '';
  $email = (isset($_POST['email'][$i])) ? $_POST['email'][$i] : '';
  $query = "INSERT INTO table (fname, lname, phone, email) VALUES ('$fname', '$lname', '$phone', '$email')";
  //execute your query here depending on use of mysqli or PDO
}

Open in new window


I leave to you the implementation of value's sanitizing and the error messages.

Cheers
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

tjyoungAuthor Commented:
Made it most of the way but can't seem to get the 'for' loop happening. I'm just not getting anything inserted. I suspect my iterating is screwy.

In my case I'm using codeigniter and am sending over 2 arrays inside the $data array. Namely $data[0] and $data[1]. $data[0] is the usual account info and the $data[1] contains my dynamic fields.

When I do a var_dump, this is what I get on a submission:
 array(2) {
  [0]=>
  array(15) {
    ["first_name"]=>
    string(6) "Subaru"
    ["last_name"]=>
    string(10) "Management"
    ["company_name"]=>
    string(13) "Super Subaru"
    ["address"]=>
    string(15) "3737 Kempt Road"
    ["city"]=>
    string(7) "New Glasgow"
    ["state"]=>
    string(11) "Nova Scotia"
    ["zip"]=>
    string(7) "B3K-4X6"
    ["country"]=>
    string(6) "Canada"
    ["language"]=>
    string(7) "English"
    ["timezone"]=>
    string(15) "America/Halifax"
    ["email_address"]=>
    string(24) "subaru@subaru.com"
    ["cc_email"]=>
    string(0) ""
    ["phone"]=>
    string(14) "(902) 555-5555"
    ["fax"]=>
    string(14) "(902) 666-6666"
    ["facebook_post_id"]=>
    string(0) ""
  }
  [1]=>
  array(4) {
    ["manager_fname"]=>
    array(2) {
      [0]=>
      string(4) "John"
      [1]=>
      string(4) "Bill"
    }
    ["manager_lname"]=>
    array(2) {
      [0]=>
      string(3) "Doe"
      [1]=>
      string(6) "Thomas"
    }
    ["manager_phone"]=>
    array(2) {
      [0]=>
      string(10) "5555555555"
      [1]=>
      string(10) "6666666666"
    }
    ["manager_email"]=>
    array(2) {
      [0]=>
      string(12) "john@jon.com"
      [1]=>
      string(12) "bill@bill.com"
    }
  }
}

Open in new window

My insert code looks like:
for ($i = 0; $i > count($data[1]['manager_fname']); $i++) {
$manager_fname = $data[1]['manager_fname'][$i];
$manager_lname = $data[1]['manager_lname'][$i];
$manager_phone = $data[1]['manager_phone'][$i];
$manager_email = $data[1]['manager_email'][$i];
$sql= "INSERT INTO appraisers (parent_id, manager_fname, manager_lname, manager_phone, manager_email) VALUES ('$id','$manager_fname', '$manager_lname', '$manager_phone', '$manager_email')";
$result=mysql_query($sql);
}

Open in new window

0
Marco GasiFreelancerCommented:
First, let me say that you should abandon mysql and use instead mysqli or PDO. MySql is now deprecated and it'll miss support soon.

Second, before to migrate to mysqli or pdo, try to use die():

$sql= "INSERT INTO appraisers (parent_id, manager_fname, manager_lname, manager_phone, manager_email) VALUES ('$id','$manager_fname', '$manager_lname', '$manager_phone', '$manager_email')";
$result=mysql_query($sql) or die('Query failed: query was<br>' . $sql . '<br>Error message:' . mysql_error());

Another thing you can do is this:

for ($i = 0; $i > count($data[1]['manager_fname']); $i++) {
  echo  $data[1]['manager_fname'][$i] . '<br>';
}
                                            

Open in new window

0
Ray PaseurCommented:
Agree with marqusG about MySQL. This article will help with the conversion.  Not sure what CodeIgniter clients will do about the issue.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

Can you please post the var_dump($_POST) so we can see the information as it comes from the form?  Thanks, ~Ray
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tjyoungAuthor Commented:
Hi,
The var_dump is above. I receive the post in my controller, grab the variables into 2 arrays into the $data array:

Shown here:
// Run validation
if ($this->form_validation->run())
{
				
$data = array (
 $data[0] = array(
'first_name'	=> set_value('first_name'),
'last_name'		=> set_value('last_name'),
'company_name'	=> set_value('company_name'),
'address'	=> set_value('address'),
'city'	=> set_value('city'),
'state'	=> set_value('state'),
'zip'	=> set_value('zip'),
'country'	=> set_value('country'),
'language'	=> set_value('language'),
'timezone'	=> set_value('timezone'),
'email_address'	=> set_value('email_address'),
'cc_email'	=> set_value('cc_email'),
'phone'	=> set_value('phone'),
'fax'	=> set_value('fax'),
'facebook_post_id'	=> set_value('facebook_post_id'),
),
						
$data[1] = array(
'manager_fname'	=> set_value('manager_fname'),
'manager_lname'	=> set_value('manager_lname'),
'manager_phone'	=> set_value('manager_phone'),
'manager_email'	=> set_value('manager_email'),
),	
						
);
						

Open in new window


Goes to my model which looks in its entirety:

public function save($id, array $data)
{
$id = (int)$id;
		
if($id)
{
$this->db
->where('id', $id)
->update('user', $data[0]);
				
for ($i = 0; $i > count($data[1]['manager_fname']); $i++) {
$manager_fname = $data[1]['manager_fname'][$i];
$manager_lname = $data[1]['manager_lname'][$i];
$manager_phone = $data[1]['manager_phone'][$i];
$manager_email = $data[1]['manager_email'][$i];
					
$sql= "INSERT INTO appraisers (parent_id, manager_fname, manager_lname, manager_phone, manager_email) VALUES ('$id','$manager_fname', '$manager_lname', '$manager_phone', '$manager_email')";

$result = mysql_query($sql) or die('Query failed: query was<br>' . $sql . '<br>Error message:' . mysql_error());
}
}
return $this;
	}

Open in new window


The table looks like the attachment if that helps at all.

The $data[0] updates fine. I should mention there is every reason to think I'm doing something just ridiculous (teaching myself)
appraisers.jpg
0
Ray PaseurCommented:
This doesn't quite make sense to me.  Usually $_POST has named (associative) array keys.   Are you sure the POST request uses numbered keys?
0
Marco GasiFreelancerCommented:
I think that is the result of the form_validation->run() function... Can we see that?
0
tjyoungAuthor Commented:
Hi,
I kept pounding away and did come across a solution in my model. I'm going to post it here though I doubt anyone in their right mind may want to follow it. Thank you very much for your efforts though, I appreciate all the input.

	public function save($id, array $data)
	{
		$id = (int)$id;
		
		if($id)
		{
			$this->db
				->where('id', $id)
				->update('user', $data[0]);
				
	$this->db->delete('appraisers', array('parent_id' => $id)); 
	
	
	$manager_fname = $data[1]['manager_fname']; 
	$manager_lname = $data[1]['manager_lname'];
	$manager_phone = $data[1]['manager_phone'];
	$manager_email = $data[1]['manager_email'];
	$count = count($data[1]['manager_fname']);
	
	for($i=0; $i<$count; $i++) {
	$manager_data = array(
	'parent_id' => $id,
	'manager_fname' => $manager_fname[$i], 
	'manager_lname' => $manager_lname[$i],
	'manager_phone' => $manager_phone[$i],
	'manager_email' => $manager_email[$i],
	'status' => '1',
	);
	$this->db->insert('appraisers', $manager_data);
	}
	
}
return $this;
}

Open in new window

0
Marco GasiFreelancerCommented:
Oh yeah! What I stupid I feel sometimes! You're right, in CodeIgniter you have to use the classes and the functions that framework makes available to you, so you can't run a mysql query  but you must use the database class and its function insert(); I should think this, since I spent sometime with CodeIgniter, but I still prefer to write all code by myself and I leaved it...

Thanks for (undeserved) points and good luck with your project.

Marco :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.