I'm embarrassed because this should be a pretty simple task, but I can't figure out why this is not working. I'm using a tab separated file to get values I need to populate a MySQL table. I have 2 MySQL tables, clients and data The clients table has an ID I need to fetch and use in the insert to the data table
<?php
// MySQL settings
define('DB_SERVER', 'localhost');define('DB_USERNAME', 'USER');
define('DB_PASSWORD', 'pass');define('DB_DATABASE', 'DB');
// connect to DB
if ($db = mysqli_connect(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE)){}
else {echo 'Connection to DB failed';die();}
// load tab delim file
$file = "file.csv";// TSV actually
$handle = fopen($file, "r"); // Make all conditions to avoid errors
$read = file_get_contents($file); //read
$lines = explode("
", $read);//get
$i= 0;//initialize
// loop over file, one line at a time
foreach($lines as $key => $value){
$cols[$i] = explode("\t", $value);
// get order ID for this URL
//$cols[$i]['6'] stores website URLs that match `salesurl` in the clients table
$getidsql = 'select `id` FROM DB.clients WHERE `salesurl` = \''. $cols[$i]['6'].'\'';
if ($result = mysqli_query($db, $getidsql)){
$totalcnt = mysqli_num_rows($result);
$idrow = mysqli_fetch_array($result);
echo '<h1>:'. $idrow['id'] .': '.$totalcnt.'</h1>'; //prints ':: 0'
} else {
echo mysqli_error($db);
echo 'OOPS<hr>'. $getidsql .'<hr>';
}
// if $idrow['id'] actually had a value, then
$insertqry = 'INSERT INTO `data` ......';
$i++;
} //end for each, file line loop
?>
The $getidsql query does work when copy pasted into PHPMyADMIN I get the id result, but within this script mysqli_num_rows is ALWAYS zero, and $idrow is never populated eg; NO ERRORS.. but no result (well, an empty result)
Turns out my code was working fine. My problem was with the data file I was working with. All the data had non-printable characters in it, in fact each character was followed by a non-ASCII character. Running this preg_replace prior to using it in my query solved the problem.
$data[$c] = preg_replace('/[\x00-\x08\x0B\x0C\x0E-\x1F\x80-\x9F]/u', '', $data[$c]);