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, 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");

now i'm not 100% sure this script is correct, so if anyone sees problems in it, please do let me know.

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.

[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");

[alex_bonillas]

Okay not sure what was your question

But I think you should check out what I did:
http://www.universowebmedia.com/applications

I think there is something in there that might help you



Actionscript ****in rocks

[mprzybylski]

well my issue is that it loads the data but only displays the third color as if everything was set to lotStatus == 3, so im guessing the problem is in the loop somewhere.

[mprzybylski]

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");

[CyanBlue]

Howdy... :)

Your PHP should spit out the string like this...
&id0=1&lot0=228&lotStatus0=1&
&id1=2&lot1=229&lotStatus1=1&
&id2=3&lot2=230&lotStatus2=1&
...
...

[mprzybylski]

so how do i go about writing that? and is my flash part ok then?

[CyanBlue]

Well... Roughly, your PHP code would look like this...
for ($i = 0; $i < $num_results; $i++) {
$row = mysql_fetch_assoc($result);
$rString .= "&id" . $i . "=" . $row['lot_id'];
$rString .= "&lot" . $i . "=" . $row['number'];
$rString .= "&lotStatus" . $i . "=" . $row['status_id'] . "&";
}
$rString .= "totalNum=" . $i . "&";

And the ActionScript would be...

function organizeLots():Void {
for (var i:Number = 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) {

Something along that line... :)

[mprzybylski]

i did what you said and it unfortunately didn't show any results, the lot map is still just all green but the php output is there (if this is how you intended it):

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

thanks for any more help you can offer.

[CyanBlue]

You've got some sort of infinite loop going on...
Can you post yourlatest codes(PHP/AS)???

[mprzybylski]

alright, here is the current PHP file i have:

<?php

// database connection variables
$server = "localhost";
$username = "demicooper";
$password = "dc9811";
$database = "demicooper_com_-_demo_siteplan";

// 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);

for ($i = 0; $i < $num_results; $i++) {
$row = mysql_fetch_assoc($result);
$rString .= "&id" . $i . "=" . $row['lot_id'];
$rString .= "&lot" . $i . "=" . $row['number'];
$rString .= "&lotStatus" . $i . "=" . $row['status_id'] . "&";
}
$rString .= "totalNum=" . $i . "&";

// echo the final output lines
echo $rString;

?>

and here is the latest AS:

// 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");


thanks for taking a look