mysql INSERT没做任何事情

Question

This query is for an IPN script integrated with PayPal. Everything works in the script except the insertion of data into the table. Here's the code to insert data, all of the objects have their appropriate value.

if ($item == "RuneCoins") {
        mail("[e-mail]", "Item purchased!", "$acc has just purchased $item for $paid.");
    mysql_query("INSERT INTO `purchases` (`id`, `email`, `price`, `product`, `fname`, `lname`, `time`, `transaction_id`, `acc`) VALUES (NULL, '$email', '$paid', '$item', '$fname', '$lname', CURRENT_TIMESTAMP, '$trans', '$acc');");
} else {
        mail("[e-mail]", "Invalid item!", "$acc purchased $item, and it was invalid.");
}

I am receiving the email that the user has purchased the item, but the data is not inserted into the table. Why is this happening?

Here is the SQL structure for the table

CREATE TABLE IF NOT EXISTS `purchases` (
  `id` int(11) NOT NULL,
  `email` longtext COLLATE utf8_unicode_ci NOT NULL,
  `price` int(11) NOT NULL,
  `product` longtext COLLATE utf8_unicode_ci NOT NULL,
  `fname` longtext COLLATE utf8_unicode_ci NOT NULL,
  `lname` longtext COLLATE utf8_unicode_ci NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `transaction_id` bigint(11) NOT NULL,
  `acc` longtext COLLATE utf8_unicode_ci NOT NULL,
  `delivered` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

What am I doing wrong?

Answer 1

I see the problem. You cannot use NULL as the value for id, because it is specifically prohibited in your create table statement. What you need to do is two fold.

First, change your insert to this:

 mysql_query("INSERT INTO `purchases` (`id`, `email`, `price`, `product`, `fname`, `lname`, `time`, `transaction_id`, `acc`) VALUES (0, '$email', '$paid', '$item', '$fname', '$lname', CURRENT_TIMESTAMP, '$trans', '$acc');");

The only change I made there is changing NULL to 0, since 0 would be valid, and works nicely with the next change. Change two, modify your create table:

CREATE TABLE IF NOT EXISTS `purchases` (
  `id` int(11) NOT NULL auto_increment,
  `email` longtext COLLATE utf8_unicode_ci NOT NULL,
  `price` int(11) NOT NULL,
  `product` longtext COLLATE utf8_unicode_ci NOT NULL,
  `fname` longtext COLLATE utf8_unicode_ci NOT NULL,
  `lname` longtext COLLATE utf8_unicode_ci NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `transaction_id` bigint(11) NOT NULL,
  `acc` longtext COLLATE utf8_unicode_ci NOT NULL,
  `delivered` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

You need to make sure your id field is auto-incremented. If it is not auto-incremented, then you have to maintain a counter in your code, which is annoying. Changing it to auto-increment will allow you to put a 0 as the id, which will be interpreted as <the next available id>.

Hope this helps.