Full Dynamic MySQL Class

I recently made myself a dynamic class that will get all data from any given table. It took a fair amount of time to get my head round it and make it full proof, but this makes displaying contents from a database very easy. All you have to do is pass the query then understand how to output the results, it doesn’t matter whether you are just getting one field or all fields or just one row, it will handle whatever you through at it (within reason).

For examples sake the table has 4 fields: id | first_name | last_name | email

Now here is the class file:

[sourcecode language=”php”]
class MySQL {

//:::::::::::::::
//::: connect :::
//:::::::::::::::
function connect($host, $conn_username, $conn_password, $database_name){

//make the connection and select db
$connection = mysql_pconnect($host,$conn_username,$conn_password);
$select = mysql_select_db($database_name,$connection);
}

//:::::::::::::::::::::
//::: executeQuery :::
//:::::::::::::::::::::
function executeQuery($query){

//Execute the query
$result = mysql_query($query) or die(‘Error, query failed. ‘ . mysql_error());

return $result;

}

//:::::::::::::::::::::::
//::: getAllTableData :::
//:::::::::::::::::::::::
function getAllTableData($query) {

//execute the query
$result = $this->executeQuery($query);

//create field array
while ($property = mysql_fetch_field($result)){
$field_array[] = $property->name;
}

// now loop through field array
for ($i=0; $row = mysql_fetch_array($result); $i++) {

//assign multi_array with table fields as identifiers
for($y=0; $y < sizeof($field_array); $y++){ $multi_array[$i][$field_array[$y]] = $row[$y]; } } return $multi_array; } } [/sourcecode] Now the key to this class if you haven't noticed already is it assigns your data to multi dimensional arrays and the identifiers are the field names in your table. So you just need to know what fields you want, simple! Now the code you need to actually use the class is shown below. [sourcecode language="php"] // instantiate the mysql object and connect to the db $mysql = new MySQL(); $mysql->connect($host, $conn_username, $conn_password, $database_name);

//call the getAllTableData method
$my_table_data = $mysql->getAllTableData(“SELECT * FROM users”);

//now we can loop through and show the data
foreach ($my_table_data as $row){

echo $row[‘id’];
echo $row[‘first_name’];
echo $row[‘last_name’];
echo $row[’email’];

echo “

“;

}
[/sourcecode]

This makes the code so much more efficient as you never need to have separate functions spitting out different data and running different queries, you can do it all from one class and your logic is completely separated from your html making it very scalable.

You can even just request one field in your SQL statement and it will still work, assigning that field name to the multi dimensional array.

🙂