Gossamer Forum
Home : Products : Gossamer Links : Version 1.x :

editing field position

Quote Reply
editing field position
anyone ever thought of adding a field position editor to the edit table definition section of editor.cgi?

I see it as a rename of table, create new table with the new field positions.. then go through the table and do an insert into TABLENAME (new fields) VALUES ($oldfields[2], $oldfields[1], $oldfields[3], $oldfields[4])

etc..

i'm currently doing this by hand with queries.. but i rather have it in editor.cgi.. it's more of a cosmetic thing for the admin really..

jerry
Quote Reply
Re: editing field position In reply to
What would be the purpose? =)

Cheers,

Alex
Quote Reply
Re: editing field position In reply to
i don't know.. but when I add a field.. i don't alwys like having it being the last one Wink

anyways.. like i said.. it's just cosmetic.. sometimes i rather have Short_Description above Description..

jerry
Quote Reply
Re: editing field position In reply to
I've thought about it...

Alex -- the reason is that because the Admin forms are dynamically generated, the position of the fields can make it easier to deal with input if admin-things were all near each other, and the dates were near the items they defined, etc. It's purely cosmetic, but there is a convenience factor for the dynamic paages like Admin.
Quote Reply
Re: editing field position In reply to
yes.. exactly..

i'm not expecting it.. but since i know "a way" to do it.. i'll try implementing it myself into editor.cgi after i put my 1.02 mods into 1.1b1..

ahh.. today i came home to find another "free" item in the mail for me.. it was a Retractable Dog Leash.. OH JOY.. i don't even have a dog.. the internet is such a cool place Wink

anyways..
jerry
Quote Reply
Re: editing field position In reply to
Has anyone come up with a modification to do this?

------------------
James L. Murray
PaintballCity.com
The Yahoo of Paintball
http://www.paintballcity.com
AIM: Paintball City
ICQ: 44147229



Quote Reply
Re: editing field position In reply to
The only thing you could do is physically re-order you database using INSERT and SELECT to take all your old links and fields and assign them to the same fields in a new database that has the fields in the order you want them in.

You'd then have to re-sync your .def file, or you'd get some _really_ strange results.

Quote Reply
Re: editing field position In reply to
To expand on this:

Take a structure dump of the current links table you are using.

Move the fields around in whatever order you want.

Then, create a new links table, called something like Links_NewOrder

Then, you'd want to take each and every record in your old database, and insert it into the new database table.

Code:
INSERT INTO Links.Links_NewOrder (Title, Keywords, Description, URL, Add_Date, Mod_Date, Status)
SELECT
Title, Keywords, Description, URL, Add_Date, Mod_Date, Status
from Links.Links
order by ID

The KEY -- let me repeat here -- THE KEY!!!! --- is to list EACH AND EVERY FIELD IN YOUR ORIGINAL LINKS TABLE IN THE NEW ORDER YOU WANT IT TO APPEAR!!

Then, you copy that line to the new table, since what MySQL will do is access the database, find the records, assign the values to the field variables, then assign them to the new table IN THE ORDER THEY ARE LISTED!

The Select is position neutral, the INSERT makes it position sensitive --- meaning the first value in the select will be assigned the the first value in the INSERT, the second value to the second, etc.

If you have a fairly small database, this will not inpact your system at all, and you can test and play with it until it works.

ONE MAJOR BUG: if you hvae fields that have been 'escaped' -- ie: that use """ "'" ">" etc, then it MAY NOT WORK !! The field-to-field copy does not preserve the escape!

The only way to do that is to write a short program that steps through the table, and SELECTS each record, then INSERTS each record into the new table, after checking to see if any string needed escapes.

You might be able to use your back-up files, to do something similar. But, there are some problems with that and preserving the LinkID.

Given enough time (and I don't have much) writing a small script would not be very hard. You just have to follow the examples for preparing the fields from the existing links routines, then INSERT them, rather than ADD them, to preserve the Links ID, and iterate through the database one record at a time.

A slow, but good way to do it for very large databases that can't be sucked into memory, is to find out how many links you have, and set up a loop with a counter, looking for record 'loop_counter' until 'loop_counter>Total'
You'd take the record, and Insert it into the new table, using a variation of the above command, but using:

INSERT INTO Links_New (field1, field2, field3...) VALUES ($value1, $value2, $value3...)

If anyone wants to do it, it's really just a hack of existing Links routines, you just have to sniff them out -- records are added in the Validate and add_record routines, you can see examples of the INSERT command in Jump.cgi (it counts hits) and such.

It's TIME CONSUMING but not hard!






Quote Reply
Re: editing field position In reply to
Here's another way to do it using mysqldump utility:

mysqldump --add-drop-table -c -uUSERNAME -pPASSWORD YOURDB Links > yourdb.txt

This will dump the contents of the Links table into a file called yourdb.txt. You can then edit the file and change the order of fields by editing the create table statement (should just be a matter of cut and paste some rows into different order).

Then just do a:

mysql -uUSERNAME -pPASSWORD YOURDB < yourdb.txt

and it will do the changes. You should then do a re-sync in the admin and you are set.

Cheers,

Alex
Quote Reply
Re: editing field position In reply to
Hello Alex,

I got an error. It took off the last 't' in '.txt':

Query Error: You have an error in your SQL syntax near 'mysqldump --add-drop-table -c -uUSERNAME -pPASSWORD milarticles Links > yourdb.tx' at line 1


USERNAME - replaced with the username name...no spaces.
PASSWORD - replaced with the password...no spaces.

Any ideas?


------------------
James L. Murray
PaintballCity.com
The Yahoo of Paintball
www.paintballcity.com
AIM: Paintball City
ICQ: 44147229





Quote Reply
Re: editing field position In reply to
The reason I didn't suggest this method is I thought when I looked before, it writes out the table defintion, it writes out the fields in the same order. But it uses the "INSERT" statement with a fully qualified field/value pair.

So, the command would be:

mysqldump --add-drop-table -c -uUSERNAME -pPASSWORD LinksDB LinksTable > dumpfile.txt

Where LinksDB is the name of the DATABASE and the LinksTable is the name of the table you want to dump (or no table if you want to dump the whole dabase).

To dump the default values:

mysqldump --add-drop-table -c -uUSERNAME -pPASSWORD Links Links > dumpfile.txt

That would dump the Links table of the Links database into dumpfile.txt

You then rearrange the fields in he "Create" table statement -- that should be just a matter of CAREFULLY moving whole lines around. Watch the COMMAS!!

When you import the file:

mysql -uUSERNAME -pPASSWORD LinksDB < datadump.txt

It will DROP (meaning it's gone!) the old Links table, and create a new one, in the new order, and inserting the records into it.

The command above you need to make the "LinksDB" what the links database is, the "default" values would be:

mysql -uUSERNAME -pPASSWORD Links < datadump.txt

DON'T FORGET: to do a re-sync in the admin!!!

If you don't, the .def file will have the fields in the wrong order, and you will get very, very screwy errors!!!


Quote Reply
Re: editing field position In reply to
GZ:

Quote:
Query Error: You have an error in your SQL syntax near 'mysqldump --add-drop-table -c -uUSERNAME -pPASSWORD milarticles Links > yourdb.tx' at line 1

MySQL only outputs a certain number of characters -- the 't' was probably one too many.

Try it again. I've used the cut/paste version of the command that Alex posted.

Make sure you have permissions to write out the file, and other such stuff.

But I did this on several databases, and with several combinations of tables, and it does work!!

Quote Reply
Re: editing field position In reply to
Oops, you are running this from the mysql monitor, not what you want to do. You want to run the mysqldump utility which is a separate program.

Normally mysqldump doesn't do complete inserts (i.e. doesn't specify the names in the insert tag), however adding -c fixes this.

Cheers,

Alex

Quote Reply
Re: editing field position In reply to
Hmm ... I don't seem to have mysqldump on my server.
Cosmetic or not, the order is important if you intend to let others use the admin section. Which I am.
So, assuming I start with an empty database, will it be safe to delete fields, and then add them in the order I want?
Also, are there any fields I can safely leave out? Such as these:

Mod_Date
Hits
isNew
isChanged
isPopular
Rating
Votes
ReceiveMail
Status
Date_Checked

John
Quote Reply
Re: [gotze] editing field position In reply to
I just did this successfully using phpMyAdmin.

Is was sooooo easy! Smile