Geek asks and answers

Select 300 tables from mysql db using default prefix

I have a program that selects about 200 tables with a prefix. e.g. PBN_products, PBN_address, PBN_others. Instead of adding a prefix to each table for the select statement, is there a way to define the prefix as the default and make a choice?

$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 a prefix that cannot be included in all tables? I have 200 tables.

+3
source share
4 answers

I would look into the class to make a simple abstraction of the request or some kind of ORM lib that does this. An example 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('*'));

, , . , , .

+1

. , "PBN_" .

$arr = array("products","address","others");
$sql = "SELECT price, description, title, cost FROM ";
foreach ($arr as $tablename) {
    $sql = $sql . "PBN_" . $tablename . ", ";
}
$sql = substr($sql, 0, -2); // Remove last comma

, .

0

- ?

$prefix = '';
if(isset($_GET['prefix'])){
    $prefix = mysql_real_escape_string(stripslashes($_GET['prefix']));
}

$sql = "SELECT price, description, title, cost 
           FROM {$prefix}products, {$prefix}address, {$prefix}others";

EDIT: , ... GET. SQL-.

$prefix = "";
if(isset($_GET['prefixid'])){
    $prefixid = mysql_real_escape_string(stripslashes($_GET['prefixid']));
    $query = "SELECT prefix FROM prefixes WHERE prefixid = $prefixid";
    $result = mysql_query($query);
    $prefix = mysql_result($result, 0, 0);
}
$sql = "SELECT price, description, title, cost 
           FROM {$prefix}products, {$prefix}address, {$prefix}others";
-1

- ?

 $prefix = GET['prefix'];

// add prefix to table names
foreach (array("products", "address", "others") as &$table) 
{
    $table = $prefix.$table;
}

mysql_connect(DB_SERVER, DB_SERVER_USERNAME, DB_SERVER_PASSWORD);
mysql_select_db(DB_DATABASE);

$sql = 'SELECT price, description, title, cost'.
       'FROM '.$table[0].', '.$table[1].', '.$table[2];
-1

More articles:


All Articles