i have a text file as abc.txt which looks like

hi hello john
how are you jelly
fine thank u

now i need a php code to select this file from html page and read this text file line by line and store each line as a seperate row in mysql database.

my html code is

<html>
<body>
<table  align="center">
<form action="upload_page.php" method="post" enctype="multipart/form-data">
<label for="file">Filename:</label>
<input type="file" name="file" id="file"><br>
<input type="submit" name="submit" value="Submit">
</form>

</table>
</body>
</html>

i need help to improve upload_page code

<?php

$a=$_POST['file'];
$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");
mysql_select_db($db) or die ("Unable to select database!"); 

$lines = file('$a');
foreach($lines as $num => $line)

    mysql_query('insert into file_tbl (file_data) values ("' . mysql_real_escape_string($line) . '");');

?>

any help is appreciated

Member Avatar for diafol

If you're going to use a loop as opposed to using the LOAD FILE syntax, then build up the sql query and send one query, not many, e.g.

$sql = array();
foreach($lines as $num => $line) $sql[] = "('" . mysql_real_escape_string($line) . "')";

$sqlString = implode(",", $sql);
$sql = "INSERT INTO file_tbl (file_data) VALUES $sqlString";
//....

Not tested

Try the following example:

Create a folder in your root, Here we named it as upload.In this folder we are uploading text file.

In you form html

<html>
<body>
<table  align="center">
<form action="upload_page.php" method="post" enctype="multipart/form-data">
<label for="file">Filename:</label>
<input type="file" name="file" id="file"><br>
<input type="submit" name="submit" value="Submit">
</form>

</table>
</body>
</html>

And your upload_page.php

<?php
//Your Data base connections
$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");
mysql_select_db($db) or die ("Unable to select database!"); 

//upload file  to upload folder
$uploaddir = 'upload/';
$uploadfile = $uploaddir . basename($_FILES['file']['name']);

//if upload is success
if (move_uploaded_file($_FILES['file']['tmp_name'], $uploadfile)) {
    //read file
    $fp = fopen($uploadfile, 'rb'); 
    while ( ($line = fgets($fp)) !== false) {
      $sql = "INSERT INTO file_tbl (file_data) VALUES '".mysql_real_escape_string($line)."'";
      mysql_query( $sql);
    }

} else {
    echo "Error in file upload";
}
?>
Member Avatar for diafol
$array = file($filename, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);

Will avoid having to write a loop.

$lines = file($fn, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
$cleanLines = array_map($lines, "mysql_real_escape_string");
$sqlString = "('" . implode("'),('"),$cleanLines) . "')";
$sql = "INSERT INTO file_tbl (file_data) VALUES sqlString";
mysql_query( $sql);

So, no loops, 1 sql query. You perhaps should check to see if the count of $lines is greater than 0 though.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.