2

I have the follow Model class, which all my models extends.

class Model { [...] protected static $_query; // Query preparated public function prepare($query = null) { [...] // Connect to PDO, bla bla bla self::$_query = self::$link->prepare($query); } [...] } class Login extends Model { public function getUser($username = null) { self::prepare('SELECT * FROM usuarios WHERE usuario = :username LIMIT 1'); self::bindValue('username', $username); return self::fetch(); } }

The problem is, I need to insert prefix to my mysql, to avoid table conflicts, but don't want to edit all my querys.

clientone_tablename clienttwo_tablename clientthree_tablename

How I can do this, parse and insert table prefix when prepare the query?

I have not tried nothing because what I know is, extend my custom PDO to PHP PDO class, which is not much now..

I have seen this: PDO - Working with table prefixes. But don't worked propertly..

Thanks!

Improve this question
5
  • do you really need a client name for each table? couldn't you generate a random set of digits and append that to say "tbl_client" so that it would become like "tbl_client4455" and then store that table name in your clients table so you know which table is for which client? Commented Apr 19, 2012 at 19:23
  • @Ozzy So ugly this way.. Commented Apr 19, 2012 at 19:25
  • I wonder what you make of parsing SQL then ?? But instead of the numbers you could just add their names (i.e. SELECT name FROM clients WHERE id=?) then append that to the table name. Besides whats ugly about it when no one is supposed to be able to see it (private info about your clients?). It should only be accessed through your program so the name is sort of hidden anyway. Commented Apr 19, 2012 at 19:27
  • @Ozzy I have tables like users, news, pages, files. I want to haveone of each (and others) table for each client. Because is one mysql with multiple client projects. So, this is not the best way. Commented Apr 19, 2012 at 19:33
  • I will post you an answer (example) to show you what I mean... Commented Apr 19, 2012 at 19:34

2 Answers 2

Reset to default
1

So i've assume you have only 1 MySQL database (minimum package on your webhost) and need to store a copy of a system for each of your clients.

What I was suggesting, is that you create a separate set of tables as you already are (for each client), but the name wont matter because you have a look-up of the table names in your clients table.

Heres my example for you: The clients table should store the table names of their own tables
(e.g. users_tbl = clientone_users for client id:1) So that later on you can just query the clients table and get his/her table names, then use that result to query on his/her user, news, pages, and files tables.

# SQL: new table structure -- store the names of the clients tables here CREATE TABLE clients( id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), name VARCHAR(50), address VARCHAR(250), email VARCHAR(50), pass BLOB, /* table names*/ users_tbl VARCHAR(70), news_tbl VARCHAR(70), pages_tbl VARCHAR(70), files_tbl VARCHAR(70) ) ENGINE = InnoDB; # PHP: Some definitions for the table structure $tbl_names = array("_users","_news","_pages","_files"); $tbl_fields = array(); $tbl_fields[0] = array("id INT","users_col1 VARCHAR(10)","users_col2 VARCHAR(20)"); $tbl_fields[1] = array("id INT","news_col1 DATE",...); $tbl_fields[2] = array(...); $tbl_fields[3] = array(...); // refers to YOUR clients table field names (see above) $clients_fields = array("users_tbl", "news_tbl", "pages_tbl", "files_tbl"); # PHP: Create a user and create the users database function createUser($name, $address, $email, $pass, $salt) { global $db, $tbl_names, $tbl_fields; $success = false; if ($db->beginTransaction()) { $sql = "INSERT INTO clients(name, address, email, pass) VALUES (?, ?, ?, AES_ENCRYPT(?, ?));" $query = $db->prepare($sql); $query->execute(array($name, $address, $email, $pass, $salt)); if ($query->rowCount() == 1) { # if rowCount() doesn't work # get the client ID # there are alternative ways $client_id = $db->lastInsertId(); for ($i=0; $i<sizeof($tbl_names); $i++) { $client_tbl_name = $name . $tbl_names[$i]; $sql = "CREATE TABLE " . $client_tbl_name . "(" . implode(',', $tbl_fields[$i]) . ");"; if (!$db->query($sql)) { $db->rollBack(); return false; } else { $sql = "UPDATE clients SET ".clients_fields[$i]."=? " ."WHERE id=?;"; $query = $db->prepare($sql); if (!$query->execute( array($client_tbl_name, (int)$client_id) )) { $db->rollBack(); return false; } } } $db->commit(); $success = true; } if (!$success) $db->rollBack(); } return $success; } # PHP: Get the Client's table names function getClientsTableNames($client_id) { $sql = "SELECT (users_tbl, news_tbl, pages_tbl, files_tbl) FROM clients WHERE id=?;"; $query = $db->prepare($sql); if ($query->execute(array((int)$client_id))) return $query->fetchAll(); else return null; } # PHP: Use the table name to query it function getClientsTable($client_id, $table_no) { $table_names = getClientsTableNames($client_id); if ($table_names != null && isset($table_names[$table_no])) { $sql = "SELECT * FROM ".$table_names[$table_no].";"; $query = $db->prepare($sql); if ($query->execute(array((int)$client_id))) return $query->fetchAll(); } return null; }
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

I think I will lose some performance, but basically, it is like self::prepare('SELECT * FROM ' . self::getPrefix([$dbname if multiple DB, or null for current DB]) . 'usuarios WHERE usuario = :username LIMIT 1');.. (static member could be $this->getPrefix(), or $db->getPrefix(), etc)
I just used the normal PDO, PDOStatement code, but if you see what I'm saying you can just alter the code to work with your database abstraction layer.
Yes yes, I'm talking: with this way, I need to have a method/function to insert the prefix. For this, I can store the prefix in config.php and get with $db->getPrefix() for instance, which is the easiest way.. With this approach, what about self::prepare('SELECT * FROM #__usuarios WHERE usuario = :username LIMIT 1');, and inside my Model prepare() replace #__ to config.php defined table prefix.
it would be easier to inclue the file that defines the prefix require_once('table_settings.php');, define the prefix as global in the function so that you have access to it getUser { global $prefix; ... }, then do it like this: self::prepare('SELECT * FROM '.$prefix.'usuarios WHERE usuario = :username'); but I think whichever way will work.
Thanks for your help, it helped me to think this way. =)
1

Just rewrite your queries to use a table prefix found in a variable somewhere. Parsing all your queries for tablenames is more trouble than it is worth. (Do you really want to write an SQL parser?)

Improve this answer

3 Comments

I think is more easy to parse.. I don't like to self::prepare('SELECT * FROM ' . self::getPrefix() . 'usuarios WHERE usuario = :username LIMIT 1');.. If I have a compelx query, this is like a non clean way to do this..
I'm not sure how you could think that writing an sql parser is easier than string interpolation. (Also, stop using static members! What if you want to use two different prefixes in the same application?)
Whit our without static members, I need to get prefix from database.. self::getPrefix('db1') and self::getPrefix('db2').

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.