Hi
In a couple of weberp installations i have bumped into a rare situation where this error message shows up when running one of the following reports:
MRPPlannedPurchaseOrders.php
MRPReschedules.php
MRPPlannedWorkOrders.php
MRPShortages.php
Database Error 1054
Unknown column 'Tables_in_testdb' in 'where clause'
The solution for this problem was simple, just wrapped the tablename part with single-quotes like this (showing example from MRPPlannedPurchaseOrders.php line 10)
From:
Code:
$sql="SHOW TABLES WHERE Tables_in_" . $_SESSION['DatabaseName'] . "='mrprequirements'";
To:
Code:
$sql="SHOW TABLES WHERE 'Tables_in_" . $_SESSION['DatabaseName'] . "'='mrprequirements'";
On the same server i also have a db named testdb-ver2
It seems like SHOW TABLES mix similar tablenames for one reason, i'm actually not sure.
The next problem was a bit trickier, maybe this problem have something in common? MySQL version problem?
When using: SHOW TABLES without arguments, all tables in testdb is listing.
When using: SHOW TABLES WHERE 'Tables_in_testdb'='mrprequirements' shows no results at all. (this is tested outside weberp as well)
Changing the query to:
Code:
$sql = "SELECT TABLE_NAME FROM information_schema.tables WHERE TABLE_SCHEMA = '" . $_SESSION['DatabaseName'] . "' AND TABLE_NAME = 'mrprequirements'";
..solves it all.
Anyone seeing this before?
This is not a bug in weberp at all, but may become a problem on some servers. I have not digged deeper down in this particular server to find out why.
Personally, I never use SHOW TABLES at all in my code. Always using information_schema.tables
JanB
Server Versions:
mysql Ver 15.1 Distrib 10.0.14-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
Server version: Apache/2.2.22 (Debian)
PHP 5.4.38-0+deb7u1
