Associative array push from mysql statement

[Robert Poole]

Okay, here's what I'm trying to do...

I have a database with some dates in it, but they're not timestrings, so I need to pull them out, turn them into timestrings and then display the results in order by this timestring.

So I do my mysql statement like "SELECT * FROM table"

Then I need to use a while loop to add them into an associative array, like

while ($row = mysql_fetch_array($whatever))
{
$newdate = turn column date into timestring
push_array($array, $newdate=>$row['column2']=>$row['column3']);
}

and then I need to sort the array by the new date, and print these in a table.

Is anybody with me? Basically I have no idea how to do the push bit, or the sorting by a specific column.

[jatar_k]

you just want 3 values per row in the array? hard to have an associative key with 2 values

what type of data is it? not timestring?, are they timestamps?

I am making an assumption that it doesn't have to be an associative array, you could do it this way

$datearr = array();
while ($row = mysql_fetch_array($whatever)) {
$newdate = turn column date into timestring
$datearr[] = array($newdate,$row['column2'],$row['column3']);
}

that would add your 3 values as an array at the end of the array

[borntobeweb]

Hi Robert,

Are the dates stored in a specific format? You can use the SQL function STR_TO_DATE to convert the string to a SQL date/time and order by that, so:

$qh = mysql_query("SELECT str_to_date(datecolumn, '%m/%d/%Y'), table.* FROM table ORDER BY 1");
while ($row = mysql_fetch_array($qh)) {
$array[] = array('date' => $newdate, 'column2' => $row['column2'], 'column3' => $row['column3']);
}
// $array is sorted by date/time
foreach($array as $item) {
print $item['date'], $item['column2'], $item['column3']
}

If not, you can do something close to what you have already:

$qh = mysql_query("SELECT * FROM table");
while ($row = mysql_fetch_array($qh)) {
$newdate = turn column date into timestring
$array[$newdate] = array('column2' => $row['column2'], 'column3' => $row['column3']);
}
ksort($array, SORT_NUMERIC); // sorts associative array by key
foreach($array as $newdate => $item) {
print $newdate, $item['column2'], $item['column3']
}

Hope this helps.

[Robert Poole]

thanks for your suggestions guys. i've used the latter suggestion from borntobeweb, using the ksort... but is there a way to sort it in descending order? at the moment it's all good but i'd like to have the most recent date at the top and the oldest at the bottom.

[Robert Poole]

Okay, it was krsort, I'm a loser.

[Robert Poole]

Okay, now I have a problem in that some of the dates are the same, but because they are being used as the key I guess they have to be unique? This means that my result set of 92 is giving me an array of 42 items, so how do I go about getting all my results in the array? Do I need to add another column to my array? Then how would I call to it in my foreach statement?

Thanks.

[jatar_k]

you could then just use the method I showed

if you don't have unique values then they can't use them as associative keys, so just use an indexed array