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.