Lets say I have this:

[tcorbeil]

If I have a table called hockey such as this:

 

submitid              Flames            Oilers              Canucks

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

1              |      Iginla        |      Smith     

2              |                      |      Roloson      |  Sedin

 

Concentrating on the Canucks column, basically if i populate the table, let's say for example on line 1, I have data for Flames & for oilers but none for Canuck at the time of entry..  As it stand, when I DO have data for Canucks, I don't want there to be an empty spot..  So if I have data on line one for Flames & Oilers, when I get data for Canucks, I want it to fill out uniform..  Is there an easy way to this?

 

I realize there is commands to alter a row but what is the process to look at row for a specific column, find the first empty space of that row, and fill out that column without leaving spaces between rows.. the end result being:   

 

submitid              Flames            Oilers              Canucks

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

1              |      Iginla        |      Smith        |  Sedin

2              |                      |      Roloson      | 

 

Any help would be much appreciated..

 

T.

[btherl]

It's an odd request.  I would start be selecting all rows where that column is empty, in order of increasing submitid, and take the first row returned.  Then put the data into that row.  I would do that for each column.

 

Then if you have data left over, create a new row and put it in there.

[fenway]

What are you storing that it requires such an obscure format?

[bubblegum.anarchy]

This can not possible the best way to get what you want but...

 

DROP TABLE IF EXISTS hockey;
CREATE TABLE hockey (
submitid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
flames TEXT NULL,
oilers TEXT NULL,
canucks TEXT NULL,
PRIMARY KEY (submitid)
) TYPE=MyISAM;
INSERT INTO hockey SET flames = 'Iginla', oilers = 'Smith', canucks = '';
INSERT INTO hockey SET flames = '', oilers = 'Roloson', canucks = 'Sedin';
# ------------------------------------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS hockey_flames SELECT flames FROM hockey ORDER BY flames DESC;
ALTER TABLE hockey_flames ADD id int(10) unsigned not null auto_increment primary key;
CREATE TABLE IF NOT EXISTS hockey_oilers SELECT oilers FROM hockey ORDER BY oilers DESC;
ALTER TABLE hockey_oilers ADD id int(10) unsigned not null auto_increment primary key;
CREATE TABLE IF NOT EXISTS hockey_canucks SELECT canucks FROM hockey ORDER BY canucks DESC;
ALTER TABLE hockey_canucks ADD id int(10) unsigned not null auto_increment primary key;

DELETE FROM hockey;
ALTER TABLE hockey AUTO_INCREMENT = 1;

INSERT INTO hockey (flames, oilers, canucks)
SELECT 
	hockey_flames.flames 
	, hockey_oilers.oilers
	, hockey_canucks.canucks
FROM hockey_flames
	INNER JOIN hockey_oilers ON hockey_flames.id = hockey_oilers.id
	INNER JOIN hockey_canucks ON hockey_flames.id = hockey_canucks.id;

DROP TABLE hockey_flames;
DROP TABLE hockey_oilers;
DROP TABLE hockey_canucks;
# ------------------------------------------------------------------------------------------------------------
SELECT * FROM hockey;

 

Be sure to make a backup copy of the `hockey` table... I might spend a little more time to have the queries appear more elegant.