SQL to TXT

Chadwick

Aloha All,

I am working on a database that, up until now, has been on a PHP enabled server. The company that I am contracted with is moving their hosting to a cheaper facility (doesn't it figure) and will no longer be able to support SQL databases of the size I am working with.

I need to make a flatfile database to work with our program but everytime i write a script, I get a connection error. I'm running out of time to have this transition moved and I'm desperate for some help! 😕

Here is the current SQL Table Format:

CREATE TABLE lyrics (
lid bigint(255) unsigned NOT NULL auto_increment,
artist varchar(255) NOT NULL default '',
album varchar(255) default NULL,
track int(3) unsigned default NULL,
song varchar(255) NOT NULL default '',
added_uid bigint(255) unsigned NOT NULL default '0',
added_date datetime default NULL,
corrected_uid bigint(30) unsigned default NULL,
corrected_date datetime default NULL,
lyrics longtext NOT NULL,
PRIMARY KEY (lid)
) TYPE=MyISAM;

I need it to be this in the flatfile:

lid|song||added_date|artist/album|lyrics|||0|No|No|0|0|Yes

Some things required with this script is that every [Enter] button command is replaced with a `` and it will not crash my machine i am on currently. :-P Also spaces need to be replaced with a "_".

This database has over 120000 entries with a total database size of over 40MB. I know I should not be using a flatfile with a DB of this magnitude but the company has left me with no other option(s).

Any help whatsoever will so much be appreciated!

daphreeek

my recommendation to you: talk them out of it! It would take ages to search through a flatfile of that size!!

another posibility is to find a queap mysql host (just the database) and host the database on a different server to the site. not idea but better than a flat file!

matt

Chadwick

My thoughts exactly. I have tried and they refused.

The database is actually not that bad seeing as it's only built once a day (and not constantly queried as with a search).

The website that is currnetly running is on very fast servers, but the processing time is horrible. Take a look at http://www.totallyrics.com/.

It works but the processing time is so hella slow. 🙁 Smaller databases work fine but for some reason this one, being larger than normal in size, is going slow.

surfgatinho

Maybe you should point your clients to this discussion.
With that many entries a text based db is a horrendous idea. One dodgy entry could mess the whole thing up and you'd have to fix it by hand. This would cost them more than a years MySQL hosting.
Text file db are fine for up to a few hundred enteries but then they start slowing down.
There are also issues with conccurrency, backups and reduced functionallity.
In short they won't be saving money in the long run.

jayant

try looking at the movabletype, ask your company to a get a commercial license for it. deploy it and convert the posts.
ps: it is written in perl/cgi.