Loading database stuff through PHP/Flash

[mprzybylski]

ok, i have a database with two tables. one table holds the following:

table 1 name: lots
columns: lot_id, number, description, status_id
table 2 name: status
columns: status_id, name, description

i have a php script that goes as follows (i'm ignoring the description fields for now):


<?php

// database connection variables
$server = "localhost";
$username = "********";
$password = "******";
$database = "***********";

// database connection statement
$connect = mysql_connect($server, $username, $password);
mysql_select_db($database, $connect);

// get values from the database
$query = "SELECT * FROM lots";
$result = mysql_query($query);
$num_results = mysql_num_rows($result);

// loop through database to get the values
for ($i = 0; $i < $num_results; $i++) {
$row = mysql_fetch_assoc($result);
$id .= $row['lot_id'] . "\n";
$lot .= $row['number'] . "\n";
$lotStatus .= $row['status_id'] . "\n";
}

// output the variables so that flash can read them
$output = "" ;
$output .= "id=" . $id . "&" ;
$output .= "lot=" . $lot . "&" ;
$output .= "lotStatus=" . $lotStatus ;

// echo the final output lines
echo $output;

?>

now i'm not 100% sure this script is correct, so if anyone sees problems in it, please do let me know. i know that it needs some additions to pull the linking table (status) to display the status_id, but i have no clue how to write that as im not familiar with php, i just took this script from a user on this forum (i apologize for forgetting your name) and edited it.

anyway, i want to be able to pull out all those fields and load them up in flash so i can use them in the scripts over there. i have a grasp on the LoadVars(); a bit but not sure how to pull all this out and if its correct so that i can use it in flash. the fields im most worried about using is the number field in the lots table and the status_id (in both i guess since its a linking table).

any help would be VERY greatly appreciated. thanks in advance.

[natronp]

can't help you with the php cuz i'm teaching myself it right now!

if you need help with the flash side of it PM me later and I'll see if I can help u out!

[mprzybylski]

ok, i have some updated code for you guys to look at.

the php file currently:


<?php

// database connection variables
$server = "localhost";
$username = "*****";
$password = "*********";
$database = "*********";

// database connection statement
$connect = mysql_connect($server, $username, $password);
mysql_select_db($database, $connect);

// get values from the database
$query = "SELECT * FROM lots, status WHERE lots.status_id = status.status_id";
$result = mysql_query($query);
$num_results = mysql_num_rows($result);

// loop through database to get the values
for ($i = 0; $i < $num_results; $i++) {
$row = mysql_fetch_assoc($result);
$id .= $row['lot_id'] . "\n";
$lot .= $row['number'] . "\n";
$lotStatus .= $row['status_id'] . "\n";
}

// output the variables so that flash can read them
$output = "" ;
$output .= "id=" . $id . "&" ;
$output .= "lot=" . $lot . "&" ;
$output .= "lotStatus=" . $lotStatus ;

// echo the final output lines
echo $output;

?>

and the flash file:


// variables for first and last lots
var firstLot:Number = 228;
var lastLot:Number = 481;

// colors array for lot status
// slot0 = empty, slot1 = available, slot2 = sold, slot3 = sale pending
var colors:Array = new Array("", 0x26992F, 0x00FF00, 0x0000FF);

// check the database for the status of the lots
function checkStatus():Void {
for (var i:Number = firstLot; i <= lastLot; i++) {
var targ_mc:String = "lot_" + i;
if (lots.lotStatus == 1) {
// if the lot status is AVAILABLE
var availableColor:Color = new Color(targ_mc);
availableColor.setRGB(colors[1]);
} else if (lots.lotStatus == 2) {
// if the lot status is SOLD
var soldColor:Color = new Color(targ_mc);
soldColor.setRGB(colors[2]);
} else if (lots.lotStatus == 3) {
// if the lot status is SALE PENDING
var pendingColor:Color = new Color(targ_mc);
pendingColor.setRGB(colors[3]);
}
}
}

function loadLots(success:Boolean):Void {
if (success) {
// make sure the php file loaded
lot_txt.text = "lots loaded";

// set variables for data from the database
var lotID = lots.id;
var lotNum = lots.lot;
var lotStat = lots.lotStatus;
checkStatus();
} else {
lot_txt.text = "not loaded";
}
}

var lots:LoadVars = new LoadVars();
lots.onLoad = loadLots;
lots.load("lots.php");

[mprzybylski]

ok, i edited the php file a bit to display a string (this works better because now i'm reading the variables correctly and the colors show up on the map, but its only showing blue, the third color, so i think the problem may be somewhere in my loop in flash):


<?php

// database connection variables
$server = "localhost";
$username = "***";
$password = "***";
$database = "***";

// database connection statement
$connect = mysql_connect($server, $username, $password);
mysql_select_db($database, $connect);

// get values from the database
$query = "SELECT * FROM lots, status WHERE lots.status_id = status.status_id";
$result = mysql_query($query);
$num_results = mysql_num_rows($result);

// loop through database to get the values
for ($i = 0; $i < $num_results; $i++) {
$row = mysql_fetch_assoc($result);
$rString .= "id=".$row['lot_id'];
$rString .= "&lot=".$row['number'];
$rString .= "&lotStatus=".$row['status_id'];
}

// echo the final output lines
echo $rString;

?>

here is the flash code, again, updated a bit:


// variables for first and last lots
var firstLot:Number = 228;
var lastLot:Number = 481;

// colors array for lot status
// slot0 = empty, slot1 = available, slot2 = sold, slot3 = sale pending
var colors:Array = new Array("", 0x26992F, 0x00FF00, 0x0000FF);

// check the database for the status of the lots
function checkStatus():Void {
for (var i:Number = firstLot; i <= lastLot; i++) {
var targ_mc:String = "lot_" + i;
if (lots.lotStatus == 1) {
// if the lot status is AVAILABLE
var availableColor:Color = new Color(targ_mc);
availableColor.setRGB(colors[1]);
} else if (lots.lotStatus == 2) {
// if the lot status is SOLD
var soldColor:Color = new Color(targ_mc);
soldColor.setRGB(colors[2]);
} else if (lots.lotStatus == 3) {
// if the lot status is SALE PENDING
var pendingColor:Color = new Color(targ_mc);
pendingColor.setRGB(colors[3]);
}
}
}

function loadLots(success:Boolean):Void {
if (success) {
// make sure the php file loaded
lot_txt.text = "lots loaded";

// set variables for data from the database
var lotID = lots.id;
var lotNum = lots.lot;
var lotStat = lots.lotStatus;
checkStatus();
} else {
lot_txt.text = "not loaded";
}
}

var lots:LoadVars = new LoadVars();
lots.onLoad = loadLots;
lots.load("lots.php");

[mprzybylski]

anyone? i'm still having this issue and need to solve it asap.

[tofuisonmyside]

maybe this is stupid but have you tried the old way ?

instead of loadVars, just use a loadVariables("lots.php",this) ?

[natronp]

what's with the ":" everywhere? var i:Number, success:Boolean, var firstLot:Number = 228; ???

as far as I know you don't need to define a string vs. boolean other than using quote marks if it's a string and "success" is a built in of the .onLoad function.

[natronp]

or string vs. literal...

[mprzybylski]

thats just strict datatyping.

i have pinpointed the problem, my loop is only catching the last value in the database which happens to be id = 18 & lot = 273 & lotStatus = 3 (the last part of the string which can be seen here: http://www.demicooper.com/demo_siteplan/lots.php) which means that my loop is not catching all the i's, its only reading the last one, and im not sure how to fix that. here is the code thus far:


// variables for first and last lots
var firstLot:Number = 228;
var lastLot:Number = 481;

// colors array for lot status
// slot0 = empty, slot1 = available, slot2 = sold, slot3 = sale pending
var colors:Array = new Array("", 0x26992F, 0x00FF00, 0x0000FF);

// check the database for the status of the lots
function organizeLots():Void {
for (var i:Number = firstLot; i <= lastLot; i++) {
var targ_mc = "lot_" + i;
// set variables for data from the database
var lotID = lots.id;
var lotNum = lots.lot;
var lotStatus = lots.lotStatus;
// set colors corresponding to each status
if (lotStatus == 1 && lotNum == i) {
// if the lot status is AVAILABLE
var availableColor:Color = new Color(targ_mc);
availableColor.setRGB(colors[1]);
} else if (lotStatus == 2 && lotNum == i) {
// if the lot status is SOLD
var soldColor:Color = new Color(targ_mc);
soldColor.setRGB(colors[2]);
} else if (lotStatus == 3 && lotNum == i) {
// if the lot status is SALE PENDING
var pendingColor:Color = new Color(targ_mc);
pendingColor.setRGB(colors[3]);
}
}
}

function loadLots(success:Boolean):Void {
if (success) {
// make sure the php file loaded
lot_txt.text = "lots loaded";
// run the function for the lot organization for info from db
organizeLots();
} else {
lot_txt.text = "not loaded";
}
}

var lots:LoadVars = new LoadVars();
lots.onLoad = loadLots;
lots.load("lots.php");

[natronp]

hmmm. strict data typing huh? i learn something everyday. don't see why you would want to use it though...

anyhow you have:


function organizeLots():Void {
for (var i:Number = firstLot; i <= lastLot; i++) {
var targ_mc = "lot_" + i;


with
i <= lastlot; you're saying "while var i is less than or equal to var lastLot add 1 to that number"

maybe you need
i < lastLot; instead. at least that's how I've done it while looping through arrays from xml

[mprzybylski]

i like to use strict datatyping so that flash can pinpoint type mismatches in the output panel instead of me guessing whats wrong if i put a string where a number should be.

anyway, that doesn't do anything. i need a statement in the loop to catch the value of i and test against it to see what the lotStatus is at that current value. i tried something like targ_mc.id = i; but that didnt work and thats the only way i know of how to catch id's in a loop (courtesy of the great scotty and his teaching methods).

so my question is does anyone know how to catch the current value of i in a variable so u can test against it?

[natronp]

var targ_mc [i] = lot[i];

something like that... ???? that's how you get the specific value of something when looping through xml object arrays.

thanks for explaining the strict data typing thing! hope this helps...

[mprzybylski]

no problem, and thanks for your help but that didnt do anything :\

[tofuisonmyside]

maybe the pb comes from the php:
for ($i = 0; $i < $num_results; $i++) {
$row = mysql_fetch_assoc($result);
$rString .= "id=".$row['lot_id'];
$rString .= "&lot=".$row['number'];
$rString .= "&lotStatus=".$row['status_id'];
}
id and lot and lotstatus should be id[i] lot[i] and lotstatus[i] in order to get them

your are feeding id and lot and lotstatus with all the vars, but each value of i comes over the previous one

[mprzybylski]

so how should the php file look because when i put "id[i]=".$row['lot_id']; etc it reflects as this:

http://www.demicooper.com/demo_siteplan/lots.php

also, does the flash file then need to be changed?

[mprzybylski]

err actually if u look at that url now i got it to display like that according to what someone on the AS.org forums told me, but the flash is still a bit messed up as in it doesnt load the colors. here is what he had me do:


// variables for first and last lots
var firstLot:Number = 228;
var lastLot:Number = 481;

// colors array for lot status
// slot0 = empty, slot1 = available, slot2 = sold, slot3 = sale pending
var colors:Array = new Array("", 0x26992F, 0x00FF00, 0x0000FF);

// check the database for the status of the lots
function organizeLots():Void {
for (var i = firstLot; i<=lastLot; i++) {
var targ_mc = "lot_"+i;
// set variables for data from the database
_level0["lotID"+i] = parseInt(lots["id"+i]);
_level0["lotNum"+i] = parseInt(lots["lot"+i]);
_level0["lotStatus"+i] = parseInt(lots["lotStatus"+i]);
// set colors corresponding to each status
if (_level0["lotStatus"+i] == 1 && _level0["lotNum"+i] == i) {
// if the lot status is AVAILABLE
var availableColor:Color = new Color(targ_mc);
availableColor.setRGB(colors[1]);
} else if (_level0["lotStatus"+i] == 2 && _level0["lotNum"+i] == i) {
// if the lot status is SOLD
var soldColor:Color = new Color(targ_mc);
soldColor.setRGB(colors[2]);
} else if (_level0["lotStatus"+i] == 3 && _level0["lotNum"+i] == i) {
// if the lot status is SALE PENDING
var pendingColor:Color = new Color(targ_mc);
pendingColor.setRGB(colors[3]);
}
}
}

// when the php file is loaded, function executes commands for lot info
function loadLots(success:Boolean):Void {
if (success) {
// make sure the php file loaded
lot_txt.text = "lots loaded";
// run the function for the lot organization for info from db
organizeLots();
} else {
lot_txt.text = "not loaded";
}
}

// load the php file in to use with flash
var lots:LoadVars = new LoadVars();

// after finished loading run the loadLots function
lots.onLoad = loadLots;
lots.load("lots.php");

[tofuisonmyside]

the lots.php file give you numbers between 0 and 93 (i) and in flash you ask for i from

// variables for first and last lots
var firstLot:Number = 228;
var lastLot:Number = 481;

so it cannot match

what are the reasons for these values 228 and 481 ?

[mprzybylski]

the lots are numbered starting with 228 and go through 481 and i use instance names of lot_228, lot_229 etc to set the colors of each lot depending on its status in the database, lotStatus. i thought this would be the easiest way to do it, plus i'm using the number field to pull through the loop, not the lot_id.

[tofuisonmyside]

&id0=1&lot0=228&lotStatus0=1&&id1=2&lot1=229&lotStatus1=1

so you dont need firstLot and lastLot

try changing : for (var i = firstLot; i<=lastLot; i++) {
in : for (var i = 0; i<=_level0.totalNum-1; i++) {

what is the result ?

[mprzybylski]

what is totalNum supposed to be set to? because that gave me the ole "script is making flash run too slow, abort?" popup msg. and on top of that it did nothing :\

[tofuisonmyside]

i do not know, this var totalNum is in the lots.php on the link you gave.


i suppose you put in there to know hoa many lots matches

[mprzybylski]

oh ok, well in any case it doesnt work :\

[tofuisonmyside]

can you post a simplified fla ?

[mprzybylski]

the full fla is pretty simple.

http://www.reintroducing.com/apnw/lot_map.fla

[tofuisonmyside]

// when the php file is loaded, function executes commands for lot info
function loadLots(success:Boolean):Void {
if (success) {
// make sure the php file loaded
lot_txt.text = "lots loaded";
var total = this.totalNum-1
for (var i = 0; i<=total; i++) {
var k = Number(228) - (-Number(i));
var targ_mc = _root["lot_"+k];
// set colors corresponding to each status
if (this["lotStatus"+i] == 1) {
// if the lot status is AVAILABLE
trace("lot status available "+i+" "+k+" "+(this["lotStatus"+i]))
var availableColor:Color = new Color(targ_mc);
availableColor.setRGB(colors[1]);
}else if (this["lotStatus"+i] == 2) {
// if the lot status is SOLD
trace("lot status SOLD "+i+" "+k+" "+(this["lotStatus"+i]))
var soldColor:Color = new Color(targ_mc);
soldColor.setRGB(colors[2]);
}else if (this["lotStatus"+i] == 3) {
// if the lot status is SALE PENDING
trace("lot status SALE PENDING "+i+" "+k+" "+(this["lotStatus"+i]))
var pendingColor:Color = new Color(targ_mc);
pendingColor.setRGB(colors[3]);
}
}
} else {
lot_txt.text = "not loaded";
}
}


// load the php file in to use with flash
var lots:LoadVars = new LoadVars();

// after finished loading run the loadLots function
lots.onLoad = loadLots;
lots.load("http://www.demicooper.com/demo_siteplan/lots.php");




this code works, after that you have to search for scope function pbs for the colors if you wanna make a function

[mprzybylski]

that does work, awesome, but what do you mean i have to search for scope function pbs for the colors if i wanna make a function???

[tofuisonmyside]

if you put the code in a function and call the function as you did before, it does not work anymore, you have to call _root.thefunction inside the onLoad event, and after thatn you hav trouble with the colors