i've got a good php class i use to contact myql rom flash. it is quite useful
<?
class DBAccess {
/************************************************** ***********************
original script by
TODO Fahad Pervez 30-5-2003
edited by
Oliver Lillie 13-9-2003
NOTE ON USE :
Remember that if you are not familar with using classes, or mysql
you will have to create a new class and to initilize the functions
and then to be able to work with mysql you have to run the 'connectToDB'
connection function. Also remember that when you have finished using the
mysql functions close the connection. See the example below
// intialize the class
$db = new DBAccess();
// connect to the db
$db->connect("localhost", "root", "password", "test");
// modify a record
$db->ModifyRecord("users", "firstname", "Oliver", "lastname", "Reynolds");
// disconnect the db
$db->disconnect();
************************************************** ***********************/
// setup class vars
var $DBlink;
var $Result;
var $error;
// FUNCTION
//
// Connects the script to your database, and keeps the connection
// opened untill closed by "disconnect"
//
// ARGUMENTS
//
// $host = the mysql host name
// $user = the username for the databse
// $pass = the password for the username to access the db
// $table = the table of the db that you wish to use
//
// USAGE
//
// $db->connect("localhost", "root", "password", "test");
//
// RETURNS
//
// true if the connection to the database and table is made
// false if either the database is not connected to or if the
// table doesn't exist
//
function connect($host, $user, $pass, $table){
$this->$DBlink = mysql_pconnect( "$host", "$user", "$pass" );
if (!$this->$DBlink){
$this->error="Could not connect to mysql";
return false;
}
if(!mysql_select_db( "$table", $this->$DBlink)){
$this->error="Couldn't connect to database : ".mysql_error();
return false;
}
return true;
}
// FUNCTION
//
// function to check the existance of a particular record
//
// ARGUMENTS
//
// $table = the table on which to operate
// $field = the field name in that table
// $value = the value of that field to be checked
//
// USAGE
//
// $db->IsRecordExists("users", "firstname", "Oliver");
//
// the above example will check that for a value "Oliver"
// to exist in the field "firstname" in thetable "users"
//
// RETURNS
//
// returns true if record exists otherwise returns false
//
function IsRecordExists($table, $field, $value){
$this->Result = mysql_query ( "SELECT * FROM $table WHERE $field='$value'" , $this->$DBlink );
if ( ! $this->Result ){
// databse connection error
$this->error="getRow fatal error: ".mysql_error();
return false;
}
if(mysql_fetch_array( $this->Result )!=null){
// record exists
return true;
}
// record does not exists
return false;
}
// FUNCTION
//
// checks fields against each other for a match
//
// ARGUMENTS
//
// $table = the table name
// $field and $field1 = field names in the table
// $value and $value1 = field values to be checked
//
// USAGE
//
// $db->CheckUser("users", "'email','worker_id','password'", "'
[email protected]','xyz001','mega'");
//
// RETURNS
//
// returns true if a both both values are found in their
// corresponding values, otherwise returns false
//
// function CheckUser($table, $field, $value, $field1, $value1){
function CheckUser($table, $fields, $values){
$fields_ar = explode(",", $fields);
$values_ar = explode(",", $values);
// length of values array
$len = count($fields_ar);
$str = "";
for($i = 0; $i < $len; $i++){
$str .= substr($fields_ar[$i], 1, -1) . "=" . $values_ar[$i];
if($i < $len-1) {
$str .= "&&";
}
}
$query = "SELECT * FROM $table WHERE $str" ;
$this->Result = mysql_query ( $query , $this->$DBlink );
if ( ! $this->Result ){
$this->error="getRow fatal error: ".mysql_error();
return false;
}
if(mysql_num_rows( $this->Result )==1){
return true;
}
return false;
}
// FUNCTION
//
// same as GetTotalNumberOfRecords but can contain multiple arguments
//
// ARGUMENTS
//
// $table = the table
// $array = the fields and values array
// = array(array(field, value), array(field, value)....)
//
// USAGE
//
// $db->GetTotalNumberOfRecords("users", array(array("firstname","Oliver"), array("age", "23")));
//
// it will return total number of users that are 18 years old and have the firstname Oliver
//
// RETURNS
//
// returns the number of matches else on failure returns false
//
function GetTotalNumberOfRecords($table, $array){
$len = count($array);
for($i = 0; $i < $len; $i++){
$where .= $array[$i][0] . "='" . $array[$i][1] . "'";
if($i>=0 && $len>1 && $i < $len-1){
$where .= "&&";
}
}
$this->Result = mysql_query ( "SELECT * FROM $table WHERE $where" , $this->$DBlink );
if ( ! $this->Result ){
$this->error="getRow fatal error: ".mysql_error();
return false;
}
return mysql_num_rows( $this->Result );
}
// FUNCTION
//
// looks for all the field and value matches and then sorts them and returns
// the required evaluation of the returns results
//
// ARGUMENTS
//
// $table = the table name
// $field = the field name
// $value = the value
// $required = max(xxx) or min(xxx) or sum(xxx) (php evaluation funcs)
//
// USAGE
//
// $db->GetRecordByCriteria("users", "firstname", "Oliver", "max(age)");
//
// will return the maximum age of all the users with the firstname of
// "Oliver"
//
// RETURNS
//
// returns the $required value of the all the $fields that contain the $value
// or returns false on failure
// $string = $field='$value'
function GetRecordByCriteria($table, $string, $required){
$query = "SELECT $required FROM $table WHERE $string";
$this->Result = mysql_query ( $query , $this->$DBlink );
if ( ! $this->Result ){
return false;
}
while($row = mysql_fetch_array( $this->Result, MYSQL_ASSOC )){
$back = $row;
}
return $back;
}
// FUNCTION
//
// returns the highest required value from the required field in the table
//
// ARGUMENTS
//
// $table = the table name
// $required = max(xxx) or min(xxx) or sum(xxx) (php evaluation funcs)
//
// USAGE
//
// $db->GetRecordByCriteria_simple("users", "max(userID)");
//
// returns with the maximum userID from the table of users
//
// RETURNS
//
// returns the required value on success and returns false on failure
//
function GetRecordByCriteria_simple($table, $required){
$this->Result = mysql_query ( "SELECT $required FROM $table " , $this->$DBlink );
if ( ! $this->Result ){
return false;
}
while($row= mysql_fetch_array( $this->Result )){
$back = $row["$required"];
}
return $back;
}
// FUNCTION
//
// simply inserts record in the table and return the insert id of
// autoincrement field (if any)
//
// ARGUMENTS
//
// $table = the table name
// $insert = the group of fields in which to insert data
// $values = the group of respective values of the fields
//
// USAGE
//
// $db->InsertRecord("users","`firstname`,`lastname`","'Oliver','Lillie'");
//
// RETURNS
//
// returns insert id on success and returns false on failure
//
function InsertRecord($table, $insert, $values){
$sql = "INSERT INTO $table ($insert) VALUES ($values);";
$this->Result = mysql_query( $sql , $this->$DBlink);
return mysql_insert_id( $this->$DBlink);
}
// FUNCTION
//
// same as GetSingleField but requires multiple conditions
//
// ARGUMENTS
//
// $table = the table name
// $array = the fields and values array
// = array(array(field, value), array(field, value)....)
// $required = the required field name
//
// USAGE
//
// $db->GetSingleField("users", array(array("firstname","Oliver"), array("email", "
[email protected]")), "PASSWORD");
//
// returns with the password only if the firstname is Oliver and the email is
// ollie at buggedcom dot co dot uk
//
// RETURNS
//
// returns the contents of the $required field. On failure returns false
//
function GetSingleField($table, $array, $required){
$len = count($array);
for($i = 0; $i < $len; $i++){
$where .= $array[$i][0] . "='" . $array[$i][1] . "'";
if($i>=0 && $len>1 && $i < $len-1){
$where .= "&&";
}
}
$query = "SELECT * FROM $table WHERE $where";
$this->Result = mysql_query ( $query , $this->$DBlink );
if ( ! $this->Result ){
return false;
}
while($row= mysql_fetch_array( $this->Result )){
$back = $row["$required"];
}
return $back;
}
// FUNCTION
//
// same as GetSingleField but used in a case when it is expected that more than
// one record can exist on a single condition so records are manipulated as an
// array of records
//
// ARGUMENTS
//
// $table = the table name
// $field = the field name
// $value = the respective value
// $required = the required field name
//
// USAGE
//
// $db->ArrayOfSingleField("users", "firstname", "Oliver", "email");
//
// it is obvious that more than one users firstname can be Oliver so we manipulate
// the returned email addresses as an array of results
//
// RETURNS
//
// returns array on success and returns false on failure
//
function ArrayOfSingleField($table, $field, $value, $required){
$this->Result = mysql_query ( "SELECT * FROM $table WHERE $field='$value'" , $this->$DBlink );
if ( ! $this->Result ){
$this->error="getRow fatal error: ".mysql_error();
return false;
}
while($row= mysql_fetch_array( $this->Result )){
$RecArray[] = $row["$required"];
}
return $RecArray;
}
// FUNCTION
//
// custom modifier----modifies records passed in query
//
// ARGUMENTS
//
// $Cquery = a custom sql query string
//
// USAGE
//
// $db->CustomModify("INSERT INTO users (`firstname`,`lastname`) VALUES ('Oliver','Lillie');");
//
// allows you to create your own query string without all the extra coding
//
// RETURNS
//
// returns true on success and returns false on failure
//
function CustomModify($Cquery){
$query=$Cquery;
$this->Result = mysql_query($query, $this->$DBlink);
if (! $this->Result){
return false;
} else {
return true;
}
}
// FUNCTION
//
// same as ModifyRecord but uses multiple conditions and only modifies only one record
//
// ARGUMENTS
//
// $table = the table
// $array = the fields and values array
// = array(array(field, value), array(field, value)....)
// $modify_field = the modifer field name
// $modify_value = the modifier field value
//
// USAGE
//
// $db->ModifyRecord("users", array(array("firstname", "Oliver"), array("lastname", "Lillie")), array(array("PASSWORD", "1234567890")));
//
// Modifies the user, with the first name "Oliver" and lastname "Lillie", changes the
// password to 1234567890
//
// RETURNS
//
// on success returns true otherwise returns false
//
function ModifyRecord($table, $array, $mod_array){
$len = count($array);
$where = $modify = "";
for($i = 0; $i < $len; $i++){
$where .= $array[$i][0] . "='" . $array[$i][1] . "'";
if($i>=0 && $len>1 && $i < $len-1){
$where .= "&&";
}
}
$len2 = count($mod_array);
for($ii = 0; $ii < $len2; $ii++){
$modify .= $mod_array[$ii][0] . "='" . $mod_array[$ii][1] . "'";
if($ii>=0 && $len2>1 && $ii < $len2-1){
$modify .= "&&";
}
}
$query="UPDATE $table set $modify WHERE $where";
$this->Result = mysql_query($query, $this->$DBlink);
if (! $this->Result){
$this->error="updateOrg update error: ".mysql_error();
return false;
}
return true;
}
// FUNCTION
//
// Deletes a particular record by providing two conditions
//
// ARGUMENTS
//
// $table = the table
// $array = the fields and values array
// = array(array(field, value), array(field, value)....)
//
// USAGE
//
// $db->DeleteRecord("users", array(array("firstname", "Oliver"), array("lastname", "Lillie")));
//
// deletes the row that contains the firstname Oliver and lastname Lillie.
// Note if more than one match is found all of the matches will be deleted
//
// RETURNS
//
// returns true on success and returns false on falilure
//
function DeleteRecord($table, $array){
$len = count($array);
for($i = 0; $i < $len; $i++){
$where .= $array[$i][0] . "='" . $array[$i][1] . "'";
if($i>=0 && $len>1 && $i < $len-1){
$where .= "&&";
}
}
$this->Result = mysql_query("DELETE FROM $table WHERE $where", $this->$DBlink);
if (! $this->Result){
return false;
}
return true;
}
// FUNCTION
//
// Similar to CustomModify but returns with data
// this function performs any kind of query passed
// preferably queries with 'SELECT' are encouraged
//
// ARGUMENTS
//
//
// USAGE
//
// $db->CustomQuery("SELECT * FROM users");
//
// RETURNS
//
// if there is no record set generated by the query this functon
// will return 'null' otherwise it will return a two dimension array
//
// given that we have four fields in the table, firstname, lastname, and PASSWORD
// the follwing will be returned
//
// for first record----> $result_[0]['firstname'],$result_[0]['lastname'],$result_[0]['PASSWORD']
// for second record----> $result_[1]['firstname'],$result_[1]['lastname'],$result_[1]['PASSWORD']
// etc
//
function CustomQuery($Query_C){
$Return_Result[]=NULL;
$Count=0;
$Query = "$Query_C";
$Show_Query_Reuslt = mysql_query($Query, $this->$DBlink) or die(mysql_error());
$Query_Result_Final = mysql_fetch_assoc($Show_Query_Reuslt);
// checking that if there is no result
// if no result mysql fetch assoc array size is == 1
if(sizeof($Query_Result_Final)==1){
return NULL;
}
do{
$Return_Result[$Count]=$Query_Result_Final;
$Count++;
}while($Query_Result_Final=mysql_fetch_assoc($Show _Query_Reuslt));
return $Return_Result;
}
// FUNCTION
//
// drops the connection to the database
//
// ARGUMENTS
//
// none
//
// USAGE
//
// $db->DBDisconnect();
//
// RETURNS
//
// nothing
//
function disconnect(){
if(!$this->Result){
}else{
// mysql_free_result($this->Result);
}
mysql_close($this->$DBlink);
}
// FUNCTION
//
// simple mail function to provide a bug tracking
//
// ARGUMENTS
//
// $line = the line number of the error
// $file = the file the error occurred in
// $error = the error
//
// USAGE
//
// $db->ReportBug(234, "retrieve.php", $error);
//
// RETURNS
//
// nothing
//
function ReportBug($line,$file,$error){
$mTo="
[email protected]";
$mSubject="DB Error";
$mMessage="Error on line: ".$line."<br>Error in file: ".$file."<br> Error: ".$error;
$mFrom="
[email protected]";
mail($mTo, $mSubject, $mMessage, "MIME-Version: 1.0\r\nContent-type: text/html; charset=iso-8859-1\r\nFrom: $mFrom\r\nX-Priority: 1 (Highest)" );
}
}
/*
$db = new DBAccess();
$result = $db->connectToDB("localhost", "root", "mega", "motorola");
$result = $db->ModifyRecord("register", array(array("user_id", "0")), array(array("user_id", "1")));
$db->DBDisconnect();
*/
// echo "test call";
?>