MySql Databases

[digip]

I created a form that puts data into a MySql database. Everything is working, but I noticed that if someone refreshes their browser, it creates duplicate entries, one right after the other. The data I use from the database plots markers on a Google map, so its ot too big a deal since the markers will be in the same exact spot, and no one will be able to tell the difference, but I do not want the database fileld with duplicates for no reason.

Is there a way to make it delete all idetical records (except for the key count number, since they all get a different count for the first field in the db)

For example, lets say records 1 2 and 3 are unique, but 4 and 5 are identical to record 3, but 6, 7, etc, are unique. How would I make it remove them from the db, or check the db to see if what is being posted already exists as a record and void the post.

Hope that makes sense.

1    Tom040308050416    123 Some Test Road    35.123455    -74.123459    Test Map Marker Data
2    Bob040308050450    555 Test Road    36.123455    -75.123459    Test Map Marker Data 2
3    Joe040308060436    234 SOmeplace    33.123451    -74.432137    Joes Test 123
4    Test040308070451    None    33.123451    -74.777771    Test Marker
5    Harry040308070402    123 happy text    33.123451    -74.131416    Foo Bar
6    Joe040308070403    Smo City    34.123451    -74.765450    Blah Blah
7    tim040308070404    Smo City    34.222221    -74.333328    Blah Blah
8    Jim Bob040408010441    9675309    34.123100    -80.121323    I see dead people.
9    Tiny Tim040408020432    Test Address    33.654457    -90.123459    Description Goes Here
10    Tiny Tim040408020432    Test Address    33.654457    -90.123459    Description Goes Here
11    Tiny Tim040408020432    Test Address    33.654457    -90.123459    Description Goes Here
12    G.W.Bush040408010414    Washington DC    38.890369    -77.031990    Washington DC!

In the above code is what I am talking about.

[Sparda]

Use each row as the primary key.

[digip]

Use each row as the primary key.

Here is what I used to create the tables

CREATE TABLE `markers` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  `name` VARCHAR( 60 ) NOT NULL ,
  `address` VARCHAR( 80 ) NOT NULL ,
  `lat` FLOAT( 10, 6 ) NOT NULL ,
  `lng` FLOAT( 10, 6 ) NOT NULL ,
  `type` VARCHAR( 30 ) NOT NULL
) ENGINE = MYISAM;

Would I need to add "PRIMARY KEY" to each row like this?

CREATE TABLE `markers` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  `name` VARCHAR( 60 ) NOT NULL PRIMARY KEY,
  `address` VARCHAR( 80 ) NOT NULL PRIMARY KEY,
  `lat` FLOAT( 10, 6 ) NOT NULL PRIMARY KEY,
  `lng` FLOAT( 10, 6 ) NOT NULL PRIMARY KEY,
  `type` VARCHAR( 30 ) NOT NULL PRIMARY KEY
) ENGINE = MYISAM;

[Sparda]

In Orocle 10 it's like this:

create table <table name>
(<field1>    numeric(10),
<field2>    numeric(10),
<field3> numeric (10),
constraint <Name_of_contraint> primary key(<field name1>, <field name2>));

You probably have to create the table then aulter it: http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

[digip]

I see a way to block it using UNIQUE in the INSERT INTO request, but if any field is the same, it blocks it altogether, and I only want it to block it if the "name address lat lng type" fields are the same, not "id name address lat lng type".

[Sparda]

I see a way to block it using UNIQUE in the INSERT INTO request, but if any field is the same, it blocks it altogether, and I only want it to block it if the "name address lat lng type" fields are the same, not "id name address lat lng type".

You probbly need to rewrite your web script.

[SomeoneE1se]

Set it as UNIQUE and make the query to see if it exists first...

you can also make the query on one script and then header redriect

<?php header('Location: /otherpage.php'); ?>

that way when they refresh it will not add a new query, and they can hit back and it wont re run the query.

[digip]

1=1-- is fail!

mysql is ultra-vuln

All SQL seems to be...But it's the checks you put in your coding that ultimately make or break the security.

[SomeoneE1se]

mysql is ultra-vuln

no it's not it works perfectly, is the code that is insecure.