MySQL class

[Zebulon]

I'm trying to make a MySQL class for AMFPHP, it's based on ParkService example from http://amfphp.sourceforge.net/.

Here is what I get at the moment :

PHP Code:

<?
// Create new service for PHP Remoting as Class.
class MySQL {
    
    // MySQL connection variables
    var $host; 
    var $db; 
    var $user; 
    var $pass; 

    // Constructor calls parent constructor and sets return types of Methods.
    function MySQL() {
        $this->methodTable = array(
                        "select" => array(
                            "description" => "Read data from MySQL and pass back assoc array",
                            "access" => "remote", // available values are private, public, remote
                            "roles" => "role, list", // currently inactive
                            "arguments" => array ("fields", "table", "whereclause", "orderby", "start", "limit")
                        ),
                        "insert" => array(
                            "description" => "Insert data into MySQL and returns result flag",
                            "access" => "remote", // available values are private, public, remote
                            "roles" => "role, list", // currently inactive
                            "arguments" => array ("table", "fields", "values")
                        ),
                        "update" => array(
                            "description" => "Update data into MySQL and returns result flag",
                            "access" => "remote", // available values are private, public, remote
                            "roles" => "role, list", // currently inactive
                            "arguments" => array ("table", "fields", "values", "whereclause")
                        ),
                        "query" => array(
                            "description" => "Send a raw query to MySQL, return assoc array or result flag",
                            "access" => "remote", // available values are private, public, remote
                            "roles" => "role, list", // currently inactive
                            "arguments" => array("query")
                        )
        );
        // Initialize db connection 
        global $myhost, $mydb, $myuser, $mypass; 
        $this->host = $myhost; 
        $this->db = $mydb; 
        $this->user = $myuser; 
        $this->pass = $mypass; 
        $this->conn = mysql_connect($this->host, $this->user, $this->pass); 
        mysql_select_db ($this->db); 
    }
    
    // select requests results and passes them back as an
    // array of associative arrays, (which comes in as an object
    // containing objects in Flash)
    function select($fields = "*", $table = "", $whereclause = "", $orderby = "", $start = 0, $limit = 0) {
        // Error => No table specified
        if($table == "") return array("error" => "No input table specified");
        // SQL query
        if($whereclause != "") $sql_whereclause = "WHERE " . $whereclause;
        if($orderby != "") $sql_orderby = "ORDER BY " . $orderby;
        if(!($start == 0 && $limit == 0)) $sql_limit = "LIMIT " . $start . "," . $limit;
        $sql = "SELECT $fields FROM $table $sql_whereclause $sql_orderby $sql_limit";
        $result = @mysql_query($sql);
        // Results
        if(!$result) return array("error" => mysql_error());
        if(mysql_num_rows($result) <= 0) return array("empty" => 0);
        $returnObj = array();
        while ($row = mysql_fetch_array($result)) $returnObj[] = $row;
        return $returnObj;
    }
    
    // insert results and return number of affected rows (-1 on error)
    function insert($table = "", $fields = "", $values = "") {
        // Error => No table, fields or values specified
        if($table == "") return array("error" => "No input table specified");
        else if($fields = "") return array("error" => "No input fields specified");
        else if($values = "") return array("error" => "No input values specified");
        // SQL query
        $sql = "INSERT INTO $table ($fields) VALUES ($values)";
        $result = @mysql_query($sql);
        // Results
        if(!$result) return array("error" => mysql_error());
        $aff = mysql_affected_rows();
        if($aff < 1) return array("empty" => 0);
        else return array("result" => $aff);
    }
    
    // update results and return a flag
    function update($table = "", $fields = "", $values = "", $whereclause = "") {
        // Error => No table, fields or values specified
        if($table == "") return array("error" => "No input table specified");
        else if($fields = "") return array("error" => "No input fields specified");
        else if($values = "") return array("error" => "No input values specified");
        // SQL query
        $f = explode(",", $fields);
        $v = explode(",", $values);
        if(count($f) <= 1) $str_upd = $f[0] . " = " . $v[0];
        else{
            $sql_upd = array();
            for($i = 0 ; $i < count($f) ; $i++) $sql_upd[] = $f[$i] . " = " . $v[$i];
            $str_upd = implode(",", $sql_upd);
        }
        if($whereclause != "") $sql_whereclause = "WHERE $whereclause";
        $sql = "UPDATE $table SET $str_upd $whereclause";
        $result = @mysql_query($sql);
        // Results
        if(!$result) return array("error" => mysql_error());
        $aff = mysql_affected_rows();
        if($aff < 1) return array("empty" => 0);
        else return array("result" => $aff);
    }
    
    //send query to database
    function query($query = ""){
        if($query == "") return array("error"=> "No query specified");
        $query = trim($query);
        $sep = strpos($query, " ");
        $mode = strtoupper(substr($query, 0, $sep));
        $result = @mysql_query($query);
        // Results
        if(!$result) return array("error" => mysql_error());
        if($mode == "SELECT"){
            if(mysql_num_rows($result) <= 0) return array("empty" => 0);
            $returnObj = array();
            while ($row = mysql_fetch_array($result)) $returnObj[] = $row;
            return $returnObj;
        }
        else{
            $aff = mysql_affected_rows();
            if($aff < 1) return array("empty" => 0);
            else return array("result" => $aff);
        }
    }
}
?>

// Include the Required NetService class files
#include "NetDebug.as"
#include "NetServices.as"

var MySQLReply = new Object();
var gatewayurl = "http://host/gateway.php";


MySQLReply.resultDefault = function(result){
    trace("server responded: ");
    trace("Result received: '"+result+"' of type "+typeof (result));
    for (var item in result) {
        if(item == "error") return this.resultError(result);
        else if(item == "empty") return this.emptyResult(result);
        trace("    "+item+" => '"+result[item]+"' of type "+typeof (result[item]));
        for (var subitem in result[item]) {
            trace("        "+subitem+" => '"+result[item][subitem]+"' of type "+typeof (result[item][subitem]));
        }
    }
}
MySQLReply.emptyResult = function(result){
    trace("empty result: "+result["result"]);
    return result["result"];
}
MySQLReply.resultError = function(result){
    trace("error: "+result["error"]);
    return result["error"];
}
MySQLReply.query_Result = MySQLReply.resultDefault;
MySQLReply.select_Result = MySQLReply.resultDefault;
MySQLReply.insert_Result = MySQLReply.resultDefault;
MySQLReply.update_Result = MySQLReply.resultDefault;

// connect to the Flash Remoting service provider
if(isGatewayOpen == null){
    // do this code only once
    isGatewayOpen = true;
    // Make the Gateway connection
    NetServices.setDefaultGatewayUrl(gatewayurl);
    gatewayConnnection = NetServices.createGatewayConnection();
    MySQL= gatewayConnnection.getService("MySQL", this.MySQLReply);
    trace("Connected");
    // Call Web service
    MySQL.select("field1,field2,field3","mytable","field4 = 'value'","field5");
    //MySQL.query("SELECT * FROM mytable");
    trace("sent request");
}
stop();

Tell me what you think...

[freddycodes]

One note, you can generate a native Flash recordset from your mysql query by simply returning the mysql_query to flash.

PHP Code:

    function select($fields = "*", $table = "", $whereclause = "", $orderby = "", $start = 0, $limit = 0) {
        // Error => No table specified
        if($table == "") return array("error" => "No input table specified");
        // SQL query
        if($whereclause != "") $sql_whereclause = "WHERE " . $whereclause;
        if($orderby != "") $sql_orderby = "ORDER BY " . $orderby;
        if(!($start == 0 && $limit == 0)) $sql_limit = "LIMIT " . $start . "," . $limit;
        $sql = "SELECT $fields FROM $table $sql_whereclause $sql_orderby $sql_limit";
        return @mysql_query($sql);
    } 


Now in flash you can check the number of records returned or use the recordset for component binding, etc...

[freddycodes]

Also instead of passing the values and fields for your inserts and updates consider sending them as objects and arrays in flash so you can just loop through and create your string in PHP. To me it makes more sense and is more manageable.

For instance

PHP Code:

    function updatePerson($obj)
    {
        $sql = "UPDATE test set ";
        $a = array();
        foreach($obj as $key => $val) {
            if ($key != 'id') {
                $a[] = "$key = '".addslashes($val)."'";
            }
        }
        $sql .= implode(",", $a);
        $sql .= " WHERE id = ".$obj['id'];
        $q = mysql_query($sql);
        if(mysql_affected_rows()) 
        {
            return "Record updated successfully";
        
        }
        else 
        {
            return "Error updating record";
        }
    } 


See what I am doing here, is passing the object from flash to be updated. Where each object property is a field name, and the id property is the primary key to update.