flash gordon Posted February 12, 2007 Share Posted February 12, 2007 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 Quote Link to comment Share on other sites More sharing options...
ToonMariner Posted February 12, 2007 Share Posted February 12, 2007 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. Quote Link to comment Share on other sites More sharing options...
flash gordon Posted February 13, 2007 Author Share Posted February 13, 2007 Thanks for the help, but I need a little more guidance. The docs are virtually no help on this one to me.... array_multisort(); Quote Link to comment Share on other sites More sharing options...
effigy Posted February 13, 2007 Share Posted February 13, 2007 Did you look at the "Example 245. Sorting database results" section here? Quote Link to comment Share on other sites More sharing options...
flash gordon Posted February 13, 2007 Author Share Posted February 13, 2007 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. Quote Link to comment Share on other sites More sharing options...
effigy Posted February 13, 2007 Share Posted February 13, 2007 Hold on a moment.... Why aren't you using MySQL's ORDER BY? Quote Link to comment Share on other sites More sharing options...
flash gordon Posted February 13, 2007 Author Share Posted February 13, 2007 order by what: year, month, and day? You can do that? Quote Link to comment Share on other sites More sharing options...
effigy Posted February 13, 2007 Share Posted February 13, 2007 See DATE_FORMAT. Quote Link to comment Share on other sites More sharing options...
flash gordon Posted February 13, 2007 Author Share Posted February 13, 2007 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! Quote Link to comment Share on other sites More sharing options...
effigy Posted February 13, 2007 Share Posted February 13, 2007 Why not SELECT * FROM table ORDER BY year, month, day? Quote Link to comment Share on other sites More sharing options...
flash gordon Posted February 13, 2007 Author Share Posted February 13, 2007 array_multisort($data['times'], SORT_DESC, $data['ids'], $data['location'], $data['more']); ::rollseyes:: Quote Link to comment Share on other sites More sharing options...
flash gordon Posted February 13, 2007 Author Share Posted February 13, 2007 Why not SELECT * FROM table ORDER BY year, month, day? didn't know I could. Quote Link to comment Share on other sites More sharing options...
flash gordon Posted February 13, 2007 Author Share Posted February 13, 2007 and it doesn't seem to output the data correctly..... Quote Link to comment Share on other sites More sharing options...
effigy Posted February 13, 2007 Share Posted February 13, 2007 Post the results of DESC table. Quote Link to comment Share on other sites More sharing options...
flash gordon Posted February 13, 2007 Author Share Posted February 13, 2007 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> Quote Link to comment Share on other sites More sharing options...
flash gordon Posted February 13, 2007 Author Share Posted February 13, 2007 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> Quote Link to comment Share on other sites More sharing options...
ToonMariner Posted February 13, 2007 Share Posted February 13, 2007 $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"; Quote Link to comment Share on other sites More sharing options...
flash gordon Posted February 15, 2007 Author Share Posted February 15, 2007 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 Quote Link to comment Share on other sites More sharing options...
roopurt18 Posted February 15, 2007 Share Posted February 15, 2007 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. Quote Link to comment Share on other sites More sharing options...
ToonMariner Posted February 17, 2007 Share Posted February 17, 2007 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.