Sort value by date value in same row

Question

I have a table like below -

Columns are like,
Id,
job_1
comp_1
date_1
job_2
comp_2
date_2
job_3
comp_3
date_3

and date is stored in varchar like '4/1990'.

So I want that value sorted by date.

I have used code like below -

$sql=mysql_fecth_array(mysql_query("SELECT * FROM table"));
for($i=1;$i<=3;$i++){
   $arr['job_'.$i]=$sql['job_'.$i];
   $arr['comp_'.$i]=$sql['comp_'.$i];
   $arr['date_'.$i]=$sql['date_'.$i];
}

Now, how can I proceed further to sort this by date?

`mysql_query`(and others with prefix `mysql_`) is deprecated. Change your initial logic — RomanPerekhrest, Dec 23 '16 at 08:03
See normalisation. Enumerated column names are often symptomatic of poor design. — Strawberry, Dec 23 '16 at 08:17

score 0 · Answer 1 · answered Dec 23 '16 at 13:47

Why not place the month after the year as an integer in a separate array column? (Or this could be done in the database). This way as years and days are consecutive you can sort on the integer values.

In SQL this would be as so:

CREATE TABLE dateTable (`date` CHAR(6));

INSERT INTO dateTable VALUES ('4/1990'), ('3/1989'), ('5/1991');

SELECT `date` FROM dateTable; # Not sorted

SELECT `date`, CONCAT(MID(`date`, INSTR(`date`, '/') + 1, 4), LEFT(`date`, INSTR(`date`, '/') - 1)) sortDate FROM dateTable ORDER BY CONCAT(MID(`date`, INSTR(`date`, '/') + 1, 4), LEFT(`date`, INSTR(`date`, '/') - 1)); # Sorted

And in PHP as so:

/* got this function from the following SO post: http://stackoverflow.com/questions/2699086/sort-multi-dimensional-array-by-value */
/* full credit to o0'. */
/* thanks! */

function aasort (&$array, $key) {
    $sorter=array();
    $ret=array();
    reset($array);
    foreach ($array as $ii => $va) {
        $sorter[$ii]=$va[$key];
    }
    asort($sorter);
    foreach ($sorter as $ii => $va) {
        $ret[$ii]=$array[$ii];
    }
    $array=$ret;
}

$sql=mysql_fecth_array(mysql_query("SELECT * FROM table"));
for($i=1;$i<=3;$i++){
   $arr['job_'.$i]=$sql['job_'.$i];
   $arr['comp_'.$i]=$sql['comp_'.$i];
   $arr['date_'.$i]=$sql['date_'.$i];

   $slashPos = strpos($sql['date_'.$i], "/");

   $arr['sortdate_'.$i]= (int)(substr($sql['date_'.$i], $slashPos + 1, 4) . substr($sql['date_'.$i], 1, $slashPos - 1));
}

aasort($arr, "sortdate")

Note that I haven't tried the PHP code, and I'm not sure why you are writing the row number to your array keys, but this should point you in the direction of a feasible solution.

Let me know if you require anything further.

James

Sort value by date value in same row

1 Answers1