Import list to SQL query

[wall.e]

Hi,

 

I have tried two different approaches for introducing a list of names to a MySQL query without any success.

The input file ($contig) contains the following:

00001

00002

 

Approach 1:

<?php
$fileArr = file($contig);
print_r($fileArr);

try 
{ 
$bdd = new PDO('mysql:host=localhost;dbname=brain_transcriptome', 'root', 'root'); 
} 
catch (Exception $e) 
{ 
        die('Error : ' . $e->getMessage()); 
} 

$req = $bdd->query('SELECT name FROM crocodylus_contig_info WHERE 1=1 AND name IN ("'.implode('", "', $fileArr).'")') or die(print_r($bdd->errorInfo()));
while ($donnees = $req->fetch(PDO::FETCH_ASSOC))
{
echo ''.$donnees['name'].'<br />';
}
$req->closeCursor();

?>

Approach 2:

<?php
$fileArr = file($contig);
print_r($fileArr);

function arrayToSQLString($fileArr)
{
   return '("'.implode('","',$fileArr).'")';
} 

$params = arrayToSQLString($fileArr);
$query = vsprintf('SELECT name FROM crocodylus_contig_info WHERE 1=1 AND name IN %s',$params);
echo $query;

try 
{ 
$bdd = new PDO('mysql:host=localhost;dbname=brain_transcriptome', 'root', 'root'); 
} 
catch (Exception $e) 
{ 
        die('Error : ' . $e->getMessage()); 
} 

$req = $bdd->query($query) or die(print_r($bdd->errorInfo()));

while ($donnees = $req->fetch(PDO::FETCH_ASSOC))
{
echo ''.$donnees['name'].'<br />';
}
$req->closeCursor();

?>

 

I have check if the input file is well upload and if the query without variables works, and there was no problem.

I would greatly appreciate ANY suggestions!

 

[MadTechie]

echo the query, check for whitespaces

 

Check the query with a single name

ie

SELECT name FROM crocodylus_contig_info WHERE name IN ("0001")

if that works, try

SELECT name FROM crocodylus_contig_info WHERE name IN ("0001","0002")

 

logically it looks fine so i would have to say its down do the data

[wall.e]

I echo the query and indeed there were spaces, so I modified the code to:

function arrayToSQLString($fileArr)
{
return '("'.implode('","',array_map('trim', $fileArr)).'")';
} 

so the query looks like this now:

SELECT name FROM crocodylus_contig_info WHERE name IN ("00001","00002","00003","00004")