1

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

Rai Vu
  • 1,595
  • 1
  • 20
  • 30
Werks
  • 49
  • 4

1 Answers1

0

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();
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • Wasn't the space between id and the equal sign. – Werks Dec 20 '16 at 04:51
  • Don't you understand what is written in my answer? – Your Common Sense Dec 20 '16 at 04:53
  • I believe the database structure is fine the one table is "ad" and it's a variable. The field is id and it's also a variable. Unless the table itself can't be a bindParam. – Werks Dec 20 '16 at 04:54
  • Well that's oxymoron. If you have a table id as a variable, then your design is not ok. But you can keep with your ideas. The more your kind, the more money will earn someone who will end up fixing that mess. – Your Common Sense Dec 20 '16 at 04:57
  • You've never needed to pass in the name of a table. How inflexible is that? – Werks Dec 20 '16 at 05:01
  • For your example it is absolutely unnecessary. If you manage to provide a real life example where you need a variable table name, then there will be a different answer. – Your Common Sense Dec 20 '16 at 05:10