Lua Database query – only one result instead of a dozen

[Darius93]

I have a problem because I have several entries in the database, but the script only creates one NPC and that’s it.


What’s the issue? I’ve been trying for several hours already, and what I thought would be the easiest part is taking me the most time...

function spawnPlayerShops()
local shops = db.storeQuery([[
    SELECT ps.player_id, ps.posx, ps.posy, ps.posz,
           p.name, p.looktype, p.lookhead, p.lookbody, p.looklegs, p.lookfeet, p.lookaddons
    FROM player_shops ps
    INNER JOIN players p ON ps.player_id = p.id
]])
if shops then
    repeat
        local sellerId   = result.getNumber(shops, "player_id")
        local pos = Position(
            result.getNumber(shops, "posx"),
            result.getNumber(shops, "posy"),
            result.getNumber(shops, "posz")
        )
        local sellerName = result.getString(shops, "name")

        -- outfit z bazy
        local outfit = {
            lookType   = result.getNumber(shops, "looktype") or 128,
            lookAddons = result.getNumber(shops, "lookaddons") or 0,
            lookHead   = result.getNumber(shops, "lookhead") or 0,
            lookBody   = result.getNumber(shops, "lookbody") or 0,
            lookLegs   = result.getNumber(shops, "looklegs") or 0,
            lookFeet   = result.getNumber(shops, "lookfeet") or 0
        }

      
        print(string.format("[MARKET DEBUG] Tworze NPC: id=%d, name=%s, pos=(%d,%d,%d)",
            sellerId, sellerName, pos.x, pos.y, pos.z))

        local npc = Game.createNpc("Shop", pos)
        if npc then
            setCreatureName(npc, sellerName)
            npc:setOutfit(outfit)
            print("[MARKET] Utworzono NPC sklepu dla gracza " .. sellerName ..
                  " na pozycji " .. pos.x .. "," .. pos.y .. "," .. pos.z)
        else
            print("[MARKET] Błąd tworzenia NPC dla gracza ID " .. sellerId)
        end
    until not result.next(shops)
    result.free(shops)
    end
end

function onStartup()
    spawnPlayerShops()
end

CREATE TABLE `player_shops` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `player_id` INT NOT NULL,
    `posx` INT NOT NULL,
    `posy` INT NOT NULL,
    `posz` INT NOT NULL,
    `opened_at` BIGINT NOT NULL,
    `expires_at` BIGINT NOT NULL,
    CONSTRAINT `fk_player_shops_player` FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

[Gesior.pl]

What’s the issue?

What does it print in console? There are many 'debug prints' in code.
Just spawns 1 NPC and ignore all other rows in database? repeat does not work?

 

[Darius93]

That doesn't work - I have no way to send it now.
But it only printed 1 NPC.

So I added it to the table and downloaded it from there, and it works

function spawnPlayerShops()
    print("[MARKET DEBUG] Start spawnPlayerShops()")

    -- najpierw pobieramy WSZYSTKIE rekordy i wrzucamy do tablicy
    local res = db.storeQuery([[
        SELECT ps.id as shop_id, ps.player_id, ps.posx, ps.posy, ps.posz,
               p.name, p.looktype, p.lookaddons,
               p.lookhead, p.lookbody, p.looklegs, p.lookfeet
        FROM player_shops ps
        INNER JOIN players p ON ps.player_id = p.id
    ]])
    if not res then
        print("[MARKET DEBUG] Brak sklepów w bazie")
        return
    end

    local shopsData = {}
    while true do
        table.insert(shopsData, {
            shop_id   = result.getNumber(res, "shop_id"),
            player_id = result.getNumber(res, "player_id"),
            posx      = result.getNumber(res, "posx"),
            posy      = result.getNumber(res, "posy"),
            posz      = result.getNumber(res, "posz"),
            name      = result.getString(res, "name"),
            lookType  = result.getNumber(res, "looktype"),
            lookAddons= result.getNumber(res, "lookaddons"),
            lookHead  = result.getNumber(res, "lookhead"),
            lookBody  = result.getNumber(res, "lookbody"),
            lookLegs  = result.getNumber(res, "looklegs"),
            lookFeet  = result.getNumber(res, "lookfeet")
        })

        if not result.next(res) then
            break
        end
    end
    result.free(res)

    print("[MARKET DEBUG] Załadowano rekordów: " .. #shopsData)

    -- teraz tworzymy NPC na podstawie tablicy shopsData
    for i, shop in ipairs(shopsData) do
        local pos = Position(shop.posx, shop.posy, shop.posz)
        local sellerName = shop.name or ("Shop_" .. shop.player_id)

        print(string.format("[MARKET DEBUG] Tworzę NPC #%d: shop_id=%d, player_id=%d, name=%s, pos=(%d,%d,%d)",
            i, shop.shop_id, shop.player_id, sellerName, shop.posx, shop.posy, shop.posz))

        local npc = Game.createNpc("Shop", pos)
        if npc then
            setCreatureName(npc, sellerName)
            npc:setOutfit({
                lookType   = shop.lookType or 128,
                lookAddons = shop.lookAddons or 0,
                lookHead   = shop.lookHead or 0,
                lookBody   = shop.lookBody or 0,
                lookLegs   = shop.lookLegs or 0,
                lookFeet   = shop.lookFeet or 0
            })
            print("[MARKET] Utworzono NPC sklepu dla gracza " .. sellerName ..
                  " na pozycji " .. pos.x .. "," .. pos.y .. "," .. pos.z)
        else
            print("[MARKET DEBUG] NIE UDAŁO SIĘ utworzyć NPC " .. sellerName ..
                  " na pozycji " .. pos.x .. "," .. pos.y .. "," .. pos.z)
        end
    end
end

function onStartup()
    spawnPlayerShops()
end