MYSQL QUERY CANNOT RECOGNIZE THE ID IN VARIABLE FORM

[phpnewbie007]

Hi Members,

 

I am search for the reason for the problem why my mysql query cannot fetch data and store in file based on id in $variable form. For example, $sql="SELECT * FROM mytable WHERE mine_id='1234'"; works for me. But when i use $sql="SELECT * FROM mytable WHERE mine_id='$id'";, files are created as empty. I chanaged the quotes and could not store the data in file. So anyone please help me.

For more clear, i attach the part of my code

for ($i=0;$i<=10;$i++)
    {

$id=$seqs[$i];
$dbo = new PDO($dbc, $user, $pass);
echo $sql = "SELECT * FROM mine_id WHERE locus_id='$id'";

$qry = $dbo->prepare($sql);
$qry->execute();
$data = fopen('file.csv', 'w');

   while ($row = $qry->fetch(PDO::FETCH_ASSOC))
              {
          fputcsv($data, $row);
             }
    }

Edited November 20, 2014 by phpnewbie007

[phpnewbie007]

I solved it by using trim(). There was whitesapce in $id. Thankss....

[Ch0cu3r]

You have solved your issue but there is a problem with your code.

 

The database connection code should be outside of the for loop.

You are using prepared queries incorrectly. Values to be used in the query should be bound to placeholders.

The code for opening the file csv should also be outside of the loop too.

 

Your code should be like

// open csv file
$data = fopen('file.csv', 'w');

// cpnnection to database
$dbo = new PDO($dbc, $user, $pass);

// initiate prepared query. The ? is the placeholder for the id.
$sql = "SELECT * FROM mine_id WHERE locus_id= ? ";
$qry = $dbo->prepare($sql);

for ($i=0;$i<=10; $i++)
{
	$id = trim($seqs[$i]);

	// bind $id to placeholder in prepared query
	$qry->execute(array($id));

	// fetch result. While loop is only needed if more then one result is returned from the query.
	$row = $qry->fetch(PDO::FETCH_ASSOC);

	// write data to csv
    fputcsv($data, $row);
}

// close csv file

[phpnewbie007]

Yes, I modified the code based on your suggestion. Thank you very much Ch0cu3r