sort multidimentional array (or other best options)

[flash gordon]

Hi ya,

 

I'm pulling dates from a database and I want to sort the dates from newest to oldest. But I need to keep the unique "id" with each date.

 

Here is what I am doing:

while ($row = mysql_fetch_array($myQuery, MYSQL_BOTH)) 
{
$ids[]   = $row['id'];
$times[] = mktime(0, 0, 0, $row['month'], $row['day'], $row['year']);
}
$data = array($ids, $times);

 

I need to sort $data[1] by the times, but I need $ids to go along with it. Does that make sense?

 

Any ideas?

Cheers

 

[ToonMariner]

Just have the one array.

 

while ($row = mysql_fetch_array($myQuery, MYSQL_BOTH))

{

$arr['id'][]  = $row['id'];

$arr['date'][] = mktime(0, 0, 0, $row['month'], $row['day'], $row['year']);

}

 

Then use multisort to maintain the relationship bewteen indices in teh ary on sorting by dtae.

[flash gordon]

Thanks for the help, but I need a little more guidance. The docs are virtually no help on this one to me....

array_multisort();

[effigy]

Did you look at the "Example 245. Sorting database results" section here?

[flash gordon]

or maybe my problem isn't with the array_multisort() function, maybe I have an error somewhere else:

// echo out info
while ($row = mysql_fetch_array($myQuery, MYSQL_BOTH)) 
{
$data['ids'][]      = $row['id'];
$data['times'][]    = mktime(0, 0, 0, $row['month'], $row['day'], $row['year']);
$data['location'][] = $row['location'];
$data['more'][]     = $row['more'];

/*
echo '<event id="' . $row["id"] . '" day="' . $row["day"] . '" month="' . $row["month"] . '" year="' . $row["year"] . '" location="' . $row["location"] . '" more="' . $row['more'] . '" />';
*/
}

array_multisort($data['times'], SORT_NUMERIC, SORT_DESC);
//print_r($data['ids']);

for ($i=0; $i<count($data['times']); $i++) 
{	
echo '<event id="' . $data['ids'][$i] . '" day="' . date("j", $data['times'][$i]) . '" month="' . date("n", $data['times'][$i]) . '" year="' . date("Y", $data['times'][$i]) . '" location="' . $data['location'][$i] . '" more="' . $data['more'][$i] . '" />';

}

out puts:

<calendar>
<event id="6" day="1" month="1" year="2008" location="Happy New Year" more="Gonna party like it is 1999! Whoop"/>
<event id="1" day="19" month="3" year="2007" location="March Madness" more="blah blah blah blah foo foo blah foo blah"/>
<event id="2" day="12" month="2" year="2007" location="Hello World" more="blah blah blah blah foo foo blah foo blah"/>
<event id="5" day="4" month="2" year="2007" location="V day bash 302" more="blah blah blah blah foo foo blah foo blah"/>
<event id="3" day="30" month="8" year="2006" location="This is old news" more="blah blah blah blah foo foo blah foo blah"/>
</calendar>

 

But the days for id 2 & 5 are backwards. In the database id 2 day=4 and id 5 day=12. Maybe it isn't the sorting....i dunno.

[effigy]

Hold on a moment.... Why aren't you using MySQL's ORDER BY?

[flash gordon]

order by what: year, month, and day? You can do that?

[effigy]

See DATE_FORMAT.

[flash gordon]

I have read that, but I don't know enough about php to make that work for me.

 

Any ideas why the code above is messing up the ids?

 

If i do this:

$query  = "SELECT * FROM $table"; // ORDER BY year DESC";

the order of the "ids" changes yet again.

 

There is something about array_multisort() that doesn't adjust all indexes accordingly. In fact it is ONLY sorting the "times". I need all arrays to sort based from the order of the "times" once sorted.

 

So back to my original question: I need to sort a particular fields in a multidimensional array but I need the rest of the fields to be reflected as well.

 

HELP!

[effigy]

Why not SELECT * FROM table ORDER BY year, month, day?

[flash gordon]

array_multisort($data['times'], SORT_DESC, $data['ids'], $data['location'], $data['more']);

 

::rollseyes::

[flash gordon]

Why not SELECT * FROM table ORDER BY year, month, day?

 

didn't know I could.

[flash gordon]

and it doesn't seem to output the data correctly.....

[effigy]

Post the results of DESC table.

[flash gordon]

i don't know what you are asking....here is what i got:

$query   = "SELECT * FROM $table"; // ORDER BY year, month, day ASC";
$myQuery = mysql_query($query);

// echo out info
while ($row = mysql_fetch_array($myQuery, MYSQL_BOTH)) 
{
$data['ids'][]      = $row['id'];
$data['times'][]    = mktime(0, 0, 0, $row['month'], $row['day'], $row['year']);
$data['location'][] = $row['location'];
$data['more'][]     = $row['more'];


echo '<event id="' . $row["id"] . '" day="' . $row["day"] . '" month="' . $row["month"] . '" year="' . $row["year"] . '" location="' . $row["location"] . '" more="' . $row['more'] . '" />';

 

Output:

<calendar>
<event id="1" day="19" month="3" year="2007" location="March Madness" more="blah blah blah blah foo foo blah foo blah"/>
<event id="2" day="4" month="2" year="2007" location="Hello World" more="blah blah blah blah foo foo blah foo blah"/>
<event id="3" day="30" month="8" year="2006" location="This is old news" more="blah blah blah blah foo foo blah foo blah"/>
<event id="5" day="12" month="2" year="2007" location="V day bash 302" more="blah blah blah blah foo foo blah foo blah"/>
<event id="6" day="1" month="1" year="2008" location="Happy New Year" more="Gonna party like it is 1999! Whoop"/>
</calendar>

[flash gordon]

And this actually works.....

//   get info from database
$query   = "SELECT * FROM $table"; // ORDER BY year, month, day ASC";
$myQuery = mysql_query($query);

// echo out info
while ($row = mysql_fetch_array($myQuery, MYSQL_BOTH)) 
{
$data['ids'][]      = $row['id'];
$data['times'][]    = mktime(0, 0, 0, $row['month'], $row['day'], $row['year']);
$data['location'][] = $row['location'];
$data['more'][]     = $row['more'];

/*
echo '<event id="' . $row["id"] . '" day="' . $row["day"] . '" month="' . $row["month"] . '" year="' . $row["year"] . '" location="' . $row["location"] . '" more="' . $row['more'] . '" />';
*/

}


array_multisort($data['times'], SORT_DESC, $data['ids'], $data['location'], $data['more']);
//print_r($data['ids']);

for ($i=0; $i<count($data['times']); $i++) 
{	
echo '<event id="' . $data['ids'][$i] . '" day="' . date("j", $data['times'][$i]) . '" month="' . date("n", $data['times'][$i]) . '" year="' . date("Y", $data['times'][$i]) . '" location="' . $data['location'][$i] . '" more="' . $data['more'][$i] . '" />';

}

 

Desired output:

<calendar>
<event id="6" day="1" month="1" year="2008" location="Happy New Year" more="Gonna party like it is 1999! Whoop"/>
<event id="1" day="19" month="3" year="2007" location="March Madness" more="blah blah blah blah foo foo blah foo blah"/>
<event id="5" day="12" month="2" year="2007" location="V day bash 302" more="blah blah blah blah foo foo blah foo blah"/>
<event id="2" day="4" month="2" year="2007" location="Hello World" more="blah blah blah blah foo foo blah foo blah"/>
<event id="3" day="30" month="8" year="2006" location="This is old news" more="blah blah blah blah foo foo blah foo blah"/>
</calendar>

[ToonMariner]

$query  = "SELECT * FROM $table"; // ORDER BY year, month, day ASC";

 

That doesn't!!!!

 

should be

 

$query  = "SELECT * FROM $table RDER BY year, month, day ASC";

 

 

[flash gordon]

Pretty sure that didn't give me the correct output. But hey, at least I know I can do that kind of stuff know.

 

Always learning.

Cheers

 

[roopurt18]

You should not be storing dates as separate fields in the first place.

 

There's no sense in:

CREATE TABLE table_one(
  month INT, day INT, year INT
)

 

when this will do just fine:

CREATE TABLE table_two(
  dateDT DATETIME
)

 

Pick up a book on MySQL.  You'll save yourself so much headache.

[ToonMariner]

Thats not strictly true roopurt...

 

If you are using year month and day to separate content (like a news archive) and use those parameters in a query then it makes sense to do so.

 

There is no interpretation to perform in php prior to or after any queries and I believe the storgate space required in the database would actually be less in the long term.