2

When i recently wanted to list all tables in a database in php i made the simple MySQL query:

$tables_query = mysql_query('show tables');
while ($test = mysql_fetch_array($tables_query)) {
    echo "Table: {$test[0]}<br />";
}

The first result is

TABLES 105
address_book

I don't have a table called "TABLES 105" but the mysql_num_rows also shows, that there is 105 results, even that my database only contains 104 table

If i try to request "show tables" directly on the MySql server, it works fine and i get 104 rows as result. It also worked before and i can't seem to find anything about this, so im hoping someone can help me in here.

It also affect when i call directly to the mysql server. I got access with an other user login for an other database, on the same server and here is no issues at all.

Casper O
  • 252
  • 1
  • 2
  • 9
  • what is `tep_db_fetch_array` is that some pseudo fetching method, or you made that custom function? anyway, you need to show the whole code – Kevin Apr 22 '15 at 10:29
  • @Ghost i just modified it, so its the correct mysql_fetch_array, the result is the same – Casper O Apr 22 '15 at 10:38
  • its still questionable, `tep_db_query` do you have some wrapper function with MySQL? if it does serve just like `mysql_query`, some parts this problematic code is missing, looking on my side, its hard to identify what the problem is. – Kevin Apr 22 '15 at 10:40
  • sorry, I completly overseen it - I changed it to the normal `mysql_query` and it still return the first value as `TABLE 105`. The custom fuctions was only so I could get how long it took to make each requests – Casper O Apr 22 '15 at 10:43
  • Please be aware that the `mysql_` functions are now no longer just discouraged (as it was over the last years), but officially [deprecated](http://php.net/manual/en/migration55.deprecated.php). You should really use [MySQLi](http://php.net/manual/en/book.mysqli.php) or [PDO](http://php.net/manual/en/ref.pdo-mysql.php), as this code will stop working very soon. See [Why shouldn't I use mysql_* functions in PHP?](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) for more information. – Oldskool Apr 22 '15 at 10:43
  • @Oldskool i'm aware of it, thx, but don't solve my current issue – Casper O Apr 22 '15 at 10:50

1 Answers1

1

Its questionable how that 105 got there in the first place, most likely this is caused by that mysql_num_rows function that you mentioned as fetch_array actually fetches the rows, but here's one on MySQLi, stop using MySQL anymore:

$db = 'test'; // database name
$con = mysqli_connect('localhost', 'username', 'password', $db);
$tables_query = mysqli_query($con, "SHOW TABLES FROM {$db}");
while($table = mysqli_fetch_assoc($tables_query)) {

    echo $table["Tables_in_{$db}"], '<br/>';
}

An alternative way of course is to delve into information_schema:

$db = 'test'; // database name
$con = mysqli_connect('localhost', 'root', '', $db);
$tables_query = mysqli_query($con, "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{$db}'");
while($table = mysqli_fetch_array($tables_query)) {
    echo $table['TABLE_NAME'], '<br/>';
}
Kevin
  • 41,694
  • 12
  • 53
  • 70
  • did as requested. Same results. No includes from the original system, just a plain PHP file with the above code replaced with my correct login data. – Casper O Apr 22 '15 at 10:58
  • @CasperO i revised it a bit, anyway, do you have permissions to actually show all the tables in that database in question? – Kevin Apr 22 '15 at 11:00
  • I do, i Have full permissions to add/remove/change/show tables. It even work as it should, when I use a Desktop program to connect to the database and make the query where it list the 104 tables, as it should do – Casper O Apr 22 '15 at 11:03
  • I just saw the 2. option, did the same but it still return `TABLES 105` in the first row – Casper O Apr 22 '15 at 11:37
  • @CasperO wow, bizarre case you got there, is this the only fetching thats not working? how about a normal `SELECT * FROM table_name` queries? – Kevin Apr 22 '15 at 11:49
  • I just made a SELECT call on a random table in the database, everything is as it should be. I will try to see, what happen if i make the request on an other database – Casper O Apr 23 '15 at 07:23
  • on an other database and username combination, same server - no issues what so ever. – Casper O Apr 23 '15 at 07:36