raw MySQL statement in question
Code: Select all
INSERT INTO profiles(card_id,productCode,customerName,email,externalID,accountNo)
VALUES('0','$productCode','$customerName','$email','$externalID','$accountNo')
Error number is 0
Error description is
the table structure & column indexes of the relevant tables
'profiles', 'CREATE TABLE `profiles` (
`id` int(11) NOT NULL auto_increment,
`card_id` int(11) NOT NULL,
`profileID` varchar(12) collate latin1_general_ci NOT NULL,
`productCode` varchar(5) collate latin1_general_ci NOT NULL,
`createDate` date NOT NULL,
`startDate` date NOT NULL,
`nextPaymentDate` date NOT NULL,
`frequency` enum('WEEK','MONT','YEAR') collate latin1_general_ci NOT NULL,
`amount` decimal(6,2) NOT NULL,
`status` enum('ACTIVE','DEACTIVE') collate latin1_general_ci NOT NULL default 'ACTIVE',
`customerName` varchar(150) collate latin1_general_ci NOT NULL,
`email` varchar(150) collate latin1_general_ci NOT NULL,
`externalID` int(11) NOT NULL,
`accountNo` varchar(40) collate latin1_general_ci NOT NULL,
`schedule` time NOT NULL,
`temp` tinyint(2) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `prof_id` (`profileID`),
KEY `card_id` (`card_id`),
KEY `FK_profiles` (`productCode`),
KEY `nextPaymentDate` (`nextPaymentDate`),
CONSTRAINT `profiles_ibfk_1` FOREIGN KEY (`productCode`) REFERENCES `products` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=39988 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci'
Now when I run the query directly in the PHP MyAdmin I get this error
#1364 - Field 'profileID' doesn't have a default value
Seems simple as profileID has a not null value. But it should get created later in my code
Code: Select all
function addProfile($card,$productCode,$customerName,$email,$externalID=0,$accountNo=0) {
$number = $card['number'];
$cvv = $card['cvv'];
$name_on_card = $card['name_on_card'];
$exp_date=$card['exp_date'];
$type = $card['type'];
echo "number is $number<br>";
echo "cvv is $cvv<br>";
echo "name on care is $name_on_card<br>";
echo "exp date is $exp_date<br>";
echo "type is $type<br>";
global $db;
$db->query("START TRANSACTION");
$sql1 = "INSERT INTO profiles(card_id,productCode,customerName,email,externalID,accountNo)
VALUES('0','$productCode','$customerName','$email','$externalID','$accountNo')";
echo "sql query is $sql1 <br>";
$r1 = $db->query($sql1);
$prof_id = $db->last_id;
$sql2 = "INSERT INTO ccards(profile_id,card_type,card_number,card_cvv,name_on_card,exp_date)
VALUES ($prof_id','$type','$number','$cvv','$name_on_card','$exp_date')";
echo "2nd query is $sql2 <br>";
$r2 = $db->query($sql2);
$card_id = $db->last_id;
$sql3 = "UPDATE profiles SET card_id='$card_id' WHERE id='$prof_id'";
echo "third query is $sql3";
$r3 = $db->query($sql3);
$ret = new Message();
//die("R1: $r1 $sql1 R2: $r2 $sql2 R3: $r3");
if ($r1 && $r2 && $r3 && $db->affected) {
$db->query("COMMIT");
$sql = "SELECT profileID FROM profiles WHERE id=$prof_id";
$r = $db->results($sql);
$profileID = $r[0]['profileID'];
$ret->result = 0;
$ret->text = "Success.";
$ret->profileID = $profileID;
} else {
$db->query("ROLLBACK");
$ret->result = 10;
$ret->text = "Error. Couldn't create the profile";
$ret->profileID = 0;
}
return $ret;
}
Code: Select all
function query($sql) {
$start = $this->getmicrotime();
$q = @mysql_query($sql,$this->mysql_link) or die("Error number is ".mysql_errno()."<br> Error description is ".mysql_error()."<br>Query is " .$sql."<br>"); ;
$end = $this->getmicrotime();
$this->qlist[] = $sql;
if ($q) {
$this->nrquery += 1;
$this->last_query_time = $end - $start;
$this->total_time += $this->last_query_time;
$this->affected = mysql_affected_rows($this->mysql_link);
$this->last_id = mysql_insert_id($this->mysql_link);
return true;
}
else return false;
}
This trigger exists in the system
Code: Select all
DROP TRIGGER IF EXISTS `recurring`.`ins_profile`//
CREATE TRIGGER `recurring`.`ins_profile` BEFORE INSERT ON `recurring`.`profiles`
FOR EACH ROW BEGIN
DECLARE am DECIMAL(6,2);
DECLARE fr CHAR(4);
DECLARE sa INT;
SELECT price, frequency, startAfter INTO am,fr,sa FROM products WHERE code=NEW.productCode;
IF NEW.createDate='0000-00-00' THEN
SET NEW.createDate = CURDATE();
END IF;
SET NEW.amount=am;
SET NEW.frequency=fr;
SET NEW.startDate=ADDDATE(NEW.createDate,sa);
SET NEW.nextPaymentDate=ADDDATE(NEW.createDate,sa);
SET NEW.profileID = nextProfileID();
END
//
Thanks for any help.