Overview and Goals: Hopefully to become one single centralized API for accessing any type of database connection including MySQL, MS SQL, ODBC, etc.
The purpose of the class is to work with databases using entirely associative arrays. The hope being that time in development will be saved by NOT having to convert from strings or database objects, at anytime during the database query process.
Examples:
1.Instead of: UPDATE table SET col1=val1, col2=val2, etc ...You can pass an assoc array of key/value pairs to the class and tell it to use the update action.
2.Instead of using “mysql_fetch_assoc($qry_result)” to extract a result object to an array, the result is already in an array.
The class supports Select queries on all afore mentioned databases, and has also been tested with UPDATE, DELETE, and INSERT actions on MySQL.
Defaults: There are certain things that the class takes as default...
1.It assumes that you are working with “mysql” database type unless specified
2.It assumes that you are handling the database connection outside of the class, unless you set the second argument to 'true' telling the class to handle the actual connection.
CODE EXAMPLES:
////////////////////
//Example 1: Simple Select w optional debugging
//return results in 2-dim Assoc Array
////////////////////
require(“class.ArrayQry.php”);
//Define DB Connection Info
$db_creds = array(
'db' => "test",
'user' => "uname",
'pw' => "mypass",
'host' => "localhost"
);
$qry = new ArrayQry($db_creds, true);//2nd arg set true telling class to handle database connection
$qry->debug(); //optional
$qry->set_conn_type(“mysql”); //optional in this case since mysql is default.
$qry->set_qry("SELECT * FROM contacts");
$rows = $qry->execute(“s”); //executing 's' is for select
print_r($rows);
////////////////////
//Example 2: Insert Record
//Inserts an Assoc Array into DB using the keys as column names and values as column data
////////////////////
$insert_info = array(
'fname' => "Tater",
'lname' => "Salad",
'phone' => "555-5555",
'email' => "me@mine.com"
);
$qry = new ArrayQry($db_creds, true);
$qry->debug(); //optional
$qry->set_tbl("contacts"); //sets working db table
$qry->set_update_array($insert_info);//pass the array of data into class
$qry->execute(“i”);//all info now gathered execute insert
////////////////////
//Example 3: Update Record
//Inserts an Assoc Array into DB using the keys as column names and values as column data
//NOTE: you have to set a 'where' clause so the db knows which record to update
////////////////////
$update_info = array(
“phone” => "222-2222",
);
$qry = new ArrayQry($db_creds, true);
$qry->set_tbl("contacts"); //sets working table
$qry->set_update_array($update_info);//the array of data
$qry->set_where("WHERE id=`9`"); //tell the class which record to update
$qry->execute(“u”);
////////////////////
//Example 4: Delete Record
////////////////////
$qry = new ArrayQry($db_creds, true);
$qry->set_qry("DELETE FROM contacts WHERE id=`12`");
$qry->execute(“d”);
Working with Multiple queries in a single connection:
/*
*Assume you needed to execute Multiple Queries in single connection, such as multiple
*updates or inserts....You DO NOT want the class to automatically handle the connection
*since this would add a lot of overhead continuously opening and closing the DB connection.
*You would need to manually handle the connection. There are 2 ways to do this:
*/
//Sample data array for the next 2 examples:
$insert_info = array(
0 =>array(
'fname' => "Ham",
'lname' => "Burgler",
'phone' => "555-5555",
'email' => "me@mine.com"
),
1 =>array(
'fname' => "Frank",
'lname' => "Enstein",
'phone' => "555-5555",
'email' => "me@mine.com"
),
2 =>array(
'fname' => "Pete",
'lname' => "Repeat",
'phone' => "555-5555",
'email' => "me@mine.com"
)
);
////////////////////
//Example 5: Multiple Insert with single database connection handled OUTSIDE of the class
////////////////////
/*
This example shows how YOU CAN define your connection info outside the class, if desired. This is preferred when performing multiple querys for performance:
*/
$conn = mysql_connect($db_creds[“host”], $db_creds[“user”], $db_creds[“pw”])or die("Unable to connect to MYSQL because: ".mysql_error());
$db_select = mysql_select_db($db_creds[“db”],$conn) or die("Could not select ".$db_creds[“db”]." database because: ".mysql_error());
$qry = new ArrayQry();//note how no params needed since connection is already made
$qry->set_tbl("contacts"); //the working table to be affected
foreach($insert_info as $arr){
$qry->set_update_array($arr);//the array of data
$qry->execute(“i”); //which action to execute
}
/*
If you open the db conection outside the class you should also close it when finished, else it is not necessary if class is handling connection
*/
mysql_close($conn);
////////////////////
//Example 5-A: Multiple Insert with single database connection handled MANUALLY INSIDE
// of the class. This is just an alternative method of example 5 with less code
////////////////////
$qry = new ArrayQry($db_creds, false);//the false param tells the class not to automatically open and close the connection
$qry->set_conn_type("mysql");
$qry->setup_conn(); //manually opening the connection will keep it open till closed manually
$qry->set_tbl("contacts"); //the working table to be affected
foreach($insert_info as $arr){
$qry->set_update_array($arr);//the array of data
$qry->execute(“i”); //which action to execute
}
$qry->close_conn(); //manually closing the connection after all queries executed
Working with other databases:
MS SQL:
This functions much the same as working with MySQL, but you have to ensure you tell the class that it is a MS connection to expect:
////////////////////
//Example 6: Select on Remote MS SQL database (using php mssql api)
////////////////////
$qry = new ArrayQry($db_creds, true);
$qry->set_conn_type(“mssql”); //required since this is not the default connection type
$qry->set_qry("SELECT * FROM contacts");
$rows = $qry->execute(“s”);
print_r($rows);
////////////////////
//Example 7: Select on Remote MS SQL database (using ODBC)
//NOTE: Assumes Windows data connection manger ODBC was setup as needed...
//this example could be used for connecting to CIS (a scenario where the server is running
//MSSQL but only odbc connectivity is available or php's mssql api is not avaialable)
////////////////////
$qry = new ArrayQry($db_creds, true);
$qry->set_conn_type(“odbc”);
$qry->set_odbc_driver(“SQL Server”); //always specify the driver on odbc queries
$qry->set_qry("select * from tbl_1 where field20=`WAITING` and field21=`whatever`");
$rows = $qry->execute(“s”);
////////////////////
//Example 8: Simple Select on proprietary Timberline database (odbc-based)
//Timberline uses Pervasive database but allows ODBC connectivity with mapped
//network drives to access data sources... this example shows how to pass
//proprietary ODBC driver info, DSN and DBQ info into class for access
////////////////////
$qry = new ArrayQry($db_creds, true);
$qry->set_conn_type(“timb”);
$qry->set_odbc_driver(“Timberline Data”);//always specify the driver on odbc queries
$qry->set_odbc_dsn(“Timberline MyCompany”);
$qry->set_odbc_dbq(“T:\MyCompany”);
$qry->set_qry("select * from tbl_1 where field20=`WAITING` and field21=`whatever`");
$rows = $qry->execute(“s”);
When all else fails:
Obviously this class can't do everything...for example, support for inserting array info on table join queries doesn't exist yet. At this time the class only officially supports working with single table queries (you could work with multiple tables, but it would require multiple queries...). So if you would still like to take advantage of the classes centralized connection capabilites you could execute a “raw” query, instead of the built in 's', 'i', 'u', 'd' query types. NOTE: this could be dangerous, but will allow you to form a query not necessarily supported by the class. You should still always use the 's' query execution on basic select queries so that you get the result in the standard array to save programming...but, if you need to run a query like and insert or update which joins tables, you have the options to write out your query and execute it as “raw”:
////////////////////
//Example 9: Sending a “raw” query, when built in options are not available
//NOTE: in this example the default mysql connection will be used and therefore not be
//specified...will return T or F as the query reports
////////////////////
$qry = new ArrayQry($db_creds, true);
$qry->set_qry("UPDATE contacts WHERE myBigA$$Qry=`bunch of tables to manipulate` AND might contain joins");
$qry->execute(“raw”);
How to display your your results to an HTML table:
////////////////////
//Example 10: Simple Select AND display results directly to HTML Table (also contains //formatting options). Note that the table produced will contain classes and Ids in the tags
// to apply your own CSS. It will also contain a blank “tfoot” row so you can throw basic
//javascript totals onto numeric columns. There are many formatting options for working
//with tables, including adding hyperlinks to cell values and adding inline css.
////////////////////
$qry = new ArrayQry($db_creds, true);
$qry->set_conn_type(“mysql”); //optional in this case since mysql is default.
$qry->set_qry("SELECT * FROM contacts");
$qry->execute(“s”); //executing 's' is for select, result array stored in the qry object
$qry->set_template_cols_array(array('ContactID'=>'<b><a href="myContactsReport.php?id=[objectid]” title="Manage Record [objectid]">[objectid]</a></b>'); //in this example converts the 'ContactID' column to hyperlinks to the associatted href, then replaces the '[objectid]' template tag with the objectid value of the same record.
$qry->set_mailto_cols_array(array('email')); //converts emails to 'mailto:' hyperlinks in 'email' column
$qry->set_tel_cols_array(array('cell','ph')); //converts phone nums to hyperlinks in 'cell' and 'ph' columns...only works with 10 digit USA phone number format
$qry->set_custom_tbl_headers(array('ID', 'Email', 'First', 'Last', 'Cell', 'Phone')); //optional...if custom headers are not defined, the class will use the field names from the qry result
$qry->set_tbl_col_widths(array('15', '45', '35', '35', '25', '25')); //optional...Will hard-code the width attribute into the cells
$qry->set_conditional_display_values(array('phone'=>array('cond' => '==', 'compare' => 'NA', 'showT' => 'None Entered'))); //optional...Allows you to change display values. Useful if a column returns 0 or 1 and you wish to display that as True or False. In this example: if the 'phone' value is equal to (==) 'NA' ...change the value to display 'None Entered'.
$qry->set_conditional_cell_css(array('first'=>array('cond' => '==', 'compare' => 'Mark', 'showT' => 'color:green;', 'showF' => 'color:red;'))); //optional...Allows you to add inline style. Useful if a column returns a value you wish to emphasize. In this example: if the 'first' name cols value is equal to (==) 'Mark', add inline style for green color else add red color:
$tbl = $qry->output_result('tbl'); //you could pass an array of column names you wish to omit from the HTML display into this function.
echo $tbl;
Paginating HTML Table Output
////////////////////
//Example 10-A: Simple Select AND paginate results to HTML Table .
// I find it is better for performance and class simplicity to handle Pagination OUTSIDE of the class
//It also gives you more control over your paging format....Note that all formatting options in the ex 10 apply.
//This is just a referance on how you can handle pagination
////////////////////
/*
gather variables for paging
*/
$page = (isset($_GET["page"])) ? $_GET["page"] : 1;
$records_per_page = 100;
$start_from = ($page-1) * $records_per_page;
/*
Define Queries
*/
$full_qry = "SELECT id,CompanyID,Name,Trade,DivisionID FROM company WHERE Type='Subcontractor' ORDER BY Name ASC";//full qry is only to grab total records available
$page_qry = $full_qry . " LIMIT $start_from, $records_per_page";//page qry only returns relevant matches
/*
Start class
*/
$qry = new ArrayQry($db_creds, FALSE);
$qry->set_conn_type(“mysql”); //optional in this case since mysql is default.
$qry->setup_conn(); //handle connection manually for mult-queries
/*
First run full query and get total records for paging
*/
$qry->set_qry("$full_qry");
$ttl_rows = $qry->get_num_rows();
$total_pages = ceil($ttl_rows / $records_per_page);
/*
Now run Paging query for display results
*/
$qry->set_qry("$page_qry");
$qry->execute(“s”);
$tbl = $qry->output_result('tbl');
$qry->close_conn(); //manually closing the connection after all queries executed
/*
Now Display the table with paging links
*/
echo $tbl;
echo "Total Records Found: ".$ttl_rows."<hr>";
for ($i=1; $i<=$total_pages; $i++) {
echo "<a href='".basename(__FILE__)."?page=$i'>$i</a> ";
};
How to dump select results to CSV:
////////////////////
//Example 11: Simple Select AND dump to CSV format
////////////////////
$qry = new ArrayQry($db_creds, true);
$qry->set_conn_type(“mysql”); //optional in this case since mysql is default.
$qry->set_qry("SELECT * FROM contacts");
$qry->execute(“s”); //executing 's' is for select, result array stored in the qry object
$csv = $qry->output_result('csv'); //you could pass the delimeter, and whether the first row is headers or not to this function...by default: Delimeter = '|', and Header row = true
echo $csv;
How to dump select results to XML:
////////////////////
//Example 12: Simple Select AND dump to XML format
////////////////////
$qry = new ArrayQry($db_creds, true);
$qry->set_conn_type(“mysql”); //optional in this case since mysql is default.
$qry->set_qry("SELECT * FROM contacts");
$qry->execute(“s”); //executing 's' is for select, result array stored in the qry object
$xml = $qry->output_result('xml');
echo $xml;
How to dump select results to JSON:
////////////////////
//Example 13: Simple Select AND dump to JSON format
////////////////////
$qry = new ArrayQry($db_creds, true);
$qry->set_conn_type(“mysql”); //optional in this case since mysql is default.
$qry->set_qry("SELECT * FROM contacts");
$qry->execute(“s”); //executing 's' is for select, result array stored in the qry object
$json = $qry->output_result('json');
echo $json;