2. PHPRunner General questions
  3. [SOLVED]  On Duplicate Update
This topic is locked
[SOLVED]

 On Duplicate Update

 		12/18/2014 7:44:20 AM
PHPRunner General questions
Graphix author

Hi All
been a long time since i posted <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=22853&image=1&table=forumtopics' class='bbc_emoticon' alt=':(' /> merry christmas to all and your families <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=22853&image=2&table=forumtopics' class='bbc_emoticon' alt=':D' />
Can anyone help me with a project i am trying to do i have a database where customers can purchase support blocks i have 7 tables when a customer places a purchase order for say 5 credits it writes the transaction to the purchase table,

i also have a kredits table where a balance of credits is kept if a customer purchased credits previously i want the total of credits increased for that customer so if they already had 2 want the table to update the total to 7 if they had not purchased before

then i just want it to create the record.
i have this code in the purchase add page after record added but my SQL code does not work, i cannot work out where i have gone wrong but i think it is to do with adding the blocks, can anyone tell me where i have gone wrong.
global $conn;

$strSQLSave = "INSERT INTO kredits SET customer_id= '".$values["customer_id"]."',blocks= '".$values["blocks"]."',

cost= '".$values["cost"]."',account_no= '".$values["account_no"]."',

ON DUPLICATE KEY UPDATE blocks=blocks+ '".$values["blocks"]."'";
db_exec($strSQLSave,$conn);
thank you

Kev
romaldus 12/18/2014



Hi All
been a long time since i posted <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=76470&image=1&table=forumreplies' class='bbc_emoticon' alt=':(' /> merry christmas to all and your families <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=76470&image=2&table=forumreplies' class='bbc_emoticon' alt=':D' />
Can anyone help me with a project i am trying to do i have a database where customers can purchase support blocks i have 7 tables when a customer places a purchase order for say 5 credits it writes the transaction to the purchase table,

i also have a kredits table where a balance of credits is kept if a customer purchased credits previously i want the total of credits increased for that customer so if they already had 2 want the table to update the total to 7 if they had not purchased before

then i just want it to create the record.
i have this code in the purchase add page after record added but my SQL code does not work, i cannot work out where i have gone wrong but i think it is to do with adding the blocks, can anyone tell me where i have gone wrong.
global $conn;

$strSQLSave = "INSERT INTO kredits SET customer_id= '".$values["customer_id"]."',blocks= '".$values["blocks"]."',

cost= '".$values["cost"]."',account_no= '".$values["account_no"]."',

ON DUPLICATE KEY UPDATE blocks=blocks+ '".$values["blocks"]."'";
db_exec($strSQLSave,$conn);
thank you

Kev




global $conn;

$strSQLInsert = "insert into kredits ( customer_id, blocks, cost, account_no) values

(".$values["customer_id"].",".$values["blocks"].",".$values["cost"].",".$values["account_no"].")

ON DUPLICATE KEY UPDATE blocks = blocks + ".$values["blocks"]."";

db_exec($strSQLInsert,$conn);
Graphix author 12/18/2014





global $conn;

$strSQLInsert = "insert into kredits ( customer_id, blocks, cost, account_no) values

(".$values["customer_id"].",".$values["blocks"].",".$values["cost"].",".$values["account_no"].")

ON DUPLICATE KEY UPDATE blocks = blocks + ".$values["blocks"]."";

db_exec($strSQLInsert,$conn);



Hi Romaldus
thank you for this but I'm still getting an error:
Unknown column 'BECURIOU' in 'field list'
the account_no field is a text field not int BECURIOU is an account number
its taking the value of account_no and using it as the field name how do i fix that <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=76471&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' />
regards

Kevan
Sergey Kornilov admin 12/18/2014

Add single quotes around account number:

global $conn;

$strSQLInsert = "insert into kredits ( customer_id, blocks, cost, account_no) values

(".$values["customer_id"].",".$values["blocks"].",".$values["cost"].",'".$values["account_no"]."')

ON DUPLICATE KEY UPDATE blocks = blocks + ".$values["blocks"]."";

db_exec($strSQLInsert,$conn);
Graphix author 12/18/2014



Add single quotes around account number:

global $conn;

$strSQLInsert = "insert into kredits ( customer_id, blocks, cost, account_no) values

(".$values["customer_id"].",".$values["blocks"].",".$values["cost"].",'".$values["account_no"]."')

ON DUPLICATE KEY UPDATE blocks = blocks + ".$values["blocks"]."";

db_exec($strSQLInsert,$conn);



Thank you Sergey

i have already tried:

global $conn;

$strSQLinsert = "insert into kredits ( customer_id, blocks, cost, account_no) values

(".$values["customer_id"].",".$values["blocks"].",".$values["cost"].",".$values["account_no"].")

ON DUPLICATE KEY UPDATE blocks = blocks + ".$values["blocks"]."";

db_exec($strSQLinsert,$conn);
still have the same error <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=76473&image=1&table=forumreplies' class='bbc_emoticon' alt=':(' />
Sergey Kornilov admin 12/18/2014

I know what you have tried and why it errors. Use the code I have supplied.
Graphix author 12/18/2014



I know what you have tried and why it errors. Use the code I have supplied.


ooopss my bad sorry Sergey

your code indeed works.
The only problem is instead of updating the customer record in the credits table it adds new ones <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=76476&image=1&table=forumreplies' class='bbc_emoticon' alt=':huh:' />
if the customer is not there thats is fine but with a customer that already has credits i wanted it to update the credit totals,

is the way i am trying to do it wrong?
thanks again

Kevan
i just realised i have two keys in the table credits_id and customer_id how do i set the code to use the customer_id key?
LOL i answered my own question all sorted now thanks for the help guys <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=76476&image=2&table=forumreplies' class='bbc_emoticon' alt=':)' />