INSERT data from two explodes into a MYSQL Database

PHP
1

As most peope seeking advise I’m a total newb so I’d appreciate some advise. I’m also old so you might have to speak up and repeat it a few times before it sinks in.
I have two strings coming out of a Flash app with pipe seperated values. I want to take these and polulate two columns in a mySQL DB
As a test, I managed to to insert using ‘explode’ and a foreach loop. This worked beautifully and here’s the code

$bmIDs         = explode ('|', $_POST['userIDs']);
$sqlQuery     = "INSERT INTO bm (bmUserID, bmID) VALUE (?,?)";
$stmt         = $con->prepare($sqlQuery);
$stmt->bind_param("si", $userID,$bmID);

foreach ($bmIDs as $bmID){
        $stmt->execute();
        }
$stmt->close();

My problems started when I tried various methods to insert both exploded strings. I understand from reading on the web that it can’t be done with a foreach so I tried various things and ended up with a for loop. It echos a treat but will not insert into the DB. There are no entries in the PHP error log so I’m really stuck. Here’s my problem code:

$bms         = explode('|', $_POST['users']);
$bmIDs         = explode ('|', $_POST['userIDs']);
$sqlQuery     = "INSERT INTO bm (bmUserID, bmID,bmName) VALUE (?,?,?)";
$stmt         = $con->prepare($sqlQuery);
$stmt->bind_param("sss", $userID,$bmID,$bmName);

for($i = 0; $i < count($bmIDs); $i++) {
$bmID = $bmIDs[$i];
$bmName = $bms[$i];
$stmt->execute();

Help would be appreciated

2

Don’t know if this is your issue or not, but shouldn’t the bin_param be

$stmt->bind_param("sis", $userID, $bmID, $bmName);
3

@DaveMaxwell THanks for a speedy response. I have tried what you suggested several hours back so we must have been on the same trian fo thought. Sadly however, it made not the slightest difference. I’m sat in front of the code now butI’ve been staring at it now for a little over 12 hours and can’t see the wood for the trees. I don’t have enough expereience to know wether I could uses a nested foreach loop or what to be honest but thatks for your response…

4

Do you need to add the brackets on the explode line?

$bms[]   = explode('|', $_POST['users']);
$bmIDs[] = explode ('|', $_POST['userIDs']);
5

@DaveMaxwell. Again Dave, thanks for getting back. I’d have thought if I’d have needed square Brackets the first foreach would not have worked but it did, beautifully. Really thought I was getting somewhere whe I ran than bit of script…ho hum!

6

If I understand how foreach works, it’s using an iterator interface, so basically it works as a collection of objects. The indicates an array object, which is different.

7

Okay, lets work with foreach a bit more.

ASSUMING: That the elements of $_POST[‘users’] and $_POST[‘userIDs’] line up with each other.

It’s perfectly fine to keep the structure you had; just a few tweaks.

Assuming this worked as anticipated:

foreach ($bmIDs as $bmID){
        $stmt->execute();
        }

The adjustment would be:

foreach ($bmIDs as $index => $bmID){
        $bmName = $bms[$index];
        $stmt->execute();
        }

The Foreach iterator takes care of filling $bmID; grabbing the optional Index of the iterator into the loop allows you to reference the correct value for $bmName. (Note that this functionally is the same as doing a for loop and using the iterator varaible, it’s just slightly cleaner IMO.)

You dont need (nor want) brackets on the variable declaration; explode() returns an array, which you want to store as the variable, not as a new index of that variable.

EDIT: To be clear:

$arr = explode(“|”,“a|b|c”);
=>
$arr = array(3) : {“a”,“b”,“c”}.

$arr = explode(“|”,“a|b|c”);
=>
$arr = array(1):{ array(3): {“a”,“b”,“c”}}

8

@DaveMaxwell, appreciate yor thoughs Dave but not entriely sure that I follow.

$bmIDs         = explode ('|', $_POST['userIDs']);

In the foreEach loop works great. My challenge is how can I use two exploded strings to get my data into the database.
bmIDs string before it is exploded looks like this 113|47|82. Exploding it and pumping it through the foreach, as I said, works fine. However, my second string $bms lookins like this Blunden|Bogart|Evlyn. My question is how can I get both explodes into my database so that the end result in the DB looks like this

113 Blunden
47 Bogart
82 Evlyn

I beleive, by looking on the web, that you cannot put two sepate statements in the foreach loop. I’ve tried and it doesn’t work. So Should I be looking to use a ‘for’ loop or is there a way to achive my result using foreach loop by nesting them… ? looking for guidance

9

@StarLion. Your assumption is correct they do align and my last post hopefully shows that. I have tried something like you suggest several times over. I was close but so very far away it seems. I have just carried out your suggestion and I’m thrilled to say it worked. I cannot tell you how close I was… Now, to your second points… Must aggree with you on the cleanliness of the foreach approach. Now about those brackets?? To which brackets do you refer perhaps a ‘before’ and ‘after’ example might help cement it for me… Is that possible?

10

$arr = says “$arr is an array. Create a new element of $arr that contains…”
$arr = says "$arr is whatever i say it contains.

putting the extra brackets creates a multidimensional array, which is not what you were wanting.

11

@StarLion. Thanks for taking the time and providing the additional clarity… It all make sense now. No, I do not want a multi dimensional array I’m struggling enough in the single dimension but again, thanks for your help and thanks to DaveMaxwell too.

12

What you need is for both arrays to be combined into one array. So one is your IDs and the other is the names of your users.

What you currently have is:

$bms[]   = explode('|', $_POST['users']); // array(113, 47, 82)
$bmIDs[] = explode ('|', $_POST['userIDs']); // array('Blunden', 'Bogart', 'Evlyn')

What you need is your array to look like this:

array(
    array(113, 'Blunden'),
    array(47, 'Bogart'),
    array(82, 'Evlyn')
)

You could write a loop or function to do that, but it’d be nice if there was a one-liner to make this happen. Guess what, there is. One thing to watch out for is that both arrays have the same amount of items, if not you’ll have to write additional code to handle that situation.

$rowBuffer =  array_map(null, $bms, $bmIDs); // shapes the array to the above

So now that your data is prepared the way that it needs to be you can insert the data.

$bms         = explode('|', $_POST['users']);
$bmIDs         = explode ('|', $_POST['userIDs']);
$sqlQuery     = "INSERT INTO bm (bmUserID,bmName) VALUE (?,?)";
$stmt         = $con->prepare($sqlQuery);

$stmt->bind_param("ss", $userID, $bmName);

$rowBuffer =  array_map(null, $bms, $bmIDs); // combine the arrays so that they end up like array(array(1, 'User1'), array(2, 'User2')), similar to other languages and libraries "zip" function

$con->query('START TRANSACTION'); // optional, but if you're inserting more than a handful of rows this makes it much faster

foreach ($rowBuffer as $row) {
    list($userID, $bmName) = $row;

    $stmt->execute();
}

$con->query('COMMIT'); // optional

Haven’t tested the above code, but it should get you really close with a few tweaks.

Your original SQL query has a bmID column. I can only assume that’s the primary key for the bm table. If it is, then you never have to insert an ID here if that column is set up to auto-generate IDs (create the first row and 1 is already assigned to that bmID column). That’s why I removed it in my example. If bmID is a foreign key to another table and you really did meant to insert a value into it, then you’ll need to zip that array too with array_map().

13

@Dreamer06 Thanks for the detailed response it was great and surprisingly for me I actually understoop most of it. If I’m not mistaken you are effectivly creating a multi dimensional array. I know it might sound like I know what I’m talking about but as a Newb this is all very much new territory for me. One thing that did interest me great too was your use of transctions. I 've never used that approach as yet but I need to as I have three or four queries that need to be guarenteed ‘delivered’ to the db… The code I posted is for a handfull of lines 2-8 which are not critical but I do have other stuff that I’ve yet to start. Perhaps I may be calling on your expertise in the future. Again thanks for your help… infact thanks to everyone that rushed to help. It nice to know that there are clever epole out there will to help an oldie out…

14

@ReggieTheDog Yes, you are correct. The end $rowBuffer ends up being a multidimensional array. It might be easier to think of it the following way. You’re at a food drive for the needy. There’s two different boxes: one with three apples and the other with three oranges. You want to give the needy a variety in fruits so that means you’ll have to rearrange your fruits from the two boxes. You decide to grab another box, grab one of each fruit, and start placing them in the box from top to bottom so that you have two fruits per “row”. Now that you’ve set up the box, bagging is a cinch. Just grab the two fruits, put them in a paper bag, and hand it over.

The previous illustrations matches up to what I did: I combined, reordered, and prepared the data so that it’s easy to just grab one row and insert it to the database.

15

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.