Page 1 of 1

Import list to SQL query

Posted: Thu Apr 22, 2010 12:19 pm
by 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

Code: Select all

<?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

Code: Select all

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

function arrayToSQLString($fileArr)
{
	return '("'.implode('","',array_map('trim', $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!

Re: Import list to SQL query

Posted: Thu Apr 22, 2010 1:17 pm
by requinix
#2 is the closest to working. What error(s) do you get?

Re: Import list to SQL query

Posted: Thu Apr 22, 2010 4:08 pm
by wall.e
No error message unfortunately...

The rest of the page is displayed normally but there is no query result :cry:

Re: Import list to SQL query

Posted: Thu Apr 22, 2010 4:44 pm
by requinix
You're sure there's stuff in the table? What if you try running the query yourself?

Re: Import list to SQL query

Posted: Fri Apr 23, 2010 1:26 am
by wall.e
I have tried to run the query directly using Query Browser and I get the correct results.
Even more, when I run the query without the 'implode' via php I also get the results.

Re: Import list to SQL query

Posted: Fri Apr 23, 2010 1:31 am
by Eran
check the value of $params before you run the query. It's probably not formatted the way you think

Re: Import list to SQL query

Posted: Fri Apr 23, 2010 1:36 am
by wall.e
When I do echo $query, the query appears to be OK

Code: Select all

$params = arrayToSQLString($fileArr);
$query = vsprintf('SELECT name FROM crocodylus_contig_info WHERE 1=1 AND name IN %s',$params);
echo $query;
SELECT name FROM crocodylus_contig_info WHERE name IN ("00001","00002","00003","00004")
Unless what I see with echo, is not what is sent...

Re: Import list to SQL query

Posted: Fri Apr 23, 2010 12:24 pm
by wall.e
I found what the problem was and now it works.

Instead of writing

Code: Select all

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

Code: Select all

$req = $bdd->query(''.$query.'') or die(print_r($bdd->errorInfo()));
Thanks everyone for your help!