Unknown column '...' in 'field list'! PLEASE HELP!!!

[maisam_uk]

hi guys, i have come to a bit of a problem in my php code that UPDATEs a table record in mysql database. my code seems perfectly fine and it works in some cases but i can't make it fully work!!!

 

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!):

 

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;

 

Here is my php code (updatescore.php)

 

<?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;
?>

 

now i know that my flash form works correctly as i have traced the values it sends to the updatescore.php and they were correct, i also used the error msg part of the php script (at the buttom of the code above) to print out the values of each variable (i.e. nickname, dateposted and score) and they are all showing the correct values.

 

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:

 

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

 

with the test data (given at the problem scenario section), trace(update_lv.nickname) returns: new_nickname, and trace (update_lv) returns: dateposted=2009%2D09%2D23&score=200&nickname=new%5Fnickname&id=13&onLoad=%5Btype%20Function%5D. both of which are correct.

 

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:

$ins_str = "INSERT INTO highscores VALUES (NULL, '".addslashes($_GET['nickname'])."', '".$_GET['dateposted']."', '".$_GET['score']."')";

but what i've noticed here there is no mention of column names in the code where as in my php update code i'm giving the names of the columns, but how else can i write an update code?!

 

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

 

 

[nuttycoder]

try this:

 

you only need to call the table name followed by the column name when using multiple tables, also you don't need to concate the data you can use a single quote inside doulbe quotes instead.

 

$update_str = "UPDATE highscores SET nickname='$name',dateposted='$date',score='$score' WHERE id='$id'";

[maisam_uk]

nuttycoder,

 

IT WORKED!!!

 

u are a genius! i knew it would be something so simple! i've been picking my brain all day over this.

 

the reason i used the name of the table followed by the column name was because i'd tried everything n i nothing worked so i just tried that to make sure its 100% accurate, i knew it wasn't gonna make a difference but still had to try! but i think what worked was simplifying the query inside one set of double quotes!

 

thank you so much!

 

[nuttycoder]

sometimes just takes a second pair of eyes.

[maisam_uk]

it sure does! thanks man