Webmaster Forum

Go Back   Webmaster Forum > Web Development > Coding Forum

Coding Forum Problems with your code? Discuss coding issues, including JavaScript, PHP & MySQL, HTML & CSS, Flash & ActionScript, and more.


Closed Thread
 
Thread Tools Display Modes
Share |
  #1  
Old 01-20-2018, 11:58 AM
poster poster is offline
Contributing Member
 
Join Date: 05-01-06
Location: San Francisco, CA
Posts: 126
iTrader: 0 / 0%
MySQL Questions

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?
 

Advertisement

Advertisement

  #2  
Old 01-20-2018, 12:53 PM
ScriptMan's Avatar
ScriptMan ScriptMan is online now
Super Moderator
 
Join Date: 02-10-07
Location: Central Kentucky
Posts: 13,441
iTrader: 4 / 100%
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.
 
  #3  
Old 01-20-2018, 01:04 PM
poster poster is offline
Contributing Member
 
Join Date: 05-01-06
Location: San Francisco, CA
Posts: 126
iTrader: 0 / 0%
Quote:
Originally Posted by ScriptMan View Post
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?
 
  #4  
Old 01-21-2018, 05:32 AM
ScriptMan's Avatar
ScriptMan ScriptMan is online now
Super Moderator
 
Join Date: 02-10-07
Location: Central Kentucky
Posts: 13,441
iTrader: 4 / 100%
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.
 
  #5  
Old 01-21-2018, 08:26 AM
poster poster is offline
Contributing Member
 
Join Date: 05-01-06
Location: San Francisco, CA
Posts: 126
iTrader: 0 / 0%
Quote:
Originally Posted by ScriptMan View Post
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!
 
  #6  
Old 01-22-2018, 06:31 AM
ScriptMan's Avatar
ScriptMan ScriptMan is online now
Super Moderator
 
Join Date: 02-10-07
Location: Central Kentucky
Posts: 13,441
iTrader: 4 / 100%
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.
 
Go Back   Webmaster Forum > Web Development > Coding Forum

Closed Thread


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Searching for PHP + MySql script Haki123 Coding Forum 0 09-17-2017 08:46 AM
Where Can I Post My Questions? Hamda shah V7N Forum Support 1 06-11-2017 06:47 AM
9 Tips for Fielding Customer Questions on Social Media snakeair Online Business Forum 0 05-18-2017 07:40 AM


V7N Network
Get exposure! V7N I Love Photography V7N SEO Blog V7N Directory


All times are GMT -7. The time now is 05:19 AM.
Powered by vBulletin
Copyright 2000-2014 Jelsoft Enterprises Limited.
Copyright © 2003 - 2018 VIX-WomensForum LLC