sort multi dimensional array?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

th3monk3y
Forum Commoner
Posts: 29
Joined: Thu Jun 19, 2003 10:34 pm

sort multi dimensional array?

Post 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
User avatar
scorphus
Forum Regular
Posts: 589
Joined: Fri May 09, 2003 11:53 pm
Location: Belo Horizonte, Brazil
Contact:

Post by scorphus »

[php_man]array_multisort[/php_man]() is your friend.

-- Scorphus
User avatar
scorphus
Forum Regular
Posts: 589
Joined: Fri May 09, 2003 11:53 pm
Location: Belo Horizonte, Brazil
Contact:

Post 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.
th3monk3y
Forum Commoner
Posts: 29
Joined: Thu Jun 19, 2003 10:34 pm

Post 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
User avatar
scorphus
Forum Regular
Posts: 589
Joined: Fri May 09, 2003 11:53 pm
Location: Belo Horizonte, Brazil
Contact:

Post 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.
th3monk3y
Forum Commoner
Posts: 29
Joined: Thu Jun 19, 2003 10:34 pm

Post 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 ...
User avatar
scorphus
Forum Regular
Posts: 589
Joined: Fri May 09, 2003 11:53 pm
Location: Belo Horizonte, Brazil
Contact:

Post 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.
th3monk3y
Forum Commoner
Posts: 29
Joined: Thu Jun 19, 2003 10:34 pm

Post 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];
?>
User avatar
scorphus
Forum Regular
Posts: 589
Joined: Fri May 09, 2003 11:53 pm
Location: Belo Horizonte, Brazil
Contact:

Post 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
th3monk3y
Forum Commoner
Posts: 29
Joined: Thu Jun 19, 2003 10:34 pm

Post 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:
User avatar
scorphus
Forum Regular
Posts: 589
Joined: Fri May 09, 2003 11:53 pm
Location: Belo Horizonte, Brazil
Contact:

Post 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.
th3monk3y
Forum Commoner
Posts: 29
Joined: Thu Jun 19, 2003 10:34 pm

Post 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.)
th3monk3y
Forum Commoner
Posts: 29
Joined: Thu Jun 19, 2003 10:34 pm

Post 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:
User avatar
scorphus
Forum Regular
Posts: 589
Joined: Fri May 09, 2003 11:53 pm
Location: Belo Horizonte, Brazil
Contact:

Post 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.
th3monk3y
Forum Commoner
Posts: 29
Joined: Thu Jun 19, 2003 10:34 pm

Post 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
Post Reply