phantom phantom - 4 months ago 18
MySQL Question

Updating sql from php

I am updating a mysql database using php. It is adding to the database once, but on subsequent instances the db doesn't update if the name and email remain the same. I want to add the same name and email as many times as I like but cannot figure out why I can't. I have checked the db and from what I can tell nothing is set as

unique
. Is there anything I am missing from my query or people think my db might have another setting somewhere.

DB

email |name
---------------------------
john@example.com | John


Mysql query

$sql = "INSERT INTO customers (name, email)
VALUES ('John', 'john@example.com')";
$conn->query($sql);
$conn->close();

Answer

is seems that your database does not have a AUTOINCREMENT PRIMARY KEY. It is a good practice to add primary key in all your tables.

In order to create the primary key, you should run the following sql:

ALTER TABLE `nameofdatabase`.`nameoftable` 
ADD COLUMN `id` INT NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (`id`);

The result table should be like this:

id | email             |name
-------------------------------------
1  | john@example.com  | John

Now you should be able to CRUD (create, read, update and delete) correctly Let me know if this helps.