Sending values from Flash (via LoadVars) to MySQL database (via PHP)

[ParkerDigital]

I've done this before in the past without any problems, but this time something has gone wrong somewhere along the line. I need help!

The ActionScript I'm using to send the variables to the PHP script is;

jobApp.load("http://www.*******.com/php/job-application.php?jobID="+jobID+"&candID="+candID+"&date="+formdDate);

...jobApp is the LoadVars object, obviously. I know I should probably add the values to the LoadVars object instead of tagging them onto the URL, but it's always worked for me in the past!

Then, the PHP code is as follows:

$appdate = $_REQUEST['date'];
$jobID = $_REQUEST['userID'];
$candID = $_REQUEST['candID'];
$jobapp_toDB = mysql_query("INSERT INTO jobApplications (id, date, candID, jobID) VALUES ('', '$appdate', $candID, $jobID)") 
                    or die("failed=yes"); 

I know I should be using $_GET instead of $_REQUEST, but again, it's always worked for me in the past! One of these days I'll get rid of all my bad programming habits

When I test the PHP script in a browser (adding the values onto the end of the URL, obviously), it just gives me "failed=yes", so the mysql query has obivously failed

[hxbro]

is id been used as an auto increment integer ?

Try

$jobapp_toDB = mysql_query("INSERT INTO jobApplications (date, candID, jobID) VALUES ('$appdate', $candID, $jobID)")
or die("failed=yes");

Check the date is correctly formatted if you're putting it in a date field, also I'm guessing $candID and jobID are supposed to be integers so you need to make sure they are valid.

In fact:

$adddate = $_GET['date'];
$jobID = $_GET['userID'];
$candID = $_GET['candID'];
settype($jobID, "integer");
settype($candID, "integer");

$query = sprintf("INSERT INTO jobApplications (date, candID, jobID) VALUES ('%s', %d, %d)", addslashes($adddate), $jobID, $candID);

$jobapp_toDB = mysql_query($query) or die "failed=yes"; 

You should really add in some code before building the query to make sure the data is valid too, e.g. is userID a valid integer for my app e.g. > 0 and the same for candID.

By splitting the query off you can easily echo back what the query is so you can test it directly in the database to get the database error thats stopping it running, and you are formatting the input removing some potential sql injection problems.

Is adddate the current date ? if so you don't need to pass it and you could use

$query = sprintf("INSERT INTO jobApplications (date, candID, jobID) VALUES (CURDATE(), %d, %d)", $jobID, $candID);

[ParkerDigital]

Wow, thanks for that.

I've checked the values being output by Flash using the trace command, and they seem OK, but you're right - I should be validating the data in PHP.

And yes, $appdate is the current date. I sent it from Flash as I know how to get a date with ActionScript but not with PHP! How does CURDATE(), %d, %d output the date? I need it to be yyyy-mm-dd for input into the database

[philbob]

Hi ParkerDigital,

hxbro suggested using the CURDATE() mysql funtion to auto-generate the date.
See http://dev.mysql.com/doc/refman/4.1/...functions.html (or http://dev.mysql.com/doc/refman/5.0/...functions.html for mysql 5) for more info on it.

He's also made a very good suggestion using the 'sprintf' function for generating your sql query in a more secure way (along with addslashes for strings). Info on how sprintf can be used is here http://uk.php.net/sprintf.

If you want to keep the date the way you have it, you can use php to generate it in the format you mentioned with:

<?php
echo date("Y-m-d", time());
?>