Mysql not being updated with full array and only with 1 entry!

Hey I have another issue! <img src="/station/images/smilies/9d71f0541cff0a302a0309c5079e8dee.gif" border="0" /> I am trying to update the mysql with the same array that I used to insert into the mysql in the first place and it only updates 1 entry XX times <img src="/station/images/smilies/9d71f0541cff0a302a0309c5079e8dee.gif" border="0" /> here is what I got so far!

the $update_id is getting information from the main character ID to use as a primary key and get all data regarding that specific # and all are unique

BTW Thank you api gods for shining down upon me! :b

while ($update_stats = $stmt->fetch()) {

<span style="white-space: pre;"> $update_id = $update_stats["character_number"];

<span style="white-space: pre;"> $ApiLink     = "http://census.daybreakgames.com/s:egrazziani/get/ps2-beta/character/" . $update_id . "?c:show=type&c:show=stats";

<span style="white-space: pre;"> $json_decode = (json_decode(file_get_contents($ApiLink), true));

<span style="white-space: pre;"> $output      = $json_decode["character_list"];

<span style="white-space: pre;"> /************************************************/

<span style="white-space: pre;"> $do_stuff = $dbh->prepare("UPDATE damage_given_vehicle SET veh_id=:veh_id, veh_name=:veh_name, veh_total=:total_value, veh_faction_nc=:veh_faction_nc, veh_faction_tr=:veh_faction_tr, veh_faction_vs=:veh_faction_vs WHERE character_number = :char_id");

<span style="white-space: pre;"> foreach ($output as $key => $value) {

<span style="white-space: pre;"> $character_id[$key] = $output[$key]["id"];

<span style="white-space: pre;"> $vehicle_kills[$key] = $output[$key]["stats"]["damage_given"]["vehicle"];

<span style="white-space: pre;"> foreach ($vehicle_kills[$key] as $row) {

<span style="white-space: pre;"> echo "

Code:

";<span style="white-space: pre;"> echo $row["id"] . "";

<span style="white-space: pre;"> echo $row["name"] . "";

<span style="white-space: pre;"> echo $row["value"];

<span style="white-space: pre;"> echo $row["faction"]["nc"] . "";

<span style="white-space: pre;"> echo $row["faction"]["tr"] . "";

<span style="white-space: pre;"> echo $row["faction"]["vs"] . "";

<span style="white-space: pre;"> $do_stuff->bindValue(':char_id', $update_id);

<span style="white-space: pre;"> $do_stuff->bindValue(':veh_id', $row["id"]);

<span style="white-space: pre;"> $do_stuff->bindValue(':veh_name', $row["name"]);

<span style="white-space: pre;"> $do_stuff->bindValue(':total_value', $row["value"]);

<span style="white-space: pre;"> $do_stuff->bindValue(':veh_faction_nc', $row["faction"]["nc"]);

<span style="white-space: pre;"> $do_stuff->bindValue(':veh_faction_tr', $row["faction"]["tr"]);

<span style="white-space: pre;"> $do_stuff->bindValue(':veh_faction_vs', $row["faction"]["vs"]);

<span style="white-space: pre;"> $do_stuff->execute();

<span style="white-space: pre;"> }

<span style="white-space: pre;"> }

}

 

First, you're only updating with a characterId clause... I'm presuming you want to update the values of EACH char & vehicle id data sets. But you're data is likely all messed up already.  I would place a unqiue key on character id and vehicle id paired together.  Then do an insert-on-duplicate-key-update query.  If you're not spamming this millions of times, it shouldn't be harmful for your db host resources.

Second, you can use c:show like this: &c:show=type,stats

 

they all have a vehicle ID but when there are more then 2 people that ID will be the same for  2 or more rows also they all share the same character ID number that is how I was only targeting those specific rows :/ any ideas ?

 

Let me expand on what Dedith said.  If this is over-explaining, my appologies.

He's suggesting that you set the primary key to two fields instead of just one.  Set your primary key to character_number and veh_id.  This allows only one row in the database to contain information about that character for that vehicle.  Otherwise, if the primary key were only on character_number, you couldn't store information on more than one vehicle per character because it would cause a duplicate key.

Once you have the primary key straightened out, you can do an insert statement that will do what you're wanting to do with the update statement.  Syntax may be a little off based on how you're doing it and you'll need to stuff this into your DB code stuff, but here's the jist of it:

INSERT damage_given_vehicle (character_number, veh_id, veh_name, veh_total, veh_faction_nc, veh_faction_tr, veh_faction_vs)

VALUES char_id, :veh_id, :veh_name, :total_value, :veh_faction_nc, :veh_faction_tr, :veh_faction_vs)

ON DUPLICATE KEY UPDATE

veh_name=VALUES(veh_name),

veh_total=VALUES(veh_total),

veh_faction_nc=VALUES(veh_faction_nc),

veh_faction_tr=VALUES(veh_faction_tr),

veh_faction_vs=VALUES(veh_faction_vs);

Here's what this does.  If there isn't already a row with that unique character_number and veh_id, it inserts a new row with those values.  If there IS already a row with that unique character_number and veh_id, it updates the values of that row with the new values as shown.  It's basically a shortcut to this:

If (rowExists) {

<span style="white-space: pre;"> Update Row;

} else {

<span style="white-space: pre;"> Insert Row;

}

But it won't work right unless you set the primary key to character_number AND veh_id.

Hope that helps.

 

ok, it seems the forums don't like me.  Yesterday and today they are preventing me from posting, let's see if this goes through.  I tried elaborating like Dethdlr did, but the post failed for some reason and I wasn't typing that all out again.  He nailed it like I was implying though.

edit: seems ie10 and the old forums don't get along well.  had to enable compatibility mode to post.