Sign in to follow this  
Followers 0
xwinterx

AutoIT MySQL and PHP!

24 posts in this topic

#1 ·  Posted (edited)

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 by xwinterx

Share this post


Link to post
Share on other sites



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!!!

:whistle:

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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


"Did you ever stop to think? ...and forget to restart!"

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

"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


"Did you ever stop to think? ...and forget to restart!"

Share this post


Link to post
Share on other sites

"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.

Share this post


Link to post
Share on other sites

CAn you make a zip file?


[center]Kesne's Bar & Grill[/center]

Share this post


Link to post
Share on other sites

"...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


"Did you ever stop to think? ...and forget to restart!"

Share this post


Link to post
Share on other sites

Zip file posted.

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

"...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


"Did you ever stop to think? ...and forget to restart!"

Share this post


Link to post
Share on other sites

#15 ·  Posted (edited)

"...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 by Pakku

Share this post


Link to post
Share on other sites

#16 ·  Posted (edited)

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 by Pakku

Share this post


Link to post
Share on other sites

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?


[font="Fixedsys"][list][*]All of my AutoIt Example Scripts[*]http://saneasylum.com[/list][/font]

Share this post


Link to post
Share on other sites

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


"Did you ever stop to think? ...and forget to restart!"

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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:

<?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.

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:

#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


"Did you ever stop to think? ...and forget to restart!"

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0