DATABASE MYSQL column automatic values calculated
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
DATABASE MYSQL column automatic values calculated
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
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
Re: DATABASE MYSQL column automatic values calculated
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.
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.
Re: DATABASE MYSQL column automatic values calculated
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...
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...
Re: DATABASE MYSQL column automatic values calculated
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.
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.
Re: DATABASE MYSQL column automatic values calculated
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.
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.
Re: DATABASE MYSQL column automatic values calculated
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
You can sign up for the list here. http://lists.runrev.com/mailman/listinfo/use-livecode
-
- VIP Livecode Opensource Backer
- Posts: 3581
- Joined: Mon Jan 22, 2007 7:36 am
- Location: Berkeley, CA, US
- Contact:
Re: DATABASE MYSQL column automatic values calculated
try putting tSQL into a field and examine it to see if it's what you expect before issuing the revDataFromQuery call.
Re: DATABASE MYSQL column automatic values calculated
Hmmm... putting tSQL into a field without evDataFromQuery call gives me the SQL query i've writen.
-
- VIP Livecode Opensource Backer
- Posts: 3581
- Joined: Mon Jan 22, 2007 7:36 am
- Location: Berkeley, CA, US
- Contact:
Re: DATABASE MYSQL column automatic values calculated
...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?
if it still looks right, can you issue the select command as written from the mysql command prompt?
Re: DATABASE MYSQL column automatic values calculated
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...."
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...."
Re: DATABASE MYSQL column automatic values calculated
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 ? :/
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 ? :/
-
- VIP Livecode Opensource Backer
- Posts: 3581
- Joined: Mon Jan 22, 2007 7:36 am
- Location: Berkeley, CA, US
- Contact:
Re: DATABASE MYSQL column automatic values calculated
is wrong. You can't expect reasonable results if your query string is wrong. Your variable isn't getting evaluated.field test gives: SELECT atstum FROM reisas where gal='"& sResaultSave & "' ORDER BY isv_laik
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;
Re: DATABASE MYSQL column automatic values calculated
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.
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.
-
- VIP Livecode Opensource Backer
- Posts: 3581
- Joined: Mon Jan 22, 2007 7:36 am
- Location: Berkeley, CA, US
- Contact:
Re: DATABASE MYSQL column automatic values calculated
<sigh> ok. let's take this a step at a time.
if that gives you valid results then move on to
(or whatever value is reasonable for the "gal" field) and then
experiment by building it up a step at a time:
Code: Select all
put " SELECT atstum FROM reisas" into tSQL
put revDataFromQuery(tSQL) into field "ats"
Code: Select all
put " SELECT atstum FROM reisas where gal='3'" into tSQL
put revDataFromQuery(tSQL) into field "ats"
Code: Select all
put " SELECT atstum FROM reisas where gal='3' ORDER BY isv_laik" into tSQL
put revDataFromQuery(tSQL) into field "ats"
Code: Select all
put " SELECT atstum FROM reisas where gal='" & sResaultSave & "' ORDER BY isv_laik" into tSQL
put revDataFromQuery(tSQL) into field "ats"
Re: DATABASE MYSQL column automatic values calculated
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
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