php class for MYSQL or SESSION ARRAY

want to build or implement sql statements to run on 2 dim. array

[willis1480]

Ok, so I have maybe 5 tables with row counts ranging from 5-50

I want to have a class that will take an SQL input and run the equivalent on a 2 dimentional array.

Short Example.

I have an array in session:

$_SESSION['table_1'] = array(
array('id, 'short_desc', 'details'), //the field names
array(1, 'category 1', 'my first category'),
array(2, 'category 2', 'my 2nd category'),
);
//now in my page I would have code like this
<?
class db{
function run_sql($sql){
//get tablenames
$tables = preg_match("/FROM[\s\w\_\-0-9]+WHERE/i",$sql); //should look for multiple tables, but keep ex. simple
$tables = preg_replace("/FROM/i","",$tables);
$tables = preg_replace("/WHERE/i","",$tables);
$tables = trim($tables); //trim lead and trail spaces
$tables = preg_replace("/\`/","",$tables); //remove "`" if its there
if(isset($_SESSION[$tables])){
return $_SESSION[$tables];
}else{
$tmpArr = array();
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)){
array_push($tmpArr,$row);
}
return $tmpArr;
}
}
}

$db = new db();
$data = $db->run_sql('SELECT * FROM `table_1` WHERE 1');

?>
So if a session variable with name 'table_1' exists it pulls the data from there otherwise it goes to the database
Im sure smary does something like this, but looking for some class that I could integrate in easily to my db class.

[coopster]

So you want to "execute" and SQL query against an array if the table has already been read and populated in your SESSION?

[willis1480]

Sorry if this is a late reply, but I wanted to do the same thing...I use I think what they call polymorphism in object world.

So I have a dbClass where I do my connections and such. Then I have 2 classes that both extend the dbClass.

I call one queriesNoSql, queriesSql....they both have the same methods in them.

Then in my page I use something like this:
$db = new dbClass();
$qry = (isset($_SESSION['mytables']) && ((time() - $_SESSION['mytables']['timestamp'])/60 > 30 /*minutes*/)) ? new queriesNoSql : new queriesSql;

//what this does is it says if the tables in session are less than 30 minutes old, then dont pull data from database, just pull data from session.

so your class for queriesNoSql might look like this:
class queriesNoSql extends dbClass{
function t1_join_t2(){
$t1 = $this->getTable('t1','my_db'); //defined in dbClass
$t2 = $this->getTable('t2','my_db');
return joinArrays($t1,'index_1',$t2, 'index_2', 'sort_asc_t1_weight'); //defined in dbClass
}
}

vs.

class queriesSql{
function t1_join_t2(){
$this->getTable('t1','my_db');//defined in dbClass...puts new version into session;
$this->getTable('t2','my_db');

$sql = 'SELECT * FROM t1 LEFT JOIN t2 ON (t1.index_1 = t2.index_2)';
return $this->query($sql,'my_db'); //defined in dbClass
//OR do joinArrays($t1,'index_1',$t2, 'index_2', 'sort_asc_t1_weight');

//running the join in MYSQL prolly better given the
}
}

my dbClass checks every getTable call for if the table in session is good. If it is, it sends that. If it isnt, it creates a db connection and runs the query to get the table.

Hope this helps