[RESOLVED] manipulating data from sql

[paran0id_Dan]

does anyone know how I can substitute data in a table returned by sql, for example, my query is

echo $sql = "SELECT priorities.PRIORITY, lastWeek.WEEK1, twoWeeksAgo.WEEK2, threeAndFourWeeksAgo.WEEK4, fourToEightWeeksAgo.WEEK8
FROM (SELECT DISTINCT call.priority FROM call
WHERE call.priority IN ('0','1','2','3','4','5','6','7','8','9')) as priorities
LEFT JOIN
( SELECT call.priority, count(*) as week8
FROM call
LEFT JOIN org on org.org_id = call.org_id
WHERE org.mnemonic = '$orgname' AND call.opendate BETWEEN '$dates[8]' AND '$dates[6]' AND call.status = 'OPEN'
GROUP BY call.priority

and the results show

PRIORITY WEEK1 WEEK2 WEEK4 WEEK8
1 NULL NULL NULL NULL
2 3 1 NULL 3
3 15 6 1 5
4 2 2 1 1
5 2 1 NULL 1
9 NULL NULL NULL NULL

what I need is to replace the NULL with either a '0' or 'None'......any ideas ??

[jib0]

Try this

SELECT priorities.PRIORITY, IFNULL(lastWeek.WEEK1, '0') AS WEEK1 ...

The same for 'None'.

Note that you don't have tu use quotes for numbers in MySQL. '0' is simply 0 except if you want the number to be a string.

[paran0id_Dan]

cant get this to work

IFNULL(WEEK1, '0');

im not using mysql, does this matter ?

do you have to add this to the php code or to the sql query ?

thanks

[jib0]

Yes this is a MySQL function. You can do the same with an other DBMS using an IF.

[paran0id_Dan]

cracked it.......used the following

SELECT priorities.PRIORITY, ISNULL(lastWeek.WEEK1, '0')AS WEEK1

works like a charm

thanks for your help