I'm trying create a create a script to load some CSV data into a database hosted with MAMP.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Load CSV to database</title>
</head>
<body>
<?php
$elements = array(
"host" => "localhost:8888",
"username" => "inky",
"password" => "pinky",
"dbname" => "po"
);
I originally used this array to plug in the values, but hard-coded because it didn't work.
// $database = new mysqli($elements['host'], $elements['username'], $elements['password'], $elements['dbname']);
$database = new mysqli("localhost:8888", "inky", "pinky", "po");
if ($database->connect_errno) {
die("Connection failed (Error Code: " . $database->connect_errno . "): " . $database->connect_error);
}
echo "Load file<br>";
$csv_file = fopen('df2.csv', 'r');
if (!$csv_file) {
die("Failed to open file.");
}
$headers = fgetcsv($csv_file);
$columnMapping = [
'Name' => 'Name',
'Gender' => 'Gender',
'Value' => 'Value',
'Year' => 'Year',
'Measure' => 'Measure'
// ... Add mappings for all columns
];
while (($line = fgetcsv($csv_file)) !== FALSE) {
$columns = [];
$values = [];
foreach ($line as $key => $value) {
if (isset($columnMapping[$headers[$key]])) {
$columns[] = $columnMapping[$headers[$key]];
$values[] = "'" . $database->real_escape_string($value) . "'";
}
}
$query = sprintf(
"INSERT INTO baby_data (%s) VALUES (%s)",
implode(", ", $columns),
implode(", ", $values)
);
// Execute the query and handle any errors
if (!$database->query($query)) {
echo "Error executing query: " . $database->error . "<br>";
}
}
fclose($csv_file);
$database->close();
?>
</body>
</html>
I'm fairly certain the credentials are right as I've used them to logon to mysql through terminal, but get this error with the php script.
Fatal error: Uncaught mysqli_sql_exception: Access denied for user 'root'@'localhost' (using password: YES) in some "/sites/csv_converter/test_connection.php":17
Stack trace:
#0 /Users/davidelks/Dropbox/Personal/php/sites/csv_converter/test_connection.php(17): mysqli->__construct('127.0.0.1', 'root', Object(SensitiveParameterValue), 'houston')
#1 {main}
thrown in /Users/davidelks/Dropbox/Personal/php/sites/csv_converter/test_connection.php on line 17
To make things more confusing: http://localhost:8888/ returns: Forbidden You don't have permission to access this resource.
What steps do I need to do to get this running?
UPDATE: just so you have the absolutely original.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Load CSV to database</title>
</head>
<body>
<?php
$elements = array(
"host" => "127.0.0.1",
"username" => "root",
"password" => "root",
"dbname" => "houston"
);
$database = new mysqli("127.0.01", "root", "root", "houston");
// $database = new mysqli($elements['host'], $elements['username'], $elements['password'], $elements['dbname']);
if ($database->connect_errno) {
die("Connection failed (Error Code: " . $database->connect_errno . "): " . $database->connect_error);
}
echo "Load file<br>";
$csv_file = fopen('../../../baby_names/df2.csv', 'r');
if (!$csv_file) {
die("Failed to open file.");
}
$headers = fgetcsv($csv_file);
$columnMapping = [
'Name' => 'Name',
'Gender' => 'Gender',
'Value' => 'Value',
'Year' => 'Year',
'Measure' => 'Measure'
// ... Add mappings for all columns
];
while (($line = fgetcsv($csv_file)) !== FALSE) {
$columns = [];
$values = [];
foreach ($line as $key => $value) {
if (isset($columnMapping[$headers[$key]])) {
$columns[] = $columnMapping[$headers[$key]];
$values[] = "'" . $database->real_escape_string($value) . "'";
}
}
$query = sprintf(
"INSERT INTO baby_data (%s) VALUES (%s)",
implode(", ", $columns),
implode(", ", $values)
);
// Execute the query and handle any errors
if (!$database->query($query)) {
echo "Error executing query: " . $database->error . "<br>";
}
}
fclose($csv_file);
$database->close();
?>
</body>
</html>
[Running] php "/Users/davidelks/Dropbox/Personal/php/sites/csv_php_loader/baby_names_load_csv.php"
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Load CSV to database</title>
</head>
<body>
PHP Fatal error: Uncaught mysqli_sql_exception: Unknown database 'houston' in /Users/davidelks/Dropbox/Personal/php/sites/csv_php_loader/baby_names_load_csv.php:16
Stack trace:
#0 /Users/davidelks/Dropbox/Personal/php/sites/csv_php_loader/baby_names_load_csv.php(16): mysqli->__construct('127.0.01', 'root', Object(SensitiveParameterValue), 'houston')
#1 {main}
thrown in /Users/davidelks/Dropbox/Personal/php/sites/csv_php_loader/baby_names_load_csv.php on line 16
Fatal error: Uncaught mysqli_sql_exception: Unknown database 'houston' in /Users/davidelks/Dropbox/Personal/php/sites/csv_php_loader/baby_names_load_csv.php:16
Stack trace:
#0 /Users/davidelks/Dropbox/Personal/php/sites/csv_php_loader/baby_names_load_csv.php(16): mysqli->__construct('127.0.01', 'root', Object(SensitiveParameterValue), 'houston')
#1 {main}
thrown in /Users/davidelks/Dropbox/Personal/php/sites/csv_php_loader/baby_names_load_csv.php on line 16
[Done] exited with code=255 in 0.096 seconds
Usually something in the CSV file is not quite right for the database. While I reach for PHP to tweak the strings, I usually find that the following is actually easier.
Use
LOAD DATA LOCAL INFILE ...with a temp table as a target.Simple tweaking can be done with the use of @variables inside the
LOADstatement.More complex tweaking can be done with SQL statements as you copy the data into the 'real' table(s).
With luck, all the loading and tweaking can be done in SQL; no need for PHP (or other app) code. Much faster; much less typing; fewer chances to make a mistake. Furthermore, I can look at the temp table to see if the data looks like and do
SELECTsagainst it when figuring how to do the tweaking.