PHP "SHOW TABLES" MySQLi query not working

Question

i'm trying to execute a prepared statement with php but it doesn't work. My prepared statement is like:

SHOW TABLES LIKE "italy_turin_mathematics"

and i do it like this:

if ($stmt = $this->mysqli->prepare("SHOW TABLES LIKE ?_?_?")) {


    $stmt->bind_param('sss', "italy", "turin", "mathematics");

    $stmt->execute();

    $stmt->store_result();
    $stmt->bind_result($column1);

    while($stmt->fetch()) {
        echo "Table: ".$column1;
    }

}

I'm sure it must return something, because with PHPMyAdmin it does, but with PHP it always skips the while loop, i think there is something wrong with the prepared statement query, maybe it needs to escape the underscore char?

How can i do it?

I'm not very familiar with prepared statements by why is there only three `?` and four params? Does that work? — Daniel Figueroa, Apr 04 '13 at 15:20
@DanielFigueroa 'sss' means i want to bind 3 strings, and the other 3 strings are the 3 parameters i want to bind — BackSlash, Apr 04 '13 at 15:20
@Jocelyn AFAIK $column1 is initialized by `$stmt->bind_result($column1);` and it should contain the only column resulted by my query, in the answer to this question it is not initialized: http://stackoverflow.com/questions/1290975/how-to-create-a-secure-mysql-prepared-statement-in-php — BackSlash, Apr 04 '13 at 15:23
@Jocelyn where do i have to put it? if i put `echo "error: ". $this->mysqli->error;` after the while loop (after the closing loop brace) it displays nothing — BackSlash, Apr 04 '13 at 15:51
@Jocelyn It prints nothing, after some tries i noticed that it is not even entering the `if ($stmt = $this->mysqli->prepare("SHOW TABLES LIKE ?_?_?"))` — BackSlash, Apr 04 '13 at 15:58

Your Common Sense · Answer 1 · 2013-04-04T15:32:18.147

1

Your database architecture is utterly wrong.

There should be only one table contains all the data, for all the places and sciences.
And you have to query it usual way, without employing SHOW TABLES at all.

So, it have to be something like

$sql = "SELECT * FROM t WHERE country=? AND city=? and science=?";
$stm = $pdo->prepare($sql);
$stm->execute(array("italy", "turin", "mathematics"));
$data = $stm->fetchAll();

the above code is in PDO, as you have to use it instead of mysqli.

Splitting tables is a very bad idea, violating the very fundamental rules of relational databases. As you can see, it makes you to run such a strange query and will make your further code even worse.

edited Apr 04 '13 at 15:32

answered Apr 04 '13 at 15:26

Your Common Sense

156,878
40
214
345

Why not posting a comment? my question is about how to make my query work, and this is not an answer to it, i know it is wrong to name different tables but i need to do it and i need to know why my query doesn't work. – BackSlash Apr 04 '13 at 15:33
1

Why PDO? Do you have a good reason to say so? I really think you don't. Also, your architecture too, is awful. (and it breaks all the normal forms) – Mathieu Amiot Apr 04 '13 at 15:34
1

Once you've used both PDO and MySQLi you'll probably reach the same conclusion. If you have the option, choose PDO and avoid accruing technical debt. Pros and cons [here](http://stackoverflow.com/questions/13569/mysqli-or-pdo-what-are-the-pros-and-cons). – bcmcfc Apr 04 '13 at 15:39
@Mathieu +1. Although first one is obvious, your second objection is absolutely correct. – Your Common Sense Apr 04 '13 at 15:39
@YourCommonSense: MySQLi is good for high-traffic environments. +5% of performance on DB Queries (which are by far the slowest operations on web applications) is not something to be skipped on. I used PDO for a while for it's ease of use, and fell back to MySQLi because it's less buggy, faster, and once you have a nice wrapper around it, it's just awesome. – Mathieu Amiot Apr 04 '13 at 16:01
@Mathieu Unfortunately, there is no real life difference. These 5% are fictional, gotten on artificial tests. While on a live site you'd never notice it. One have to optimize the query itself, not API running it. – Your Common Sense Apr 04 '13 at 16:21

n0tiz · Answer 2 · 2013-04-04T15:39:28.637

1

if ($stmt = $this->mysqli->prepare("SHOW TABLES LIKE ?")) {

    $country = "italy";
    $city = "turin";
    $course = "mathematics";

    $stmt->bind_param('s', $country . "_" . $city . "_" . $course);

    $stmt->execute();

    $stmt->store_result();
    $stmt->bind_result($column1);

    while($stmt->fetch()) {
        echo "Table: ".$column1;
    }

}

As far as I know the code you had would result in a query looking as follows:

SHOW TABLES LIKE 'italy'_'turin'_'mathematics'

edited Apr 04 '13 at 15:39

answered Apr 04 '13 at 15:29

n0tiz

352
1
4

1

AFAIK in prepared statements you must not use single or double quotes around a `?` parameter... – BackSlash Apr 04 '13 at 15:31

Sammitch · Accepted Answer · 2013-04-04T16:30:21.790

0

You cannot concatenate like that in mySQL, or any form of SQL I can think of.

SHOW TABLES LIKE ?_?_?

Should be:

SHOW TABLES LIKE CONCAT(?, '_', ?, '_', ?) --this gives an error, see below

And I fully agree with @your-common-sense's commentary that this is a terrible way to design a database and you will come to regret it in more ways than just this one messed up query.

edit:

MySQL does not seem to allow functions in a SHOW TABLES statement, so either you'll have to concatenate the table name to a single string in PHP, or you can use a query like:

SELECT 
  TABLE_NAME
FROM    
  INFORMATION_SCHEMA.TABLES    
WHERE    
  table_schema = 'mydb' AND    
  table_name LIKE CONCAT(?, '_', ?, '_', ?);

edited Apr 04 '13 at 16:30

answered Apr 04 '13 at 16:10

Sammitch

30,782
7
50
77

Doesn't work. It doesn't even enter the `if ($stmt = $this->mysqli->prepare("SHOW TABLES LIKE CONCAT(?, '_', ?, '_', ?)"))` – BackSlash Apr 04 '13 at 16:13
@Harlandraka then what is the error message that is generated? – Sammitch Apr 04 '13 at 16:15
`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 'CONCAT(?, '_', ?, '_', ?)' at line 1` – BackSlash Apr 04 '13 at 16:17
@Harlandraka it looks like mySQL does not like any functions in a `SHOW TABLES` statement, see my edit. – Sammitch Apr 04 '13 at 16:30
Fantastic! it works, thank you! (and yes, i will change my database structure, i know it is not normalized, i just wanted to know how to do it) – BackSlash Apr 04 '13 at 16:37

PHP "SHOW TABLES" MySQLi query not working

3 Answers3

edit: