I was coming up with a way to get table information and one thing led to another and I ended up creating something really neato. I call it variable variable functions, and the easiest way to get simple results from a database. I have not put this into any "real" projects I do but I had fun messing around with it and I could see how it could be molded into somthing more useful.
The first half is just a sql class i made and threw into the page so I didn't have to work with multiple files to demo, easy to copy for demoing...
The only part you need to change is the database connections values at the top of the script and the variable names at the bottom of the script, a comment or two should say what needs to be done.
All in all, you can query all or some the results from a database table by CREATING A VARIABLE, THATS IT! Well a "variable variable function".
To get all the results from a table all you need to do is:
print_r($select_mx_rpg_user());
If you need to select one with an id just throw that id in the paramter, it will auto select your primary field (assuming its simple / you only have 1 in that table).
<?php
define('DB_HOST', 'localhost'); //Database Host
define('DB_USER', 'root'); //Database User Name
define('DB_PASSWORD', ''); //Database User Password
define('DB_NAME', 'mx_rpg'); //Database Name
define('DB_TYPE', 'mysql'); //Database Type: mysql or odbc
#----------------------------------------------------------------------------------
// START MYSQL
#----------------------------------------------------------------------------------
/**
$sql = new sql(DB_HOST,DB_NAME,DB_USER,DB_PASSWORD);
$sql->go($sql_query) # Execute query
$sql->fetchArray() # Get one result back OR loop through the function using while($var = $sql->fetchArray()) to get all results
$sql->fetchAll() # Get all results in an array $var[#][table_column]
$sql->numRows() # Number of results from the query
$sql->effected_rows() # Number of effected rows from the query
$sql->lastId() # last insert id
$sql->clearResult() # clear result in class
$sql->close() # Close db connection
**/
class sql
{
var $db_link;
var $dbname;
var $result;
var $mysql_flag = MYSQL_BOTH; // MYSQL_BOTH or MYSQL_ASSOC or MYSQL_NUM
var $use_log = '';
//Sets up database link using variables from a config file
function sql( $dbhost = '',$dbname = '',$dbuser = '',$dbpassword = '' )
{
$this->use_log .= 'sql:';
sql::editLog("Connecting to: [$dbname] Host: [$dbhost] User: [$dbuser]");
// If no values sent use the config default connection values
$dbhost = empty( $dbhost ) ? DB_HOST : $dbhost;
$dbname = empty( $dbname ) ? DB_NAME : $dbname;
$dbuser = empty( $dbuser ) ? DB_USER : $dbuser;
$dbpassword = empty( $dbpassword ) ? DB_PASSWORD : $dbpassword;
// Set the classes database name
$this->dbname = $dbname;
$this->db_link = @mysql_connect( $dbhost, $dbuser, $dbpassword, true ) or die( 'Error: Could Not Connect To Database'.mysql_error() );
@mysql_select_db( $this->dbname, $this->db_link ) or die( 'Error: Could Not Select Database'.mysql_error() );
}
function showLog()
{
$this->use_log .= "showLog()"."<br />\n";
return $this->use_log;
}
function editLog( $content = NULL )
{
$this->use_log .= ' ' . $content . "<br />\n";
}
// Preform any sql query
function go( $query = NULL, $ref = 0 )
{
$this->use_log .= 'go:';
if( $query != NULL )
{
$this->result[$ref] = @mysql_query( $query, $this->db_link ) or die('Error: Database Query Error<br><br>'.mysql_error());
sql::editLog("q: [$query] ref: [$ref]");
return $this->result[$ref];
} else
{
sql::editLog("q: [$query] ref: [$ref] -> EMPTY QUERY");
return false;
}
}
// Return array with one result
function fetchArray( $ref = 0 )
{
$this->use_log .= 'fetchArray:';
if( isset( $this->result[$ref] ) && !( empty( $this->result[$ref] ) ) )
{
sql::editLog("ref: [$ref]");
return @mysql_fetch_array( $this->result[$ref], $this->mysql_flag );
}
else
{
sql::editLog("ref: [$ref] -> ERROR: Reference Not Found");
return false;
}
}
// Return an array with all the results
function fetchAll( $ref = 0 )
{
$this->use_log .= 'fetchAll:';
if( isset( $this->result[$ref] ) && !( empty( $this->result[$ref] ) ) )
{
$x = 0;
$result = array();
while( $a = @mysql_fetch_array( $this->result[$ref], $this->mysql_flag ) )
{
$result[$x] = $a;
$x++;
}
sql::editLog("ref: [$ref]");
return $result;
} else
{
sql::editLog("ref: [$ref] -> ERROR: Reference Not Found");
return false;
}
}
// Number of rows returned from last called query
function numRows( $ref = 0 )
{
$this->use_log .= 'numRows:';
if( isset( $this->result[$ref] ) && !( empty( $this->result[$ref] ) ) )
{
sql::editLog("ref: [$ref]");
return @mysql_num_rows( $this->result[$ref] );
} else
{
sql::editLog("ref: [$ref] -> ERROR: Reference Not Found");
return false;
}
}
// Number of affectedrows returned from last called query
function affectedRows( $ref = 0 )
{
$this->use_log .= 'affectedRows:';
if( isset( $this->result[$ref] ) && !( empty( $this->result[$ref] ) ) )
{
sql::editLog("ref: [$ref]");
return @mysql_affected_rows( $this->result[$ref] );
} else
{
sql::editLog("ref: [$ref] -> ERROR: Reference Not Found");
return false;
}
}
// Return the last queries insert id
function lastId()
{
return @mysql_insert_id();
}
}
$sql = new sql(DB_HOST,DB_NAME,DB_USER,DB_PASSWORD);
#----------------------------------------------------------------------------------
// END MYSQL
#----------------------------------------------------------------------------------
class tables
{
var $tables = array();
function tables()
{
// Globalize database object
global $sql;
// Only show names for keys
$sql->mysql_flag = MYSQL_ASSOC;
// Get table information
$sql->go("SHOW TABLE STATUS");
$table_data = $sql->fetchAll();
// Loop through tables
foreach($table_data as $row)
{
$this->tables[$row['Name']] = $row;
// Get column information for current table
$sql->go("SHOW COLUMNS FROM {$row['Name']}",'column');
$col_data = $sql->fetchAll('column');
$pri_key = '';
// Add columns to table array and make a array to hold all primary keys in the table
foreach($col_data as $col_row)
{
$this->tables[$row['Name']]['Column'][$col_row['Field']] = $col_row;
// Define the primary keys
if($col_row['Key'] == 'PRI')
{
$this->tables[$row['Name']]['Primary_Keys'][] = $col_row['Field'];
$pri_key = " AND {$col_row['Field']} = '{\$pri_key}'";
}
}
# START - Dynamic variable function loaders
// These only work with a single table, no joining (well you could but you wouldn't be able to retreive any of the data from the joined tables ;) )
// Simple mode, works on 1 required Primary Key
// Send in the primary key id
// $get_*table_name*($idtolookup)
$auto_function = create_function('$pri_key = \'-1\'',
"
global \$sql;
\$sql->go(\"SELECT * FROM {$row['Name']} WHERE 1=1 {$pri_key}\");
return \$sql->fetchAll();
");
$_ENV['get_'.$row['Name']] = $auto_function;
// Advanced mode, you define what columns to lookup for table
// Send in option lookup parameter
// $select_*table_name*('user_id = 5')
// $select_*table_name*()
// $select_*table_name*('user_id = 5 AND char_id = 2')
$where = '';
$auto_function = create_function('$where = \'\'',
"
global \$sql;
\$where = (\$where == '') ? '' : 'WHERE '.\$where;
\$sql->go(\"SELECT * FROM {$row['Name']} {\$where}\");
return \$sql->fetchAll();
");
$_ENV['select_'.$row['Name']] = $auto_function;
# END - Dynamic variable function loaders
}
}
}
// create an object
$table = new tables();
// Create the dynamic function variables
foreach($_ENV as $a_key=>$a)
{
if(substr($a_key,0,3) == 'get' || substr($a_key,0,3) == 'sel')
{
// Variable Variable Functions... FUN FUN!
$$a_key = $a;
// Clean out function variables from the Environment array
unset($_ENV[$a_key]);
}
}
// --------------------------------------------------------------------
// CHANGE STUFF BELOW HERE TO FIT YOUR TABLE NEEDS/NAMES
// EXAMPLES!!!
// WHAT TO DO:
// Change the words after "get_" or "select_" to the name of the table you will be using
echo '<pre>';
echo '<hr>';
// You can do this if you have a SIMPLE primary key..
// Select all from "mx_rpg_user" WHERE *PrimaryKey* = '3'
print_r($get_mx_rpg_user('3'));
echo '<hr>';
// Can do this if you need to select on a more complex situation
// Select * from "mx_rpg_char" WHERE user_id = '3' AND char_id = '5'
print_r($select_mx_rpg_char("user_id = '3' AND char_id = '1'"));
echo '<hr>';
// Select all from the table, can it get much simpler?
// Select * from "mx_rpg_user"
print_r($select_mx_rpg_user());
echo '<hr>';
// Get all the tables and table info
print_r($table->tables);
echo '</pre>';
?>
Maybe you can see use for this, maybe not. It was just fun thinking of this and getting it to work. If you don't like it thats fine, just move onto the next post, no biggy, just sharing somthing I made and thought was unique because I have not seen this in anyone else code before (not saying someone hassn't already done this). Oh and it spits out all your tables table info with print_r($table->tables);
P.S. post was too long so had to delete a couple sql functions I wassnt using, hope I didn't break the snytax!
