Extracting data from txt files to import in a mysql database with php [closed]
Want to improve this question? Update the question so it focuses on one problem only by editing this post.
name: XXX surname: YYY age: ZZZ My problem is to read, for each txt file, these infos and to populate a mysql database for each correspondent field: name, surname, age. As txt files are very huge i need to handle only the header of each file (the first 15 lines) where are stored the infos i need. However in the headers the infos i need are not formatted in the same way, so i think that using regular expressions might be the best choice. Could someone help me? The following lines are the code i’m using right now. How and where i could modify the code to reach my goal?
Where do you need help? You have a job consisting of a lot of smaller issues: try to break it up, try come up with a solution for them, and if something specific does not work, come back with a question about that. As it stands, this question is to broad to be answered in a useful form for this site.
Oh, i’m sorry, excuse me for this. I can edit my question and insert a code. So I hope will be deleted my negative rates.
4 Answers 4
In my experience it is not practical to write a batch job in PHP. However, in your post you mention you would like to use the crontab, so I presume you are on some flavor of Linux, in which case you can use Bash.
Create a script: /home/yourid/bin/processdata.sh
#!/bin/bash # set-up some variables outstanding="/some/dir/outstanding" processed="/some/dir/processed" tempfile="/tmp/$$.sql" # set a trap to delete our $ on exit or ctrl+c trap "rm -f $" EXIT INT # list each .txt file in the outstanding directory ls -1 $/*.txt | while read filename do # stash the data after the ":" into a bash variable name=$(awk -F":" '/^name/ < print $2 >' $/$) surname=$(awk -F":" '/^surname/ < print $2 >' $/$) age=$(awk -F":" '/^age/ < print $2 >' $/$) # echo a mysql command into our $ echo "INSERT INTO some_table (name,surname,age) VALUES(\"$\",\"$\",\"$\")" > $ # run a mysql command using these variables mysql --user=username --password=password db_name < $|| < # if there is a problem, shout about it echo "Error while processing file: $/$" # break the loop (to leave the file in $ break > # move the file out of the way mv $/$ $/ done Then add a crontab entry to run it every 5 minutes:
*/5 * * * * /home/yourid/bin/processdata.sh >> /home/yourid/logs/processdata.log 2>&1 - The script does very little error handling.
- SQL is executed from a file which is a security risk (albeit somewhat negated by using $$).
- The directories used must exist for this to work.
Flat file databases [closed]
Want to improve this question? Update the question so it can be answered with facts and citations by editing this post.
What are the best practices around creating flat file database structures in PHP? A lot of more matured PHP flat file frameworks out there which I attempt to implement SQL-like query syntax which is over the top for my purposes in most cases. (I would just use a database at that point). Are there any elegant tricks out there to get good performance and features with a small code overhead?
I’d like to add that there is a package here for Flat File Database github.com/tmarois/Filebase I know this is an old question, but this package is the most recent build and maintained, plus full of features most neglect to include.
I am developing a CMS and I use a flat text file text database. It’s taken many hours to make and many hours to refracture but it works perfectly. Queries will be performed a lot faster with a fully indexed and optimised database. However, I avoid the need for queries by storing meta data and with careful organisation and structure. When I need data, I get it without a for loop (unless I am using all the data in the folder), therefore it performs a lot faster than a database would. I would go into detail and give a very good answer but unfortunately this question is closed.
11 Answers 11
Well, what is the nature of the flat databases. Are they large or small. Is it simple arrays with arrays in them? if its something simple say userprofiles built as such:
$user = array("name" => "bob", "age" => 20, "websites" => array("example.com","bob.example.com","bob2.example.com"), "and_one" => "more"); and to save or update the db record for that user.
$dir = "../userdata/"; //make sure to put it bellow what the server can reach. file_put_contents($dir.$user['name'],serialize($user)); and to load the record for the user
but again this implementation will vary on the application and nature of the database you need.
You might consider SQLite. It’s almost as simple as flat files, but you do get a SQL engine for querying. It works well with PHP too.
SQLite was build into 5.0+ by default, but discountinued (!) from PHP 5.4+ on . As I write this in July 2012, SQLite will not work on up-to-date systems anymore by default. Official statement here
Installing the SQLite PDO driver is pretty trivial if you have server access. On Ubuntu/Debian running Apache2 just do apt-get install php5-sqlite service apache2 restart
In reaction on the comment from @Sliq, stating that «SQLite was . discontinued» is sort of true: the extension named «SQLite» was discontinued and «SQLite3» is now enabled by default. php.net/manual/en/sqlite.installation.php «Since PHP 5.0 this extension was bundled with PHP. Beginning with PHP 5.4, this extension is available only via PECL.» php.net/manual/en/sqlite3.installation.php «The SQLite3 extension is enabled by default as of PHP 5.3.0.» «This extension was briefly a PECL extension but that version is only recommended for experimental use.»
In my opinion, using a «Flat File Database» in the sense you’re meaning (and the answer you’ve accepted) isn’t necessarily the best way to go about things. First of all, using serialize() and unserialize() can cause MAJOR headaches if someone gets in and edits the file (they can, in fact, put arbitrary code in your «database» to be run each time.)
Personally, I’d say — why not look to the future? There have been so many times that I’ve had issues because I’ve been creating my own «proprietary» files, and the project has exploded to a point where it needs a database, and I’m thinking «you know, I wish I’d written this for a database to start with» — because the refactoring of the code takes way too much time and effort.
From this I’ve learnt that future proofing my application so that when it gets bigger I don’t have to go and spend days refactoring is the way to go forward. How do I do this?
SQLite. It works as a database, uses SQL, and is pretty easy to change over to MySQL (especially if you’re using abstracted classes for database manipulation like I do!)
In fact, especially with the «accepted answer»‘s method, it can drastically cut the memory usage of your app (you don’t have to load all the «RECORDS» into PHP)
That’s true. serialize() can be pretty useful for that as well. I think the trick to coming up with a viable system is finding some way to index the data nodes without killing yourself with complexity.
I give you a scenario that you don’t want to use SQLite or in fact any database and go straight to file system. you have 80million transactions records in your system, lentgh of each transaction record is just 126 character, you are adding 1800 transactions in a second to it and you only read this data once a day after midnight.
One framework I’m considering would be for a blogging platform. Since just about any possible view of data you would want would be sorted by date, I was thinking about this structure:
One directory per content node:
Subdirectories of each node including
As well as simple text files in the node directory for pre- and post-rendered content and the like.
This would allow a simple PHP glob() call (and probably a reversal of the result array) to query on just about anything within the content structure:
Would return paths including all articles tagged «funny».
Here’s the code we use for Lilina:
* @package Lilina * @version 1.0 * @license http://opensource.org/licenses/gpl-license.php GNU Public License */ /** * Handler for persistent data files * * @package Lilina */ class DataHandler < /** * Directory to store data. * * @since 1.0 * * @var string */ protected $directory; /** * Constructor, duh. * * @since 1.0 * @uses $directory Holds the data directory, which the constructor sets. * * @param string $directory */ public function __construct($directory = null) < if ($directory === null) $directory = get_data_dir(); if (substr($directory, -1) != '/') $directory .= '/'; $this->directory = (string) $directory; > /** * Prepares filename and content for saving * * @since 1.0 * @uses $directory * @uses put() * * @param string $filename Filename to save to * @param string $content Content to save to cache */ public function save($filename, $content) < $file = $this->directory . $filename; if(!$this->put($file, $content)) < trigger_error(get_class($this) . " error: Couldn't write to $file", E_USER_WARNING); return false; >return true; > /** * Saves data to file * * @since 1.0 * @uses $directory * * @param string $file Filename to save to * @param string $data Data to save into $file */ protected function put($file, $data, $mode = false) < if(file_exists($file) && file_get_contents($file) === $data) < touch($file); return true; >if(!$fp = @fopen($file, 'wb')) < return false; >fwrite($fp, $data); fclose($fp); $this->chmod($file, $mode); return true; > /** * Change the file permissions * * @since 1.0 * * @param string $file Absolute path to file * @param integer $mode Octal mode */ protected function chmod($file, $mode = false) < if(!$mode) $mode = 0644; return @chmod($file, $mode); >/** * Returns the content of the cached file if it is still valid * * @since 1.0 * @uses $directory * @uses check() Check if cache file is still valid * * @param string $id Unique ID for content type, used to distinguish between different caches * @return null|string Content of the cached file if valid, otherwise null */ public function load($filename) < return $this->get($this->directory . $filename); > /** * Returns the content of the file * * @since 1.0 * @uses $directory * @uses check() Check if file is valid * * @param string $id Filename to load data from * @return bool|string Content of the file if valid, otherwise null */ protected function get($filename) < if(!$this->check($filename)) return null; return file_get_contents($filename); > /** * Check a file for validity * * Basically just a fancy alias for file_exists(), made primarily to be * overriden. * * @since 1.0 * @uses $directory * * @param string $id Unique ID for content type, used to distinguish between different caches * @return bool False if the cache doesn't exist or is invalid, otherwise true */ protected function check($filename) < return file_exists($filename); >/** * Delete a file * * @param string $filename Unique ID */ public function delete($filename) < return unlink($this->directory . $filename); > > ?> It stores each entry as a separate file, which we found is efficient enough for use (no unneeded data is loaded and it’s faster to save).
IMHO, you have two. er, three options if you want to avoid homebrewing something:
If you’re familiar with PDO, you can install a PDO driver that supports SQLite. Never used it, but I have used PDO a ton with MySQL. I’m going to give this a shot on a current project.
Done this many times for relatively small amounts of data. XMLReader is a lightweight, read-forward, cursor-style class. SimpleXML makes it simple to read an XML document into an object that you can access just like any other class instance.
Good option for smallish amounts of data, just read/write file and json_decode/json_encode. Not sure if PHP offers a structure to navigate a JSON tree without loading it all in memory though.