AutoIT MySQL and PHP!

[xwinterx]

Simple AutoIT to MySQL via PHP example:

I won't go into much detail here. A while ago I asked around on the forums for a way to use PHP to access a MySQL database instead of using the MySQL UDFs found on here. Though the MySQL UDFs are much friendlier and do not require you to know PHP, I needed a solution that allowed me to access data from any computer without always having to install the MySQL ODBC 3.1 Driver, especially since on most of the computers I did not have the access rights to do so.

I have included a .rar file with 4 files spanning 2 examples. These examples are READ ONLY and make no changes to your MySQL database. There is no sql file included to make a simple MySQL database, table and fields. You'll need to create them yourself and modify the php and au3 files included for your user name, password, database, table, and field names. Refer to your MySQL manual for this assistance. All 4 files should be commented enough to act as a small tutorial. If there seems to be some interest, I may write up a doc tutorial and include it in the archive at a later date.

Example Database Info:

1 MySQL Database

1 MySQL Database table

1 row with the following field names: id, name, email

Example 1 - No Variable Passing (no_variable.au3, no_variable.php)

This example shows simple data retrieval using a "hard coded" php file. It retrieves the data you put into your three fields and displays them in a message box.

Example 2 - With Variable Passing (variable.au3, variable.php)

This example shows simple data retrieval by using variables that are passed through the url to your php file. This can allow for dynamic data retrieval using a simple GUI to enter in a value or id in which you want all that data from.

These examples return the unique id that is inserted into the "id" field at row creation, a name you store into the "name" field, and an email address stored in the "email" field.

Forgive me if this is a long post about very little info. If you find this at all useful, please let me know. I am not an expert with AutoIT, PHP, or MySQL. This is just a neat little thing I needed to use and figured it was time to post something about it. Hopefully it will be of some use. I will try to give more examples or answers to help anyone out. Feel free to post here or PM me at all.

You will need to know or learn basic PHP and MySQL inorder to expand these examples or modify them. I can help with what little I know, but I made my little program and pages and they work great.

Note: My au3 code utilizes those GREAT HTTP UDFs found in these forums. I just renamed them for my personal preference. I take NO credit for them whatsoever.

Update: added zip archive

Example.rar

Example.zip

Edited April 5, 2007 by xwinterx

[Ed_Maximized]

Personally I would like to learn more of this subject.

I support your idea to write a tutorial for the novices like me.

Good job!!!

[xwinterx]

Personally I would like to learn more of this subject.

I support your idea to write a tutorial for the novices like me.

Good job!!!

thx... I'm gonna make an sql file and put up a sample data base.

[gseller]

Awesome!! This is what I am looking for, I do alot of php/mysql stuff and php/mssql ans have an extensive intranet setup at my work with an mssql db. I look forward to trying this out to see if I can hook into my db with it.

Thank You

Jim

[xwinterx]

cool. I havent had a chance to do anything else with this as far as extending the tutorial if anyone else has any interest in it I can try to give examples on what you are wanting to do, beyond that, this little tidbit should be enough. its simple, please remember!

[gsb]

I have used Flash -> PHP -> mySQL -> PHP -> Flash often.

I believe there are even some Actionscript/PHP type mySQL class libs.

You could embed a small hidden flash control in the GUI as a "communicator" to/from your mySQL DB.

I would think that this could make your overall project easier, no?

gsb

[xwinterx]

I have used Flash -> PHP -> mySQL -> PHP -> Flash often.

I believe there are even some Actionscript/PHP type mySQL class libs.

You could embed a small hidden flash control in the GUI as a "communicator" to/from your mySQL DB.

I would think that this could make your overall project easier, no?

I have no clue what you mean. Like building a flash interface? I don't do flash whatsoever.

This "communicator" idea, would that still require installing the MS ODBC drivers?

[gsb]

"I don't do flash whatsoever."

Ah sorry. Was just a thought.

"...would that still require installing the MS ODBC drivers?"

No. Not what I was thinking. But is mute point now, no?

gsb

[xwinterx]

"I don't do flash whatsoever."

Ah sorry. Was just a thought.

"...would that still require installing the MS ODBC drivers?"

No. Not what I was thinking. But is mute point now, no?

Sorry, I didn't read how I worded my last reply. I mean that I have no clue how to do flash since I have never done it so the concept is lost to me. Please explain because that could be another alternative as you were saying.

[keen]

CAn you make a zip file?

[gsb]

"...without always having to install the MySQL ODBC 3.1 Driver..."

"...utilizes those GREAT HTTP UDFs found in these forums."

These issues jumped out at me.

I am too learning how to use AutoIt3 for some tasks of mine: I am no expert... LOL

I do not even use flash but rather a cheaper version, SWiSHmax, which also generates .swf files that run in a browser with the Flash Player plug-in. This plug-in (FP) is on most user's computers. See here: http://www.adobe.com/products/player_census/flashplayer/

The FP is designed to talk to the server and easily works with PHP for dynamic content.

My use of AutoIt3 has centered around integrating Flash content as part of an au3 control. This works well thanks to much help by lod3n and others here on the forum. So now an au3 script communicates data to/from an embedded swf easily.

Therefore, as a logical extension, au3 scripts can just as easily pass data to/from a mySQL database via an embedded swf, or so I think. This makes the swf simply a communicator between the au3 script and php/mySQL on the server side.

What I also eluded to is that the "Flash community," which is very large, may well have Flash/PHP/mySQL communication classes already setup and available for use. So you would need to only set up the standards for the au3 to/from flash. Perhaps a less daunting task.

Or so I thought...

gsb

[xwinterx]

Zip file posted.

Hmmm... that would be interesting. could you come up with an example?

[Pakku]

Hi,

i have also used this methode to get data from a MySQL database and i found it interessing to see that you are doing the same

keep it up!

Arjan

[gsb]

"...i have also used this methode to get data from a MySQL..."

I have not. Was just suggesting it as an alternative for xwinterx.

Perhaps you have a simple example to zip and share.

gsb

[Pakku]

"...i have also used this methode to get data from a MySQL..."

I have not. Was just suggesting it as an alternative for xwinterx.

Perhaps you have a simple example to zip and share.

gsb

well after i read this topic i was thinking of making a UDF so you have to wait for a few days or so...

Edited November 16, 2010 by Pakku

[Pakku]

Hi all,

here an example:

au3 code:

#include <INet.au3>

Func _MySQL_Query($page,$user,$pass,$database,$query)
    $s_URL = $page & "?func=_MySQL_Query&user="&$user&"&pass="&$page&"&database="&$database&"&query="&$query
    $inet = _INetGetSource($s_URL)
    Return $inet
EndFunc

php file code:

<?php
if ($_GET["func"] = "_MySQL_Query") {
    mysql_connect("localhost", $_GET["user"], $_GET["pass"]) or die("");
    mysql_select_db($_GET["database"]);
    $result = mysql_query($_GET["query"]);
    echo $result;
    mysql_close();
}
?>

if there are any questions, just ask!

p.s. i might be clear that you can add your own functions to communicate with MySQL, i will post a few later this week or so.

Edited November 16, 2010 by Pakku

[lod3n]

Wow, you're passing your db username and password in the HTTP query string? And then you're executing arbitrary SQL queries? Do you have any idea what could happen to your database?

[gsb]

Wow, you're passing your db username and password in the HTTP query string? And then you're executing arbitrary SQL queries? Do you have any idea what could happen to your database?

I agree totally.

I believe that the queries need to be in the php script and the db configuration data as in include file from outside the public html site area.

I will make an example as well... at work just now. (And that is a 4-letter word you know.)

gsb

[xwinterx]

That isn't too far off from what I am doing, except I keep all the db config stuff in the php file. I didn't do it as an include on the server side because it was just a simple example. yeah, not a good idea to keep your db stuff in the au3.

[gsb]

So... A small example I said...

Almost I guess.

There are 4-parts to it:

1) mySQL DB table,

2) PHP script,

3) ActionScript/Flash (SWiSHmax here) and

4) AutoIt script.

Database table and PHP interface script first:

CREATE TABLE IF NOT EXISTS `au3Table` (
  `id` int(11) NOT NULL auto_increment,
  `last_name` varchar(30) NOT NULL default '',
  `first_name` varchar(30) NOT NULL default '',
  `comment` varchar(255) NOT NULL default '',
  `date` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=100;

INSERT INTO `au3Table` VALUES (101, 'Baker', 'Greg', 'Nice guy this Greg.', '2004-10-30');
INSERT INTO `au3Table` VALUES (102, 'Bush', 'George', 'Not so nice a guy this Bush.', '2007-11-18');
INSERT INTO `au3Table` VALUES (103, 'Williams', 'Barbara', 'Good writter of fiction.', '2006-12-30');
INSERT INTO `au3Table` VALUES (104, 'Adams', 'Evonne', 'Cute!', '2006-10-15');

Simple enough I guess.

...and the PHP:

expandcollapse popup

<?php

    /* DATEBASE CONFIGURATION */

    require( "/home/.../au3PHPmySQL.inc" );
    
    // I always keep the DB configuration data out of the public html.
    // That file looks like this example:
    //  $dbHost   = "localhost";
    //  $dbName   = "db name";
    //  $dbUser   = "db user name";
    //  $dbPasswd = "db password";
    //  $DBH      = "";


    // Example action: dump the data records
    if( $HTTP_GET_VARS && isset($HTTP_GET_VARS['action']) && $HTTP_GET_VARS['action'] == "dump" ) {

        db_connect();
        $query = " SELECT * FROM au3Table ";
        $data = mysql_query( $query ) or die("ERROR: Bad select [" . $query . "]");

        if( mysql_num_rows( $data ) ) {

            // if successful, make the data CSV format and send it back to AutoIt via flash
            while( $row = mysql_fetch_array( $data, MYSQL_NUM ) )
                echo array2string( $row ) . "\n";

        } else {

            die("ERROR: No returned data [" . $query . "]");

        }

        mysql_free_result( $data );

        mysql_close();

    } else {

        echo "ERROR: Bad action requested [" . $HTTP_GET_VARS['action'] . "].";

    }


    exit(0);

//---------------------------- Utility Functions

function array2string( $a ) {
    for($n=count($a),$s="",$i=0;$i<$n;$i++)$s .= ($i?",":"").$a[$i];
    return( $s );
}

function db_connect() {  // connect to db
    global $dbHost, $dbName, $dbUser, $dbPasswd, $DBH;
    if (! $DBH ) {
        if (! $DBH = mysql_connect($dbHost, $dbUser, $dbPasswd)) {
            $error  = "ERROR: Can't connect to $dbHost as $dbUser";
            $error .= "MySQL Error: " . mysql_error();
            die($error);
        }
        if (! mysql_select_db($dbName, $DBH)) {
            $error  = "ERROR: Unable to select database $dbName";
            $error .= "MySQL Error: " . mysql_error();
            mysql_close();
            die($error);
        }
    }
}

?>

This Flash code has my debugger code in it. To view the debug statements you need my first au3 script, gsbTrace.exe.

expandcollapse popup

onload() {

    // gsbTrace utility - requires gsbTrace.exe
    _global.debug=_level0.gsbTraceLevel?parseInt(_level0.gsbTraceLevel):0;
    if(debug){_global.outgoing=new LocalConnection();
        _global.gsbTrace=function(str){outgoing.send("_4gsbTraceWin","gsbTrace",str);return true;};
        gsbTrace("gsbTrace initialized @ " add Timer.ticks()+"  "+debug );  // announce!
    } else _global.gsbTrace=function():Boolean{return true;};


    // Function called from AutoIt to communicate with mySQL
    _global.DBaction = function( action, callback ) {
        _global.data = new LoadVars();
        data.onData = function(s) {
            if(!s) s = "ERROR:  No data returned.";
            else if(debug>1) gsbTrace("Returned data:\n"+s);

            eiCall("au3Direct", callback, s);  // return results to AutoIt script
            delete _global.data;  // cleanup
        };
        data.load( "http://www.gypsytrader.com/AutoIt/au3PHPmySQL/au3PHPmySQL.php?action="+action);
    };

    // wait until communications established with AutoIt
    this.initialized = false;
    this.onEnterFrame = function():Void {
        if( initialized ) delete this.onEnterFrame;
        else {
            _global.eiCall=flash.external.ExternalInterface.call;
            _global.eiCallBack=flash.external.ExternalInterface.addCallback;
            eiCallBack( "gsbTrace", _global, gsbTrace );
            eiCallBack( "DBaction", _global, DBaction );
            eiCall("au3Direct", "initialize");  // initialize communications
        }
    };
}

onFrame(2){
    stop();
}

And finally the AutoIt script:

expandcollapse popup

#include <GUIConstants.au3>
#include <IE.au3>


Opt("RunErrorsFatal", 0)
_IEErrorHandlerRegister()


Global $myWinTitle = "au3PHPmySQL"


if _Singleton($myWinTitle,1) == 0 Then
    MsgBox(262208,$myWinTitle & " - Error","...already running. Aborting.  ", 5)
    _Exit()
EndIf


HotKeySet("^+x", "_Exit")


$swfFilename = @TempDir & "\au3PHPmySQL.swf"
FileDelete($swfFilename)
FileInstall("au3PHPmySQL.swf", $swfFilename)

$htmlFilename = @TempDir & "\au3PHPmySQL.html"
FileDelete($htmlFilename)

$sHTML  = '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">' & @CRLF
$sHTML &= '<html><head>' & @CRLF
$sHTML &= '<style type="text/css" media="screen">' & @CRLF
$sHTML &= '  * {margin:0px;padding:0px;}' & @CRLF
$sHTML &= '  html {height:100%;overflow:hidden;}' & @CRLF
$sHTML &= '  body {height:100%;overflow:hidden;}' & @CRLF
$sHTML &= '</style>' & @CRLF
$sHTML &= '&lt;script type="text/javascript">' & @CRLF
$sHTML &= 'function au3Direct(cmd,args){' & @CRLF
$sHTML &= '  var targetObj = top.document.parentwindow.document.documentElement;' & @CRLF
$sHTML &= '  targetObj.params = new function(){this.obj=window["gsbMovie"];this.cmd=cmd;this.args=args;}' & @CRLF
$sHTML &= '}</script>' & @CRLF
$sHTML &= '</head><body scroll="no">' & @CRLF
$sHTML &= '<object classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" ' & @CRLF
$sHTML &= 'codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=8,0,22,0" ' & @CRLF
$sHTML &= 'id="gsbMovie" width="100%" height="100%">' & @CRLF
$sHTML &= '<param name="movie" value="' & $swfFilename & '">' & @CRLF
$sHTML &= '<param name="bgcolor" value="#FFFFFF">' & @CRLF
$sHTML &= '<param name="quality" value="high">' & @CRLF
$sHTML &= '<param name="loop" value="false">' & @CRLF
$sHTML &= '<param name="allowscriptaccess" value="always">' & @CRLF
$sHTML &= '<param name="flashvars" value="gsbTraceLevel=2">' & @CRLF
$sHTML &= '</object></body></html>' & @CRLF

FileWrite($htmlFilename,$sHTML)


$oIE = ObjCreate("Shell.Explorer.2")
$flashyGUI = GUICreate( $myWinTitle, 400, 300, -1, -1 ,BitOR($WS_SYSMENU, $WS_CAPTION, $WS_SIZEBOX ), $WS_EX_TOPMOST)
$GUIActiveX = GUICtrlCreateObj($oIE, -10, -10 , 1 , 1 )
_IENavigate($oIE, $htmlFilename)
GUISetState(@SW_SHOW)
FileDelete($htmlFilename)
FileDelete($swfFilename)


;; Flash to AutoIt event generator (by lod3n)
Sleep(1);; needed for old sytems and slow machines
$targetObj = $oIE.document.parentwindow.document.documentElement
$targetObj.setAttribute("params",1)
$handler = ObjEvent($targetObj,"IeEvent_")


;; Flash object for calls
;; Set by initialization call
Global $flashObj = ""


$testButton = GUICtrlCreateButton("Dump DB", 9, 7, 60, 24)


While 1
    $msg = GUIGetMsg()
    If $msg = $GUI_EVENT_CLOSE Then
        _Exit()

    ElseIf $msg = $testButton Then
        If IsObj( $flashObj ) Then $flashObj.DBaction("dump","dumpReturnData")
    EndIf
Wend

Exit;; unreachable


;;----------------- Functions
Func IeEvent_onpropertychange()
    $oElement = @COM_EventObj
    if isobj($oElement.params) then
        $oElement.params.obj.gsbTrace("au3 " & $oElement.params.cmd)
        $StageManager = $oElement.params.obj

        Select
            Case $oElement.params.cmd = "initialize"
                $oElement.params.obj.SetVariable("/:initialized","true");
                $flashObj = $oElement.params.obj
                
            Case $oElement.params.cmd = "dumpReturnData"
                _Alert("Dump Return Data:" & @LF & $oElement.params.args & @LF )
            ;; USAGE:  $a = StringSplit($oElement.params.args,",")
                
            Case Else
                _Alert("Unknown r:" & @crlf & "Cmd: " & $oElement.params.cmd & @crlf & "Args: " & $oElement.params.args)
        EndSelect

    EndIf
    $oElement.params = 1
EndFunc

Func _Alert( $str )
    MsgBox(262208,"Alert", $str, 15)
EndFunc

Func _Exit()
    Exit
EndFunc

;;----------------- from:  Misc.au3
Func _Singleton($occurenceName, $flag = 0)
    Local $ERROR_ALREADY_EXISTS = 183
    $occurenceName = StringReplace($occurenceName, "\", ""); to avoid error
    Local $handle = DllCall("kernel32.dll", "int", "CreateMutex", "int", 0, "long", 1, "str", $occurenceName)
    Local $lastError = DllCall("kernel32.dll", "int", "GetLastError")
    If $lastError[0] = $ERROR_ALREADY_EXISTS Then
        If $flag = 0 Then
            Exit -1
        Else
            SetError($lastError[0])
            Return 0
        EndIf
    EndIf
    Return $handle[0]
EndFunc

Here are the files including the gsbTrace.exe: au3PHPmySQL.zip

gsb