• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

How should I specify the unique key in the table to get it to work?

 
Ranch Hand
Posts: 59
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have implemented this code to insert and update the data in my database but when I try it a new row's data is always being inserted in the table and it is not being updated How can I define the unique key in my case to get it to work? I have the primary key id.

This is the whole code I have in my php script plus the connection.php file, which just contains the connection statement. In my mysql I just have one database in it the route_4 table is being created. My table just consists of the route_4 table.

 
Ranch Hand
Posts: 310
18
MS IE Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The "INSERT (...) ON DUPLICATE KEY UPDATE (...)" works as following:

Try to INSERT a row A to table X. If the table X contains a row B that has the same primary or unique key as the row A, perform UPDATE on the row B, instead of performing INSERT (which is not possible anyway).

To make the database engine recognize that you want to insert a duplicate key, you must to provide value of the column that is the key In your case, that column is ID.

In your code you don't provide a specific value for your key ID:


Because the column ID has AUTO_INCREMENT set, every new row without a specified value for ID will contain a new auto-incremented value for the key.

What you need to do, is to specify the value of ID for every row.

Tell me what do you want to achieve with your code, what is the purpose of that table and how do you want to collect the data there? This SQL looks a little weird, I would want to show you how to correct it but I must know what do you want to achieve with it.
 
Alex Ardoin
Ranch Hand
Posts: 59
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hallo,
Thanks for your answer.

I am implementing an Android app to track buses location. Route, number, longitude and latitude will be collected by the app and send to the server every 60 seconds. The rider can use the app to select the route number he is interested in and all instances of this route number will be displayed in the google map.
 
Andrew Polansky
Ranch Hand
Posts: 310
18
MS IE Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
So as I understand, the database has to save GPS coordinates provided every 60 seconds.
If two (or more) the same coordinates come in a row, then the coordinates should be saved only once - because the driver can be not moving for a longer time due to ie. a traffic jam and we don't want to record several same coordinates.
However, the same coordinates may be saved in a route if they doesn't appear next to each other. The route may involve driving twice through the same street or intersection, and that may generate the same GPS coordinates.

For this purpose I think the best solution is just to use a stored procedure.

Create your table with the following SQL:



Then create a stored procedure using this SQL. You can execute it right after the table creation SQL above.



Now to save the GPS coordinates use the following SQL:



The first parameter ("testRoute" in this example) is the table where you want to save the coordinates, then comes latitude and longitude.

Here is a test that proves that this method does not save the same coordinates in a row (that is, that come next to each other), but still can save the same coordinates if they appear later:






 
Alex Ardoin
Ranch Hand
Posts: 59
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Do not you thing that my database design is weird and I have to store all route number plus their instances in one table? At the moment each route table just consists of instances of this route number for example 10 buses the route 4 in the same table.
 
Alex Ardoin
Ranch Hand
Posts: 59
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I found out that my code works when I did the following. I deleted UNIQUE key from id and added it to latitude and longitude like this:

 
Alex Ardoin
Ranch Hand
Posts: 59
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Is it possible to manage to get the last record deleted from the table before update it when some changes happend? Because with the current state I am getting new records in my table if there is some changes but what I want to reach is first insert and then if eixts, just update the one record.
 
Andrew Polansky
Ranch Hand
Posts: 310
18
MS IE Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am sorry Alex, but I am totally lost, I still cannot understand what do you want to achieve. I don't know how I can help you.
 
Alex Ardoin
Ranch Hand
Posts: 59
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ok I was wrong and I had to redesign my table as the following and give on column the UNIQUE key "mac" it works now as it should be



 
Andrew Polansky
Ranch Hand
Posts: 310
18
MS IE Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Great, I am glad you got it working!
 
Switching from electric heat to a rocket mass heater reduces your carbon footprint as much as parking 7 cars. Tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic