DATABASE MYSQL column automatic values calculated

[poisonnnn]

Hello, Need help. I'm new in LiveCode.

The problem is, that I have a database, which has 3 colums which are from query normal, but the 4th column in field(table or smth) must take values from rowns on the left and make some calculations with them automaticly.... Any advice ? Please...

id distance weight PRICE(4th)
1 100 30 40$(take from distance column 1, id1...)
2 233 41 ???(take from distance column 2, id2...)
3 150 10 ???

How it's should be made ? with arrays or something ?
only the part from the queries, structure of table... (i use scrolling field), connecting to DB etc are good.
help guys

[bangkok]

Hum... Sorry but it's not very clear.

You need to UPDATE the 4th column ? Or you need to calculate before you make the INSERT ?

Or you need to have MySQL making itself a calculation when you do a SELECT ?

You should post here the script in which you create your query.

It will help us to understand what you want to achieve exactly.

[poisonnnn]

Thank for the reply:

I think... I need to have MySQL making itself a calculation when I do a SELECT. The 4th columns values are from ANOTHER table, but the whole calculation of the query must be with the values of column3 etc.

###this will form a table (filled in my example: column 1,2,3...perhaps I'll need to change something here to)
put "SELECT isv_laik,atv_laik,atstum,vyksta FROM reisas where gal='"& sResaultSave & "'ORDER BY isv_laik" into tSQL
put revDataFromQuery(tab, cr, sDatabaseID, tSQL) into tData
put tData into field "ats"

### column 4. result can be also it the same field "ats"
put "select kaina from tarifai use index (tarifas) where (kg_nuo<'"& sGalutine1 &"' and '"& sGalutine1 &"'<=kg_iki) and (km_nuo<'" & tAtstum &"' and '"& tAtstum &"'<=km_iki)" into tSQL
put revDataFromQuery(tab, cr, sDatabaseID, tSQL) into tTest
put tTest & "Lt" into field "test"

tAtstum - this DB record value must be taken from a column 3 field with right row ID+ some calculations -attachment shot.
every time another result must be.

Somewhere there was mentioned about arrays or smthing, i didn't figured out .
I will upload attachment.

Please help guys...

[sturgis]

I think what you're looking for is something like

select col1, col2, col3, (col2 * 5) as dynamicCol from mytable where.......

This should return col1, col2, col3 and a result column named dynamicCol that is populated with the result of the calculation col2 * 5

Not sure if this is what you want, and am not all that knowledgeable about sql myself but I think its close to what you're asking.

[poisonnnn]

Hmmm, I've tried something like this as you said:

put "SELECT isv_laik,atv_laik,atstum,(select kaina from tarifai use index (tarifas) where (kg_nuo<'"& sGalutine1 &"' and '"& sGalutine1 &"'<=kg_iki) and (km_nuo<atstum and atstum<=km_iki) as dynamicCol FROM reisas where gal='"& sResaultSave & "'ORDER BY isv_laik" into tSQL
put revDataFromQuery(tab, cr, sDatabaseID, tSQL) into tData
put tData into field "ats"

atstum - column 2 value.. not working :/. It's like query in a query with filter i gues...

Help please.

[sturgis]

I think I don't understand enough to help with this one. If you don't have luck getting a response here that solves the issue you might post a question on an sql specific forum, and also might post a question to the use-livecode list. I think there are some heavy sql users on the list that may not frequent the forums.

You can sign up for the list here. http://lists.runrev.com/mailman/listinfo/use-livecode

[mwieder]

try putting tSQL into a field and examine it to see if it's what you expect before issuing the revDataFromQuery call.

[poisonnnn]

Hmmm... putting tSQL into a field without evDataFromQuery call gives me the SQL query i've writen.

[mwieder]

...but it should have the variable values filled in properly and the quotes should be balanced, etc.

if it still looks right, can you issue the select command as written from the mysql command prompt?

[poisonnnn]

it doesn't. look, I will take a more simple query:

put " SELECT atstum FROM reisas where gal='"& sResaultSave & "' ORDER BY isv_laik" into tSQL
#put revDataFromQuery(tab, cr, sDatabaseID, tSQL) into tAtstum
put tSQL into field "test"

field test gives: SELECT atstum FROM reisas where gal='"& sResaultSave & "' ORDER BY isv_laik

But as i know :

put revDataFromQuery(tab, cr, sDatabaseID, tSQL) into tAtstum
put tSQL into field "test"

= is the same as

put revDataFromQuery(tab, cr, sDatabaseID, "SELECT atstum FROM reisas where gal='"& sResaultSave & "' ORDER BY isv_laik") into tAtstum
put tSQL into field "test"

So the field will give the writen query "SELECT...."

[poisonnnn]

so again...

put "SELECT isv_laik,atv_laik,atstum,(select kaina from tarifai use index (tarifas) where (kg_nuo<'"& sGalutine1 &"' and '"& sGalutine1 &"'<=kg_iki) and (km_nuo<atstum and atstum<=km_iki) as dynamicCol FROM reisas where gal='"& sResaultSave & "'ORDER BY isv_laik" into tSQL
put revDataFromQuery(tab, cr, sDatabaseID, tSQL) into tData

atstum - it must take different value ot "atstum" in every row from column 2... but in the end column 4 must show (select kaina...) , where "kaina" is from the other table.
anybody ? :/

[mwieder]

field test gives: SELECT atstum FROM reisas where gal='"& sResaultSave & "' ORDER BY isv_laik

is wrong. You can't expect reasonable results if your query string is wrong. Your variable isn't getting evaluated.

Again, try issuing the SQL query from the MySQL command line prompt. Once you've got the syntax working there you can start to convert it to the form where you can substitute variables in a LiveCode command string. If you can't get MySQL to accept the syntax then no amount of coaxing from LiveCode is going to help.

Code: Select all

mysql>SELECT atstum FROM reisas where gal='someValue' ORDER BY isv_laik;

[poisonnnn]

In MySQL it's fine.
In LC I don't get any data without revDataFromQuery

put " SELECT atstum FROM reisas" into tSQL
put tSQL into field "ats"

gives me:SELECT atstum FROM reisas.

[mwieder]

<sigh> ok. let's take this a step at a time.

Code: Select all

put " SELECT atstum FROM reisas" into tSQL
put revDataFromQuery(tSQL) into field "ats"

if that gives you valid results then move on to

Code: Select all

put " SELECT atstum FROM reisas where gal='3'" into tSQL
put revDataFromQuery(tSQL) into field "ats"

(or whatever value is reasonable for the "gal" field) and then

Code: Select all

put " SELECT atstum FROM reisas where gal='3' ORDER BY isv_laik" into tSQL
put revDataFromQuery(tSQL) into field "ats"

experiment by building it up a step at a time:

Code: Select all

put " SELECT atstum FROM reisas where gal='" & sResaultSave & "' ORDER BY isv_laik" into tSQL
put revDataFromQuery(tSQL) into field "ats"

[poisonnnn]

First of all thanks for the replies mwieder , sorry, I just want to make it work.

The query works fine, until i put something like this:

put "SELECT isv_laik,atv_laik,atstum,(SELECT info FROM table_home WHERE info < atstum) FROM reisas where gal='3' ORDER BY isv_laik" into tSQL

So the question is: how to use data from other table in the same query for filtering:

SELECT A, B ,C, (SELECT D FROM TABLE2 WHERE C < B[from table1]) FROM TABLE1; <-- the stucture of query