select from mysql db with 300 tables using a default prefix

Question

I have a program that selects from about 200 tables with prefix. eg PBN_products, PBN_address, PBN_others. Instead of appending the prefix on each table for the select statement, is there a way of defining the prefix as default value and do the selection?

$prefix=GET['prefix'];
mysql_connect(DB_SERVER, DB_SERVER_USERNAME, DB_SERVER_PASSWORD);
mysql_select_db(DB_DATABASE);
$sql = 'SELECT price, description, title, cost'.
        'FROM products, address, others';

How can I define the prefix not to include in all tables? I have 200 tables.

Answer 1

I would look into a class to do some simple query abstraction or some kind of ORM lib that does this. A sample would be like this.

class Query {
    function from($tbl){
        return new Table($tbl);
    }
}
class Table {
    var $prefix = 'PBN_';
    var $tblname = '';

    function Table($name){
        $this->tblname = $this->prefix.$name;
    }
    function select($cols, $where = false, $order = false, $limit = false){
        $query = "SELECT {$cols} FROM {$this->tblname}";
        if($where) $query .= " WHERE ".$where; //add where
        if($order) $query .= " ORDER BY ".$order; //add order
        if($limit) $query .= " LIMIT ".$limit; //add limit
        return $query;
    }
}

$q = new Query;
$results = mysql_query($q->from('products')->select('*'));

This is obviously nowhere near complete or secure. Just a sample of how an abstraction class could speed up your sql and do you your prefixes for you.