[RESOLVED] Retreive records from database

[Nightwalker83]

Hi,

As you might remember I wrote code here (http://www.vbforums.com/showthread.php?t=574247) to save a customer registration form to a database. Now what I want to do is retrieve the saved information, this is the php code:


//select user record where username and password matches
$query = "select * from Customers where Username='$username'AND Password='$password'";

if (!($result = mysql_query($query))){ //if query fails echo message and exit
echo 'authenticated=queryFailed';
exit;
}
if ($row = mysql_fetch_array($result)) {
$cid = $data['cid'];
$fname = $data['fname'];
$lname = $data['lname'];
$snum = $data['snum'];
$sname = $data['sname'];
$suburb = $data['suburb'];
$pcode = $data['pcode'];
$country = $data['country'];
$phone = $data['phone'];
$email = $data['email'];
}


I tried to follow the steps here (http://php.about.com/od/phpwithmysql/ss/mysql_php_2.htm)! However, it is not working. In the flash project I have:


function sendData(event:MouseEvent):void {
var url:String = "http://localhost/flash/dynamicWebsite/custLogin.php";
var req:URLRequest = new URLRequest(url);
req.method = URLRequestMethod.GET;
req.data = variables;
tiCustomerID.text = variables.cid;
tiFName.text = variables.fname;
tiLName.text = variables.lname;
tiSNum.text = variables.snum;
tiSName.text = variables.sname;
tiSuburb.text = variables.suburb;
tiPCode.text = variables.pcode;
tiCountry.text = variables.country;
tiPhone.text = variables.phone;
tiEmail.text = variables.email;
tiUser.text = variables.user;
tiPassword.text = variables.upassword;
//Send data to php script
Dataloader.load(req);
}


Thanks,


Nightwalker

[SambaNeko]

All of the $data['...'] should be $row['...'].

[Nightwalker83]

Still not working! This is all the php code:


<?php
// Database connection variables
$dbDatabase = "BazaarCeramics";
//convert the post variables from flash to local variables
$username = $_POST['username'];
$password = $_POST['uPassword'];
//connect to server or exit
if (!($conn = mysql_connect("localhost", "admin", "") )){
echo 'result=connection+failed';
exit;
}
//connect to database or exit
if (!(mysql_select_db($dbDatabase, $conn))){
echo 'message=db+selection+failed';
exit;
}
//select user record where username and password matches
$query = "select * from Customers where Username='$username'AND Password='$password'";

if (!($result = mysql_query($query))){ //if query fails echo message and exit
echo 'authenticated=queryFailed';
exit;
}
if ($row = mysql_fetch_array($result)) { //if user exists
//the following is just one of many different ways of retrieving the information from the select query
//the fetch_array command returns one record/row from the db formatted as an indexed or associative array.
echo "authenticated=true";
$cid = $row['cid'];
$fname = $row['fname'];
$lname = $row['lname'];
$snum = $row['snum'];
$sname = $row['sname'];
$suburb = $row['suburb'];
$pcode = $row['pcode'];
$country = $row['country'];
$phone = $row['phone'];
$email = $row['email'];
}else { //user doesn't exist
echo "authenticated=false";
}
?>


The variables supposed to transfer the data to the form don't send the data after user logs in.

Edit:

It seems that since I am using to frames in Flash the data from the php file verifying the user is working. However, since the second page loads when the user logs in the data doesn't submitted.

[SambaNeko]

It seems that since I am using to frames in Flash the data from the php file verifying the user is working. However, since the second page loads when the user logs in the data doesn't submitted.

I don't understand what you mean here.

Flash doesn't import PHP's variables, so simply setting all of this data...

$cid = $row['cid'];
$fname = $row['fname'];
$lname = $row['lname'];
$snum = $row['snum'];
$sname = $row['sname'];
$suburb = $row['suburb'];
$pcode = $row['pcode'];
$country = $row['country'];
$phone = $row['phone'];
$email = $row['email'];

...isn't doing anything for Flash. You need to echo it out in a way that Flash understands. Try changing the above block into:

echo "cid=".$row['cid'].
"&fname=".$row['fname'].
"&lname=".$row['lname'].
"&snum=".$row['snum'].
"&sname=".$row['sname'].
"&suburb=".$row['suburb'].
"&pcode=".$row['pcode'].
"&country=".$row['country'].
"&phone=".$row['phone'].
"&email=".$row['email'];


Also noticed in your sendData() function in your first post... Should this - and all the ones that follow - be the other way around?

tiCustomerID.text = variables.cid;

You want to send "variables" off to the URL Request, but you're assigning values to text fields in Flash, not to "variables."

[Nightwalker83]

I don't understand what you mean here.


What I'm saying is I'm using two frames, the first is for the user log-in and the second is for the user details. The user log-in works but the details retrieval does not.


...isn't doing anything for Flash. You need to echo it out in a way that Flash understands. Try changing the above block into:

echo "cid=".$row['cid'].
"&fname=".$row['fname'].
"&lname=".$row['lname'].
"&snum=".$row['snum'].
"&sname=".$row['sname'].
"&suburb=".$row['suburb'].
"&pcode=".$row['pcode'].
"&country=".$row['country'].
"&phone=".$row['phone'].
"&email=".$row['email'];




Also noticed in your sendData() function in your first post... Should this - and all the ones that follow - be the other way around?

tiCustomerID.text = variables.cid;

You want to send "variables" off to the URL Request, but you're assigning values to text fields in Flash, not to "variables."

I just tried that but it still did not work! What I'm trying to do is use the username and password for the customer log-in on the first frame and a retrieval for the customers details to the form on the second frame.

Edit:

I have managed to retrieve one field!

[SambaNeko]

What I'm saying is I'm using two frames, the first is for the user log-in and the second is for the user details. The user log-in works but the details retrieval does not.

I see. Could it be then that your second frame is taking action before the first frame's actions are completed? How are you controlling the playhead's movement between the frames?

[Nightwalker83]

I see. Could it be then that your second frame is taking action before the first frame's actions are completed? How are you controlling the playhead's movement between the frames?

The movement between frame is done via submit button! The flash determines if the data entered matches the records of the database and if so gives the user access to the second frame.

Although, like I said above I manages to retrieve one of the fields (the id field). However, that is the only field that works, this is the php code I am using:


if ($row = mysql_fetch_array($result)) { //if user exists
//the following is just one of many different ways of retrieving the information from the select query
//the fetch_array command returns one record/row from the db formatted as an indexed or associative array.

//php Database
$cid = $row['cid'];
$fname = $row['Fname'];
$lname = $row['Lname'];
$snum = $row['Housenum'];
$sname = $row['Streetname'];
$suburb = $row['Suburb'];
$pcode = $row['Postcode'];
$country = $row['Country'];
$phone = $row['Phone'];
$email = $row['Email'];
$password = $row['Password'];
echo "cid=$cid& t=$snum";
//echo "name=$lname&snum=$snum&sname=$sname&suburb=$suburb&pcode =$pcode&country=$country&phone=$phone&email=$email&password=$password";
}


The flash code remains the same except the method is "Post" instead of "Get".

[SambaNeko]

Okay, made myself a sample Flash and PHP file. Here's my PHP:

<?php
//db connection

$sql_result = mysql_query("SELECT * FROM myTable WHERE rowid=10");
if($row = mysql_fetch_array($sql_result)){
echo "comments=".$row['comments']."&name=".$row['name']."&iu=".$row['i_url'];
}
?>


And here's my Actionscript:

var Dataloader:URLLoader = new URLLoader();
Dataloader.dataFormat = URLLoaderDataFormat.VARIABLES;
Dataloader.load(new URLRequest("getdata.php"));
Dataloader.addEventListener(Event.COMPLETE, loaderCompleteHandler);

function loaderCompleteHandler(event:Event):void {
myText.text = Dataloader.data.name;
}


Where "myText" is just a text field with the name "myText," for displaying my result. The last part of "Dataloader.data.name" can be changed to any of the vars I returned in my PHP ("comments," "name," or "iu").

Aaaaanything useful here?...

[Nightwalker83]

Yes, I've nearly figured it out anyway just have to figure out the last two variables.

Edit:

Damn! The password field is not being transfer I need to trace the string inside the php file!

[Nightwalker83]

I finally manage get the retrieve system working using this php code.


if ($row = mysql_fetch_array($result)) { //if user exists
//the following is just one of many different ways of retrieving the information from the select query
//the fetch_array command returns one record/row from the db formatted as an indexed or associative array.
//convert the post variables from local variables to flash
//php Database
$cid = $row['cid'];
$firstname = $row['FName'];
$lastname = $row['LName'];
$housenum = $row['Housenum'];
$streetname = $row['Streetname'];
$suburb = $row['Suburb'];
$pcode = $row['Postcode'];
$country = $row['Country'];
$phone = $row['Phone'];
$email = $row['Email'];
$upassword=$row['Password'];
// Match the following line to the variabes above.
echo "cid=$cid&fname=$firstname&lname=$lastname&snum=$housenum&sname=$streetname&suburb=$suburb&pcode=$pcode&country=$country&phone=$phone&email=$email&upassword=$upassword";
}


However, I still have not idea what was stopping the password field data from appearing originally when all the another fields worked.

Edit:

Maybe it was because I was using "Password" in the textbox name in Flash but it was a reserved key word.