How can I use a for loop to insert data from a csv table to a sql database?

Question

This is the sample csv file I'm using:

Column1,Column2,Column3
data1,data2,data3
data1,data2,data3
data1,data2,data3
data1,data2,data3

The purpose of my program is to insert this table into a php file via command line and insert the data into an SQL database. I'm using this project as a way of learning how to use MySql.

A csv file is taken and the data is then converted into an array or arrays. A database is then created and the data is supposed to be inserted into the table.

Instead of my data being inserted, I get only one row with null values.

<?php

/**
 * Created by PhpStorm.
 * User: 
 * Date: 6/16/2017
 * Time: 11:32 AM
 */

$servername = "localhost";
$username = "pop-user";
$password = "pop-pw";
$database = 'popdb';

parse_str(implode('&', array_slice($argv, 1)), $_GET);
$file = array_map('str_getcsv', file($argv[1]));

// connecting to MySQL
$link = mysqli_connect($servername,$username,$password);

// check if connection completed
if ($link->connect_error) {
    die("Connection failed: ". $link->connect_error);
}

//creating database
printf("Creating database...\n");
$dbcheck = mysqli_select_db($link, $database);

// if database doesn't exist, then one will be created
if (!$dbcheck) {
    $sql = 'CREATE DATABASE '. $database;
    if (mysqli_query($link, $sql)) {
        echo "Database ". $database ." created\n";
    }
    else {
        echo "Failed to create database:\n";
        echo $link->error."\n";
    }
}

printf("Creating table...");
//creating table to hold information
$sql = 'USE '. $database. ';';
printf("\r\n");
mysqli_query($link,$sql);


$sql2 = "CREATE TABLE popCensus (";
foreach ($file[0] as $rows) {
    if ($rows != end($file[0]))
        $sql2 .= "{$rows} varchar(33), ";
    else
        $sql2 .= "{$rows} varchar(33)";
}
$sql2 .= ");";

echo $sql2;
printf("\r\n");
mysqli_query($link,$sql2);

printf("Inserting data into table...\n");
$cnt = 1;
$sql3 = "";
//Not inserting data
foreach ( $file as $file[$cnt]) {
    $sql3 = "INSERT INTO popcensus VALUES ( ";
    foreach ($file[$cnt] as $rows) {
        if ($rows != end($file[$cnt]))
            $sql3 .= "{$rows} , ";
        else
            $sql3 .= "{$rows});";
    }
    printf($sql3);
    printf("\n");
    mysqli_query($link, $sql3);
    $cnt++;
    printf("cnt: ". $cnt."\n");
    $sql3 = "";
}

printf("\nDone\n");
mysqli_close($link);
?>

edit: I'm able to parse the information into an arrays of arrays. the problem I'm having is trying to insert them into a table afterwards.

a database should be available as should be a table. a) you should not rely on a user input to create tables. b) the user by which php uses the database, should not have rights to create tables (dangerous action) let alone that user should have rights to create databases; — Ivo P, Jun 30 '17 at 20:13
Rather than writing it to the database (for now). Try echoing the results to the screen and see where and why it's failing. — Difster, Jun 30 '17 at 20:15
futher: table names are CaseSenSetive, depending on your operating system. En look at the command LOAD DATA INFILE to load data in een CSV quickly — Ivo P, Jun 30 '17 at 20:15
Possible duplicate of [How to parse a CSV file using PHP](https://stackoverflow.com/questions/9139202/how-to-parse-a-csv-file-using-php) — zod, Jun 30 '17 at 20:27
@zod I'm able to parse the information into an arrays of arrays. the problem I'm having is trying to insert them into a table afterwards. — iii, Jun 30 '17 at 20:30
but why the long way around if one could define the table beforehand and use load-data-infile? And else: try to debug your loop retrieving the lines from the file — Ivo P, Jun 30 '17 at 20:37

score 0 · Accepted Answer · answered Jun 30 '17 at 20:41

0

you need to use prepared statement inside the loop

answered Jun 30 '17 at 20:41

zod

12,092
24
70
106

score 0 · Answer 2 · answered Jun 30 '17 at 20:54

0

Try checking the query for an error result inside the foreach loop.

if (false === mysqli_query($link, $sql3)) {
    throw new Exception(mysqli_error($link));
}

Beyond that, you should really be using a prepare statement.

answered Jun 30 '17 at 20:54

Tony

89
3

How can I use a for loop to insert data from a csv table to a sql database?

2 Answers2