• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 445
  • Last Modified:

Read values from a file and post to database

Hi,

I have multiple text files in a folder.

I want to read 5 text files at a time and copy their content (1 line at a time to a mysql database). If there are duplicate items in the various files, the count for the word should increase based on the number of instances that are found

Example of: ABC.txt with these values:

World Wide Web
Bill Gates
Internet Connect
Beach
Information Exchange

Example of another file: DEF.txt with these values:

Information Exchange
Water Park
Market Operator
Genius
Beach

My database should look like:

item_name, count, last_updated_datetime
World Wide Web, 1, 2012-08-15 03:00:00
Bill Gates, 1, 2012-08-15 04:00:00
Internet Connect, 1, 2012-08-15 03:20:00
Beach, 2, 2012-08-15 03:00:00
Information Exchange, 2, 2012-08-15 03:30:00
Water Park, 1, 2012-08-15 03:00:00
Market Operator, 1, 2012-08-15 03:00:00
Genius, 1, 2012-08-15 03:00:00

Once the file has been parsed and the values have been inserted to the database, the file should then move to a different folder. This folder should be labeled with todays date "20120815"

Can you help me get started by giving me an example script on how to take care of this?
0
nainil
Asked:
nainil
2 Solutions
 
lwadwellCommented:
OK.
To open and read a file line by line, use fopen() and fgets() in PHP.  Some example at this manual page:
http://www.php.net/manual/en/function.fgets.php

To move a file from the original directory to another, use rename()
http://www.php.net/manual/en/function.rename.php

To make a new directory, use mkdir()
http://php.net/manual/en/function.mkdir.php

MySQL database work ... you could use the mysqli extension in PHP ... and example script of various interactions is below:
<?php 
ini_set('display_errors', TRUE);
error_reporting(E_ALL);

// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQLi
// MAN PAGE: http://www.php.net/manual/en/mysqli.overview.php
// MAN PAGE: http://www.php.net/manual/en/mysqli.setup.php
// MAN PAGE: http://www.php.net/manual/en/class.mysqli.php
// MAN PAGE: http://www.php.net/manual/en/mysqli.connect-errno.php
// MAN PAGE: http://www.php.net/manual/en/mysqli.connect-error.php
// MAN PAGE: http://www.php.net/manual/en/mysqli.real-escape-string.php
// MAN PAGE: http://www.php.net/manual/en/mysqli.query.php
// MAN PAGE: http://www.php.net/manual/en/mysqli.errno.php
// MAN PAGE: http://www.php.net/manual/en/mysqli.error.php
// MAN PAGE: http://www.php.net/manual/en/mysqli-result.fetch-assoc.php
// MAN PAGE: http://www.php.net/manual/en/mysqli-result.fetch-array.php
// MAN PAGE: http://www.php.net/manual/en/mysqli-result.free.php
// MAN PAGE: http://www.php.net/manual/en/mysqli.affected-rows.php
// MAN PAGE: http://www.php.net/manual/en/mysqli.insert-id.php
// MAN PAGE: http://www.php.net/manual/en/mysqli.close.php

// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "XXXX"; // This will need to be changed to your own value
$db_user = "XXXX"; // This will need to be changed to your own value
$db_word = "XXXX"; // This will need to be changed to your own value


// OPEN A CONNECTION TO THE DATA BASE SERVER
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);
if ( $mysqli->connect_errno ) {
	handle_error ("Connect", $mysqli->connect_errno, $mysqli->connect_error);
}
// IF THE SCRIPT GETS THIS FAR IT CAN DO QUERIES


// ESCAPE ALL DATA FIELDS BEFORE USE IN MYSQLi QUERIES
$safe_username = $mysqli->real_escape_string($_POST["username"]);


// CREATE AND SEND A SELECT QUERY AND TEST THE RESULTS
$sql = "SELECT id FROM my_table WHERE username='$safe_username'";
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if ( !$res ) {
    $err = handle_error( "Query: " . $sql, $mysqli->errno, $mysqli->error );
    // HANDLE THE PROGRAMMATIC CONSEQUENCES HERE
    die($err);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQLi FUNCTIONS


// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num = $res->num_rows;
$fmt = number_format($num);
if ( !$num ) {
    echo "<br/>Query found NO DATA: ";
    echo "<br/> $sql <br/><br/>";
} else {
    echo "<br/>Query found $fmt row(s) of data: ";
    echo "<br/> $sql <br/><br/><br/>";
}


// ITERATE OVER THE RESULTS SET TO SHOW WHAT WE FOUND
while ( $row = $res->fetch_assoc() ) {
    // ROW BY ROW PROCESSING IS DONE HERE
    var_dump($row);
    echo "<br />";
}


// ANOTHER WAY OF DETERMINING HOW MANY ROWS WE HAVE IN A TABLE
$sql = "SELECT COUNT(*) FROM my_table";
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, REPORT AND HANDLE THE ERROR
if ( !$res ) {
    $err = handle_error( "Query: " . $sql, $mysqli->errno, $mysqli->error );
    // HANDLE THE FAILURE HERE
    die($err);
}

// GET THE RESULTS SET ROW IN AN ARRAY WITH A NUMERIC INDEX - POSITION ZERO IS THE COUNT
$row = $res->fetch_array(MYSQLI_NUM);
$num = $row[0];
$fmt = number_format($num);
echo "<br/>There are $fmt rows in the table<br/><br/>";

// FREE THE MEMORY USED BY THE QUERY IF NO LONGER NEEDED
$res->free();


// MAKING AN INSERT QUERY AND TESTING THE RESULTS
$sql = "INSERT INTO my_table (username) VALUES ('$safe_username')";
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if ( !$res ) {
    $err = handle_error( "Query: " . $sql, $mysqli->errno, $mysqli->error );
    // HANDLE THE FAILURE HERE
    die($err);
} else {
    // DETERMINE NUMBERS ALTERED IN DATABASE
    $num = $mysqli->affected_rows;
    $fmt = number_format($num);
    echo "<br/>There were $fmt rows inserted/updated/deleted in the table<br/><br/>";
}


// GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED - PER THE DB CONNECTION
$id  = $mysqli->insert_id;;
echo "<br/>You just inserted a record with auto_increment ID = $id<br/><br/>";


// CLOSE THE DATABASE CONNECTION
$mysqli->close();


function handle_error ( $action, $errno, $error ) {
    $err = 'Error message: (' . $errno . ') ' . $error;
    echo "<br/>An error occured during: ", $action;
    echo "<br/> $err <br/>";
    return $err;
}
?>

Open in new window

0
 
Julian HansenCommented:
I have not tested the code but this should mostly work post back if you have issues
NB: I have ignored db connection as lwadwell seems to have comprehensively covered it above.
<?php

$buff = get_lines(5, 'source/path', 'archive/path');

// Assumes database is connected
//Connect as described in post above or however you need to
// Assume primary key on item_name

foreach($buff as $k=>$c) {
  $query = "INSERT words (item_name, count, last_updated_datetime) 
    VALUES ('$k', $c, NOW()) 
    ON DUPLICATE KEY UPDATE count=count+$c";
  execute_query($query); // mysqli_query($query) or whatever mechanism you are using
}

function get_lines($numfiles, $dir, $archive)
{
  $lines = array();
  
  if (is_dir($dir)) {
    if ($dh = opendir($dir)) {
      while (($file = readdir($dh)) !== false) {
        if ($file == '.' && $file == '..) continue;
        $temp = explode("\n", file_get_contents($dir . SEP . $file));
        foreach($temp as $t) {
          if (empty($lines[$t])) $lines[$t] = 1;
          else $lines[$t]++;
        }
        // assuming same drive
        rename($dir . SEP . $file, $archive . $SEP . $file);
      }
      closedir($dh);
    }
  }
  return $lines;
}
?>

Open in new window

0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now