Hi I didnt get this to work. Seems it's not accepting the parameter. Might be a glitch in mysqli? Or did I miss something, or is there any workaround?
conn = new mysqli($host, $user, $pwd, $db);
$sql = 'SHOW TABLES LIKE ?';
$stmt = conn->prepare($sql);
$stmt->bind_param('s', $table);
$stmt->execute();
$stmt->bind_result($bla);
echo $bla;Are you getting a parse error? If so, you are missing $ in front of your conn variable.
nope. the missing $ happened when I copy/pasted over here.
I get a
Fatal error: Call to a member function bind_param() on a non-object
for this line:
$stmt->bind_param('s', $table);
This means that this line didn't work successfully:
$stmt = conn->prepare($sql);
that's why I suspect mysqli doesn't accept the query
Bjom
you dont define what $table is
see the first example here:
http://us3.php.net/manual/en/mysqli-stmt.bind-param.php
Which doesn't even matter for my problem because it is the line BEFORE that, that doesn't work properly.
Yes in this code example $table is not defined, my bad. In my original testing file I had it set to a sensible value. Doesn't change a thing. Throw in any value you like for testing.
Bjom
Bjom;10919571 wrote:nope. the missing $ happened when I copy/pasted over here.
Which one? There are two missing $'s in your code...
sigh all of them... sry I adapted it out of a class I wanted to use it in and deleted out the $this-> and stuff, and apparently a bit too much.
Here is a clean version:
<?php
$dbHost = '';
$dbUser = '';
$dbPwd = '';
$dbDb = '';
$dbTable = 'myTable';
$conn = new mysqli($dbHost, $dbUser, $dbPwd, $dbDb);
$sql = 'SHOW TABLES LIKE ?';
$stmt = $conn->prepare($sql);
$stmt->bind_param('s', $dbTable);
$stmt->execute();
$stmt->bind_result($bla);
echo $bla; Still same problem:
Fatal error: Call to a member function bind_param() on a non-object in quicktest.php on line 11
means that: $stmt = $conn->prepare($sql); did not work.
Bjom
What is the error message that MySQL returns?
MySQL 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 '?' at line 1
Maybe MySQL doesn't like variable table names; I tried looking through the manual but it's a bit vague on the subject. (In PostgreSQL the job is a matter of running a SELECT query on the system catalogue or the views in the standard Information Schema catalogue - speaking of which, does MySQL provide the Information Schema interface?)
Weedpacket;10919831 wrote:speaking of which, does MySQL provide the Information Schema interface?
Yup. Perhaps something like this:
USE DATABASE information_schema;
SELECT TABLE_NAME FROM TABLES WHERE TABLE_NAME LIKE ? AND TABLE_SCHEMA = ?where the TABLE_NAME is, obviously, the name of the table, and TABLE_SCHEMA is the name of the database.
Connecting to 'information_schema'
$dbHost = '';
$dbUser = '';
$dbPwd = '';
$dbDb = 'information_schema';
$dbTable = 'myTable';
$conn = new mysqli($dbHost, $dbUser, $dbPwd, $dbDb);
$sql = 'SELECT TABLE_NAME FROM TABLES WHERE TABLE_NAME = ?';
if (!$stmt = $conn->prepare($sql)) {
echo sprintf('MySQL error message: %s<br />',$conn->error);
}
$stmt->bind_param('s', $dbTable);
$stmt->execute();
$stmt->bind_result($bla);
if ($stmt->fetch()) {
echo 'table found';
} else {
echo 'table not found';
}This works...
...as well as retrieving all tables and comparing the table name to the result:
$dbHost = '';
$dbUser = '';
$dbPwd = '';
$dbDb = 'myDB';
$dbTable = 'myTable';
$conn = new mysqli($dbHost, $dbUser, $dbPwd, $dbDb);
$rst = $conn->query('SHOW TABLES');
$result = 'table not found';
while ($row = $rst->fetch_row()) {
if (in_array($dbTable, $row, true)) {
$result = 'table found';
break;
}
}
echo $result;The difference is:
In the first case I need a different connection (to the database 'information_schema').
In the second case I need to retrieve all the table names.
Both of which I would have liked to avoid.
Version 1 seems to be the better, right?
Thanks for delving into that 
Bjom
Bjom wrote:In the first case I need a different connection (to the database 'information_schema').
No you don't; you just need to query a different database...
EDIT: Just to clarify; I meant you need to specify that the table you're querying is in a different database, e.g. by using the db_name.table_name syntax - you don't even have to switch databases.
Thanks for pointing that out.
To sum up what we've found out: SHOW TABLES cannot be used with parameters in a prepared statement.
Alternatively the TABLES table in the information_schema database, that is part of a MySQL schema, can be queried. It is necessary to set the connection to point to that db for this, or use a different connection.
Here is what I've come up with:
$dbHost = '';
$dbUser = '';
$dbPwd = '';
$dbDb = 'myDB';
$dbTable = 'myTable';
$conn = new mysqli($dbHost, $dbUser, $dbPwd, $dbDb);
$rst = $conn->query('SELECT DATABASE();');
$dbName = $rst->fetch_row();
$conn->select_db('information_schema');
$sql = 'SELECT TABLE_NAME FROM TABLES WHERE TABLE_NAME = ?';
$stmt = $conn->prepare($sql);
$stmt->bind_param('s', $dbTable);
$stmt->execute();
$stmt->bind_result($bla);
if ($stmt->fetch()) {
echo 'table found';
} else {
echo 'table not found';
}
$stmt->close();
$conn->select_db($dbName[0]);Thanks for your time, everyone!
Bjom
Bjom wrote:It is necessary to set the connection to point to that db for this, or use a different connection.
Neither of those statements is true; see my post above.
(Unless normal user accounts don't have access to the information_schema database, in which case yes you'd have to login as root or another account with permissions.)
bradgrafelman wrote:Unless normal user accounts don't have access to the information_schema database,
If the Information_schema database is implemented properly, all users have SELECT permissions on all of its views.
True, but I've seen many DB's setup by people who have no business being a DBA, so I thought I'd throw that little caveat in there. :p
For 99.9% of situations, however, no other special permissions are needed; just use the "db_name.table_name" format in the SELECT statement and all should work well - no separate connection or database switching needed.
I totally overlooked that. Thanks once more.
$conn = new mysqli($dbHost, $dbUser, $dbPwd, $dbDb);
$result = 'table not found';
$sql = 'SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_NAME = ?';
$stmt = $conn->prepare($sql);
$stmt->bind_param('s', $dbTable);
$stmt->execute();
$stmt->bind_result($result);
if ($stmt->fetch()) {
$result = 'table found';
}
$stmt->close();
echo $result;now let's see if I finally got it right...