PDA

View Full Version : MySQL and ActionScript


jterrell
10-02-2003, 10:41 PM
Is there a way to query a MySQL database using Actionscript WITHOUT using ColdFusion? I want to be able to access and store data within a MySQL database that my host server provides.

annexion
10-02-2003, 11:20 PM
You can use a plethora of different server side languages such as PHP, Perl, ASP...

In short, you can not interact with a MySQL database without a middle man, as it were.

Good luck.

buggedcom@work
10-03-2003, 04:53 AM
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";

?>

CyanBlue
10-03-2003, 05:14 AM
Hm... Can you show us some sample AS codes that interact with the PHP Class up there so that I can see what that thing does, buggedcom??? :D

buggedcom@work
10-03-2003, 05:36 AM
ok cb,

below are two codes, one is another simplified php script that uses the DBAccess class to check for user and password match, blow that is the as that i would use to communicate with it

// settings
include_once("includes/_config.php");

// classes
include_once("includes/class.db.php");

// table to access
$table = urldecode($_POST['table']);

$user = trim(urldecode($_POST['user']));
$password = trim(urldecode($_POST['password']));

// db connection
$db = new DBAccess();

// connect to the db with settings from _config.php
$db->connect($db_host, $db_user, $db_pass, $db_table);

// return existence of user
$exists = $db->CheckUser($table, "'user','password'", "'$user','$password'");

// diconnect from db
$db->disconnect();

if(!$exists){

// no user found
die("&match=false&completed=true& ");

}

// user exists
die("&match=true&completed=true& ");


/**********************************************
GLOBAL REFERENCE FOR CONNECTIONG TO THE DB
**********************************************/
_global.contactDB = function($file, $obj, $callback, $initfunc, $obj_array) {
$obj_array[0].sendToPHP($file, $obj, $obj_array[1], $callback, $initfunc);
};
/**********************************************
CONNECT TO PHP AND SEND DATA
xxx.sendToPHP(file, array, receiver, callback);
**********************************************/
LoadVars.prototype.sendToPHP = function($file, $obj, $receiver, $callback, $initfunc) {
$initfunc(false);
$receiver.onLoad = function(success) {
$initfunc(true);
$callback(this);
};
for (var $i in $obj) {
this[$i] = $obj[$i];
}
this.sendAndLoad($file, $receiver, "POST");
};
ASSetPropFlags(LoadVars.prototype, ["sendToPHP"], 1);
/**********************************************
SUBMIT EMAIL AND ID FOR CHECKING
**********************************************/
function submitLogin($user_id, $password) {
// get the password length
var $len = $password.length;
// check to see if the passwords have been entered
if ($password == "") {
displayError("Please enter your password");
return;
}
if ($len<5 && $len>10) {
displayError("Your password should be between 5 and 10 characters long");
return;
}
// email and id are valid and accounted for
// and therefore the function hasn't been broken by the
// return command so do below
// create loadvars for send data
$sender = new LoadVars();
// create loadvars for received data
$receiver = new LoadVars();
// contact database using the contact db function
contactDB("user.match.php", {table:"register", user_id:$user_id, password:$password}, processLogin, showConnection, [$sender, $receiver]);
}
/**********************************************
USAGE
**********************************************/
submitLogin(buggedcom, xxxxx);


It's fairly easy to use as you can see, however i would imagine there could be simpler ways, but for my first divulgence into php i don't think it's too shabby

CyanBlue
10-03-2003, 05:43 AM
It's fairly easy to use as you can seeHm... Don't forget to care about our mortals... :D

I'll check it out and see if I can understand the code as soon as I am done with what I am supposed to do... ;)