Pulling an Integer value from SQL database to be used for calculation

Question

I am having trouble just trying to pull data out of my table. I just want to pull the integer value from column Diff and add/subtract numbers to it.

Once I am done adding/subtracting, I want to update each row with the new value"

My Table chart for "users" in ruby

This is my code as of now

require 'date'
require 'mysql2'
require 'time' 

def test()
  connect = Mysql2::Client.new(:host => "localhost", :username => "root", :database => "rubydb")
  result = connect.query("SELECT * FROM users where Status='CheckOut'")
  if result.count > 0
    result.each do |row|
    stored_diff = connect.query("SELECT * FROM users WHERE Diff")
    #stored_diff = stored_diff.to_s  
    puts stored_diff
    end
  end
end

test()

I am sure the code in the hashtag does not work since I am getting like #Mysql2::Result:0x000000000004863248 etc. Can anyone help me with this?

That's your resultset, it seems perfectly okay. I wanted to help you but it's not clear to me what you want to achieve. — Ursus
– Ursus, Commented Jul 25, 2018 at 19:24
@Ursus I want to update the existing "Diff" value by adding this to it. 'time_change = ((Time.parse(row['CheckOutDateTime'].to_s) - Time.parse(row['CheckInDateTime'].to_s)) / 60).round' So row 1's "Diff" will add it's original value to time_change — KTH
– KTH, Commented Jul 25, 2018 at 19:39

Roshana Pitigala · Accepted Answer · 2018-07-25 19:59:04Z

0

I have no knowledge of ruby but I'll show you the steps to achieve what you are trying based on this and this.

Get User Ids and the Diff numbers.

SELECT `Id`, `Diff` FROM users where `Status`='CheckOut'

Iterate the result.
```
result.each do |row|
```

Assign Diff and Id into variables.

usrId = #{row['Id']};
diffCal = #{row['Diff']};

Do your calculations to diffCal variable.

Execute the UPDATE query.

UPDATE `users` SET `Diff` = '#{diffCal}' WHERE `Id` = '#{usrId}'

edited Jul 25, 2018 at 19:59

answered Jul 25, 2018 at 19:29

Roshana Pitigala

8,8569 gold badges53 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

KTH Over a year ago

how would you assign Diff into a variable?

Roshana Pitigala Over a year ago

Yeah, sry about that

Collectives™ on Stack Overflow

Pulling an Integer value from SQL database to be used for calculation

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related