PDO bind variables to prepared mysql statement and fetch using while loop

Question

I've used several of your guides but I can not get the following to run. If I hardcode the 2 variables in the Select statement it runs fine. I need to use variables, and I can't get the bind statement to work. Plenty of experience with the old Mysql_ but the PDO is still a challenge at this point.

$db_table = "ad";
$ID       = "1";

$dsn = "mysql:host=$hostname;dbname=$database;charset=$charset";
$opt = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

$pdo  = new PDO($dsn, $username, $password, $opt);

$result = $pdo->prepare("SELECT * FROM :ad WHERE id= :id ");    //  Line 359 
$result->bindParam(':ad', $db_table, PDO::PARAM_STR);
$result->bindParam(':id', $ID, PDO::PARAM_STR);
$result->execute();

while($row = $result->fetch(PDO::FETCH_ASSOC))
    {
    $product    = $row["product"];
    $msrp       = $row["msrp"];
    $sale       = $row["sale"];
    $content    = $row["content"];
    echo "<strong>$product</strong>&nbsp;-&nbsp;$content<br />";
    // echo $msrp . "<br />";
    if($msrp != "0.00") { echo "MSRP $$msrp";  }
    if($sale != "0.00") { echo "<img src='/images/c.gif' width='75' height='6' border='0'><span style='color: red;'>Sale $$sale</span>"; }
    }

$pdo = null;

The above generates this error,

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? WHERE id=?' at line 1' in /XXXXXXXXXXXX/index_desktop_pdo.php:359

Answer 1

Your database structure is wrong. There should be always only one table to hold all the similar data. And therefore no need to make a variable table name.

To distinguish different parts of data just add another field to this table. This is how databases work.

So your code should be

$section = "ad";
$ID      = "1";

$result = $pdo->prepare("SELECT * FROM whatever WHERE section=:ad AND id= :id");
$result->bindParam(':ad', $section);
$result->bindParam(':id', $ID);
$result->execute();