PHP mysql infile not working

Question

Below is my program that should upload a text file into my database. the File I am trying to upload to the database is about 308MB. When I try to run this program it seems to run fully through but when I look into the database I don't see anything added. so I added in the $db-> stuff to see what errors it will give and to make sure it connect to the database.

<?php

set_time_limit(0); // unlimited max execution time


$username = "root";
$auth = 'i-have-removed-it';
$db = $db = new mysqli("localhost", $username, $auth, 'testdb');
if(mysqli_connect_errno())
{
    die("Connection could not be established");
}



print_r([
    "db->connect_errno1"        => $db->connect_errno,
    "db->connect_error1"        => $db->connect_error,
    "db->errno1"                => $db->errno,
    "db->error1"                => $db->error,
    "db->sqlstate1"            => $db->sqlstate,
    "db->info1"                => $db->info,
    "db->get_warnings1"        => $db->get_warnings(),
]);
$file = "./datafile_worldmap.txt";

$query = "load data infile '$file'
into table `World_Map`
character set ascii
fields terminated by '|'
lines terminated by '\r\n'
ignore 1 lines

(`GeoID`, `X`, `Y`, `Wood`, `Clay`, `Iron`, `Stone`, `Food`, `TerrainSpecificTypeID`, `TerrainCombatTypeID`, `RegionID`)
";

$result = $db->query($query);
print_r([
    "db->connect_errno2"        => $db->connect_errno,
    "db->connect_error2"        => $db->connect_error,
    "db->errno2"                => $db->errno,
    "db->error2"                => $db->error,
    "db->sqlstate2"            => $db->sqlstate,
    "db->info2"                => $db->info,
    "db->get_warnings2"        => $db->get_warnings(),
]);

$total_num_rows = $result->num_rows;

echo "The Results Are : <br>";

?>

Output

Array
(
    [db->connect_errno1] => 0
    [db->connect_error1] =>
    [db->errno1] => 0
    [db->error1] =>
    [db->sqlstate1] => 00000
    [db->info1] =>
    [db->get_warnings1] =>
)
Array
(
    [db->connect_errno2] => 0
    [db->connect_error2] =>
    [db->errno2] => 1045
    [db->error2] => Access denied for user 'User'@'%%%.%%%.%%%.%%%' (using password: YES)
    [db->sqlstate2] => 28000
    [db->info2] =>
    [db->get_warnings2] =>
)

news error

<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">
<html><head>
<title>500 Internal Server Error</title>
</head><body>
<h1>Internal Server Error</h1>
<p>The server encountered an internal error or
misconfiguration and was unable to complete
your request.</p>
<p>Please contact the server administrator,
 <<email>> and inform them of the time the error occurred,
and anything you might have done that may have
caused the error.</p>
<p>More information about this error may be available
in the server error log.</p>
<p>Additionally, a 500 Internal Server Error
error was encountered while trying to use an ErrorDocument to handle the request.</p>
</body></html>

put this as an answer as this is just what i need. THANK YOU VERY MUCH!!! — kodabear
– kodabear, Commented Jan 12, 2017 at 4:18

JYoThI · Accepted Answer · 2017-01-12 04:20:02Z

1

Add LOCAL to your statement

use this LOAD DATA LOCAL INFILE instead of load data infile

answered Jan 12, 2017 at 4:20

JYoThI

12.1k1 gold badge13 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

kodabear Over a year ago

hmm well it seems to fill up the database with stuff but when i look into the table i dont see anything in it but its still taking up space

kodabear Over a year ago

it should be. I guess I will run it with the sample file to see what happens

kodabear Over a year ago

well, it works just fine with the sample file. might be size/memory issue

Keyur Mistry · Accepted Answer · 2017-01-12 03:56:45Z

0

You are required to increase file upload size. Default file upload size is 2MB Following are the variables which need to change:

memory_limit = 500M
upload_max_filesize = 200M // 200MB file size
post_max_size = 500M  // increase your post limit to 500 MB

Using this you can able to upload file with the maximum size of 200MB

answered Jan 12, 2017 at 3:56

Keyur Mistry

9266 silver badges19 bronze badges

Collectives™ on Stack Overflow

PHP mysql infile not working

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related