Need MySql Help

[Psyfire]

Hello,

I got a little script where I want to select from 2 tables special fields.

Code: Select all

"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?

[rosc2112]

Post your code.

[Psyfire]

Code: Select all

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 ..."
			}
		}

[rosc2112]

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

• to create the data line:
  

  Code: Select all

  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]]
  

[Psyfire]

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: Select all

[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.

[rosc2112]

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.

[Psyfire]

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?

[rosc2112]

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.)