0

On my php server side, the code below works fine.

$sql = 'select make, model from cars';
$stmt = $pdo->query($sql);
$row = $stmt->fetchall(PDO::FETCH_ASSOC);
echo json_encode($row);

Here's what the front-end saw in the console (records php fetched from database): front-end console

But then when I tried to make the sql statements more flexible by using variables, I met with troubles. Here is the code. Neither this one:

$sql1 = "select make, model from :tb";
$stmt = $pdo->prepare($sql1);
$stmt->execute(array('tb' => 'cars'));
$row = $stmt->fetchall(PDO::FETCH_ASSOC);
echo json_encode($row);

Nor this one worked:

$sql2 = "select make, model from ?";
$stmt = $pdo->prepare($sql2);
$stmt->execute(array('cars'));
$row = $stmt->fetchall(PDO::FETCH_ASSOC);
echo json_encode($row);

I guess something was wrong with the execute statement, but could not figure this out. What did I miss here?

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
H.Li
  • 33
  • 6
  • What's the point in making the table name as a parameter? Do you have any other table with make and model columns? – Your Common Sense Oct 27 '20 at 05:36
  • This is my preparation step before I could send more selection variables from the client side so that the $sql could be 'select [col1], [col2]' from table where ...'. Besides that, I want to solve this problem. – H.Li Oct 27 '20 at 06:15
  • "where..." what? what will be in the where part? Looks like you just want to send the entire SQL from the client side, spare the few keywords like 'FROM'. No, prepared statements (and overall client-server interaction) do not work the way you probably picture this: having just a silly bit of code on the server side that can serve all kinds of requests. – Your Common Sense Oct 27 '20 at 06:19
  • Yes, the update is correct. Tables/columns are known as identifiers they can be encapsulated in backticks if they contain reserved characters. They do not use single quotes which strings use. Also identifiers can have `.`s which will denote parent/child relationships. e.g. `mydb.mytable.mycolumn = 1`. – user3783243 Oct 27 '20 at 17:46

0 Answers0