Select MySQL table with dynamic content

Question

I have a bunch of tables that are like Client# and the # changes. Is there a way to create a query to query that table based on the client number you get from logging in as their use?

Example to give idea:

      $q2 = "SELECT * FROM users WHERE username = '".$_SESSION['username']."'";
        $result2 = mysql_query($q2,$conn);
        $row = mysql_fetch_assoc($result2);
        $_CLIENT_ID = $row['CLIENTID'];
        $q2 = "SELECT * FROM client+".$_CLIENT_ID."";

Is there a better way to do this?

I'm trying to keep clients in their own tables so they do not get to massive.

Michael Berkowski · Accepted Answer · 2012-04-07 19:29:48Z

3

I'm trying to keep clients in their own tables so they do not get to massive.

This is almost always the wrong strategy. The table size isn't as important as how you have indexed it for access, and it can be partitioned later, should that become necessary. The proper way to handle this is simply a column in one table which identifies the client id.

With a proper client-identifying column you can query as:

SELECT othertable.* 
FROM
  users 
  JOIN othertable ON users.CLIENTID = othertable.CLIENTID
WHERE users.username = '$_SESSION['username']'

Dealing with dynamic table names becomes troublesome not only because it is more difficult to query against. You cannot, for example, use a placeholder in place of a table name with many parameterized query APIs like PDO & MySQLi:

// Won't work with a PDO prepared statement.  :clienttable place holder isn't allowed
SELECT * FROM :clienttable WHERE value=:someval

edited Apr 7, 2012 at 19:29

answered Apr 7, 2012 at 19:15

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Select MySQL table with dynamic content

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related