Adding new db field alphabetically

[squiggerz]

Ok, I have a table with about 10 fields right now, about 6 or 7 of those fields are named by domain names (i.e. phpfreaks.com, domain.com, website.com).

 

I'm wanting to be able to query through php to add a new field name to this table that is a domain name but add it in alphabetically with the other fields that are domain names.

 

For instance, say I have

 

ID | Name | Domain.com | Phpfreaks.com | Website.com |  as field names already.

 

What if I want to add a new field called Freelance.com, but I want it to be added between Domain.com and Phpfreaks.com (alphabetically, right). Is there any way possible to do this?? I'm guessing I could probably select all fields that end with .com, .net, or whatever with a regex in php, then figure out a way to get it in the list alphabetically, but how would I structure a query of this sort?

 

 

[vijayfreaks]

Hi..

            for that you have to compare strings with every entered values in db..

            you have to gone for your own logic..? if its not there any other way?

            accordingly whatever results you r getting on that basis you can insert it in to db..

           

            may I know what you want from this type of operation..? i.e. why you want to do this? 

Regards,

Vijay           

[radalin]

He's not trying to insert something in the table, he's trying to alter table and add another coloumn.

 

Mysql has feature that you can add a coloumn after another coloumn. Something like this:

ALTER TABLE mytable ADD new_col VARCHAR( 50 ) NULL AFTER `old_col` ;

 

But I think that you want to automate this solution using a programming language. Use somthing like mysql_field_names() which will return an array. Then add you new col to the result array. Sort it, find your new coloumns name's index and while altering the database use something like:

mysql_query("ALTER TABLE mytable ADD {$fields[$i]} VARCHAR( 50 ) NULL AFTER {$fields[($i-1)]}") ;

 

Anyway if I get wrong what you said. And that you don't want to add fields but rows in the database no matter what order you have inserted them, while showing the data you can sort them with any field you want.

SELECT * FROM mytable ORDER BY ordercol ASC

 

Hope that helps.

[Illusion]

run the query

 

SHOW COLUMNS FROM DATABASENAME.TABLENAME;

 

<?php

returns the column name after which u want to add the new column //$COLUMN

?>

 

ALTER TABLE TABLENAME ADD COLUMNNAME DATATYPE AFTER '$COLUMN;

 

this is what i got in my first thought there may be better alternative...........

[fenway]

I'm wanting to be able to query through php to add a new field name to this table that is a domain name but add it in alphabetically with the other fields that are domain names.

 

This is TERRIBLE idea -- normalize your tables, don't have artibrarily named columns!