Assigning lines of text file to variables

[jcoones]

I have a text file that consists of 200 lines that are delimited with the # sign. Each five lines in the text file make up one record for a mysqli database. That means, of course, that the 200 lines in the text file make up 40 records.  I am trying to find a way to read 5 lines of text at a time, (1 record), assign them to variables and INSERT INTO the database, until all 40 records in the text file have been read and inserted.

 

I can get it to display each record on the screen using this code:

$handle = fopen("testfile.txt", "rb");
   $delimiter = "#";
   while (!feof($handle) ) {
          for ($x = 1; $x < 5; $x++) {
            $line = fgets($handle);
            $data = explode($delimiter, $line);

            foreach($data as $v) {
	   echo nl2br($v) . "\n\n";
	 }
        }
    }
    fclose($handle);

but I can’t figure out how to assign them to variables to insert into the MySQLi database.

 

Any help would be greatly appreciated.

 

Thanks in advance.

Edited February 28, 2015 by jcoones

[Ch0cu3r]

Rather than individual variables. It would be better to use arrays. Without seeing example data from your text file this is untested code

$lines = file('testfile.txt', FILE_IGNORE_NEW_LINES);

// split lines into chunks of 5
$LinesOf5 = array_chunk($lines, 5);

$records = array();

// loop over the chunks of lines
foreach($LinesOf5 as $lines)
{
   // implode the 5 lines into 1 line
   $data = implode('', $lines);

   // explode the data delimeted by #
   $record = explode('#', $data);

   // add record to records array
   $records[] = $record;
}

// should output 40 records?
echo '<pre>'. print_r($records, true) . '</pre>';

[jcoones]

Easy to see that I'm new at php.    Thank you very much for your reply.

 

I tried your code and and while it does get and display the data pretty much similar to the way my code did, I have two questions.

 

This is the display output that I get with your code using only the first two records in the text file for testing purposes.

Array
(
    [0] => Array
        (
            [0] => Also known as the English Coonhound or the Redtick Coonhound, this breed traces back to the earliest English Foxhounds brought to the southern United States in the mid-seventeenth century and crossed with local types adapted to the harsher climate and terrain. Today, these are particularly agile, fast and enduring hounds. They have an excellent voice, which they put to good use. The breed is kown for its kind temperament and can fit into family life if given a lot of exercise, but they are happiest when hunting.
            [1] => American English Coonhound
            [2] => medium-large
            [3] => 22-25 in.
            [4] => 40-65 lbs.
            [5] => 
        )

    [1] => Array
        (
            [0] => The American Bulldog is a powerful and athletic breed that benefits from mental as well as physical challenges. The breed is relatively quiet when at rest and in the home, but requires a fairly high amount of exercise and plenty of diversions. These are powerful dogs that can make excellent companions as long as they are properly socialized, supervised and exercised. They have a strong pack instinct and will protect their families and property with vigor. There are two types of American Bulldog, the Standard and the Bully. The Bully is larger, heavier, and has a shorter muzzle.
            [1] => American Bulldog
            [2] => large
            [3] => 21-27 in.
            [4] => 60-125 lbs.
            [5] => 
        )

)

So my questions are:

 

1) How do I get rid of the blank array element at the bottom of each record?

 

2) Without using variables, how do I insert the records into the MySQLi database?

 

Thanks again.

Edited February 28, 2015 by jcoones

[Ch0cu3r]

1) Change the foreach $linesOf5 loop to this.

foreach($LinesOf5 as $lines)
{
    // remove the # from the start/end of each line
    $record = array_map(function($v) {
                            return trim($v, '#');
                        }, $lines);

    // add record to records array
    $records[] = $record;
}

2) You can do something like this

// connect to mysql
$mysqli = new mysqli('localhost', 'username', 'password', 'database');

// use a prepared query to insert data into database
/*
   NOTE: You will need to change:
    - tableName to the name of your table
    - ...listTableColumnsHere... with a list of your column names eg: description, breed, size, length, weight
*/
$stmt = $mysqli->prepare('INSERT INTO tableName (...listTableColumnsHere...) VALUES(?, ?, ? ,?, ?)');
if(!$stmt)
{
    trigger_error('MySQL Error - Unable to prepare query: ' . mysqli_error($mysqli));
}

$paramTypes = 'sssss';

// loop over the records and insert data into table
foreach ($records as $record)
{
    // bind values to prepared querie
    $bind_params = array();
    $bind_params[] = &$paramTypes;
    foreach ($record as $key => $value) {
        $bind_params[] = &$record[$key];
    }
    call_user_func_array(array($stmt, 'bind_param'), $bind_params);

    // execute the prepared query
    if(!$stmt->execute())
    {
        trigger_error('MySQL Error- Unable to execute prepared query: ' . mysqli_error($mysqli));
    }
}

As I do not know your table structure you will need to the modify the query. See the code comments between /* and */

Edited March 1, 2015 by Ch0cu3r