MySQL PHP load data infile

Question

I'm trying to make update a MySQL database using PHP with the data to be inserted coming from a text file. However, when I run a query from PHP the database does not update.

If I run the mysql client from the terminal, mysql -u root -p --local-infile videos and enter LOAD DATA LOCAL INFILE /home/user/movies.txt INTO TABLE films; it works fine.

Here is the code from my Index PHP file (hosted on Apache2).

Am I doing anything wrong? Like syntax, etc?

<?php

$username="root"; $password="12345"; $database="videos";

$con = mysqli_connect("localhost",$username,$password, $database);

mysqli_query("LOAD DATA LOCAL INFILE '/home/user/movies.txt' INTO TABLE films");

$result = mysqli_query($con, "SELECT title, yr, genre, plot, rating FROM films") or die("Unable to query.");

echo "<table border='1'>";
echo "<tr> <th>Title</th> <th>Year</th> <th>Genre</th> <th>Plot</th> <th>Rating</th> </tr>";

// keep getting the next row until no rows left
while($row = mysqli_fetch_array( $result )) {
  // print out contents of each row into a table
  echo "<tr> <td>";
  echo $row['title'];
  echo "</td><td>";
  echo $row['yr'];
  echo "</td><td>";
  echo $row['genre'];
  echo "</td><td>";
  echo $row['plot'];
  echo "</td><td>";
  echo $row['rating'];
  echo "</td></tr>";
}

echo "</table>";
?>

Thanks.

EDIT: Echoed the error message for the LOAD DATA LOCAL.., Errormessage: The used command is not allowed with this MySQL version

You should echo the actual errors to the browser or check your error logs. — Jay Blanchard
– Jay Blanchard, Commented Aug 3, 2015 at 18:00
Your first sentence seems to say mySql is the programming language and PHP is the database. That is of course not the case — Dirk Horsten
– Dirk Horsten, Commented Aug 3, 2015 at 19:03

Community · Accepted Answer · 2017-05-23 11:57:58Z

1

Found this: using mysql LOAD statment in PHP fails, but doing it via command line works

And I used:

$conn = mysqli_init();
mysqli_options($conn, MYSQLI_OPT_LOCAL_INFILE, true);
mysqli_real_connect($conn,server,user,code,database);

edited May 23, 2017 at 11:57

CommunityBot

11 silver badge

answered Aug 3, 2015 at 18:53

rharper

5751 gold badge7 silver badges15 bronze badges

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

Collectives™ on Stack Overflow

MySQL PHP load data infile

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related