Link to home
Create AccountLog in
Avatar of chtru
chtru

asked on

Updating or inserting records into mysql using flex through php

Could someone help me with some simple code that I can then work with to get my application up and running.

I need to build a flex application that can retrieve a record, update it if desired and also post new records to a mysql database. I have php5 on my server fyi.
I'm also conscious of security concerns but don't know much beyond that however I have a basic understanding of sql statements and very basic flex and php knowledge.

Example db table
serviceID
serviceName
servicePhone
serviceEmail
serviceLink

I had a go at creating some php code to do this from searching this site but it's not getting records from the flex app?  
Thanks everyone.

<?php
$hostname_conn = "localhost";
$database_conn = "mydbname";
$username_conn = "myusername";
$password_conn = "mypassword";
$conn = mysql_pconnect($hostname_conn, $username_conn, $password_conn)
 
$testUserName = $_POST['testUserName'];
$testEmail = $_POST['testEmail'];
mysql_select_db($database_conn, $conn);
 
$sql = sprintf("INSERT INTO users (userid , username , emailaddress) VALUES ('', %s, %s);",
	sql_quote($testUserName), sql_quote($testEmail));
 
$postService = mysql_query($sql, $conn) or die(mysql_error());
	
function sql_quote( $value ) 
{ 
  if( get_magic_quotes_gpc() ) { 
    $value = stripslashes( $value ); 
  } 
  
  //check if this function exists 
  if( function_exists( "mysql_real_escape_string" ) ) { 
    $value = mysql_real_escape_string( $value ); 
  } else { 
     $value = addslashes( $value ); 
  } 
  return $value; 
}

Open in new window

SOLUTION
Avatar of Gary Benade
Gary Benade
Flag of South Africa image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of chtru
chtru

ASKER

hey hobbit.  I gave this a go but it didn't work. Could it be a problem with my PHP file? Or perhaps because I have an autonumber field in that table?

apit i'll try that solution but I was hoping to get it working the first way as well as I'm more familiar how to build an interface that way.
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
P.S the above was flex code :-)

Avatar of chtru

ASKER

Hi Robinson,
Thanks for writing back.  It's not working for me. I'm thinking that your flex code is fine, perhaps it's my php file?
I've attached as code my php, and also what I did with your flex code (just in case I made a mistake).
Thanks mate.

The text box I setup is showing 'resultok' so the posting appears to be fine. perhaps it's my php file? nothing is showing up in the database.  how would i alter my php file to spit up an error so I can work out what's wrong with it?

<?php
$hostname_conn = "localhost";
$database_conn = "mydb";
$username_conn = "myusername";
$password_conn = "mypassword";
$conn = mysql_pconnect($hostname_conn, $username_conn, $password_conn)
 
$testUserName = $_POST['testUserName'];
$testEmail = $_POST['testEmail'];
mysql_select_db($database_conn, $conn);
 
$sql = sprintf("INSERT INTO users (userid , username , emailaddress) VALUES ('', %s, %s);",
	sql_quote($testUserName), sql_quote($testEmail));
 
$postService = mysql_query($sql, $conn) or die(mysql_error());
	
function sql_quote( $value ) 
{ 
  if( get_magic_quotes_gpc() ) { 
    $value = stripslashes( $value ); 
  } 
  
  //check if this function exists 
  if( function_exists( "mysql_real_escape_string" ) ) { 
    $value = mysql_real_escape_string( $value ); 
  } else { 
     $value = addslashes( $value ); 
  } 
  return $value; 
} 
 
 
Flex code
<?xml version="1.0" encoding="utf-8"?>
<mx:Application xmlns:mx="http://www.adobe.com/2006/mxml" width="535" height="345">
<mx:Script>
  <![CDATA[
    import mx.controls.Alert;
    import mx.events.PropertyChangeEvent;
    import mx.events.CollectionEventKind;
    import mx.events.CollectionEvent;
    import mx.events.DataGridEvent;
    import mx.controls.TextInput;
    import mx.rpc.events.ResultEvent;
    import mx.collections.ArrayCollection;
 
   
   function ResFunction(event):void {
   	testtext.text="resultok";
   }
   function FaultFunction(event):void {
   	testtext.text="fault";
   }
   function sendServiceFunct(event):void {
   	httpLogin.send();
   }
 
  ]]>
</mx:Script>
	<mx:HTTPService id="httpLogin" url="http://www.enablebydesign.net/kn/postservice.php" result="ResFunction(event)" fault="FaultFunction(event)" useProxy="false" showBusyCursor="true" method="POST">
		<mx:request xmlns="">
			<testUserName>
				jon
			</testUserName>
			<testEmail>
				testemailflex
			</testEmail>
		</mx:request>
	</mx:HTTPService>
	<mx:Button click="sendServiceFunct(event)"/>
	<mx:Text id="testtext" text="start"/>
</mx:Application>

Open in new window

ok here goes

i created a usertable lacally to test this for you and have tested all code

create a new web based project and copy paste the following mxml code


<?xml version="1.0" encoding="utf-8"?>
<mx:Application xmlns:mx="http://www.adobe.com/2006/mxml" layout="absolute">
 
	<mx:String id="username">Chris Robinson</mx:String>
	<mx:String id="email">cghrmauritius@gmail.com</mx:String>
	
	<mx:HTTPService id="httpUserInput" url="mysqltest.php" result="ResultFunction()" fault="FaultFunction(event)" useProxy="false" showBusyCursor="true" method="POST">
		<mx:request xmlns="">
			<action>
				insertuser
			</action>
			<username>
				{username}
			</username>
			<emailaddress>
				{email}
			</emailaddress>
		</mx:request>
	</mx:HTTPService>
	<mx:Script>
		<![CDATA[
			import mx.controls.Alert;
			import mx.rpc.events.FaultEvent;
			private function ResultFunction():void{
				Alert.show("Result : " + httpUserInput.lastResult.insertedid);
			}
			
			private function FaultFunction( evt:FaultEvent ) : void {
				Alert.show("Error: " + evt.message);
			}
			
		]]>
	</mx:Script>
	<mx:Button x="167" y="150" label="Send" click="{httpUserInput.send()}"/>
</mx:Application>
 
 
 
create a php file in the same destination as the mxml file called mysqltest.php
 
<?php
ini_set("display_errors","0");
error_reporting(2047);
 
$hostname = "localhost";
$database = "testdemo";
$username = "chris";
$password = "1234";
 
mysql_pconnect($hostname, $username, $password) or trigger_error(mysql_error(),E_USER_ERROR); 
mysql_select_db($database);
 
 
foreach(array_keys($_POST) as $field){
    $_POST[$field] = trim($_POST[$field]);
}
 
extract($_POST);
 
 
if($action=="insertuser"){
 
	$res = mysql_query("INSERT INTO users (username,emailaddress) VALUES ('$username','$emailaddress')");
	xml("insertedid",mysql_insert_id());
 
}
 
function xml($key,$value){
    $key = str_replace("<","&lt;",$key);
    $value = str_replace("<","&lt;",$value);
    echo "<$key>$value</$key>\n";
}
 
?>
 
 
Ok as long as your db table is correct this will work

Open in new window

what i have done here is run a php script for an action called insertuser, this will enable you to put many different insert and select statements in to the one php file and only run a single one, depending on what value you put in the mxml request action.

The return value which is alerted to you when you click send, is the identity of inserted record which i often find usefull to get back.

Good Luck

let me know if you need anything else
Avatar of chtru

ASKER

Thanks Robinson.
I copied and pasted it in but got this error.  I can follow your code but for some reason just can't get it to work. I've dropped the table on the database and recreated it with only those two fields with no requirements on it just in case but still am not getting it to work.  When I type the php url directly into the browser it goes there without an error.

I tried removing the 'insertedid' property from the alert code to stop this error from coming up, and then it appears to post successfully but there's nothing in the database still and the alert box shows 'result:' as to be expected, but I would have thought it would have posted anyway?
Do you think php is encountering an error when it's trying to post? Is there a way we can see what that is?

Error below.
Thanks again for sticking it out this far.

ReferenceError: Error #1069: Property insertedid not found on String and there is no default value.
      at SubmitServices/ResultFunction()[C:\Documents and Settings\Conrad Truscott\My Documents\Flex Builder 3\SubmitServices\src\SubmitServices.mxml:25]
      at SubmitServices/__httpUserInput_result()[C:\Documents and Settings\Conrad Truscott\My Documents\Flex Builder 3\SubmitServices\src\SubmitServices.mxml:7]
      at flash.events::EventDispatcher/dispatchEventFunction()
      at flash.events::EventDispatcher/dispatchEvent()
      at mx.rpc.http.mxml::HTTPService/http://www.adobe.com/2006/flex/mx/internal::dispatchRpcEvent()[E:\dev\flex_3_beta3\sdk\frameworks\projects\rpc\src\mx\rpc\http\mxml\HTTPService.as:282]
      at mx.rpc::AbstractInvoker/http://www.adobe.com/2006/flex/mx/internal::resultHandler()[E:\dev\flex_3_beta3\sdk\frameworks\projects\rpc\src\mx\rpc\AbstractInvoker.as:198]
      at mx.rpc::Responder/result()[E:\dev\flex_3_beta3\sdk\frameworks\projects\rpc\src\mx\rpc\Responder.as:48]
      at mx.rpc::AsyncRequest/acknowledge()[E:\dev\flex_3_beta3\sdk\frameworks\projects\rpc\src\mx\rpc\AsyncRequest.as:81]
      at DirectHTTPMessageResponder/completeHandler()[E:\dev\flex_3_beta3\sdk\frameworks\projects\rpc\src\mx\messaging\channels\DirectHTTPChannel.as:387]
      at flash.events::EventDispatcher/dispatchEventFunction()

Avatar of chtru

ASKER

Thanks guys finally worked it out. Cheers.
The Error being thrown that you have submitted is because the return from the httpservice is directly looking for the insertedid value which you say you have removed from the php.

This appears to be a php / mysql error. the best way to check this is to create a seperate test php form with two input fields which posts the action of the form to the mysqltest.php page.

This should insert a record into the database. If it doesnt and i am expecting it not to! then you have a php / mysql error but it should provide you the error in the php

What version of php are you running and what version of mysql are you running also have you tested the database user credentials locally, i.e. using sqlyog if you havnt got this application i would suggest you download it from
http://www.webyog.com/en/downloads.php
Test your Db first by connecting with the user credentials you are using in your php script
then try inserting a record via the sqlyog sql console

Then test your php using the above method of using a test php page to submit the data

Also what version of flex builder  are you using?

if you need me to create the test php page let me know and ill code it for you

But this is definately an error not relating to flex because my test project is fine and working perhaps i should post you my project file as a zip.