[RESOLVED] Multi-dimensional array to related tables??

[kimgokce]

I need a bit of a nudge on a design I haven't been able to get right. I am receiving an xml doc which I parse into an array with the resulting example elements:

[0] => Array
    (
        [id] => 0D1XZ6QKN166HC6ANXB7
        [organization_id] => 2T93DOPGN9Z7FTNLS21V
        [firstname] => Shan
        [lastname] => Jahan
        [phonenumber] => [phone]
        [email] => [e-mail]
        [logininfo_roles_role_name] => Records
        [logininfo_roles_role_id] => 1000
        [logininfo_roles_role_name2] => Manager
        [logininfo_roles_role_id2] => 2000
        [logininfo_roles_role_name3] => User
        [logininfo_roles_role_id3] => 3000
        [logininfo_roles_role_name4] => Seller
        [logininfo_roles_role_id4] => 4000

    )

[1] => Array
    (
        [id] => 0D1XZ6QKN166HC6ANXB7
        [organization_id] => 2T93DOPGN9Z7FTNLS21V
        [firstname] => Roland
        [lastname] => Oliver
        [phonenumber] => [phone]
        [email] => [e-mail]
        [logininfo_roles_role_name] => Records
        [logininfo_roles_role_id] => 1000
        [logininfo_roles_role_name2] => Manager
        [logininfo_roles_role_id2] => 2000
        [logininfo_roles_role_name3] => User
        [logininfo_roles_role_id3] => 3000
        [logininfo_roles_role_name4] => Seller
        [logininfo_roles_role_id4] => 4000
        [logininfo_roles_role_name5] => Lowly Coder
        [logininfo_roles_role_id5] => 5000

    )

I am trying to insert each array as a record in a "user" table. I can do this fine as long as the items are static in number. I am stumbling on how to insert a 'n' number of roles (along with their id's). In this example, user 1 has 4 roles while user 2 has 5 roles. I am at a loss for how to test for the presence of one of these "role" element types within the existing foreach and process them distinctly from the other elements.

Normalising the data into two tables would be nice (user & roles) but I am at a loss for how to extract the roles info while populating the 'id' from the array as the foreign key to be used in a 'roles' table. I'd settle for serializing each user's roles into one string and storing it in the "user" table in a "roles" field. In either case, I am not clear how to grab the roles info into a separate array for processing differently from the other elements.

At the moment, I am limited to looping through the array and executing static INSERT statements. Obviously, this doesn't work with a dynamic set of elements to insert. My current loop minus role handling is:

   foreach ($arr AS $item) {
	$sql = "INSERT INTO user (id, organizationid, firstname, lastname, phone, email) VALUES ('$item[id]', '$item[organization_id]', '$item[firstname]', '$item[lastname]', '$item[phone]', '$item[email]')";
	mysql_query($sql) OR die(mysql_error());
}

Conceptually, I think I need to create a new array inside the foreach that contains elements for 'id,' 'role_name,' and 'role_id.' I could then use this created array to populate a 'roles' table with foreign key, 'id,' to the user table. Is it a nested foreach that I need to implement??? If so, I am unclear about how to test for 'n' role elements ...

I think I have all the blocks to build what I need but I'm not sure how to put them together. Trial and error continues for now! Thanks for reading my long post.

[benbox]

I think you can do this: -

<?php
foreach ($arr as $item) {
	$sql = "INSERT INTO user (id, organizationid, firstname, lastname, phone, email) VALUES ($item['id'], $item['organization_id'], $item['firstname'], $item['lastname'], $item['phone'], $item['email'])";
	mysql_query($sql) OR die(mysql_error());


// assuming the initial 6 values in the array are always there, and id/name for each roll thereafter..
$number_of_rolls = (count($item) - 6) / 2;

for($i=1; $i<=$number_of_rolls; $i++) {

	$i = $k==1 ? '' : $k;

	$roll_name = $item['logininfo_roles_role_name'.$i];
	$roll_id = $item['logininfo_roles_role_id'.$i];

	// insert multiple rows into rolls table using the above values

}	
}
?>

[kimgokce]

Very quick insight BenBox! I didn't think about the predictability of the precending number of elements. I can absolutely control that - thanks for the break-thru. I can definitely implement what I need with your nudge! Thank you.

For those that come after, I made a couple of tweaks after troubleshooting:

$k=1; //added to initialize $k to trigger condition where $k==1

for($i=1; $i<$number_of_rolls; $i++) { // removed "equal to" operator to eliminate extra pass in case where only 1 role occurs.

    $i = $k==1 ? '' : $k;

    $roll_name = $item['logininfo_roles_role_name'.$i];
    $roll_id = $item['logininfo_roles_role_id'.$i];
	echo "<div>".$roll_name." | ".$roll_id."</div>"; //echo values to troubleshoot behavior
	$k++; //added incrementor for $k

}

[benbox]

😃

Are you sure you don't need the equal to operator? because I did it that way, starting from 1.. rather than 0

E.g.,
[logininfo_roles_role_name] => Name
[logininfo_roles_role_id] => ID
[logininfo_roles_role_name2] => Name2
[logininfo_roles_role_id2] => ID2
[logininfo_roles_role_name3] => Name3
[logininfo_roles_role_id3] => ID3

there are 3 above, 1, 2, 3

for($i=1; $i<=3; $i++) {
// first time around $i = 1
// 2nd time around $i = 2
// 3rd time around $i = 3
// no 4th time
}

Otherwise it would only do 1 and 2.. because its not starting from 0 (0, 1, 2 for three values).. but 1, 2, 3 because your array is starting at 1 (but no value) then, 2 and 3

I don't think I've explained that very well, infact I know I haven't .. bit of a jumble! .. but I hope it makes sense!

[benbox]

I just noticed I've used "rolls" instead of "roles" too, haha.. but minor variable name language thing 😛

[kimgokce]

I think it is necessary due to the behavior of:

$i = $k==1 ? '' : $k;

When $k == 0 on the first pass thru the foreach, $i gets set to 0. Then, on pass 2, $i gets set to ''. This is problematic when there is only one role because the foreach would execute 2 times and I need it to execute once in this case (where $i should be ''). I'll continue validating the results but my first tests look very good.

[kimgokce]

Ok - after further review 🙂

The comparison for the foreach should be '<=' but this combined with:

$i = $k==1 ? '' : $k;

creates a case where $i is equal to 1 initially and then '' ... so, after processing the values I have added:

$i = $i=='' ? 1:$i;

This causing the foreach to quit execution appropriately when there is only one role. There may be a more elegant way to manage this loop but this works. For the record, this tests well for both the single role and multi-role cases:

		$k=1;
     for($i=1; $i<=$number_of_roles; $i++) {

    $i = $k==1 ? '' : $k;

    $role_name = $item['logininfo_roles_role_name'.$i];
    $role_id = $item['logininfo_roles_role_id'.$i];

    echo "<div>".$item[id]." | ".$role_name." | ".$role_id."</div>";
    $i = $i=='' ? 1:$i;
    $k++;
}

In spite of the twisted trail to get here, I think I have arrived at a workable solution. Very much appreciate the lesson in creative problem solving!