| View previous topic :: View next topic |
| Author |
Message |
Psyfire
Voice
Joined: 05 Nov 2006
Posts: 36
|
 Posted: Sun Mar 04, 2007 9:19 pm Post subject: Need MySql Help |
 |
|
Hello,
I got a little script where I want to select from 2 tables special fields.
| Code: |
"SELECT hlstats_PlayerNames.playerId,hlstats_PlayerNames.name,hlstats_Players.skill FROM hlstats_PlayerNames, hlstats_Players WHERE name = '$arg'"
|
I try to explain my problem.
I want 2 fields from hlstats_PlayerNames and 1 field from hlstats_Players.
If you watch your stats now via the command in the channel you get the following information for 2 users.
User1
PlayerId = 1
Name = Test1
Skill = 1500
User2
PlayerId = 2
Name = Test2
Skill = 1500
As you can see, the playerId and the name are different, that means it works. But if you look at the skill, you got everytime at every user 1500. I know that is something wrong like I select the skill from the second table hlstats_Players
But how can I use that it works? |
|
| Back to top |
|
 |
rosc2112
Revered One
Joined: 19 Feb 2006
Posts: 1454
Location: Northeast Pennsylvania
|
| Posted: Mon Mar 05, 2007 12:20 am Post subject: |
|
|
| Post your code. |
|
| Back to top |
|
|
Psyfire
Voice
Joined: 05 Nov 2006
Posts: 36
|
| Posted: Mon Mar 05, 2007 10:10 am Post subject: |
|
|
| Code: |
proc amxx:db:stats {nick host hand chan arg} {
global sql
set searchid [lindex $arg 0]
if {![string match "*" $searchid]} {
putserv "NOTICE $nick :( HLStats ) Wrong syntax! - Type: $::hlstats(trigger)stats nickname"
return
}
set sql(handle) [mysqlconnect -host $sql(host) -user $sql(user) -password $sql(pass) -db $sql(db)]
set sma(qry) [mysqlsel $sql(handle) "SELECT hlstats_PlayerNames.playerId,hlstats_PlayerNames.name,hlstats_PlayerNames.lastuse,hlstats_Players.skill,hlstats_PlayerNames.kills,hlstats_PlayerNames.deaths,hlstats_PlayerNames.suicides FROM hlstats_PlayerNames, hlstats_Players WHERE name = '$arg'" -flatlist]
mysqlclose $sql(handle)
if {$sma(qry) != ""} {
set gelesen(playerId) [lindex $sma(qry) 0]
set gelesen(name) [lindex $sma(qry) 1]
set gelesen(lastuse) [lindex $sma(qry) 2]
set gelesen(skill) [lindex $sma(qry) 3]
set gelesen(kills) [lindex $sma(qry) 4]
set gelesen(deaths) [lindex $sma(qry) 5]
set gelesen(suicides) [lindex $sma(qry) 6]
putserv "PRIVMSG $chan :»» Player ID: $gelesen(playerId)"
putserv "PRIVMSG $chan :»» Nickname: $gelesen(name)"
putserv "PRIVMSG $chan :»» Last Use: $gelesen(lastuse)"
putserv "PRIVMSG $chan :»» Skill: $gelesen(skill)"
putserv "PRIVMSG $chan :»» Kills: $gelesen(kills)"
putserv "PRIVMSG $chan :»» Deaths: $gelesen(deaths)"
putserv "PRIVMSG $chan :»» Suicides: $gelesen(suicides)"
} else {
putserv "PRIVMSG $chan :$arg not found in HLStats Database ..."
}
}
|
|
|
| Back to top |
|
|
rosc2112
Revered One
Joined: 19 Feb 2006
Posts: 1454
Location: Northeast Pennsylvania
|
| Posted: Mon Mar 05, 2007 2:36 pm Post subject: |
|
|
And please post the result for set sma(qry) (maybe 2 examples, to compare the data.) I suspect when you use lindex, there are spaces in the fields of data, and lindex is getting the wrong items. You can get the result by putting a
putcmdlog "smaqry '$sma(qry)'"
into your script after the line with set sma(qry)
If that is the case, where the fields have spaces, you could try using [list] to create the data line:
| Code: |
set sma(qry) [list [mysqlsel $sql(handle) "SELECT hlstats_PlayerNames.playerId,hlstats_PlayerNames.name,hlstats_PlayerNames.lastuse,hlstats_Players.skill,hlstats_PlayerNames.kills,hlstats_PlayerNames.deaths,hlstats_PlayerNames.suicides FROM hlstats_PlayerNames, hlstats_Players WHERE name = '$arg'" -flatlist]]
|
|
|
| Back to top |
|
|
Psyfire
Voice
Joined: 05 Nov 2006
Posts: 36
|
| Posted: Mon Mar 05, 2007 10:09 pm Post subject: |
|
|
Hello,
I replaced your line with my line and the result is nothing.
What happened:
[03:03:28] <Myrdin> !stats2 Myrdin
[03:03:33] <EA-Bot> »» Nickname:
[03:03:34] <EA-Bot> »» Skill:
As you can see it will display nothing. Then I edited the line and added the putcmdlog after the sma qry line. Of course I am logged on into the partyline. The Partyline showed the following things.
| Code: |
[03:06:56] <(EA-Bot> [03:01] smaqry '26 Myrdin 1311 26 Myrdin 1151 26 Myrdin 1607 26 Myrdin 1136 26 Myrdin 1371 26 Myrdin 917 26 Myrdin 927 26 Myrdin 859 26 Myrdin 1083 26 Myrdin 1012 26 Myrdin 769 26 Myrdin 889 26 Myrdin 932 26 Myrdin 1127 26 Myrdin 932 26 Myrdin 1004 26 Myrdin 995 26 Myrdin 973 26 Myrdin 988 26 Myrdin 1030 26 Myrdin 897 26 Myrdin 1070 26 Myrdin 1245 26 Myrdin 1000 26 Myrdin 1101 26 Myrdin 1139 26 Myrdin 1457 26 Myrdin 985 26 Myrdin 839 26 Myrdin 882 26 Myrdin 932 26 Myrdin 938 26 Myrdin 703 26 Myr
[03:07:26] <(EA-Bot> [03:01] smaqry '9879 {shoot the butmunch} 1311 9879 {shoot the butmunch} 1151 9879 {shoot the butmunch} 1607 9879 {shoot the butmunch} 1136 9879 {shoot the butmunch} 1371 9879 {shoot the butmunch} 917 9879 {shoot the butmunch} 927 9879 {shoot the butmunch} 859 9879 {shoot the butmunch} 1083 9879 {shoot the butmunch} 1012 9879 {shoot the butmunch} 769 9879 {shoot the butmunch} 889 9879 {shoot the butmunch} 932 9879 {shoot the butmunch} 1127 9879 {shoot the butmunch} 932 9879 {shoot the butmunch}
|
As you can see, the number 1311 will be shown at my name (Myrdin) and some friend (shoot the butmunch)
Its the same skill like ever. It only displayed my skill on all other nicknames. It does not work 
Edit: If you want, you can join #bla-blubb at quakenet. Just write me and we can test it live together. |
|
| Back to top |
|
|
rosc2112
Revered One
Joined: 19 Feb 2006
Posts: 1454
Location: Northeast Pennsylvania
|
| Posted: Mon Mar 05, 2007 11:58 pm Post subject: |
|
|
| It looks like my suspicion was correct, your lindex's simply do not grab the right fields of data. Count your output fields correctly and you'll get the right data.. Fields enclosed in {} count as one field. |
|
| Back to top |
|
|
Psyfire
Voice
Joined: 05 Nov 2006
Posts: 36
|
| Posted: Tue Mar 06, 2007 6:20 am Post subject: |
|
|
Lindex only grabs the wrong data from the second table...
From the first table lindex grabs all data correctly.
What do I need to change now? |
|
| Back to top |
|
|
rosc2112
Revered One
Joined: 19 Feb 2006
Posts: 1454
Location: Northeast Pennsylvania
|
| Posted: Tue Mar 06, 2007 7:38 pm Post subject: |
|
|
Not being an mysql guru, it's hard for me to tell if both of your tables data are in the same var/result. I'm assuming it is not, since you're saying the problem is the var gets the data from table 1, but not table 2 so it sounds like the results from getting table2 data, is clobbering table1's data..
In which case, you should get the data from table 1, then use lappend to add that to a new varname, then get the data from table 2, and again use lappend to put the right data into the same varname as the stuff from table 1. THEN you can sort out the bits you want (grabbing it with lindex, of course.) |
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
