Question
Using a value in a record of one table to identify which table to query?
Hi,
I'm creating a site to be used by independent store owners belonging to a group. I have authentication code that will identify which store is logging in, and forward them to their appropriate page. That page then queries the database table containing a list of their customers. Easy enough, but I'm trying to automate it further. I'd like to just have ONE CF template that all logged-in stores are forwarded to, and have that CF template automatically query the appropriate table containing their customers. I'm aware you can't use a variable as a table name in a SQL query, but it seems like there should be some technique to accomplish this??
For example:
We have the "Stores" table. It contains three records, for: StoreA, StoreB, StoreC. Each store's record needs to be tied somehow to its corresponding customer's table. For instance:
StoreA's record is tied to table CustomersA, StoreB's record is tied to table CustomersB, etc.
When a store logs in, the application authenticates them using "username" and "password" data in the Stores table (hashed). The application then knows which store has logged in. It then needs to SELECT * FROM (appropriate Customers table).
So, if a user from StoreA logs in, the app identifies the user as StoreA. It then runs this query: SELECT * FROM CustomersA
Any thoughts?
Thanks,
Joe
I'm creating a site to be used by independent store owners belonging to a group. I have authentication code that will identify which store is logging in, and forward them to their appropriate page. That page then queries the database table containing a list of their customers. Easy enough, but I'm trying to automate it further. I'd like to just have ONE CF template that all logged-in stores are forwarded to, and have that CF template automatically query the appropriate table containing their customers. I'm aware you can't use a variable as a table name in a SQL query, but it seems like there should be some technique to accomplish this??
For example:
We have the "Stores" table. It contains three records, for: StoreA, StoreB, StoreC. Each store's record needs to be tied somehow to its corresponding customer's table. For instance:
StoreA's record is tied to table CustomersA, StoreB's record is tied to table CustomersB, etc.
When a store logs in, the application authenticates them using "username" and "password" data in the Stores table (hashed). The application then knows which store has logged in. It then needs to SELECT * FROM (appropriate Customers table).
So, if a user from StoreA logs in, the app identifies the user as StoreA. It then runs this query: SELECT * FROM CustomersA
Any thoughts?
Thanks,
Joe
AdChoices