2 Replies - 511 Views - Last Post: 07 May 2011 - 06:15 AM Rate Topic: -----

#1 carmelraj  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 50
  • Joined: 28-April 11

Mysql query for inserting is not working

Posted 07 May 2011 - 05:50 AM

The value is inserting... suppose i insert the same data or values it will be updated it won't get insert. But this update is not working plz help me


$sql="INSERT INTO order_details(co_id,company_name,bookingf_no,amount,discount,amount_payable,amount_collected,balance_payable_on,balance_payed,executive,receipt_no,receipt_date,no_of_iss,s_size,d_size,remarks)VALUES('$id','$_POST[company_name]',$_POST[bookingf_no],'$_POST[amount]','$_POST[discount]','$_POST[amount_payable]','$_POST[amount_collected]','$_POST[balance_payable_on]','$_POST[balance_payed]','$_SESSION[User]','$_POST[receipt_no]','$_POST[receipt_date]','$_POST[no_of_issues]','$_POST[s_size]','$_POST[d_size]','$_POST[remarks]') ON DUPLICATE KEY UPDATE SET  VALUES('".$id."','$_POST[company_name]',$bookingf_no,$amount,$discount,'$_POST[amount_payable]','$_POST[amount_collected]','$_POST[balance_payable_on]','$_POST[balance_payed]','$_SESSION[User]','$_POST[receipt_no]','$_POST[receipt_date]','$_POST[no_of_issues]','$_POST[s_size]','$_POST[d_size]','$_POST[remarks]')";


Is This A Good Question/Topic? 0
  • +

Replies To: Mysql query for inserting is not working

#2 CTphpnwb  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 2984
  • Posts: 10,320
  • Joined: 08-August 08

Re: Mysql query for inserting is not working

Posted 07 May 2011 - 06:02 AM

First, NEVER put user supplied data directly into a query unless you're using prepared statements. Next, associative arrays should have the index be quoted. While PHP will let you get away with not doing so, the index may not point to the intended item.
Was This Post Helpful? 0
  • +
  • -

#3 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3719
  • View blog
  • Posts: 5,990
  • Joined: 08-June 10

Re: Mysql query for inserting is not working

Posted 07 May 2011 - 06:15 AM

Your SQL syntax is wrong.

The INSERT ... ON DUPLICATE KEY UPDATE syntax should look more like this:
INSERT INTO mytbl(id, val, last_updated)
VALUES (123, 'updated value', NOW())
ON DUPLICATE KEY UPDATE
    val = VALUES(val),
    last_updated = VALUES(last_update);


There is no SET clause, and you need to specify each column you want updated, just like in an UPDATE query.
The VALUES() function there allows you to fetch the value that should have been inserted for the column you specify.

View PostCTphpnwb, on 07 May 2011 - 01:02 PM, said:

Next, associative arrays should have the index be quoted. While PHP will let you get away with not doing so, the index may not point to the intended item.

It's OK inside a string. One of those ancient exceptions that sounded like a good idea at the time :)
I don't like it though. Much prefer to use:
$str = "{$arr['elem']}";

Was This Post Helpful? 1
  • +
  • -

Page 1 of 1