[mysql] Query that gives points and takes items

[wesoly136]

Hi!
I need a query that will take an item from player and give them amount of points.

For example.
Account manager has item with id 9999

and When I execute the query he will lose this item and get points which I will write in query

It should work for all players in database.

Thanks

 

[Doggynub]

Actually this depends where is these points located in database.
another thing you want this like you use somthing item removed and points added in
db by excuting a query?

 

[wesoly136]

Im very weak at mysql, I need a query that takes item with id which I will complete and gives to account>premium_points and amount of points I will set too.

 

[Doggynub]

local amount = 4
doPlayerRemoveItem(cid,item,count)
---(if point in account then)
  db.executeQuery("UPDATE `accounts` SET `premium_points` = `premium_points` + " .. amount .. " WHERE `name`= '"..getAccountName(cid).."'' LIMIT 1;")

??

 

[wesoly136]

I dont need it to a script, I want to execute it in phpmyadmin for ALL players

 

[Doggynub]

oh

 

[Doggynub]

why not just as script it can aslo get al players who has this item and add al them the points too
just saying

 

[wesoly136]

Because I want to restart lvls and pay for items with premium ppoints

 

[Doggynub]

Try searching here if you want to increse your skills
SQL Tutorial

 

[wesoly136]

Ok, it can be two different queries. Something like that but I don't know how to write it correctly

UPDATE accounts SET premium_points = premium_points + 30 WHERE player_id AND players_items[ AND itemtype] = 9999;

And the second that removes these items

UPDATE players_items DELETE [hmm.. "line"? :P] WHERE itemtype = 9999;

Any ideas?

 

[Darkhaos]

Ok, it can be two different queries. Something like that but I don't know how to write it correctly

UPDATE accounts SET premium_points = premium_points + 30 WHERE player_id AND players_items[ AND itemtype] = 9999;

And the second that removes these items

UPDATE players_items DELETE [hmm.. "line"? :P] WHERE itemtype = 9999;

Any ideas?

delete * from player_items where itemtype = xx and player_id  = xx;

 

[wesoly136]

Yes but I want it to delete it from all players , so only remove 'and player_id = xx' ??

 

[Cykotitan]

ok here's a talkaction (coded in a noob way though, hope you don't mind )

function onSay(cid, words, param, channel)
	if not param or param == '' then
		return doPlayerSendTextMessage(cid, MESSAGE_STATUS_CONSOLE_BLUE, 'Command requires parameters.\nUsage: ' .. words .. ' itemid, points (per item)')
	end

	param = string.explode(param, ',')
	if #param ~= 2 then
		return doPlayerSendTextMessage(cid, MESSAGE_STATUS_CONSOLE_BLUE, 'Insufficient or too many parameters.\nUsage: ' .. words .. ' itemid, points (per item)')
	end
	param[1], param[2] = tonumber(param[1]), tonumber(param[2])
	if not param[1] or not param[2] then
		return doPlayerSendTextMessage(cid, MESSAGE_STATUS_CONSOLE_BLUE, 'One or more parameters are not numbers.')
	end

	local players, done, nplayers, npoints = db.getResult("SELECT `player_id` FROM `player_items` WHERE `itemtype` = " .. param[1] .. ";"), {}, 0, 0
	if players:getID() ~= -1 then
		repeat
			local cur, get = players:getDataInt('player_id'), 0
			if not isInArray(done, cur) then
				table.insert(done, cur)
				local player = getPlayerByGUID(cur)
				if player then
					get = getPlayerItemCount(player, param[1])
					doPlayerRemoveItem(player, param[1], get)
				else
					local _get = db.getResult("SELECT SUM(`count`) as `count` FROM `player_items` WHERE `itemtype` = " .. param[1] .. " AND `player_id` = " .. cur .. ";")
					get = _get:getDataInt('count')
					_get:free()
					db.executeQuery("DELETE FROM `player_items` WHERE `itemtype` = " .. param[1] .. " AND `player_id` = " .. cur .. ";")
				end
				local add, accid = param[2] * get, player and getPlayerAccountId(player)
				if add > 0 then
					if not accid then
						local _get = db.getResult("SELECT `account_id` FROM `players` WHERE `id` = " .. cur .. ";")
						accid = _get:getDataInt('account_id')
						_get:free()
					else
						doPlayerSendTextMessage(cid, MESSAGE_EVENT_ADVANCE, 'You have received ' .. add .. ' premium points.')
					end
					db.executeQuery("UPDATE `accounts` SET `premium_points` = `premium_points` + " .. add .. " WHERE `id` = " .. accid .. ";")
					npoints, nplayers = npoints + add, nplayers + 1
				end
			end
		until not players:next()
		players:free()
	end
	
	return doPlayerSendTextMessage(cid, MESSAGE_STATUS_CONSOLE_BLUE, 'A total of ' .. nplayers .. ' player(s) have received ' .. npoints .. ' point(s).')
end

 

[wesoly136]

Could you say me how it works, and the player which will get points have to be online ?

 

[Cykotitan]

He can be offline, or online. Doesn't matter ^_^

/command itemid, points

 

[wesoly136]

Its still problematicaly, I wanted to do it for all players saying/executing one command :S

 

[Cykotitan]

what's the problem? it does it for all players

 

[wesoly136]

Oh, so sorry, Im tired today :S

 

[wesoly136]

Okay, this scripts rox but I cant rep you If I will remember I will rep you in the future :*

 

[wesoly136]

Need it for storages