Hello:

I have a query where I'm trying to insert data using the ON DUPLICATE KEY syntax.

When I run the script, I receive the following message:
FS30YF100FS30YF100Unknown column 'a' in 'field list'.

Can someone help me with this error message?

Thank you in advance. If you need any other info, I'll be happy to pass it along. I just don't know what info you may need so I'm showing the script that produces the error message.

This is the code:

<?php

//session_start();
include('header.html');

require_once ('mysql_connect.php');

$query = "SELECT a.id, a.lender_code, b.id, b.program_code, CONCAT(a.lender_code,b.program_code) AS prl FROM lender a, loan_program b
          WHERE a.id=$_SESSION[lender_id] AND b.id=$_SESSION[program_id]";

$result = @mysql_query ($query) or die (mysql_error()); 

if ($result) {

while($row=@mysql_fetch_array($result, MYSQL_ASSOC))
{
$lender_code = $row['lender_code'];
$program_code = $row['program_code'];
$prl = $row['prl'];

echo $lender_code;
echo $program_code;
echo $prl;
}
}


$query2 = "INSERT INTO test (program_id, program_code, lender_id, lender_code, prog_lend, rate, price, date, time)
           VALUES ('$_SESSION[program_id]','$_SESSION[program_code]','$_SESSION[lender_id]','$_SESSION[lender_code]','$prl','$_SESSION[rate1]','$_SESSION[price1]', CURRENT_DATE, CURRENT_TIME),
                  ('$_SESSION[program_id]','$_SESSION[program_code]','$_SESSION[lender_id]','$_SESSION[lender_code]','$prl','$_SESSION[rate2]','$_SESSION[price2]', CURRENT_DATE, CURRENT_TIME),
                  ('$_SESSION[program_id]','$_SESSION[program_code]','$_SESSION[lender_id]','$_SESSION[lender_code]','$prl','$_SESSION[rate3]','$_SESSION[price3]', CURRENT_DATE, CURRENT_TIME),
                  ('$_SESSION[program_id]','$_SESSION[program_code]','$_SESSION[lender_id]','$_SESSION[lender_code]','$prl','$_SESSION[rate4]','$_SESSION[price4]', CURRENT_DATE, CURRENT_TIME)
           ON DUPLICATE KEY UPDATE program_id=VALUES(a), program_code=VALUES(b), lender_id=VALUES(c), lender_code=VALUES(d), prog_lend=VALUES(e), rate=VALUES(f),
                  price=VALUES(g), date=VALUES(h), time=VALUES(i)";
$result1 = mysql_query ($query2) or die (mysql_error());   

header("Location:admin_update_rate.php");        

?>

    1. Don't double-post

    2. VALUES(a), VALUES(b), ..., VALUES(i)... MySQL doesn't know what a, b, ..., i is !

      what is "a"? in the first query it is an alias for the table lender. in the 2nd one it is... well - nothing really. you are using it like a field in the table test - i assume there is no such column (like the error tells you).

      if you want to insert the value "a", put it in quotes. if you want the value from another field, put the value of that field there. also, i am not familiar with that VALUES() syntax in that last part of the query.

      http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html

        Hi,

        I'm trying to do a simple insert/update to a table. If something doesn't exist insert it, if it does update it.

        This is basically what I want to do. I have a form which selects a program and lender and the person enters four rates and prices. If the program/lender doesn't exist in the table, insert the data. If the program/lender does exist, update the data.

        Today, the rates would be 5.000, 5.500, 5.750, 6.000.
        Tomorrow the rates change so the person selects the program/lender and enters the new rates. The new rates would be: 7.000, 7.250, 7.750, 8.000.

        I need to write an update statement which will take 5.000 and update it with 7.000; 5.500 with 7.250, 5.750 with 7.750, 6.000 with 8.000.

        My table looks like this:

        id | program_id | lender_id | rate

        1 | 1 | 1 | 5.000
        2 | 1 | 1 | 5.500
        3 | 1 | 1 | 5.750
        4 | 1 | 1 | 6.000

        So when the new rates are entered the table should look like this:

        id | program_id | lender_id | rate

        1 | 1 | 1 | 7.000
        2 | 1 | 1 | 7.250
        3 | 1 | 1 | 7.750
        4 | 1 | 1 | 8.000

        The field id is the primary key. I hope this helps a bit to explain what I'm trying to accomplish.

        I'm not sure how to write the update statement which will allow me to udpate multiple values for one column, ie: rate and price. There are four rates and four prices per program/lender combo.

        Any ideas of how I can do this?

        Thanks for the help.

          Write a Reply...