PDO SELECT - Struggling to find the problem

[afridy]

Hi folks,

strange my below query is not working. i am strangling to find the problem.

echo "<select name='" . $select_name . "' id='" . $select_id . "'>";
   echo "<option value='Select'>Select</option>";
    
    $query="SELECT * FROM :table_name";

    $stmt = $db->prepare($query);
    $stmt->execute(array(':table_name' => $table_name));
    while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
       $value=$row['position_id'];
       $name=$row['position'];
       echo "<option value='" . $value . "'>" . $name . "</option>";
    }

echo "</select>";

The html select control does not popup any data when i execute the above.

[Michael_Morris]

When is $table_name set?

[droopsnoot]

And what happens if you add in

echo $row['position_id'] . " " . $row['position'];

as the first line of your while() loop?

[afridy]

$table_name=$_GET['table_name'];

Table name is correctly comming

[afridy]

i am leaving office. ill come to u from home

[ahundiak]

Named parameters only work for values. Not table names.

By the way, configure pdo to throw exceptions on errors. This will make trouble shooting less challenging.

error_reporting(E_ALL);

$dsn = 'mysql:dbname=appgames;host=127.0.0.1';
$db  = new PDO($dsn, 'user', 'password');

// Throw exceptions on errors
$db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);

// Use assoc arrays by default
$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC);

[afridy]

@ahundiak

Named parameters only work for values. Not table names.

Golden quote. it works!
Thanks every one for the support. really appriciated.

[afridy]

@ahundiak

$db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);

// Use assoc arrays by default
$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC);

Thanks, i added those two lines to my pdo_connection.php file . kindly verify it.

<?php

/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'xxxxx';

/*** mysql password ***/
$password = 'xxxxx';

try {
    $db = new PDO("mysql:host=$hostname;dbname=hrsjedco_visa", $username, $password);
    /*** echo a message saying we have connected ***/
    //echo 'Connected to database'; 
       
    // Throw exceptions on errors
    $db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
    
    // Use assoc arrays by default
    $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC);        

    array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET time_zone = \'+03:00\'')    ;
}catch(PDOException $e){
    echo $e->getMessage();
}
?>

[Michael_Morris]

Named parameters only work for values. Not table names.

I did not know that. Never tried to do it though.

[ahundiak]

Thanks, i added those two lines to my pdo_connection.php file . kindly verify it.

It looks right. Try executing some invalid sql. You should get an error message.

[droopsnoot]

@Michael_Morris

> Named parameters only work for values. Not table names.

I did not know that. Never tried to do it though.

Nor me, good to know.

[ahundiak]

The whole idea behind prepared statements is to separate the sql statement from the data. The sql statement is sent to the db server where it is parsed and optimized. The data is then sent independently. If table and column names are not known up front then the process simply won’t work. We would be back to basically using string functions to build sql which in turn would bring back all the sql injection issues.

[afridy]

@ahundiak

he whole idea behind prepared statements is to separate the sql statement from the data. The sql statement is sent to the db server where it is parsed and optimized. The data is then sent independently. If table and column names are not known up front then the process simply won’t work. We would be back to basically using string functions to build sql which in turn would bring back all the sql injection issues.

Thanks for the additional piece of explanation.