re-ordering array keys

[tito]

Hello,

I have 2 players in this example:

Array
(
    [event15] => Array
        (
            [player1] => 50 25
            [player4] => 30 35
        )

[event17] => Array
    (
        [player1] => 1
    )

[event16] => Array
    (
        [player4] => 1
    )

)

I need to insert a row in database per player

like player1 value of event15, value of event17
and
player 4 value of event15, value of event16

event15, event16 and event17 are columns in which I need to insert the values for each player.

Please how can this be accomplished?

Thank you.

[BuzzPHP]

On the question of "re-ordering array keys".

You can sort by array keys using ksort().

On the topic of adding new player data, just use standard arrays: Eg: Your data can be added using the following.

$database[15][1] = "50 25";
$database[15][4] = "30 35";
$database[17][1] = "1";
$database[16][4] = "1";

Im guessing you have relized, that if you add the data out of order, then when you interate arrays using "foreach", you wont get events and players displayed in order based on their ID.

ksort() will solve that problem for you.

Or, if you are displaying results in a large HTML table and the event and player id's are packed. ie: there are no large gaps in id numbers, then code like this will work:

<?php

$database[15][1] = "50 25";
$database[15][4] = "30 35";
$database[17][1] = "1";
$database[16][4] = "1";

$nevents = 17; // Total number of events
$nplayers = 20; // Total number of players

echo "<table width=100% border=1>";
for ($eid = 0; $eid <= $nevents; $eid++)
{
  echo "<tr><th>" . ($eid ? "$eid" : "&nbsp;") . "</th>";
  for ($pid = 1; $pid <= $nplayers; $pid++)
  {
    $s = $database[$eid][$pid];
    echo $eid ? "<td>" . ($s ? $s : "&nbsp;") . "</td>" : 
      "<th width=" . (100 / $nplayers) . "%>$pid</th>";
  }
  echo "</tr>";
}
echo "</table>";

?>

Im just guessing btw. It looks like you are doing some sport/footy etc coding.

If you have large gaps in either event id's or player id's, then you will have to collect all id's in index tables, sort those index tables, and then iterate those to prevent large blank areas in such table displays.

Sing out if you need more help

[tito]

Thank you. Yes this is a sport application

ksort gave me

Array
(
    [event15] => Array
        (
            [player1] => 50 25
            [player4] => 30 35
        )

[event16] => Array
    (
        [player4] => 1
    )

[event17] => Array
    (
        [player1] => 1
    )

)

which is fine but I am not clear on the db insert

INSERT INTO table (playerid, event15, event 16, event17) VALUES('player1', '50 25', '', 1)

INSERT INTO table (playerid, event15, event 16, event17) VALUES('player4', '30 35', 1, '')

[BuzzPHP]

O, right. Got it. Ok, Without telling people your database schema, no one can tell you how to insert data into your database.

What I would do is create 3 tables. Events, Players and Scores.

Event records would hold data like, event name, event time, event locations, etc.
Player records would hold player names, contact details, age, etc etc.
Each record in "scores" would link to an event and a player.

When a player registers for an event, create a score record that links the player to the event, but keep the score result set to NULL. example:

INSERT INTO scores (eid, pid) VALUES (15, 1)
INSERT INTO scores (eid, pid) VALUES (15, 4)
INSERT INTO scores (eid, pid) VALUES (16  4)
INSERT INTO scores (eid, pid) VALUES (17, 1)

When you have a result from an event you run SQL code like:

UPDATE scores SET result="50 25" WHERE eid=15 AND pid=1
UPDATE scores SET result="30 35" WHERE eid=15 AND pid=4
UPDATE scores SET result="1" WHERE eid=16 AND pid=4
UPDATE scores SET result="1" WHERE eid=17 AND pid=1

Hope that helps.

Using this system means, you can:

1) Maintain a record of players who have registered for events
2) Identify what events are missing results. Since their result field will be NULL
3) Prevent scores from being entered for players who have not registered for an event.

You can also do simple stuff like list all players for a given event, or list all events for a given player.

[BuzzPHP]

But, if you want todo it the way you are doing it, then you need todo something like this:

$results = array();
$pid = "player1";

for ($eid = 15; $eid <= 17; $eid++)
{
  $results[] = $database["event".$eid][$pid];
}

$sql = "INSERT INTO table (playerid, event15, event 16, event17) VALUES('$pid', " . 
implode(",", $results) . ")";

Note: This code is untested. Its just sample code. I wouldn't do it this way, but if you want to, so be it.

[tito]

I have to try it that way.

The complete story:

I do not know what event15, event16 and event17 are. They were database fields dynamically created

the schema is simple.

statsid, playerid, gameid, season, and all those events

everytime there is a soccer game, i need to insert the stats per soccer player for that game. 50 25 for example are 2 goals given by that player at 50 and 25 minutes of the game and 1 is a yellow card received. Those data must be inserted into their dynamic events

and I was planning to explode all those data and insert sizeof(explodedarray) for that event

event15 for example in reality is 15 and I just rebuild the column with 'event' . $fieldid (making it event15 to insert into dynamic event15)

I must keep that structure because later on when I need the player stats I simply do a SUM(dynamicevent15) as dynamicevent15 ...

The results have been tested by dynamically recreating the fields. It's the INSERTS that are giving me a hard time. AND by hard time, I mean driving me crazy

[tito]

In reality, there are no event or player prefix in array. I put them here to better explain what I am trying to accomplish

[BuzzPHP]

Ok. Mmm. Seems like a lot of logic needs to be pulled apart and defined.

Here is how I do things. Break down problems into small chunks! Lets say you have a single players data. You just need to create a function to insert that data into a table. One might write:

function SQLInsertPlayerResult($statsid, $playerid, $gameid, $season, $events)
{
  return "INSERT INTO table 
  (statsid,playerid,gameid,season," . implode(",", array_keys($events)) .") 
  VALUES($statsid,$playerid,$gameid,$season," . 
  implode(",", $events) . ")"; 
}

And then to use this function we call it with:

echo SQLInsertPlayerResult(10, 20, 30, 40, 
  array(
    event1 => 1, 
    event2 => 2, 
    event3 => 3, 
    event4 => 4));

The results of this function is:

INSERT INTO table (statsid,playerid,gameid,season,event1,event2,event3,event4) VALUES(10,20,30,40,1,2,3,4)

Now that is all valid code. We have a simple function that generates SQL with will insert players data into a database. Your next question is then, how do you extract a players data from your internal array:

Well code like this will extract the event results for a given player:

$database[event15][player1] = "50 25"; 
$database[event15][player4] = "30 35"; 
$database[event17][player1] = "1"; 
$database[event16][player4] = "1"; 

$pid = "player1";

foreach ($database as $eid => $event)
{
  if ($event[$pid]) $result[$eid] = $event[$pid];
}

print_r($result);

But, you might not know all the players, in which case you need to write a function that will build up a list of all players within your array.

So, we have 3 functions:

1) A function to get an array of all players within the array
2) A function to get all event results for a given player
3) A function to convert a players details and event results to a SQL INSERT statement.

You combine the 3 functions together, and bingo, you have your requirements.

Sing out if Im completely off the mark

[tito]

hummm I think we are getting close. This all make sense to me. I will try later because I have not slept since yesterday morning and am exhausted. I'll let you know.

Thanks for everything.

[tito]

except that playerid is part of the array

here are the 3 parameters that I know. Playerid is in $playersdata

function updatePlayerStats($gameid, $season, $playersdata = array())