PHP - adding a string to a variable (array) CSV --> MySQL

340 views Asked by At

I have a problem thats just baffling my mind, I'm unable to add a string gradually loaded from a CSV file to an array (through a while loop) which after writes to a database.

The code runs, it echoes the fact that it has written to the database, but nothing actually happens, when I remove the string it writes to the database just fine.

The syntax should be $variable = 'string' . $csvValue; It won't write to the database when this is the case, but it will when it is: $variable = $csvValue;

Here is the code:

<?php
// Script to insert data from a CSV-file with AD information in it to a MySQL database.

// Connect to the database 
$connect = mysqli_connect("localhost","root","password","dbname"); 

if(!$connect){
   die('Could not <span id="IL_AD1" class="IL_AD">
    connect to</span> MySQL: ' . mysql_error());
}

// Charset
mysqli_query($connect, "SET NAMES 'utf8'");

// File path
define('CSV_PATH','/home/administrator/Desktop/'); 

$csvName = CSV_PATH . "AnsattInfo.csv"; // Name of CSV-file
$csvfile = fopen($csvName, 'r');
$theData = fgets($csvfile);
$i = 0;

while(!feof($csvfile)){

    $csv_data[] = fgets($csvfile, 2048);
    $csv_array = explode(",", $csv_data[$i]);
    $insert_csv = array();
    $insert_csv['sAMAccountName'] = $csv_array[0];
    $insert_csv['displayName'] = $csv_array[1];
    $insert_csv['employeeNumber'] = $csv_array[2];
    $insert_csv['mail'] = $csv_array[3];
    $insert_csv['company'] = $csv_array[4];
    $footer = str_replace(' ', '_', $insert_csv['company']);
    //$footer = '@' . str_replace(' ', '_', $insert_csv['company']);

    $query = "INSERT INTO playsms_tblUserTest (status, username, enable_webservices, webservices_ip, name, mobile, email, footer, country, datetime_timezone, language_module, fwd_to_mobile, send_as_unicode)
        VALUES(
            3,
            ".$insert_csv['sAMAccountName'].",
            1,
            '*.*.*.*',
            ".$insert_csv['displayName'].",
            ".$insert_csv['employeeNumber'].",
            ".$insert_csv['mail'].",
            ".$footer.",
            137,
            '+0100',
            'nb_NO',
            1,
            1         
    ) ON DUPLICATE KEY
        UPDATE name = $insert_csv[displayName], mobile = $insert_csv[employeeNumber], email = $insert_csv[mail], footer = $footer";

   mysqli_query($connect, $query);

   $i++;
}

fclose($csvfile);

echo "CSV successfully imported to User-table! \n";

mysqli_close($connect); // closing connection  
?>

What I actually want to do here is to add a string infront of the $footer value, as @ is groupname of a contact group, where '@' is actually the indicator of it being a group.

In the CSV file the company name does not have a '@' infront of it, which is what I want to add. Like this:

$footer = '@' . str_replace(' ', '_', $insert_csv['company']);

This does not give any syntax errors, but nothing actually gets written to the database.

Does anyone have a suggestion on a solution here?

Cheers for any help.

1

There are 1 answers

3
low_rents On

if the field footer in your database is of some "string-type", then you also have to fill it with a string:

/* ... */
            ".$insert_csv['employeeNumber'].",
            ".$insert_csv['mail'].",
            '".$footer."',
            137,
            '+0100',
            'nb_NO',
            1,
            1         
    ) ON DUPLICATE KEY
        UPDATE name = $insert_csv[displayName], mobile = $insert_csv[employeeNumber], email = $insert_csv[mail], footer = '$footer'";
/* ... */

and to declare it as a string in your mysql-query you have to put it in between quotes like shown above with the $footer value.

i think you got the same error multiple times in your query: for example $insert_csv['mail'] will be a string too, i guess.

hint: if you used some kind of error-handling, then you would have seen that there is some mysql-error (since i am using pdo i don't really know how this works in mysqli).