i'm using a flash form with data grid which loads up with data from the database! it works perfectly fine. i can add a new record to the database using this form and i can also delete records. however i've added an Update button to it and ran into some problems.
PROBLEM SCENARIO:
test data: original database record on the flash data grid (data grid header: Nickname | Score | Date Posted):
Spiderman 100 2009-08-23
record after the change:
new_nickname 200 2009-09-23
after selecting a record on the flash data grid, changing the values and clicking the 'Update Selected' button i get this mysql error: Unknown column 'new_nickname' in 'field list'. it somehow thinks the 'new_nickname' value is referring to the name of a column in the table!
i have a bit of code that prints out the result of the procedure (success or fail) on the form. as a test i outputted the complete UPDATE statement in my php script to see if i have put something wrong or missed something out. here is the outcome:
Unknown column 'new_nickname' in 'field list' id: 13 nickname: new_nickname score: 200 dateposted: 2009-09-23 MY UPDATE QUERY: UPDATE highscores SET highscores.nickname=new_nickname, highscores.dateposted=2009-09-23, highscores.score=200 WHERE highscores.id=13
as you can see everything makes sense and should work! but it doesnt! the strange thing is if i change the value of the 'Nickname' field on the flash form to a number, i.e. 10, the update statement works! BIZZAR! same thing goes for other fields, for example if i put a text value in 'Date Posted' it gives me the same error and thinks that text value is referring to a name of a column in the table! you can see in my table structure that the 'nickname' field in my table is a VARCHAR so it doesn't just have to be a number in order for the update to be successful. another thing, i also get an error when i have a space in the nickname field, i guess because it sees it as a column name and you're not allowed to have space in the table column name.
Here is my table in the database (only one table!):
Code: Select all
CREATE TABLE highscores (
id mediumint(9) NOT NULL auto_increment,
nickname varchar(50) NOT NULL default '',
dateposted varchar(10) NOT NULL default '',
score mediumint(9) NOT NULL default '0',
PRIMARY KEY (id)
) ENGINE=MyISAM;
Code: Select all
<?php
// fill with correct data for your server configuration
$server = "localhost";
$username = "xxx";
$password = "yyy";
$database = "zzz";
$id = stripslashes($_GET['id']);
$name = stripslashes($_GET['nickname']);
$score = stripslashes($_GET['score']);
$date = stripslashes($_GET['dateposted']);
if (!mysql_connect($server, $username, $password)) {
$r_string = '&errorcode=1&';
} elseif (!mysql_select_db($database)) {
$r_string = '&errorcode=2&';
} else {
if ($_GET['id'] > 4) {
$update_str = "UPDATE highscores SET " . "highscores.nickname=" . $name . ", highscores.dateposted=" . $date . ", highscores.score=" . $score . " WHERE highscores.id=" . $id;
if (!mysql_query($update_str)) {
// I used this bit to output my whole query string to make sure it is right!
$msg = mysql_error() . " id: " . $id . " nickname: " . $name . " score: " . $score . " dateposted: " . $date . " MY UPDATE QUERY: " . $update_str;
$r_string = '&errorcode=3&msg='.$msg;
} else {
$r_string = '&errorcode=0&';
}
} else {
$r_string = '&errorcode=4&';
}
}
echo $r_string;
?>
if it helps here is the bit of Action Scrip 2 in flash that sends the values of the selected field on the form to the php file:
Code: Select all
function updateRecord() {
// remember which element of the array is to be updated
updateIndex = scores_dg.selectedIndex;
// get the id of the record to update, to pass it to updatescore.php
var id:Number = scores_dg.selectedItem.record;
update_lv.id = id;
var nickname:String = scores_dg.selectedItem.nickname;
update_lv.nickname = nickname;
var score:Number = scores_dg.selectedItem.score;
update_lv.score = score;
var dateposted:String = scores_dg.selectedItem.dateposted;
update_lv.dateposted = dateposted;
//trace(update_lv.nickname);
//trace (update_lv);
update_lv.sendAndLoad(filepath + "updatescore.php", update_lv, "GET");
}
update_btn.addEventListener("click", updateRecord);
as explained above i also traced this down to the php script to test all the variables and the output was:
nickname: new_nickname score: 200 dateposted: 2009-09-23 MY UPDATE QUERY: UPDATE highscores SET highscores.nickname=new_nickname, highscores.dateposted=2009-09-23, highscores.score=200 WHERE highscores.id=13
the php code in insertscore.php which handles the procedure to add a record from the data grid to the database works perfectly fine:
Code: Select all
$ins_str = "INSERT INTO highscores VALUES (NULL, '".addslashes($_GET['nickname'])."', '".$_GET['dateposted']."', '".$_GET['score']."')";Please can you take a look and guide me to the right direction coz i'm going round circles and not getting any further. to me everything seems ok but i'm sort of new to this so i'd appreciate it if you could help me with this bizzzzar problem.
thank you in advance