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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
maisam_uk
Forum Newbie
Posts: 2
Joined: Wed Sep 23, 2009 10:58 am

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

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

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;
 
Here is my php code (updatescore.php)

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

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

Code: Select all

$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 :)
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

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

Post by Darhazer »

You are missing the quotes in your UPDATE statement:

Code: Select all

$update_str = "UPDATE highscores SET " . "highscores.nickname='" . $name . "', highscores.dateposted='" . $date . "', highscores.score=" . $score . " WHERE highscores.id=" . $id;
And by the way, it's vulnerable to SQL Injection
maisam_uk
Forum Newbie
Posts: 2
Joined: Wed Sep 23, 2009 10:58 am

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

Post by maisam_uk »

thanks,

earlier i tried this:

Code: Select all

$update_str = "UPDATE highscores SET nickname='$name',dateposted='$date',score='$score' WHERE id='$id'";  
and it did the trick ;)

i'v also used mysql_real_escape_string to get over the security problem!

i appreciate u putting your time on this man. cheers ;)
Post Reply