6 replies to this topic

[Soujirou]

I need help with my php code...

I have a mysql table with a bunch of information

then I have a php/html table made to display the mysql table exactly except with one extra field(column) which is populated by data calculated from the mysql data (of the same row)

Here is my problem, I would like to SORT this new calculated field. Since its not a field from mysql I can't just rewrite the query with a ORDER BY... Is there some good way I can sort this table with the new field?

here is a rough quick "diagram" explaination
column 1 and 2 is from mysql, column 3 is calculated based on 1 and 2


column 1 | column 2 | column 3
......1.......|......2.......|......3
..... 4.......|......7.......|......11
......3.......|......2.......|.......5

I would like to sort column 3



the calculated field is done on the php side, I'm rather newb at this but I'm assuming I can do the calculations from mysql and order it from there?

Greatly appreciated if anyone can help...
Many thanks in advance

[Orjan]

This should work:

SELECT col1, col2, col1+col2 AS col3 ORDER BY col3

[Soujirou]

thanks for the reply

that was just an example table to show what i want to do

the actual calculation for "column 3" is rather complicated

[Orjan]

but the sorting is done the same way. if you post your table structure and your calculation, I can help you for the real query

[Soujirou]

still in the planning stages so I don't have an actual code, this is gist of what I want the calculations to be like

field1:

Name: Status

value: "New", "Hold", "Complete"

field2:

Name: Importance

value: # from 1-10

field3:

Name: Submit_Date

value: Date

while (fetch(query)) {

  $priority = current time - field3 + field2 //return number of days

  

  if (field1 = "complete") {

    $priority = $priority - 365  // no priority for a year

  }

  

  if (field2 = "Hold") {

    $priority = $priority - 30  //no priority for a month

  }

  

  echo field1, field2, field3, $priority;

}

[Orjan]

You could do this by storing the values "0", "30" and "365" in field 1 instead of the texts, and translate them to text in your php instead (or have another table assigning text to your values), so your sql could do like this:

SELECT field1, field2, field3, DATEDIFF(now(), field3) + field2 -field1 AS priority FROM table ORDER BY priority

or with another table haveing text values

SELECT text, field2, field3, DATEDIFF(now(), field3) + field2 - field1 AS priority FROM table JOIN trans ON table.field1=trans.id ORDER BY priority

assuming the table trans fields is named id and text.

[Soujirou]

Exactly what I needed :w00t:

Thanks for all the help