[SOLVED] Sort columns in a database

[mattal999]

Hi guys,

 

I have a table structured like so:

 

id | name      | userid | songcount | song1 | song2 | ... | song20

---------------------------------------------------------------

6  | First      | 1      | 6            | 134    | NULL  | ... | 255

 

Now I want to go through this row, and put all of the numbers from song1 - song20 in order. Like say I had data like:

 

134, 234, NULL, NULL, 234, 577, 231, NULL, etc.

 

I want it like so:

 

134, 234, 234, 577, 231, NULL, NULL, NULL, etc.

 

So I want the empty columns shifted to the end, and the numbers moved forward to the next empty column, so in the end the numbers are all before the empty columns, still in the same order they were before. Any ideas how I would go about this? I was thinking some kind of foreach statement, but couldn't come up with anything.

[BioBob]

SELECT * , nickname IS NULL AS isnull

ORDER BY isnull ASC, nickname ASC

 

create a column for a NULL value then order by it in your order clause

[ozestretch]

Something to consider would be sorting the songs prior to inserting into the table initially 

[mattal999]

I would, but im looking for something like a foreach loop or while clause that would just sort them with UPDATE statements or something. I need to do this so that I can organise it properly. Kind of like a CRON script if you will. Example:

 

<?php

// Connect to DB
include("includes/connect.php");

// Grab row
$query = mysql_query("SELECT * FROM playlists WHERE id='".$id."'");
$row = mysql_fetch_array($query);

// Loop through row
while(xxxx) {
    // Sort the columns (No idea)
    // Run some UPDATE statements to make it sort properly
}

?>

[mattal999]

Nobody has any ideas?

[ozestretch]

Could try in the sql forum

[ozestretch]

Maybe set each song into an array, not placing NULL entries obviously, then run foreach statement to update songlist, leaving the last entries remaining of the 20, if any exist, to update as NULL?

[mattal999]

Wow. I think you've just solved my problem. I'll get coding!