[MySQL] ON DUPLICATE KEY

[Thor]

I am in need of guidance again.

I want to add the currencies into the database, but if the currency already exists then I just want the record of the existing currency to be updated.

I read about "ON DUPLICATE KEY" on http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html but to be honest that manual is just overwhelming, my question is, what is the KEY? How do SQL know the KEY is duplicate I don't understand how the KEY works?

This is my current statement:

$sql = "UPDATE `currency` SET currency_price = :currency_price, currency_percentage = :currency_percentage, currency_history = :currency_history WHERE currency_name = :currency_name";
$statement = $db->prepare($sql);
$statement->execute(array(':currency_name' => $currency_name, ':currency_price' => $currency_price, ':currency_percentage' => $currency_percentage, ':currency_history' => $currency_history));

 

[freddster]

Create a unique index on currencyName. It ensures uniqeness in that column. Same as a primary key.

So , when you have this
PK
Sign Name
R Rand

If sign is unique, you can'r have two Rs , you create a unique index on that column. that would be your key.
When you try and insert another sign R, the row with R that is currently there will be updated with teh new info.
Also best practice would be to use the sign as your where clause.

Just put your table design here as well then we can see how it interacts.

 

[Thor]

Create a unique index on currencyName. It ensures uniqeness in that column. Same as a primary key.

So , when you have this
PK
Sign Name
R Rand

If sign is unique, you can'r have two Rs , you create a unique index on that column. that would be your key.
When you try and insert another sign R, the row with R that is currently there will be updated with teh new info.
Also best practice would be to use the sign as your where clause.

Just put your table design here as well then we can see how it interacts.

How about:

$unique_hash = hash('SHA256', $currency_name);
[PHP]

Then $unique_hash acts as my unique KEY? I have no idea really.

 

[Thor]

This is my current structure

 

[koeksGHT]

This is my current structure

View attachment 381693

You should have an autoincrement id in every table for every record

 

[freddster]

Put your table design here, as well as the rows you have. If you store the hash values generated, your where currency_name = :currencyName won't work.
Is this :currency_name correct, musn't it be $currency_name. Did this stuff a few months ago.

 

[freddster]

Id is correct as PK. Now what you want is currency_name to be a unique index or constraint. create that on currency_name and try it again. Not sure of mysql but on MS SQL if you create a unique constraint it automatically creates the index.

 

[Thor]

$sql = "INSERT INTO `currency_test` (`currency_name`, `currency_price`, `currency_percentage`, `currency_history`) (:currency_name, :currency_price, :currency_percentage, :currency_history)  ON DUPLICATE KEY UPDATE currency_price = :currency_price, currency_percentage = :currency_percentage, currency_history = :currency_history";
$statement = $db->prepare($sql);
$statement->execute(array(':currency_name' => $currency_name, ':currency_price' => $currency_price, ':currency_percentage' => $currency_percentage, ':currency_history' => $currency_history));

 

[freddster]

Can't see the attachments.

 

[Johnatan56]

I sent this as a PM to Thor (hope you don't mind me posting it here):

This is my current structure

View attachment 381693

Are you setting the currency name, price and percentage as the primary key? Wouldn't it be easier to just create a timestamp and use timestamp+currency name as a PK? You can then use the timestamp for a table later or you could isolate the data according to date/time.

So:



Just add what you're actually trying to do and what each column is for, I am sure someone can give you a better table design.

Oh that makes a hell of a lot more sense!! Thank you.

I am trying to replace the contents in the DB every time the cronjob runs so the DB doesnt grow every minute.

I used to just do UPDATE, which works, but it is not scalable if I want to add 4 more currencies tomorrow.

So then use the timestamp as a surrogate key in order to identify that which you wish to remove from the table.

 

[Thor]

I will re upload.


I from a PM I send.


====
I am trying to replace the contents in the DB every time the cronjob runs so the DB doesnt grow every minute.

I used to just do UPDATE, which works, but it is not scalable if I want to add 4 more currencies tomorrow.
====

//Currency Updates

// set quotes 
$quotes = array("USDZAR", "EURZAR", "GBPZAR");

//Start the loop
foreach ($quotes as $quote) {
    $url = "http://api.com/$quote";
    $json = file_get_contents($url);
    $json = json_decode($json); 
    $currency_name = $json->basicQuote->securityName;
    $currency_price = $json->basicQuote->price;
    $currency_percentage = round($percentage = $json->basicQuote->percentChange1Day, 2, PHP_ROUND_HALF_UP);
    $currency_history = json_encode($json->priceTimeSeries[0]->price);
    $modified_time = date("Y-m-d H:i:s");

$sql = "INSERT INTO `currency_test` (`currency_name`, `currency_price`, `currency_percentage`, `currency_history`) (:currency_name, :currency_price, :currency_percentage, :currency_history)  ON DUPLICATE KEY UPDATE currency_price = :currency_price, currency_percentage = :currency_percentage, currency_history = :currency_history";
$statement = $db->prepare($sql);
$statement->execute(array(':currency_name' => $currency_name, ':currency_price' => $currency_price, ':currency_percentage' => $currency_percentage, ':currency_history' => $currency_history)); 
  
}
?>

 

[freddster]

What I said. ONly have ID as PK. The columns that you want unique each as a unique index/constraint. I don't think you need to touch your PHP. Try that.

 

[IdlePhaedrus]

I am not sure if this is going to come through as I was previously blocked from posting by CloudFlare with an SQL statement included in the post.

Anyway, all the advice here is just terrible when it comes to relational database design.

The primary key for the currency table should always be the currency code. The currency name can then be broken out into a separate table for different languages where the primary key is currency code and language code.

History should also be broken out into a separate table and the key there should be currency code and timestamp. And there is no reason I can see for you updating this information more than once a day for a normal e-commerce site, and if you are doing something more complex that requires frequent updates, then you are going to need a lot of space.

Thor, you need to study up on relational database design, and what normalisation and denormalisation mean.

Here are a few links you might like to look into:
https://www.ntu.edu.sg/home/ehchua/programming/sql/Relational_Database_Design.html
http://www.ovaistariq.net/199/datab...n-which-is-the-better-technique/#.V62yQzWZgjo
https://en.wikipedia.org/wiki/Denormalization

 

[gkm]

You should have an autoincrement id in every table for every record

No. Only if you do not have anything else that can form a nice unique primary key. In this case, the currency symbol will probably make a nice primary key.

Update: I see IdlePhaedrus said the same thing just before me. I agree, study up on third normal form.

 

[IdlePhaedrus]

No. Only if you do not have anything else that can form a nice unique primary key. In this case, the currency symbol will probably make a nice primary key.

Update: I see IdlePhaedrus said the same thing just before me. I agree, study up on third normal form.

The three letter currency code is the best to use, and is used generally the world over. Especially as some symbols such as the Euro are not in the ASCII code page. So, you want to use ZAR, USD, GBP etc. Here is a list:

http://www.xe.com/iso4217.php

 

[rward]

Anyway, all the advice here is just terrible when it comes to relational database design.


Thor, you need to study up on relational database design, and what normalisation and denormalisation mean.

Here are a few links you might like to look into:
https://www.ntu.edu.sg/home/ehchua/programming/sql/Relational_Database_Design.html
http://www.ovaistariq.net/199/datab...n-which-is-the-better-technique/#.V62yQzWZgjo
https://en.wikipedia.org/wiki/Denormalization

Give this man a Bells!!

 

[DA-LION-619]

You should definitely watch a YT vid on databases.

 

[Thor]

I am not sure if this is going to come through as I was previously blocked from posting by CloudFlare with an SQL statement included in the post.

Anyway, all the advice here is just terrible when it comes to relational database design.

The primary key for the currency table should always be the currency code. The currency name can then be broken out into a separate table for different languages where the primary key is currency code and language code.

History should also be broken out into a separate table and the key there should be currency code and timestamp. And there is no reason I can see for you updating this information more than once a day for a normal e-commerce site, and if you are doing something more complex that requires frequent updates, then you are going to need a lot of space.

Thor, you need to study up on relational database design, and what normalisation and denormalisation mean.

Here are a few links you might like to look into:
https://www.ntu.edu.sg/home/ehchua/programming/sql/Relational_Database_Design.html
http://www.ovaistariq.net/199/datab...n-which-is-the-better-technique/#.V62yQzWZgjo
https://en.wikipedia.org/wiki/Denormalization

I need updates every 55 seconds no need for a large space hence why I want to update and not add so the database stays the same size regardless, give or take 3 or 5 extra currencies over time.

This is a follow up form this:

http://mybroadband.co.za/vb/showthread.php/834801-JSON-and-Charting/page5

which is a follwo up from this:

http://mybroadband.co.za/vb/showthread.php/834567-RSS-and-MySQL/page3


No need to complicate things more than necessary

I get the data from the api. I save that data in the DB via a cron job

I then pull that data from the db and show it to visitors as they land on the page.

=====
Previously I simply ran the cron job once with INSERT INTO to have values the DB for the first time and then the cron job is changed to UPDATE instead of INSERT which works 110%.


I just want to challange myself a bit by now creating a new scenario where I might add a nth currency so the UPDATE instruction will not work thus I cannot scale this. That is my scenario now.

To simplify all I want to do is this:

IF (currency_name exists)
  run update command
ELSE
  run insert command

 

[Thor]

You should definitely watch a YT vid on databases.

Funny how in all the threads you never give advice, but normally comments like. This thread went gold, you should watch a video, etc etc.

Makes one think...

 

[DA-LION-619]

Funny how in all the threads you never give advice, but normally comments like. This thread went gold, you should watch a video, etc etc.

Makes one think...

Database structures isn't code you can refactor later. You have to know what you plan to do.