I'm having this very annoying problem for couple of days already, and I did research and everything, but it seems to be bigger than my programming skills and knowledge.
I have a situation where user can define custom columns in tables in my database, and now I need to import CSV
files into that user's custom tables. Since I recently found out that tables need to be "dynamic" this is how I handled importing before:
$file = $_FILES['file']['tmp_name'];
$handle = fopen($file, "r");
$name = $_FILES['file']['name'];
if (strrpos($name, '.csv', -4)) {//handling the csv file
do {
if ($data[0]) {
$mysqli->query("INSERT INTO `test` (`name`, `surname`, `email`) VALUES
(
'" . addslashes($data[0]) . "',
'" . addslashes($data[1]) . "',
'" . addslashes($data[2]) . "'
)
");
}
} while ($data = fgetcsv($handle, 1000, ",", "'"));
And that worked perfectly with static tables. But since I dont't know how many columns table will have, I tried something like this:
Building query method
if (strrpos($name, '.csv', -4)) {//ubacije vrijednosti csv fajla u bazu
do {
if ($data[0]) {
$query = "INSERT INTO $tabela (";
$last = end($anotherArray);
foreach ($anotherArray as $something) {//$anotherArray contains names of fields in user's custom table
if ($something != $last) {
$query .= "" . $something . ",";
} else {
$query .= "" . $something . ")";
}
}
$query .= "VALUES (";
foreach($data as $val){
$query .= "'$val',";
}
}while ($data = fgetcsv($handle, 1000, ",", "'"));
$query=rtrim($query,',');
$query.=")";
//var_dump($query);
$rez=$mysqli->query($query);
//var_dump($rez);
But problem with this code is if csv file contains for example 2 or more colums like so:
everything becomes part of that VALUES
part of query. So query looks like this: "INSERT INTO tabela12312334 (user_facebook_id,ime,prezime) VALUES('123123123','Ognjen','Babic','123123123','Neven',Babic)"
and of course number of fields isn't same as number of values.
Please help me to solve this, I'm desperate.