fopen php mysql import - empty row in sql

Question

I'm using the code specified below to import a txt file into mysql via php and fopen. It imports all the rows of data from my txt file as intended, however no matter what the txt file contains my script always adds one extra unintended row with a blank field for column data 2. I checked the txt file and there is no spaces or whitespace at the very end.

mysql_connect("$dbhost", "$dbuser", "$dbpasswd") or die(mysql_error());
mysql_select_db("$dbname") or die(mysql_error());

$f = fopen("import.txt", "r");
while(!feof($f)) 
{
    $data = explode(" ", fgets($f));

    $data1 = 'foobar';
    $inputkey = $data[0];

    mysql_query("INSERT INTO `reviewdata` (data1, data2)
        VALUES ('$data1', '$inputkey')") or die(mysql_error());
}
fclose($f);

Can you share example output, to get an idea about what you mean? Also: make sure you [sanitize your input](http://stackoverflow.com/questions/129677/whats-the-best-method-for-sanitizing-user-input-with-php). — Kevin Van Ryckegem, Nov 14 '15 at 22:16
Sure. So if import.txt contains 1 2 3 4, then in the table there would be foobar | 1 - foobar | 2 - foobar | 3 - foobar | 4 - foobar | - foobar (this last row shouldnt exist) — michelle, Nov 14 '15 at 22:18
Can you try this instead: while(($data = fgets($f)) !== false) { $data = explode(" ", $data); instead of your while statement? (also remove your $data = explode.. statement) — Kevin Van Ryckegem, Nov 14 '15 at 22:22

score 1 · Accepted Answer · edited May 23 '17 at 12:15

1

Try the following code. Also have a look at a similar question. That question explains why using feof is wrong.

mysql_connect("$dbhost", "$dbuser", "$dbpasswd") or die(mysql_error());
mysql_select_db("$dbname") or die(mysql_error());

$f = fopen("import.txt", "r");
while(($data = fgets($f)) !== false) 
{
    $data = explode(" ", $data);

    $data1 = 'foobar';
    $inputkey = trim($data[0]);

    mysql_query("INSERT INTO `reviewdata` (data1, data2)
        VALUES ('$data1', '$inputkey')") or die(mysql_error());
}

fclose($f);

edited May 23 '17 at 12:15

Community

1
1

answered Nov 14 '15 at 22:26

Kevin Van Ryckegem

1,915
3
28
55

there's no more blank row but in data2 ($inputkey) there's now a special character when i view the table with mysql workshop that says 000A with a box around it – michelle Nov 14 '15 at 22:30
@michelle Using trim() around the data will fix this. It's a line feed character. I updated the code. Also: I'm not sure why you use the explode() function here. Do you only want to save the first item (before spaces) in every line? – Kevin Van Ryckegem Nov 14 '15 at 22:38
Thank you so much. This problem was driving me INSANE. – michelle Nov 14 '15 at 22:41

fopen php mysql import - empty row in sql

1 Answers1