Good with MySQL? Please read

[Euclid]

Hello all. I am hoping someone can help me with this.

I use pafiledb on my website for my downloads. The site deals with computers and computer security information so I feal the download section should have some security to it. So I thought it would be nice to have a field for md5sum information, wich by default it does not have.

I had a little bit of trouble getting it to work, but I am pleased with myself as it now works. The only thing is I could only get it to work by modifying the install script [ wich makes all the mysql tables]. Thats all well and good but the only way to get this modification working is if you apply it on the first install otherwise you have to drop the tables from the database and start over. No one is going to want to do that and I dont blame them. What I am trying to do now is make a dump script that will update the table that allready exists and add the line for the md5sum field.

I messed around with it but cant seem to get it to go in the right place. It takes the code but when you check back to the site every thing is in the wrong place. md5sum desplays in name field ect. ect....

I was hoping someone could take a look at this and help me make the dump file that i need

-----------------------------------
This is the default settings for the install script :

CREATE TABLE $db[prefix]_files (
file_id int(10) NOT NULL auto_increment,
file_name text,
file_desc text,
file_creator text,
file_version text,
file_longdesc text,
file_ssurl text,
file_dlurl text,
file_time int(50) default NULL,
file_catid int(10) default NULL,
file_posticon text,
file_license int(10) default NULL,
file_dls int(10) default NULL,
file_last int(50) default NULL,
file_pin int(2) default NULL,
file_docsurl text,
file_rating text NOT NULL,
file_totalvotes text NOT NULL,
PRIMARY KEY (file_id)
)";

-----------------------------------------------------
I need it to be like this :

CREATE TABLE $db[prefix]_files (
file_id int(10) NOT NULL auto_increment,
file_name text,
file_desc text,
file_creator text,
file_version text,
file_mdsum text, // this is the field i added and it has to be in this spot //
file_longdesc text,
file_ssurl text,
file_dlurl text,
file_time int(50) default NULL,
file_catid int(10) default NULL,
file_posticon text,
file_license int(10) default NULL,
file_dls int(10) default NULL,
file_last int(50) default NULL,
file_pin int(2) default NULL,
file_docsurl text,
file_rating text NOT NULL,
file_totalvotes text NOT NULL,
PRIMARY KEY (file_id)
)";

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

Can someone help me make the dump file with the line file_mdsum text in the correct spot as seen above.. Any help would be apreciated. Thanks

[MrLinus]

Which version of MySQL and what platform?

[Euclid]

I cant think of the version number of mysql... Its recent and i am using red hat linux.

[MrLinus]

Well, you can get the version number by doing a mysql --version.

And I think what you want is a script that updates the columns rather than re-creates the columns in the MySQL (if I'm understaning correctly). So rather than have it do a create (which is for creating a table for the first time) use ALTER command. (The link is to mysql.com's manual).

BTW, what's the PHP code for the "dump"?

[Euclid]

mysql Ver 11.18 Distrib 3.23.54, for pc-linux (i686).

Yea I know it is alter that I need to use, I should have clarified. I am having trouble getting file_mdsum text, to go in the right spot with it... If you look at what i posted above counting file_id int(10) NOT NULL auto_increment, as line one i need the file_mdsum text, to be the sixth line down.

[MrLinus]

From MySQL Manual
# In MySQL Version 3.22 or later, you can use FIRST or ADD ... AFTER col_name to add a column at a specific position within a table row. The default is to add the column last. From MySQL Version 4.0.1, you can also use the FIRST and AFTER keywords in CHANGE or MODIFY.

If it's created for the first time, I would think everything would line up. What I'm understanding is after it's created, table is then altered (and the new column put at the end rather than in position six) and thus you have problems. This alter tag using ADD .. AFTER should help.

If I understand correctly (I may not. It's been one of those ugly weeks)