+- SphereCommunity (https://forum.spherecommunity.net)
+-- Forum: Sphere 0.56d (/Forum-Sphere-0-56d)
+--- Forum: Script Help (/Forum-Script-Help)
+--- Thread: SQL Problem... (/Thread-SQL-Problem)
SQL Problem... - admin_teclis - 09-12-2015 12:33 AM
Good evening,
I have a problem with a SQL query.
Use the script to create the paperdoll Avatar.
Because there is a bug, that is, every time a client connects rewrites again his skill, I made a query that checks whether it already exists and performs an UPDATE instead of rewriting.
Here lies the problem ... The query works, but instead of cycling all skills I marked all as necromancy ... and of course with its value ..
The query is this:
// Character skills UPDATE
Db.query "SELECT * FROM WHERE myrunuo_characters_skills char_id = '<eval <uid >>'
if (<DB.ROW.NUMROWS>> 0)
for x 1 <eval <DB.ROW.NUMROWS >>
Db.Execute "UPDATE myrunuo_characters_skills September skill_id = <dlocal.x>, skill_value = << def.player_skill_ <dlocal.x >>> WHERE char_id = <eval <uid >>"
endfor
endif
Db.query "SELECT * FROM WHERE myrunuo_characters_skills char_id = '<eval <uid >>'
if (<DB.ROW.NUMROWS> == 0)
// Insert Character Skills
for 1 x 49
db.query insert into myrunuo_characters_skills (char_id, skill_id, skill_value) VALUES ('<eval <uid >>', '<dlocal.x>', '<< def.player_skill_ <dlocal.x >>>')
endfor
endif
RE: SQL Problem... - azmanomer - 09-12-2015 12:54 AM
Db.query "SELECT * FROM WHERE myrunuo_characters_skills char_id = '<eval <uid >>' ---> this one should be without eval just use <uid>
if (<DB.ROW.NUMROWS>> 0)
for x 1 <eval <DB.ROW.NUMROWS >> --> rows starts from 0 so you should start for loop from 0 and <eval <db.row.numrows>-1>
Db.Execute "UPDATE myrunuo_characters_skills September skill_id = <dlocal.x>, skill_value = << def.player_skill_ <dlocal.x >>> WHERE char_id = <eval <uid >>" why did you use "<>" just write <def.player_skill_<dlocal.x>> also same use <uid> without eval
endfor
endif
Db.query "SELECT * FROM WHERE myrunuo_characters_skills char_id = '<eval <uid >>' ---> this one should be without eval just use <uid>
if (<DB.ROW.NUMROWS> == 0)
// Insert Character Skills
for x 0 48 ---> also start from 0
db.query insert into myrunuo_characters_skills (char_id, skill_id, skill_value) VALUES ('<eval <uid >>', '<dlocal.x>', '<< def.player_skill_ <dlocal.x >>>') ---> why did you use "<>" just write '<def.player_skill_<dlocal.x>>' also same use <uid> without eval
endfor
endif
also you can use <serv.skill.<dlocal.x>.name> for example <serv.skill.0.name> is return Alchemy i dont understand what do you want exactly so i wish these could help
RE: SQL Problem... - admin_teclis - 09-12-2015 01:57 AM
Hello,
thanks for the reply .
I must just make sure that :
When the logs pg automatically checks whether the DB is already rpesente quell UID . If there takes skill which at that time and performs an UPDATE on the basis of ' UID .
If there is no must write them all .
RE: SQL Problem... - Coruja - 09-12-2015 03:29 AM
you must use the arguments in correct order, you wrote SELECT [a] FROM WHERE [b] [c] but the correct is SELECT [a] FROM [b] WHERE [c]
Code:
DB.QUERY "SELECT * FROM myrunuo_characters_skills WHERE char_id='<UID>' LIMIT 1
IF (<DB.ROW.NUMROWS>)
FOR 1 49
DB.EXECUTE "UPDATE myrunuo_characters_skills SET skill_id=<dLOCAL._FOR>, skill_value=<I.<LOCAL._FOR>> WHERE char_id=<UID> LIMIT 1"
ENDFOR
ELSE
FOR 1 49
DB.QUERY "INSERT INTO myrunuo_characters_skills (char_id, skill_id, skill_value) VALUES ('<UID>', '<dLOCAL._FOR>', '<I.<LOCAL._FOR>>')"
ENDFOR
ENDIFRE: SQL Problem... - admin_teclis - 09-17-2015 08:12 AM
(09-12-2015 03:29 AM)Coruja Wrote: you must use the arguments in correct order, you wrote SELECT [a] FROM WHERE [b] [c] but the correct is SELECT [a] FROM [b] WHERE [c]Hello , I tried your correction but the problem remains ... If log in the first time makes the correct entry . If you change the skill and the skill log out saves only 49 with the exact value for 49 times .
Code:
DB.QUERY "SELECT * FROM myrunuo_characters_skills WHERE char_id='<UID>' LIMIT 1
IF (<DB.ROW.NUMROWS>)
FOR 1 49
DB.EXECUTE "UPDATE myrunuo_characters_skills SET skill_id=<dLOCAL._FOR>, skill_value=<I.<LOCAL._FOR>> WHERE char_id=<UID> LIMIT 1"
ENDFOR
ELSE
FOR 1 49
DB.QUERY "INSERT INTO myrunuo_characters_skills (char_id, skill_id, skill_value) VALUES ('<UID>', '<dLOCAL._FOR>', '<I.<LOCAL._FOR>>')"
ENDFOR
ENDIF
RE: SQL Problem... - Extreme - 09-17-2015 12:15 PM
You forgot to specify from what table you are querying.
Select * from table_name where bla_bla
RE: SQL Problem... - Artyk - 09-17-2015 05:44 PM
(09-12-2015 03:29 AM)Coruja Wrote: you must use the arguments in correct order, you wrote SELECT [a] FROM WHERE [b] [c] but the correct is SELECT [a] FROM [b] WHERE [c]
Code:
DB.QUERY "SELECT * FROM myrunuo_characters_skills WHERE char_id='<UID>' LIMIT 1
IF (<DB.ROW.NUMROWS>)
FOR 1 49
DB.EXECUTE "UPDATE myrunuo_characters_skills SET skill_id=<dLOCAL._FOR>, skill_value=<I.<LOCAL._FOR>> WHERE char_id=<UID> LIMIT 1"
ENDFOR
ELSE
FOR 1 49
DB.QUERY "INSERT INTO myrunuo_characters_skills (char_id, skill_id, skill_value) VALUES ('<UID>', '<dLOCAL._FOR>', '<I.<LOCAL._FOR>>')"
ENDFOR
ENDIF
Coruja did correct your code in the right way, the problem is that there is a logical error: for each row you're updating skill_id and skill_value checking if char_id = <UID>. You should only set skill_value=<I.<LOCAL._FOR>>, checking if char_id=<UID> and skill_id=<dlocal._FOR>