CODEWITHSUNDEEP
phpmysql
mintobit
mintobit

Reputation: 2383

Specific mysql insert

The structure of my mysql table is:

CREATE TABLE IF NOT EXISTS `tb_hour_counts` (
  `date` date NOT NULL,
  `subid` int(20) NOT NULL,
  `unique_ids` int(20) NOT NULL,
  `total_ids` int(20) NOT NULL,
  `unique_ips` int(20) NOT NULL,
  `total_ips` int(20) NOT NULL,
  `global` int(20) NOT NULL,
  `time` text NOT NULL,
  UNIQUE KEY `ind_1` (`date`,`time`(5),`subid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I have an array of data like:

$array = array(
    '0' => array(
        'date' => '2011-10-10',
        'time' => '00:00',
        'subid' => '2',
        'unique_ids' => '588'
    ),
    '1' => array(
        'date' => '2011-10-10',
        'time' => '00:00',
        'subid' => '2',
        'unique_ips' => '3'
    ),
    '2' => array(
        'date' => '2011-10-10',
        'time' => '00:00',
        'subid' => '2',
        'total_ids' => '3995'
    ),
    '3' => array(
        'date' => '2011-10-10',
        'time' => '00:00',
        'subid' => '2',
        'total_ips' => '1000'
    ),
    '4' => array(
        'date' => '2011-10-10',
        'time' => '00:00',
        'subid' => '2',
        'global' => '1000'
    ),
    '5' => array(
        'date' => '2011-10-10',
        'time' => '01:00',
        'subid' => '3',
        'unique_ids' => '766'
    ),
    '6' => array(
        'date' => '2011-10-10',
        'time' => '01:00',
        'subid' => '3',
        'unique_ips' => '10'
    ),
    '7' => array(
        'date' => '2011-10-10',
        'time' => '01:00',
        'subid' => '3',
        'total_ids' => '934'
    ),
    '8' => array(
        'date' => '2011-10-10',
        'time' => '01:00',
        'subid' => '3',
        'total_ips' => '950'
    ),
    '9' => array(
        'date' => '2011-10-10',
        'time' => '01:00',
        'subid' => '3',
        'global' => '7554'
    )
);

The goal is to insert the data into the table. The result of inserting the data from this array should be the following:

result

I believe this could be done either by mysql request, or by preparing the array for usual insert.

Upvotes: 0

Views: 104

Answers (3)

Jvr
Jvr

Reputation: 97

Once in a database, order is irrelevant.

Every call to retrieve data automatically sorts via data via a default column.

When you want to pull the data from the database you sort it via the SQL query. ei.

SELECT date, subid, unique_ids, total_ids, unique_ips, total_ips, global, time FROM tb_hour_counts 
WHERE date = '2011-10-10'
ORDER BY time;

Hope this helps.

Upvotes: 1

Prisoner
Prisoner

Reputation: 27618

It's very messy, but:

// your array below
$array = array();

$newArray = array();

foreach($array as $item){
        $newArray[$item['subid']]['date'] = $item['date'];
        $item['time'] > $newArray[$item['subid']]['time'] ? $newArray[$item['subid']]['time'] = $item['time'] : null;
        $newArray[$item['subid']]['unique_ids'] += $item['unique_ids'];
        $newArray[$item['subid']]['total_ids'] += $item['total_ids'];
        $newArray[$item['subid']]['unique_ips'] += $item['unique_ips'];
        $newArray[$item['subid']]['total_ips'] += $item['total_ips'];
        $newArray[$item['subid']]['global'] += $item['global'];
}

print_r($newArray);

This gives you:

Array
(
    [2] => Array
        (
            [date] => 2011-10-10
            [time] => 00:00
            [unique_ids] => 588
            [total_ids] => 3995
            [unique_ips] => 3
            [total_ips] => 1000
            [global] => 1000
        )

    [3] => Array
        (
            [date] => 2011-10-10
            [time] => 01:00
            [unique_ids] => 766
            [total_ids] => 934
            [unique_ips] => 10
            [total_ips] => 950
            [global] => 7554
        )

)

You can then just do something like:

foreach($newArray as $key=>$value){
        echo "INSERT INTO `table` ('{$value['date']}',{$key},{$value['total_ids']})"; // etc. etc.
}

Upvotes: 2

balkon_smoke
balkon_smoke

Reputation: 1196

If you want to insert depending of date, group the data in $array using php before inserting

Upvotes: 1

Related Questions