0

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();
I'm Geeker
  • 4,601
  • 5
  • 22
  • 41
  • 4
    [Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! [Don't believe it?](http://stackoverflow.com/q/38297105/1011527) – Jay Blanchard Jul 15 '16 at 14:50
  • Have you checked your error logs? – Jay Blanchard Jul 15 '16 at 14:51
  • 1
    Show use the `CREATE` statement for your table. – Jay Blanchard Jul 15 '16 at 14:51
  • 1
    Done any basic debugging, like checking if the query actually succeeded? Unless you explicitly enable exceptions in mysqli/pdo, they default to returning boolean false on failure, which you're NOT checking for. – Marc B Jul 15 '16 at 14:57
  • You could always try testing the result of running the query `$res = $conn->query($sql); if ( $res === false) { echo $conn->error; exit;}` The error message give **dirty great clues to help debugging** – RiggsFolly Jul 15 '16 at 14:57
  • I think, because the row is already present, it won't INSERT it again. YOu might want to look at adding a unique ID. – Lee Jul 15 '16 at 15:03

1 Answers1

0

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.

Daniel Ferrans
  • 26
  • 5