Need help MYSQL Query on dynamic table..

[salbahis]

---

Need help MYSQL Query on dynamic table..., been doing for almost a week but still it didn't work!, im using PHP5 and MYSQL, im using PS_Pagination class script that paginates my table, that's why im restricted to do it in one line of query... below is an example of what my specs is...

e.g.
DB_LIST.DBID => database/table
ID DBID
1 100
2 300

USER_100.account => database/table
ID name
1 Name1
2 Name2

USER_200.account => database/table
ID name
1 Name1
2 Name2

Query:
'SELECT account.name FROM DB_LIST.DBID INNER JOIN USER_{DBID}.account ON account.id=DBID.accountid'

any help will be appreciated!!!

[cold_fusion]

The main problem of this is your database structure.
Just create one table "accounts" with the following fields: dbid, id, name

Then you could do the select easily:
SELECT account.name
FROM account
INNER JOIN db_list ON db_list.dbid = account.dbid

[salbahis]

that's the problem im not allowed to change the DB structure, they want it AS IS, i guess i hit a dead end on this one!!!

[carmicael]

most probably naay anomaly ang structure sa database. try reading "database anomalies" sa net. cheers.

[salbahis]

anomaly?, you mean a bug on the database engine itself?

[bishop__]

bro,

why not create a stored procedure for this? from you db design, the only link between your account tables and dbid tables is thru the account table name which I think cannot be made variable inside a query or can it?

but then again, it is still wise to take cold_fusion's suggestion since if you add another entry in your dbid table, you'll probably need to create another account table... that is not a good db design right?

[carmicael]

by anomaly i mean something present in the structure of your database, not something with the dbase engine itself. cheers.