CODEWITHSUNDEEP
phpmysqlsql
Nicholas Walters
Nicholas Walters

Reputation: 13

Duplicate entry for key 'PRIMARY' in mysql code

I keep getting this error, whenever I want to insert something into the database I have looked around stack, but the answers are so complicated.

the error is : Order Error: Duplicate entry '936791155' for key 'PRIMARY'

$orderID = rand();
$orderQuery = "INSERT INTO Orders (OrderID, PersonID, ProductID, Quantity, Price, 
OrderDate)
VALUES(".$orderID.",".$customerID.",".$productID.",".$selectedQuantity.",".$totalPrice.",'".$today."'";

if(mysqli_query($conn, $sqlQuery)) 
{
    echo "Order has been Successfull!";
} else {
    echo "Order Error: ".$sql. "<br>" . mysqli_error($conn);
}

HERE IS MY SET UP CODE FOR MYSQL:

CREATE TABLE IF NOT EXISTS Orders (
OrderID int(11) AUTO_INCREMENT, -- Connects to table 'Customer' and ID
PersonID int(11) NOT NULL,  -- Connects to table 'Orders' and OrderUserID
ProductID int(11) NOT NULL,
Quantity int(11) NOT NULL,
Price int(11) NOT NULL,
OrderDate DATETIME,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Customers(PersonID),
FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

EDIT . I think its a problem with $customerID

$customerID = rand();
$sqlQuery = "INSERT INTO Customers (PersonID, FirstName, Address, Phone)
VALUES (".$customerID.",'".$userName."','".$address."','".$phone."')";
    if(mysqli_query($conn, $sqlQuery)) {
     echo "Member verified, in database";
} else{
    echo "Member Error: " . $sql . "<br>" . mysqli_error($conn);
}

Upvotes: 0

Views: 1069

Answers (2)

S4NDM4N
S4NDM4N

Reputation: 924

I know there is an answer for this but just let me show you how to use prepared statements this makes your SQL much secure.

$stmt = $conn -> prepare("INSERT INTO Orders (PersonID, ProductID, Quantity, Price, OrderDate) VALUES (?,?,?,?,?)");
$stmt -> bind_param("iiiss", $customerID, $productID, $selectedQuantity, $totalPrice, $today);

if($stmt -> execute()){
    echo "Order has been Successfull!";
}else {
    echo "Order Error: ".$sql. "<br>" . mysqli_error($conn);
}

Upvotes: 0

user8766035
user8766035

Reputation:

OrderID is an auto increment column, you don't have to set its value in the insert statement, use this instert instead:

$orderQuery = "INSERT INTO Orders (PersonID, ProductID, Quantity, Price, 
OrderDate)
VALUES(".$customerID.",".$productID.",".$selectedQuantity.",".$totalPrice.",'".$today."')";

Just get rid of the ".$orderID.", from the insert.

I also recommend you to use sql parameters to pass the values to the query, and don't use string concatenation.

Upvotes: 2

Related Questions