sort multi dimensional array?
Moderator: General Moderators
sort multi dimensional array?
Hi All,
How can I do this ... I have written a script that queries one database and inserts into another...
I have 9 variables of data (out of a total of 45) i need to group and then sort by date. Once sorted I need to insert the data into my second database in the same query that inserts for all rows.... Would i use a multi dimensional array, and then do a sort for this?
database 1 (same row)
$sd1_indicator
$sd1
$sd1_date
$sd2_indicator
$sd2
$sd2_date
$sd3_indicator
$sd3
$sd3_date
I need to sort these "groups" of data by date and then place them in my new database. So after being sorted, $sd3_date might be the newest date and need to be inserted into $sd1_date in my second db..
thanks in advance!
-P
How can I do this ... I have written a script that queries one database and inserts into another...
I have 9 variables of data (out of a total of 45) i need to group and then sort by date. Once sorted I need to insert the data into my second database in the same query that inserts for all rows.... Would i use a multi dimensional array, and then do a sort for this?
database 1 (same row)
$sd1_indicator
$sd1
$sd1_date
$sd2_indicator
$sd2
$sd2_date
$sd3_indicator
$sd3
$sd3_date
I need to sort these "groups" of data by date and then place them in my new database. So after being sorted, $sd3_date might be the newest date and need to be inserted into $sd1_date in my second db..
thanks in advance!
-P
- scorphus
- Forum Regular
- Posts: 589
- Joined: Fri May 09, 2003 11:53 pm
- Location: Belo Horizonte, Brazil
- Contact:
Or yet, you could try this one I've just developed:
what outputs:
Regards,
Scorphus.
Code: Select all
<?php
function array_recursivesort (&$array) {
foreach ($array as $key => $value)
if (is_array($value))
array_recursivesort($array[$key]);
sort($array);
}
$array = array(5, 2, 8, 4, 0);
$array[] = array(4, 1, 8, 3, 6, 4, 0, 5, 2, 8, 3, 0);
$array[] = array(5, 4, 3, 2, 1);
array_recursivesort($array, SORT_NUMERIC);
print_r($array);
?>Code: Select all
Array
(
ї0] => 0
ї1] => 2
ї2] => 4
ї3] => 5
ї4] => 8
ї5] => Array
(
ї0] => 1
ї1] => 2
ї2] => 3
ї3] => 4
ї4] => 5
)
ї6] => Array
(
ї0] => 0
ї1] => 0
ї2] => 1
ї3] => 2
ї4] => 3
ї5] => 3
ї6] => 4
ї7] => 4
ї8] => 5
ї9] => 6
ї10] => 8
ї11] => 8
)
)Scorphus.
Hi Scorphus,
Here is the problem ... i am accessing a database that I have no control over and downloading the data to my server and database. The column structure is a bit weird on the first database...
$sd1_indicator
$sd1
$sd1_date
$sd2_indicator
$sd2
$sd2_date
$sd3_indicator
$sd3
$sd3_date
the columns in the first db are exactly as above .. including a "space column" between the "data sets"
associating the 3 seperate columns per data set needs to be done in the script because there is nothing in the database to associate each date column with other than their similar naming.
it is super important the "groups" of data stay together after being sorted.. I probably failed to mention .. the above data is all in the same row as well ...
Here is the problem ... i am accessing a database that I have no control over and downloading the data to my server and database. The column structure is a bit weird on the first database...
$sd1_indicator
$sd1
$sd1_date
$sd2_indicator
$sd2
$sd2_date
$sd3_indicator
$sd3
$sd3_date
the columns in the first db are exactly as above .. including a "space column" between the "data sets"
associating the 3 seperate columns per data set needs to be done in the script because there is nothing in the database to associate each date column with other than their similar naming.
it is super important the "groups" of data stay together after being sorted.. I probably failed to mention .. the above data is all in the same row as well ...
- scorphus
- Forum Regular
- Posts: 589
- Joined: Fri May 09, 2003 11:53 pm
- Location: Belo Horizonte, Brazil
- Contact:
What is the format of the date (06/07/2004 or 2004-07-06 or yet 06 Jul 2004)? How are you fetching teh date from DB? Could you please post the script? Also, please post the output of the function [php_man]print_r[/php_man]() applyed in one row fetched from the DB? Something like
so I can have an idea about the data especially about the date format.
Thanks,
Scorphus.
Code: Select all
<?php
// ...
echo "<pre>\n";
while ($row = mysql_fetch_row($result)) {
// ...
print_r($row);
}
echo "</pre>\n";
// ...
?>Thanks,
Scorphus.
Hi Scorphus,
the first database (db1) is in the format of 12/17/68 .. I have wrote some script to change it to the accepted Mysql format before insertion into db2
which brings up a nice little problem in that he doesn't have the first database defined as a date column and just a var char column .. shoot! I just thought about that!!! I guess i could use the code below, and get it in a useable format first! then put each "set" of records into the first array(s). then put those into the next dimension and sort by date... then I could insert according to index.... sound good? hehehe
the first database (db1) is in the format of 12/17/68 .. I have wrote some script to change it to the accepted Mysql format before insertion into db2
which brings up a nice little problem in that he doesn't have the first database defined as a date column and just a var char column .. shoot! I just thought about that!!! I guess i could use the code below, and get it in a useable format first! then put each "set" of records into the first array(s). then put those into the next dimension and sort by date... then I could insert according to index.... sound good? hehehe
Code: Select all
<?php
$date_nos = explode("/", $sale_date);
$formatted_sale_date = $date_nos[2] . "-" . $date_nos[0] . "-" . $date_nos[1];
?>- scorphus
- Forum Regular
- Posts: 589
- Joined: Fri May 09, 2003 11:53 pm
- Location: Belo Horizonte, Brazil
- Contact:
Well, I think there could be a better choice. You could use the MySQL's Date and Time functions to do all the job for you.
First, UNIX_TIMESTAMP() could be used to retrive the date from db1 in Unix timestamp (seconds since '1970-01-01 00:00:00' GMT) as an unsigned integer and tell MySQL to order the rows by this integer. Something like:
After the retrival you filter the array of rows and use FROM_UNIXTIME() to insert the data in db2:
Considering the filed which will receive FROM_UNIXTIME() function is of the DATE type.
I think this way it will be faster and simpler. Also, why don't you let MySQL filter those rows for you? How are you filtering them?
Hope that helps.
-- Scorphus
First, UNIX_TIMESTAMP() could be used to retrive the date from db1 in Unix timestamp (seconds since '1970-01-01 00:00:00' GMT) as an unsigned integer and tell MySQL to order the rows by this integer. Something like:
Code: Select all
$sql = "SELECT indicator_column, space_column, UNIX_TIMESTAPM(date_column) AS date ORDER BY date";Code: Select all
$sql = "INSERT INTO table_name VALUES(null, $indicator, $space _column, FROM_UNIXTIME($date)";I think this way it will be faster and simpler. Also, why don't you let MySQL filter those rows for you? How are you filtering them?
Hope that helps.
-- Scorphus
- scorphus
- Forum Regular
- Posts: 589
- Joined: Fri May 09, 2003 11:53 pm
- Location: Belo Horizonte, Brazil
- Contact:
Ok, think I got what you mean. Sory, I couldn't get it early...
Anyways, let's say you have an array with 9 grabbed "data sets" gouped in an array ($array):
what outputs:
Examine the script and it's output and see what you can take from it.
Regards,
Scorphus.
Anyways, let's say you have an array with 9 grabbed "data sets" gouped in an array ($array):
Code: Select all
<?php
// This array was grabbed from db1:
$array = array('indicator1', 'data1', '12/21/03',
'indicator2', 'data2', '12/15/03',
'indicator3', 'data3', '12/27/03',
'indicator4', 'data4', '12/02/03',
'indicator5', 'data5', '12/19/03');
$size = sizeof($array) / 3;
$data_sets = array();
for ($i = 0; $i < $size; $i++) {
$slice = array_slice($array, 3*$i, 3);
$date = explode('/', $slice[2]);
$slice[2] = strtotime($slice[2]);
$data_sets[$slice[2]] = $slice;
}
ksort($data_sets);
foreach($data_sets as $set) {
$sql = "INSERT INTO table_name_on_db2 VALUES(null, '${set[0]}', '${set[1]}', ${set[2]});";
echo "$sql\n";
}
?>Code: Select all
INSERT INTO table_name_on_db2 VALUES(null, 'indicator4', 'data4', 1070330400);
INSERT INTO table_name_on_db2 VALUES(null, 'indicator2', 'data2', 1071453600);
INSERT INTO table_name_on_db2 VALUES(null, 'indicator5', 'data5', 1071799200);
INSERT INTO table_name_on_db2 VALUES(null, 'indicator1', 'data1', 1071972000);
INSERT INTO table_name_on_db2 VALUES(null, 'indicator3', 'data3', 1072490400);Regards,
Scorphus.
scorphus,
I found this function on the php manual pages usort()
Think this will work? Let's say these were three of my arrays.. I need to sort the three deed variables by the date column and then insert into db2 .. I know this is totally screwy looking and will not work; but maybe you can see what I am trying to do...
// pull data from db1 and put into array
$deed1 = array( "sd1_indicator" => $sd1_indicator, "sd1" => $sd1, "sd1_date" => $sd1_date,);
$deed2 = array( "sd2_indicator" => $sd2_indicator, "sd2" => $sd2, "sd2_date" => $sd2_date,);
$deed3 = array( "sd3_indicator" => $sd3_indicator, "sd3" => $sd3, "sd3_date" => $sd3_date,);
//create multi-dimensional array here.. sort by date
$sd1_indicator_db2['0'] ['sd1_indicator'] = 'some data';
$sd1__db2['0'] ['sd1'] = 'some data';
$sd1_date_db2['0'] ['sd1_date'] = 'some data';
$sd2_indicator_db2['1'] ['sd2_indicator'] = 'some data';
$sd2__db2['1'] ['sd2'] = 'some data';
$sd2_date_db2['1'] ['sd2_date'] = 'some data';
$sd3_indicator_db2['2'] ['sd3_indicator'] = 'some data';
$sd3__db2['2'] ['sd3'] = 'some data';
$sd3_date_db2['2'] ['sd3_date'] = 'some data';
// sort the data?
usort(some,args, here,"DateSort");
// insert into db2 by using sorted index
insert into table_name (sd1_indicator_db2,....) values($sd1_indicator_db2[0],....etc.)
I found this function on the php manual pages usort()
Think this will work? Let's say these were three of my arrays.. I need to sort the three deed variables by the date column and then insert into db2 .. I know this is totally screwy looking and will not work; but maybe you can see what I am trying to do...
// pull data from db1 and put into array
$deed1 = array( "sd1_indicator" => $sd1_indicator, "sd1" => $sd1, "sd1_date" => $sd1_date,);
$deed2 = array( "sd2_indicator" => $sd2_indicator, "sd2" => $sd2, "sd2_date" => $sd2_date,);
$deed3 = array( "sd3_indicator" => $sd3_indicator, "sd3" => $sd3, "sd3_date" => $sd3_date,);
Code: Select all
<?php
function DateSort($a,$b,$d="-") { //$d is the delimeter
if ($a == $b)
{ return 0; }
else { //Convert into dates and compare
list($am,$ad,$ay)=split($d,$a);
list($bm,$bd,$by)=split($d,$b);
if (mktime(0,0,0,$am,$ad,$ay) < mktime(0,0,0,$bm,$bd,$by))
{ return -1; }
else { return 1; }
}
}
?>$sd1_indicator_db2['0'] ['sd1_indicator'] = 'some data';
$sd1__db2['0'] ['sd1'] = 'some data';
$sd1_date_db2['0'] ['sd1_date'] = 'some data';
$sd2_indicator_db2['1'] ['sd2_indicator'] = 'some data';
$sd2__db2['1'] ['sd2'] = 'some data';
$sd2_date_db2['1'] ['sd2_date'] = 'some data';
$sd3_indicator_db2['2'] ['sd3_indicator'] = 'some data';
$sd3__db2['2'] ['sd3'] = 'some data';
$sd3_date_db2['2'] ['sd3_date'] = 'some data';
// sort the data?
usort(some,args, here,"DateSort");
// insert into db2 by using sorted index
insert into table_name (sd1_indicator_db2,....) values($sd1_indicator_db2[0],....etc.)