0

There are many tutorials out there but i haven't been able to make it work somehow.

I am trying to update my table if data in all but except on column matches the data i'm inserting through a form. I am using php as the scripting language and mysql at the backend.

$name = $_POST['name'];
$phone = $_POST['phone'];
$genders = $_POST['gender'];
$hno = $_POST['hno'];
$sector = $_POST['sector'];
$city = $_POST['city'];
$state = $_POST['state'];
$pin = $_POST['pin'];
$ailt = $_POST['ailt'];

$q="INSERT INTO manage (name, phone, gender, hno, sector, city, state, pin, ailt)
VALUES ('$name', '$phone', '$genders', '$hno', '$sector','$city','$state','$pin','$ailt')
ON DUPLICATE KEY UPDATE ailt='$ailt'";

What I'm trying to do is insert the data if it doesn't already exists, or if all the fields except ailt match, update that row with the new ailt.

I have been stuck on this for a couple of hours. Looks simple, but i just can't get it to work, as the rows keep inserting. They don't update, even when I keep all the data same and put a different value for ailt.

Celeo
  • 5,583
  • 8
  • 39
  • 41
user3508140
  • 285
  • 2
  • 18
  • 4
    You are vulnerable to [sql injection attacks](http://bobby-tables.com). And exactly **HOW** is this not working? the insert fails? the on dupe doesn't work? – Marc B Mar 30 '15 at 17:17
  • Are you getting an error? Can you post that? – mjuarez Mar 30 '15 at 17:17
  • its is a very strange query – ErasmoOliveira Mar 30 '15 at 17:20
  • The rows keep inserting. They don't update, even when i keep all the data same and put a different value for ailt. – user3508140 Mar 30 '15 at 17:21
  • @MarcB I'm aware this is vulnerable to sql injection. But this is just a small school project. I'll work on that aspect at a later moment. – user3508140 Mar 30 '15 at 17:22
  • @ErasmoOliveira Strange as in? I am a beginner, so any suggestions to improve upon the query are welcome. – user3508140 Mar 30 '15 at 17:24
  • If it's not updating, then nothing in your query is causing a unique key violation. e.g. your `ailt` field is not unique. – Marc B Mar 30 '15 at 17:29
  • @user3508140 strange = when you use insert, its just for new registers, you need to use update to modify existing records. See the difference here http://www.w3schools.com/sql/sql_update.asp and http://www.w3schools.com/sql/sql_insert.asp – ErasmoOliveira Mar 30 '15 at 17:36
  • you need set in your database your id colum with auto_increment too. – ErasmoOliveira Mar 30 '15 at 17:37
  • @MarcB Ahh right. So i need to set the combination of all the fields except ailt unique, right? Because i need for every same entries of all the fields except ailt, it gets updated. – user3508140 Mar 30 '15 at 17:46
  • @user3508140 - See my updated answer. You'll want a UNIQUE/PRMARY key based on whatever fields uniquely identify an individual. In your case, a key covering "name" and "phone" may likely be enough, but you'll need to judge that based on your own use case. – xathien Mar 30 '15 at 17:48
  • I inserted the clarification from the comments into the question post. – Celeo Mar 30 '15 at 22:23

1 Answers1

0

Given that the comments are flooded with SQL injection, I won't hit on that again.

The syntax you're looking for is UPDATE ailt=VALUES(ailt).

See the paragraph in the docs starting with "You can use the VALUES(col_name) function" for more detailed information.

Edit: Regarding your comment about how the rows "keep inserting", it's likely because you don't have a PRIMARY or UNIQUE key on the database that can identify what row to update. See the first paragraph of the docs I linked above for more reference.

xathien
  • 812
  • 7
  • 10
  • It works now. Another thing. My id column(primary key and auto increment) increments by 2 everytime i update a row. For example my initial id was 1. When that row is updated, it remains 1. When i add another unique row, the id is now 3. Any workaround here, so it comes out be 2? – user3508140 Mar 30 '15 at 18:02
  • Not really. There are a multitude of reasons why an autoincrementing ID might not be perfectly sequential. For example, if you have records 5, 6, and 7, and delete 6, you'll still have record 5 and 7. Expecting them to be sequential is not worth the worry. – xathien Mar 30 '15 at 18:05
  • I needed the id's to be consecutive. I can manage that with PHP on the front-end. – user3508140 Mar 31 '15 at 03:44