Page 1 of 2

sort multi dimensional array?

Posted: Sun Jul 04, 2004 12:00 pm
by th3monk3y
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

Posted: Sun Jul 04, 2004 12:27 pm
by scorphus
[php_man]array_multisort[/php_man]() is your friend.

-- Scorphus

Posted: Sun Jul 04, 2004 12:41 pm
by scorphus
Or yet, you could try this one I've just developed:

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);
?>
what outputs:

Code: Select all

Array
(
    &#1111;0] =&gt; 0
    &#1111;1] =&gt; 2
    &#1111;2] =&gt; 4
    &#1111;3] =&gt; 5
    &#1111;4] =&gt; 8
    &#1111;5] =&gt; Array
        (
            &#1111;0] =&gt; 1
            &#1111;1] =&gt; 2
            &#1111;2] =&gt; 3
            &#1111;3] =&gt; 4
            &#1111;4] =&gt; 5
        )

    &#1111;6] =&gt; Array
        (
            &#1111;0] =&gt; 0
            &#1111;1] =&gt; 0
            &#1111;2] =&gt; 1
            &#1111;3] =&gt; 2
            &#1111;4] =&gt; 3
            &#1111;5] =&gt; 3
            &#1111;6] =&gt; 4
            &#1111;7] =&gt; 4
            &#1111;8] =&gt; 5
            &#1111;9] =&gt; 6
            &#1111;10] =&gt; 8
            &#1111;11] =&gt; 8
        )

)
Regards,
Scorphus.

Posted: Sun Jul 04, 2004 2:08 pm
by th3monk3y
Scorphus,
this looks nice.. I am just not sure how to incorporate it... how can I sort by date with your function?
thanks,
P

Posted: Sun Jul 04, 2004 10:21 pm
by scorphus
It is a lot easier to leave this with the DB. Why don't you query the database to fetch the rows in date order? Then take out thos 9 ones and insert them in the other table. How are tou querying the DB? Could you post the code here?

Regards,
Scorphus.

Posted: Tue Jul 06, 2004 12:57 pm
by th3monk3y
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 ...

Posted: Tue Jul 06, 2004 4:29 pm
by scorphus
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

Code: Select all

<?php
// ...
echo "<pre>\n";
while ($row = mysql_fetch_row($result)) {
    // ...
    print_r($row);
}
echo "</pre>\n";
// ...
?>
so I can have an idea about the data especially about the date format.

Thanks,
Scorphus.

Posted: Tue Jul 06, 2004 6:17 pm
by th3monk3y
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

Code: Select all

<?php
$date_nos = explode("/", $sale_date);
$formatted_sale_date = $date_nos[2] . "-" . $date_nos[0] . "-" . $date_nos[1];
?>

Posted: Tue Jul 06, 2004 7:51 pm
by scorphus
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:

Code: Select all

$sql = "SELECT  indicator_column, space_column, UNIX_TIMESTAPM(date_column) AS date ORDER BY date";
After the retrival you filter the array of rows and use FROM_UNIXTIME() to insert the data in db2:

Code: Select all

$sql = "INSERT INTO table_name VALUES(null, $indicator, $space _column, FROM_UNIXTIME($date)";
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

Posted: Tue Jul 06, 2004 10:21 pm
by th3monk3y
Hi Scorphus,
the problem is ... we are talking about one row ... each row in db1 has 500 columns ... I need to grab 18 columns from one row in db1, group them in an array, sort them by the date column, then, insert them into db2

I think I will take a stab at it, and post my failure here :lol:

Posted: Wed Jul 07, 2004 12:27 am
by scorphus
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):

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";
}
?>
what outputs:

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);
Examine the script and it's output and see what you can take from it.

Regards,
Scorphus.

Posted: Wed Jul 07, 2004 12:34 am
by th3monk3y
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,);

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; } 
           } 
   } 

?>
//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.)

Posted: Wed Jul 07, 2004 12:39 am
by th3monk3y
yes!! you got it while i was writing out my jibberish..lol .. look at what i have and tell me if that is the same thing you did above...
thanks,
Paul :lol:

Posted: Wed Jul 07, 2004 12:45 am
by scorphus
When I saw your post I imagined you were writing it right after I posted mine... lol too...

Good idea that yours! I think it works too!

Well, I'm glad we did it!

Cheers,
Pablo.

Posted: Wed Jul 07, 2004 12:51 am
by th3monk3y
saweet! I am going to play with the code you wrote (it works) and see if i can get it to work for me (dynamically).. thank you so much for your time... ( i may need some more help :P )
-Paul