Need help with database insert

[keithappleby]

Hi guys, 

ok this is a weird one. i have a piece of code that opens a csv file, takes the data, and inserts it into a database, or at least its supposed to..  this is what i have

CSV FILE EXAMPLE

"DMC    157","Cornflower Blue very","1173","5.9","6"

 

PHP SCRIPT

$conn = mysqli_connect($servername, $username, $password, $database);
// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  exit();
}


$lineno = "1";
$fh = fopen('Diamonds.CSV','r');
while ($line = fgets($fh)) {
  // <... Do your work with the line ...>
    if ($lineno <> "1")
        {
        $newid = "00001";
    	$details = explode("\"", $line);
    	$dmc = $details[1];
    	$colour2 = $details[3];
    	$drills = $details[5];
    	$grams2 = ceil($drills/200);
        $addfile = "INSERT INTO diamondlists(artID, dmcnum, dmccolour, dmcqty, dmcgrams)values('" . $newid . "', '" . $dmc . "', '" . $colour2 . "', '" . $drills . "', '" . $grams2 . "')";
        echo $addfile . "<br>";
        mysqli_query($addfile) or die ("could not add record");
        } 
        $lineno++;
}
fclose($fh);
mysql_close($conn);

 

RESULTING PHP PAGE

INSERT INTO diamondlists(artID, dmcnum, dmccolour, dmcqty, dmcgrams)values('00001', 'DMC 157', 'Cornflower Blue very', '1173', '6')
could not add record

 

now.. if i use the above code

INSERT INTO diamondlists(artID, dmcnum, dmccolour, dmcqty, dmcgrams)values('00001', 'DMC 157', 'Cornflower Blue very', '1173', '6')
and put it directly in the database it works fine.

i am using the correct databsae, and get no connection error as per the FAILED to connect part of the script.

However no matter what i try i cannot get it to insert the records.. 

 

any ideas please.. its driving me crazy.

 

using PHP 7.3

 

[ginerjm]

You're not using the db connection in your query call.  Do you have error checking turned on to see other errors?  And you should really be learning how to use a prepared query instead of just pushing un-validated data into your db

[Barand]

As you are processing a csv file, use fgetcsv()

You indexes for the data are wrong - "dmcnum" will be index [0] (arrays number from 0 by default).

You are giving all records inserted the same ID (00001) - not good. Use an auto_incrementing key in your db table.

Defore connecting to your DB, call this code below to automate mysql error reporting

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);

Forget mysqli and use the superior PDO library (better and easier)