0

I have a text(.txt) file as shown in below.

0   1   1       1   30  2014-08-30 14:38:40
1   1   1       1   30  2014-08-30 14:41:55
2   1   1       1   30  2014-08-30 14:47:54
3   1   1       1   30  2014-08-30 14:53:04
4   1   5       1   30  2014-10-02 14:29:42

I want to insert some of the data from that file into database. And here is the structure of staff_detail table:

id(int32) primary key
no(int32)
staff_id(int32)
date_time(datetime)

So, I read the data from the .txt file and try to insert into database as shown in below:

<?php
    include('dbconfig.php');
    $handle = fopen($_FILES['record']['tmp_name'], "r");
    while(!feof($handle)){
        $row = fgets($handle);
        if( strlen(trim($row)) > 0 ) {
            $data = explode("\t",$row);
            $no = $data[0];
            $staff_id = $data[2];
            $date = $data[6];
            echo "No: $no | ID: $staff_id | Date: $date <br/>";
            $query = "INSERT INTO staff_detail(`no`,`staff_id`,`date_time`)VALUES($no,$staff_id,$date)";
            $rsl = mysql_query($query) or die(mysql_error());
        }
    }
?>

When I run this file, I get error like this: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1

But, I can see all data in echo and just only can't insert into database.

When I check the text file data in Firebug. I see the data as shown below:

�0� �1� �1� �   �1� �3�0�   �2�0�1�4�-�0�8�-�3�0� �1�4�:�3�8�:�4�0�
�
�1� �1� �1� �   �1� �3�0�   �2�0�1�4�-�0�8�-�3�0� �1�4�:�4�1�:�5�5�
�
�2� �1� �1� �   �1� �3�0�   �2�0�1�4�-�0�8�-�3�0� �1�4�:�4�7�:�5�4�
�
�3� �1� �1� �   �1� �3�0�   �2�0�1�4�-�0�8�-�3�0� �1�4�:�5�3�:�0�4�
�
�4� �1� �5� �   �1� �3�0�   �2�0�1�4�-�1�0�-�0�2� �1�4�:�2�9�:�4�2�
�
�5� �1� �5� �   �1� �3�0�   �2�0�1�4�-�1�0�-�0�2� �1�4�:�3�7�:�4�4�
�
�6� �1� �7� �   �1� �3�0�   �2�0�1�4�-�1�0�-�0�2� �1�4�:�4�6�:�2�2�
�
�7� �1� �8� �   �1� �3�0�   �2�0�1�4�-�1�0�-�0�2� �1�4�:�4�7�:�5�6�

So, I try to check $no with var_dump($no) when $no value is 0.(I mean is test with the first line of txt file).

And I get the result like this: string(3) "0". I think it was wrong because it has just only one string as "0". But it shows string(3).

So, I guess it may be white space and so I try with str_replace and change $no and $staff_id type to int and try to insert into table again.

But it shows the same error and I have stuck with this problem. And now, I have no idea to solve it. I'm very appreciate for any answer and suggestion.

Cloud
  • 1,004
  • 1
  • 18
  • 47
  • Every time you use [the `mysql_`](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) database extension in _meow_ code **[a Kitten is strangled somewhere in the world](http://2.bp.blogspot.com/-zCT6jizimfI/UjJ5UTb_BeI/AAAAAAAACgg/AS6XCd6aNdg/s1600/luna_getting_strangled.jpg)** it is deprecated and has been for years and is gone for ever in PHP7. If you are just learning PHP, spend your energies learning the `PDO` or `mysqli` database extensions. [Start here](http://php.net/manual/en/book.pdo.php) – RiggsFolly Sep 09 '16 at 10:47
  • First thing to try is to wrap all text values in the VALUE() list in single quotes like `VALUES('$no','$staff_id','$date')` wrapping integer items is not a problem but all text items MUST be in quotes – RiggsFolly Sep 09 '16 at 10:49
  • 1
    But your main problem is that your data is UTF-8 encoded and you have not told your table to expect UTF-8 – RiggsFolly Sep 09 '16 at 10:50

0 Answers0