Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

What I'm trying to do is insert each line from a text file into a new row of a mysql database. What am I doing wrong?

I have a text file that looks something like the following. 

11111,customer1
11112,customer2
11113,customer3
11114,customer4

My MySQL DB has the fields id, number, customer

My php code which isn't working looks like the following. 

<html>
<head>
<title>Add File To DB</title>
</head>

<body>
<form action="list.php" method="post">
<input type="submit" value="Submit File" />
<table>

<?php
    $f = fopen("textfile.txt", "r") or exit("Unable to open file!");

    // Read line by line until end of file
    while (!feof($f)) { 

    // Make an array using comma as delimiter
       $arrM = explode(',',fgets($f)); 
    // Write links (get the data in the array)
       echo '<tr><td name="number">' . $arrM[0] . '</td><td name="customer">' . $arrM[1] . '</td></tr>'; 

    }

    fclose($f);
if (isset($_POST['submit'])) {
include 'connection.php';
$sql="INSERT INTO list (number, customer) VALUES ('$_POST[number]','$_POST[customer]')";
if (!mysqli_query($con,$sql))
  {
  die('Error: ' . mysqli_error());
  }

mysqli_close($con);
}
?>
</table>
<input type="submit" value="Submit File" />
</form>
</body>
</html>
share|improve this question

4 Answers

up vote 4 down vote

Your values are in the array $arrM not in $_POST. Please try this:

$sql="INSERT INTO list (number, customer) VALUES ('$arrM[0]','$arrM[1]')";

Also you want to make sure you run this $sql in a loop.

share|improve this answer
$arrM won't be visible at the $sql= line – bansi Aug 5 at 4:22
up vote 1 down vote

You have multiple problems in your code.If you want to just insert from the text file you can try the following

<html>
<head>
<title>Add File To DB</title>
</head>

<body>
<form action="list.php" method="post">
<input type="submit" value="Submit File" />
<table>

<?php
    $f = fopen("textfile.txt", "r") or exit("Unable to open file!");
    $arr_to_insert = array();
    // Read line by line until end of file
    while (!feof($f)) { 

    // Make an array using comma as delimiter
       $arrM = explode(',',fgets($f)); 
    // Write links (get the data in the array)
       echo '<tr><td name="number">' . $arrM[0] . '</td><td name="customer">' . $arrM[1] . '</td></tr>';
       //strore text file row to an array 
       $arr_to_insert[] = $arrM;
    }

    fclose($f);
if (isset($_POST['submit'])) {
include 'connection.php';
foreach($arr_to_insert as $ai){
    $sql="INSERT INTO list (number, customer) VALUES ('{$ai[0]}','{$ai[1]}')";
    if (!mysqli_query($con,$sql))
      {
      die('Error: ' . mysqli_error());
      }

}
mysqli_close($con);
}
?>
</table>
</form>
</body>
</html>

The original version of your code with the problems commented.

<html>
<head>
<title>Add File To DB</title>
</head>

<body>
<form action="list.php" method="post">
<input type="submit" value="Submit File" />
<table>

<?php
    $f = fopen("textfile.txt", "r") or exit("Unable to open file!");
    // you need to strore text file row to an array to later insert to database.
    // Read line by line until end of file
    while (!feof($f)) { 

    // Make an array using comma as delimiter
       $arrM = explode(',',fgets($f)); //$arrM is private inside while loop.
    // Write links (get the data in the array)
       echo '<tr><td name="number">' . $arrM[0] . '</td><td name="customer">' . $arrM[1] . '</td></tr>'; 

    }

    fclose($f);
if (isset($_POST['submit'])) {
include 'connection.php';
//you are trying to insert $_POST[number] and $_POST[customer] which are non-existent
//also you need to loop through the rows in your text file and store each row.
$sql="INSERT INTO list (number, customer) VALUES ('$_POST[number]','$_POST[customer]')";
if (!mysqli_query($con,$sql))
  {
  die('Error: ' . mysqli_error());
  }

mysqli_close($con);
}
?>
</table>
<!-- duplicate submit button -->
<input type="submit" value="Submit File" />
</form>
</body>
</html>

PS: Please note I am just pointing out the errors in your code and fixing as it is. I am not trying to optimize it. It looks like you are trying to learn PHP rather than get better solution.

share|improve this answer
Correct just learning. With the <input type="submit" value="Submit File" /> If I wanted one on the bottom and one at the top could I do that or would the to buttons conflict with each other? – Ben P. Dorsi-Todaro Aug 5 at 5:23
You can have more than 1 submit. It won't conflict and both will work also. – bansi Aug 5 at 7:56
up vote 0 down vote

There are multiple problems with your script.

  1. First, you are using $_POST['submit'] but haven't used name='submit' in your submit button.
  2. As @vinod said your values are in the array $arrM not in$_POST`.

Now you need to insert your data in loop. Make sure you included your connection only once and close the connection after all database operation is complete.

<html>
 <head>
  <title>Add File To DB</title>
 </head>

 <body>
  <form action="list.php" method="post">
  <input type="submit" name='submit' value="Submit File" /> <!-- Provide name `submit` to your button so that you can access $_POST['submit'] -->
  <table>

  <?php
    $f = fopen("textfile.txt", "r") or exit("Unable to open file!");

    //include your connection around here so it is included only once
    include "connection.php";

    // Read line by line until end of file
    while (!feof($f)) { 

    // Make an array using comma as delimiter
       $arrM = explode(',',fgets($f)); 
    // Write links (get the data in the array)
       echo '<tr><td name="number">' . $arrM[0] . '</td><td name="customer">' . $arrM[1] . '</td></tr>'; 
       if (isset($_POST['submit'])) {               
            $sql = "INSERT INTO list (number, customer) VALUES ('$arrM[0]','$arrM[0]')"; //here should be $arrM
            if (!mysqli_query($con,$sql)) {
              die('Error: ' . mysqli_error());
            }               
        }
    }

    fclose($f);
    mysqli_close($con); //close your database connection here
  ?>
 </table>
 <!--<input type="submit" value="Submit File" />-->
 </form>
 </body>
</html>
share|improve this answer
up vote 0 down vote 
If you want to insert data of the text file into the database then you can do this.
<?php   
include('dbcon.php');
$file_content=explode("\n",file_get_contents("demo.txt"));//write your text file name instead of demo.txt   
for($i=0;$i<count($file_content)-1;$i++){
    $exploded_content=explode(",",$file_content[$i]);
    $q=mysqli_query($con,"insert into demo (id,name) values('$exploded_content[0]','$exploded_content[1]')");
         //put your table name instead of demo it has two columns id and name
}

?>
Below is the dbcon.php file
// Create connection
$con=mysqli_connect("localhost","user","pass","dbname");
//put your host user pass database name above i put it dummy
// Check connection
if (mysqli_connect_errno($con))
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.