asked on

Upload CSV on server to a mysql table

I would like to create a button on a php page that would table the records from the file on the server and load it into a mysql table.

This is a php page. It has records in the form of a table on the page. I will have the button id, the same as the file I would like to load into the mysql table. The files already reside on the server in a folder called uploaded.

Here is what I can do so far.

1. Capture the name of the file on the table with the id using jquery and ajax to push the file name to a php file that is to write the rows in the the csv file to the table.

A simple example with a field or two would be very helpful. All my fields are text as there is some characters that make the input from the csv file a little unpredictable.

Please note the first row will have the field headings in the csv, so would need to start at row 2 and loop through the rest of the rows.

leakim971

1 -
client side :
<!DOCTYPE html>
<html>
<head>
<script>
jQuery(function($) {
$("#button_ID").click(function() {
$.post("/path/to/code.php", { file_id: $("#file_ID").val() }, function() {
alert("done");
}).fail(function() { alert("something bad happened"); });
});
</script>
</head>
<body>
<input type="text" id="file_ID">
<button id="button_ID">CLICK</button>

Server side :
http://php.net/manual/en/mysqli.query.php#example-1894
https://dev.mysql.com/doc/refman/8.0/en/load-data.html

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

/* Create table doesn't return a resultset */
if ($mysqli->query("CREATE TEMPORARY TABLE myCity LIKE City") === TRUE) {
    printf("Table myCity successfully created.\n");
}

/* Select queries return a resultset */
if ($result = $mysqli->query("SELECT filename FROM files WHERE id=" . $_POST["file_id"])) {
    printf("Select returned %d rows.\n", $result->num_rows);

$row = $result->fetch_assoc();
$mysqli->query("LOAD DATA INFILE " . $row["filename"] . " INTO TABLE your_table fields terminated by ',' ignore 1 lines");

    /* free result set */
    $result->close();
}

Open in new window

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.