Dynamic table name setting with PDO

Question

So I am quite new to using PDO and although I have to it to work using static settings I would like to be able to change the table name in a statement.

I had a look at this question here. and saw the highly up voted answer. So I attempted to recreate a simple version which would just dump all the information within a column.

Question: Why is my edited version of that persons example not work and return this error below.

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 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 '' at line 1' in C:\xampp\htdocs\ *****\database.php:84 Stack trace: #0 C:\xampp\htdocs\ *****\database.php(84): PDOStatement->execute() #1 C:\xampp\htdocs\ *****\database.php(88): buildQuery(1) #2 {main} thrown in C:\xampp\htdocs\ *****\database.php on line 84

The code from the example linked with my small alterations.

function buildQuery( $get_var ) 
        {
            switch($get_var)
            {
                case 1:
                    $tbl = `r16.7`;
                    break;
            }
            global $db;
            $sql = "SELECT * FROM $tbl";
            $stmt = $db->prepare($sql);

            $stmt->execute();
            $result = $sth->fetchAll(PDO::FETCH_COLUMN, 1);
            var_dump($result);
        }
        buildQuery(1);

Echo the `$sql` variable and you'll see. The problem isn't in PDO or PHP, the problem is that you passed on an invalid SQL to MySQL. Another thing - you're using a prepared statement, yet you are not using any parameters. In that case, a statement isn't needed, you are probably better off just doing `$result = $db->query("SELECT * FROM your_table")->fetchAll()`; — N.B., Aug 13 '15 at 15:05
Ah I see it isn't passing the table. Although it still doesn't really answer my question about getting the results dynamically. I tried your non prepared on and it worked, but only if I hard coded the table name into it. So I'm still unsure of what I should do. — Liably, Aug 13 '15 at 15:14
Your `switch` statement isn't working like you want it. How about you set a default value for `$tbl` so it's always defined? — N.B., Aug 13 '15 at 15:17
Nope that's still not working. The SQL statement is still echoing without the table name. — Liably, Aug 13 '15 at 15:24
I found the error was with the name. It didn't seem to like `r16.7` I change it to a name without a decimal place and it seems to have worked. — Liably, Aug 13 '15 at 15:33

score 0 · Accepted Answer · edited May 23 '17 at 12:14

0

Do not confuse PHP and SQL.
You just used SQL quotes in PHP. While you have to use SQL quotes in SQL.

Linked answer is now fixed, so, I am closing this as a duplicate.

edited May 23 '17 at 12:14

Community

1
1

answered Aug 13 '15 at 15:45

Your Common Sense

156,878
40
214
345

Dynamic table name setting with PDO

1 Answers1