mySQL tutes. WHY is it so hard?

[beau]

All I want to do is learn how to get Flash and mySQL to communicate. That's all. Read and write to the database.

All I need is one text input field, a button to add it, and then a text field that writes out all the things i've added so far. Honestly. I don't want anything else right now.

WHY are all the tutorials so overblown and complicated, (and so far, WRONG? haven't found one that worked successfully yet, thanks to poor spelling maybe? bad grammar? I dunno).

I'm so frustrated. Please PLEASE tell me. IS there a way to just write out the mysql query in flash and pass THAT to a php page which will then return the data?

Can you just give me the idiot flowchart on this? I've read about LoadVar objects, php files that do all sorts of different methods to read the information. Unfortunately, half of the tutorials are (I GUESS) for AS 1.0? Flash 5? I have no clue. But so far, no good.

Honestly, one text field, an add button and a dynamic text box that gets populated with the stuff. Simple makes it so much easier to learn.

Sorry for the rant, but honest, I've searched this site, googled, gone through at least 4 tutorials so far and I'm no closer than I was five days ago.

[Cota]

Lucky you, I'm currently writting a tutorial for database inegration with ASP or PHP and MySQL or MS SQL. Since it wont be ready for a few more days, I'll just get straight to the question. You asked for a simple flow chart, well here it is:
Flash -> PHP -> MySQL (if data is returned) -> PHP -> flash.

So you want to create an SQL Query in flash...its simple enough. But data are you using to build this query? Here's an example in flash, lets say you were building a login query in flash. 2 text fields, user name and password, its just easy to work with, thats why

var strSQL:String = "SELECT * FROM tblTableName WHERE ";

strSQL = strSQL + "userName='" + username.text + "'";
strSQL = strSQL + " AND password='" + password.text + "'";

myVars = new LoadVars();
myvars.strSQLout = strSQL;
myVars.onLoad = function(success) {i
f(success){
if (this.server_mes == "ok"){
//The user exists
}else{
//User does NOT exist
}
}
}
myVars.sendAndLoad("database.php", myVars, "POST");


Work with me on the PHP, I'm an ASP guy by nature

<?
/*----------Get variable from Flash-------------*/
$flashSQL = $_POST['strSQLout'];


/*--------- DATABASE CONNECTION INFO---------*/
$hostname="localhost";
$mysql_login="myusername";
$mysql_password="mypassword";
$database="mydatabase";

// connect to the database server
if (!($db = mysql_pconnect($hostname, $mysql_login , $mysql_password))){
die("Can't connect to database server.");
}else{
// select a database
if (!(mysql_select_db("$database",$db))){
die("Can't connect to database.");
}
}

/*------------------Run SQL from Flash--------------------*/

$request = mysql_query($flashSQL);
while ($row = mssql_fetch_array($result)
{
if(!$row) {
echo "&server_mes=ok";
}
else {
echo "&server_mes=fail&";
}
}
?>


Keep in mind, the PHP code may be incorrect, as I said, I'm an ASP guy. But that should get you going in the right direction.

[Paerez]

Oh... I feel your pain.

I spent about 6-8 hours crawling the web and messing with flash before I finally got php+mySQL+Flash 8 working. I am at work right now, but I will check this thread when I am home and paste my code for you. Probably in about 2-4h.

[Gibberish]

I can't test it but here is some PHP for you Cota

<?php
/*----------Get variable from Flash-------------*/
$flashSQL = $_POST['strSQLout'];


/*--------- DATABASE CONNECTION INFO---------*/
$hostname="localhost";
$mysql_login="myusername";
$mysql_password="mypassword";
$database="mydatabase";

// connect to the database server
mysql_connect($hostname, $mysql_login , $mysql_password) or die (mysql_error());
mysql_select_db($dbname) or die (mysql_error());

/*------------------Run SQL from Flash--------------------*/

$request = mysql_query($flashSQL);
while ($row = mysql_fetch_array($result) {
if(!$row) {
echo "&server_mes=ok";
} else {
echo "&server_mes=fail&";
}
}

mysql_free_result($result);
mysql_close();
?>

[Cota]

Thanks Gibberish...my PHP isnt all that great....

[Gibberish]

no worries. I'll be sure to hit you up next time I get an ASP site project :). It's all really the same structure, just differnet function names and minor syntax.

[Paerez]

OK So here is my method. I tried to make it more object oriented, using classes and callbacks and stuff.

import mx.remoting.*;
import mx.rpc.*;

var loginResponder:Responder = new Responder();
// This is for the file Login.php in the music folder
var loginService:Service = new Service("http://pathto/gateway.php", null, "music.Login", null, loginResponder);
loginResponder.onResult = function(event:ResultEvent) {
output += "Result: "+event.result+"\n";
if(event.result) {
_root.gotoAndStop("Streamer");
}
}
loginResponder.onFault = function(event:FaultEvent) {
output += "Fault: "+event.fault.faultstring+"\n";
}
// when we click my login button, it sends the info
loginButton.onRelease = function() {
loginService.checklogin(userbox.text, calcMD5(pwbox.text));
};


Here is my php Login class, which connects to mySQL.

<?php
session_start();
header("Cache-control: private"); //IE 6 Fix
class Login {
var $db_host = 'localhost';
var $db_name = 'music';
var $db_user = 'login';
var $db_pwd = 'secret';

function Login() {
$this->connection = @mysql_connect($this->db_host, $this->db_user, $this->db_pwd);
@mysql_select_db($this->db_name);
$this->methodTable = array(
"checklogin" => array (
"description" => "Checks a username, password",
"access" => "remote",
"arguments" => array("user", "pass")
),
"insert" => array (
"description" => "Creates a user",
"access" => "remote",
"arguments" => array("user", "pass")
),
"update" => array (
"description" => "Updates user info",
"access" => "remote",
"arguments" => array("user", "pass")
)
);
}
function checklogin($username, $password) {
if($this->connection) {
//$password = encrypt($password);
$query = "select username from users where username = '$username' and password = '$password'";
$result = mysql_query($query);
$row = mysql_fetch_array($result);
if ($username == $row[0]) {
$_SESSION['uname'] = $username;
return true;
}
} else {
return false;
}
}
/*
function insert($username, $password) {
if($this->connection) {
$query = "insert into users (username, password) values ('$username','$password')";
$result = @mysql_query($query);
return $result;
if ($result) { return true;}
else {return false;}
}
else {return false;}
}
function update($username, $password) {
$password = encrypt($password);
$query = "update users SET password='$password' WHERE username='$username'";
mysql_query($query);
}
*/
}
?>

Hope that helps some.

[beau]

so - first question before I begin to put this together:
- what is server_mes?

How can i ADD users? Like I said - I don't want to just read, I need to write to the DB. This is a great start, but I need to put together code to write...

I suppose I could just modify the sql query line. I'll try that.

Secondly - Paerez - your code is twice as much to accomplish the same tasks. Why? Just want to know why you chose your method and what are the advantages to it? Expandability? I'm still in the shallow-end with classes and OOB. I SHOULD be advanced, but everytime I use OOB and classes, it's something specific, so I'm still cloudy on the basic principles/uses.

Thanks peeps. I hope this opens the doors in my cluttered brain.

[Cota]

so - first question before I begin to put this together:
- what is server_mes?


server_mes is a variable send back to flash from the PHP script that just says everything ran ok. The theory is this, in the PHP file, when ever you encounter an error, echo server_mes = fail. This send the variable back to flash with the value "fail". Its a method of letting flash and the user know there was an error on the server side of things. Its optional.

[beau]

Cota,
I'm trying your example with no success right now. Not sure what the glitch is at this point. Looks like your code works when that frame is encountered, so I created the text fields on the first frame with a button than then just plays the movie til it gets to the frame with all your code, and then, depending on the return, it either goes to frame 5 (failure mssg) or 6 (success mssg). It's always going to 5 right now.

okay. the this.server_mes is coming back as undefined. Any ideas?

Paerez,
I'm looking at your code, and there's more to it than those two files, right?
- gateway.php
what is this?

- mx.remoting.*
- mx.rpc.*
are these two classes included in flash, so they SHOULD be there? When I try to export, I'm getting errors all over the place, "Responder could not be loaded" etc.

I appreciate help from both of you. I hope I figure this mess out.

[CyanBlue]

You definitely don't want to go to the Flash Remoting route until you know what's happening here... That's got to give you more things to study... ;)

[Cota]

For now just stick it all in one frame, textfields, buttons, etc. Also, you need a database set up with info in it. My code only checks if a user exists.

[beau]

is this right?

$request = mysql_query($flashSQL);
while ($row = mysql_fetch_array($result) {

That's the only occurrence of the variable "$result" except at the end:
mysql_free_result($result);

[Cota]

Yes, then we test rows, cause we set that equal to the result. Its just testing of the user is in the database or not. Again, make sure you have a database to connect to, change the table name and fields on the script to match..

[beau]

Yeah - I've done all that, and I'm coming up empty. At this point, I'm just trying to get a php page to return the values in the table:

$request = mysql_query("SELECT * FROM users");

while ($row = mysql_fetch_array($result) {
if(!$row) {
echo "&server_mes=ok";
} else {
echo "&server_mes=fail&";
}
}
And, sure, I realize that I won't see a list of the users in this while loop, I would hope to at least see SOMETHING, but I'm getting an error on the line with $result on it.

MAN I'm rusty on php and mysql. I guess I'm going to have to backtrack and do another frickin tutorial on php. Jeez I wish my brain could retain information.

[Cota]

Gibberish had corrected my PHP code a page back. What error are you getting?

[beau]

I dont know. I gave up for now - we're heading out of the office for the hurlidays. I'll be back on monday to whine and mope.

Thanks for your help so far. Have a safe holiday.

[Cota]

Sorry we couldnt solve it today..my last suggestion is to check if the PHP file is running correctly by running the PHP directly.

[Gibberish]

I made an error in my code. I am used to calling the query $result rather then $request.

This should work:

<?php
/*----------Get variable from Flash-------------*/
$flashSQL = $_POST['strSQLout'];


/*--------- DATABASE CONNECTION INFO---------*/
$hostname="localhost";
$mysql_login="myusername";
$mysql_password="mypassword";
$database="mydatabase";

// connect to the database server
mysql_connect($hostname, $mysql_login , $mysql_password) or die (mysql_error());
mysql_select_db($dbname) or die (mysql_error());

/*------------------Run SQL from Flash--------------------*/

$request = mysql_query($flashSQL);
while ($row = mysql_fetch_array($request) {
if(!$row) {
echo "&server_mes=ok";
} else {
echo "&server_mes=fail&";
}
}

mysql_free_result($request);
mysql_close();
?>

[beau]

For now just stick it all in one frame, textfields, buttons, etc. Also, you need a database set up with info in it. My code only checks if a user exists.
Okay, I'm back, 4 pounds heavier and well rested.

You're saying, put this all in a single frame, so then, how does the information get sent to the server? If there's no action/button to submit the login/password, how do I test this?

I love confusion. It makes me feel so capable as a human.

[Cota]

You place everything in one frame, including buttons and text fields. Also, Gibberish made this correction

$request = mysql_query($flashSQL);
while ($row = mysql_fetch_array($request) {
if(!$row) {
echo "&server_mes=ok";
} else {
echo "&server_mes=fail&";
}
}

notice "results" is gone...

[beau]

So then, what action does the button do?

Augh. I give up. I'm going with dhtml. I hate it, but this is killing me.

[Cota]

The button, will trigger all the LoadVars stuff....which will trigger the PHP, etc etc

[ovydiu]

did you try to make a test.php file which you load with a web browser (http://localhost/test.php) to make sure php is working?