[Resolved] database sorting

kevinsequeira

i inherited this badly designed db from a an earlier programmer

id|name|CC1|CC2|CC3|CC4|export
test values ..
1|abc|123|456|NULL|NULL|yes
2|def|678|NULL|NULL|NULL|yes
3|ghi|901|234|567|NULL|yes

using only SQL statements is possible for me to get the result this way

1|abc|123
1|abc|456
2|def|678
3|ghi|901
3|ghi|234
3|ghi|567

that is the id, name and then CC1
if CC2 is not NULL on the next line id,name,CC2 and so.

im not sure if it is possible using only SQL statements without PHP.

any help appreciated, thanks for your time
reg
Kevin

barand

You could do it with unions if your database supports them

SELECT id, name, CC1 FROM mytable
UNION
SELECT id, name, CC2 FROM mytable
UNION
SELECT id, name, CC3 FROM mytable
ORDER BY id

hth

barand

Don't forget the WHERE CC1 NOT NULL (I did) etc

kevinsequeira

thanks for your help Barand,
reg
kevin

barand

Yes, if you managed to get the output you wanted from the first post. But you'll have use php for this one.

Having got your query results, loop thru them testing for a change of id. On the first rec of each new id

echo id | name | CC

and on subsequent recs with same id

echo | CC

Using a table might be best way of getting tidy display.

tsinka

Hi,

if your database doesn't support UNION you could move the logic for that to the php code. That shouldn't bee too expensive because you have only four fields to analyse.

Do something like

SELECT id,name,CC1,CC2,CC3,CC4 FROM tablename WHERE ...

In the php code you could then walk through the result set with e.g. mysql_fetch_row and do all the stuff in php, e.g. build an array.

Example for one row fetched by mysql_fetch_row() assuming that the variable containing the row array has the name $row:

function buildArray($row,$field) {
    return array('id' => $row['id'], 'name' => $row['name'], 'CC' => $row[$field]);
}

...
...

$resArray = "";

while ($row = mysql_fetch_row($result) {
    if ($row['CC1']) {
        $resArray[] = buildArray($row,"CC1");
        if ($row['CC2']) {
            $resArray[] = buildArray($row,"CC2");
            if ($row['CC3']) {
                $resArray[] = buildArray($row,"CC3");
                if ($row['CC4']) {
                    $resArray[] = buildArray($row,"CC4");
                }
            }
        }
    }
}

It's probably not the best way and coding style, just an example (a multi dimensional $resArray might be better because id and name are redundant in the example above) 🙂

I couldn't test the code above so there might be syntax or logical errors 🙁

You should then have an array that contains the data like shown in your result table.

Regards,
Thomas

kevinsequeira

hi Thomas,
i knew i could do it in php but wanted to know if it was possible only through SQL statements.
thanks for your response

reg
kevin