Import list to SQL query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
wall.e
Forum Newbie
Posts: 5
Joined: Thu Apr 22, 2010 11:16 am

Import list to SQL query

Post 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!
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Import list to SQL query

Post by requinix »

#2 is the closest to working. What error(s) do you get?
wall.e
Forum Newbie
Posts: 5
Joined: Thu Apr 22, 2010 11:16 am

Re: Import list to SQL query

Post by wall.e »

No error message unfortunately...

The rest of the page is displayed normally but there is no query result :cry:
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Import list to SQL query

Post by requinix »

You're sure there's stuff in the table? What if you try running the query yourself?
wall.e
Forum Newbie
Posts: 5
Joined: Thu Apr 22, 2010 11:16 am

Re: Import list to SQL query

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Import list to SQL query

Post by Eran »

check the value of $params before you run the query. It's probably not formatted the way you think
wall.e
Forum Newbie
Posts: 5
Joined: Thu Apr 22, 2010 11:16 am

Re: Import list to SQL query

Post 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...
wall.e
Forum Newbie
Posts: 5
Joined: Thu Apr 22, 2010 11:16 am

Re: Import list to SQL query

Post 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!
Post Reply