没有从MySQL获得预期的价值

Question

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)

Answer 1

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]);