CODEWITHSUNDEEP
phpmysqldatabaseclassmysqli
wiredmark
wiredmark

Reputation: 1108

Showing column names

This class is for managing a database through mysqli. I'm stuck on the prepared statement to show all the column names of a given table.

class databaseManager {
    function showTable ($tableName, $mysqli) {
        $stmt = $mysqli -> prepare("SHOW COLUMNS FROM ?");
        $stmt -> bind_param('s', $tableName);
        $stmt -> execute();

        while ($stmt -> fetch) {
            $Field;
        }

        $stmt -> free_result();
    }
}

I get the error "Call to a member function bind_param() on a non-object", but $mysqli connection exists so I suppose the mysqli -> prepare is wrong.

EDIT: clearly I passed $mysqli

$databaseManager = new databaseManager();
$databaseManager -> showTable("blog", $mysqli);

Upvotes: 1

Views: 4844

Answers (2)

Your Common Sense
Your Common Sense

Reputation: 157870

First, you aren't doing any error handling. Always check results of all database interactions.

For example, 

$stmt = $mysqli->prepare("SHOW COLUMNS FROM ?");
if (!$stmt) {
    throw new Exception($mysqli->error);
}

so, you will get an error message from database.

Next, you can't bind identifier via prepared statement.
So, you have to either whitelist it of format it properly, according to documentation

By the way, I see not much point in such a function. I am using console for this purpose and other people usually see their database structure via PHPMyAdmin

Also, using bare API is not the best choice. By using some abstraction layer you can make your code in one line:

function showTable ($tableName) {
    return $this->db->getCol("SHOW COLUMNS FROM ?n", $tableName);
}

You don't need this query to show table contents though, as column names always already present in a resultset. So, just request your data first and then extract column names from it

by using such a library mentioned above, it would be like this

$data  = $this->db->getAll("SELECT * FROM ?n", $tableName);
$names = array_keys($data[0]);

But still PHPMyAdmin is WAY better at it, give it a try

If you need some sort of online table editor, it is seldom works such straight way. There are always some issues which require pre- or post-processing of data. And for whatever complex data structure you always end up with dedicated code. To show nice column titles for example.
But for a few plain string values it would work.

Upvotes: 2

John Woo
John Woo

Reputation: 263713

By design, table names and column names cannot be parameterized. But you can still concatenate it on the string and wrapping it with backquotes as the first level of defense against sql injection.

    $stmt = $mysqli -> prepare("SHOW COLUMNS FROM `" . $tableName . "`");
    $stmt -> execute();

Upvotes: 0

Related Questions