MySQL Questions

[poster]

I have a MySQL question.

I am calling a JSON API, which I can covert into 1,000 rows of data, e.g.

Let's say I have these columns: id, ticker, title, description, price, info, date.

I need to update "price", "info", and "date" every 5 minutes from the API and if there are new rows I need to insert them into MySQL.

JSON data does not have the "id" in it, I am going to create it in MySQL. The only unique column in the JSON data is the "ticker". I need to have the id stay constant for each ticker as this data is being used in other places, so
I need to match up the "ticker" in the JSON file with the "ticker" column in MySQL.

I can call a PHP script every 5 minutes, so it's not an issue but what about the rest? Do I do 1,000 updates and then INSERT IGNORE?

[ScriptMan]

Sorry I don't understand the question.

Also might the 1000 lines of data have more than one update for a single ticker symbol?

Usually php mysql transactions are done with a fore_each or a limit tag.

[poster]

Originally Posted by ScriptMan

Sorry I don't understand the question.

Also might the 1000 lines of data have more than one update for a single ticker symbol?

Usually php mysql transactions are done with a fore_each or a limit tag.

It's one update per ticker.

So, let's say have the data coming in from JSON:

"TBC", "Crazyhorse", "Crazyhorse is a small restaurant", "0.07", "45992", "8982222"
"ETV", "Tradinghouse", "Tradinghouse is not here but there", "1.24", "813348", "4539876"
"UTT", "Utahdome", "Not a great place", "5.12", "87222","5431345"

and 1,000 more rows like this

in my MySQL table, I have:

"234", "TBC", "Crazyhorse", "Crazyhorse is a small restaurant", "2.42", "24666", "9998882"
"556", "ETV", "Tradinghouse", "Tradinghouse is not here but there", "5.57", "813348", "2129345"

so, I have to update rows with id 234 and 556
but I don't have the UTT. So I have to insert it.

So, what would MySQL queries look like? What's the best approach?

[ScriptMan]

We are all in a hurry to get a sig but please read the sig rules.




While a unique id number is always a good thing you do not have to use that to update the tables. You may use any of the table items.

The following will all be pseudo code as I sometime struggle with query statements.

So there are several ways to approach this:

1. Query the DB Select from table ticker where value = 'Crazyhorse' and get the id number then update the table values you want using the id number as an absolute id. This is a two step process and utilizes more sever resources.

2. Just update the values contained in the row that contains the ticker symbol and name.

All ticker symbols could be set as unique (as they should be) in the DB making the ID unnecessary and ensuring correct insertion.

Any queries I would write in actual code would be a couple of PHP version out of date as I am behind on my education. Knowing which version is running on your server might help you avoid problems. That information should be in your control panel.

[poster]

Originally Posted by ScriptMan

We are all in a hurry to get a sig but please read the sig rules.




While a unique id number is always a good thing you do not have to use that to update the tables. You may use any of the table items.

The following will all be pseudo code as I sometime struggle with query statements.

So there are several ways to approach this:

1. Query the DB Select from table ticker where value = 'Crazyhorse' and get the id number then update the table values you want using the id number as an absolute id. This is a two step process and utilizes more sever resources.

2. Just update the values contained in the row that contains the ticker symbol and name.

All ticker symbols could be set as unique (as they should be) in the DB making the ID unnecessary and ensuring correct insertion.

Any queries I would write in actual code would be a couple of PHP version out of date as I am behind on my education. Knowing which version is running on your server might help you avoid problems. That information should be in your control panel.

It sounds like the #2 approach could work. I will set a unique constraint for the ticker symbols.

What MySQL query would I write to update this row:

"TBC", "Crazyhorse", "Crazyhorse is a small restaurant", "0.07", "45992", "8982222"

Thanks!

[ScriptMan]

As I said earlier 'not my strong point'. For me it is write, test verify rinse and repeat until I get it right. I don't have your DB, scripts or source for testing purposes.

I would suggest this as a real nice read https://www.w3schools.com/php/php_mysql_update.asp and one of us will try to answer questions.