Website: Market

Step-by-step guide for web developers - show Market listings (every item for sale, a character's store) and sold listings (recent sales, price history, top sellers) on the website, with SQL, Node.js a

The Market keeps each character's store in one binary column and every sale in one table. This guide shows how to use both on a website: an all listings page, a character store page, a recent sales feed, price history and a sellers ladder.

What you can show

Page	Shows	Source
All listings	Every priced item on the server. Filter by currency, item, level. Sort by price or newest	Character.MarketStore
Character store	One character's priced rows	Character.MarketStore
My store (owner only)	All rows, unlisted rows and the wallet	Character.MarketStore
Recent sales	Last sales: item, seller, buyer, price, time	dbo.MarketSaleLog
Price history	Average / lowest sale price of an item	dbo.MarketSaleLog (or the archive)
Top sellers	Sales count and earnings per seller	Archive

The database does not store if a store is open or closed. A listing on the website means "this row has a price". The player buys it in game: by clicking the seller, or from the Market search (S key) when the store is open.

Where the data lives

Data	Where
Store rows + wallet	Character.MarketStore, varbinary(1536). NULL = empty store
Sales	dbo.MarketSaleLog, one row per sale
Item bytes	Same 16-byte format as Character.Inventory. Use your site's existing item decoder for names, options and images

Both are created by DataServer/SQL/Migrations/0002_market.sql (schema 2).

The store column is saved together with the inventory, right after each change (deposit, withdraw, open, close, sale, collect). A price change is saved within a few seconds.

Step 1 - Read the store blob

All numbers are little-endian (least significant byte first). Total size 1471 bytes.

Offset	Size	Field
0	1	Version. 1 = valid. 0 or NULL column = empty store
1	8	Wallet zen, signed 64-bit
9	4	Wallet Jewel of Bless (single jewels)
13	4	Wallet Jewel of Soul
17	4	Wallet Jewel of Chaos
21	29 × 50	50 rows. Row r starts at 21 + r × 29

Row (29 bytes):

Offset	Size	Field
0	16	Item, 16-byte DB format. All 0xFF = empty row
16	1	Currency. 0 = in the store but not listed
17	8	Price, signed 64-bit
25	4	Listed at, Unix time in seconds (UTC). Set when the price was set

Currency codes (same in MarketSaleLog.Currency):

Code	Currency
1	Zen
2	Jewel of Bless
3	Jewel of Soul
4	Jewel of Chaos
5	WCoinC
6	WCoinP
7	GoblinPoint

A row is for sale when the item is not empty, Currency is 1-7 and Price > 0. Jewel prices count single jewels.

Useful item bytes (offsets inside the 16-byte item):

Field	How
Section	byte9 >> 4
Number	byte0 + ((byte7 & 0x80) << 1)
Level (+0..+15)	(byte1 >> 3) & 15
Excellent options	byte7 & 63 (bit mask)
Durability or stack count	byte2
Serial	bytes 3-6, big-endian

Node.js / TypeScript

// marketStore.ts
export const CURRENCY = ["", "Zen", "Bless", "Soul", "Chaos", "WCoinC", "WCoinP", "GoblinPoint"];

export function readMarketStore(b: Buffer | null) {
  if (!b || b.length < 1471 || b[0] !== 1) return null;   // empty store
  const wallet = {
    zen: b.readBigInt64LE(1),
    bless: b.readUInt32LE(9),
    soul: b.readUInt32LE(13),
    chaos: b.readUInt32LE(17),
  };
  const rows = [];
  for (let r = 0, p = 21; r < 50; r++, p += 29) {
    const item = b.subarray(p, p + 16);
    if (item.every((x) => x === 0xff)) continue;           // empty row
    const currency = b[p + 16];
    const price = b.readBigInt64LE(p + 17);
    rows.push({
      row: r,
      item: Buffer.from(item),                              // pass to your item decoder
      section: item[9] >> 4,
      number: item[0] + ((item[7] & 0x80) << 1),
      level: (item[1] >> 3) & 15,
      currency,
      price,
      listed: currency >= 1 && currency <= 7 && price > 0n,
      listedAt: new Date(b.readUInt32LE(p + 25) * 1000),
    });
  }
  return { wallet, rows };
}

Prices and zen are bigint. Zen can be above Number.MAX_SAFE_INTEGER, so convert with .toString() for JSON.

PHP

<?php
// market_store.php
const MARKET_CURRENCY = ['', 'Zen', 'Bless', 'Soul', 'Chaos', 'WCoinC', 'WCoinP', 'GoblinPoint'];

function readMarketStore(?string $b): ?array {
    if ($b === null || strlen($b) < 1471 || ord($b[0]) !== 1) return null;   // empty store
    $w = unpack('Pzen/Vbless/Vsoul/Vchaos', $b, 1);
    $rows = [];
    for ($r = 0, $p = 21; $r < 50; $r++, $p += 29) {
        $item = substr($b, $p, 16);
        if ($item === str_repeat("\xFF", 16)) continue;                       // empty row
        $x = unpack('Ccurrency/Pprice/VlistedAt', $b, $p + 16);
        $rows[] = [
            'row'      => $r,
            'item'     => $item,                                              // pass to your item decoder
            'section'  => ord($item[9]) >> 4,
            'number'   => ord($item[0]) + ((ord($item[7]) & 0x80) << 1),
            'level'    => (ord($item[1]) >> 3) & 15,
            'currency' => $x['currency'],
            'price'    => $x['price'],
            'listed'   => $x['currency'] >= 1 && $x['currency'] <= 7 && $x['price'] > 0,
            'listedAt' => $x['listedAt'],                                     // Unix time, UTC
        ];
    }
    return ['wallet' => $w, 'rows' => $rows];
}

Needs 64-bit PHP 7.1+ (P = 64-bit little-endian). Read MarketStore with PDO::SQLSRV_ENCODING_BINARY so it comes back as raw bytes.

Step 2 - All listings page

Reading 1471 bytes per character in code is fine for one store. For a page that searches every store, add this view once. It turns each priced row into one SQL row, so you can filter, sort and page in SQL.

Run it against the MuOnline database in SQL Server Management Studio. It only adds a new object (WEB_ prefix); it changes nothing the game uses.

CREATE VIEW dbo.WEB_MarketListings
AS
SELECT c.Name                                              AS Seller,
       x.r                                                 AS [Row],
       SUBSTRING(c.MarketStore, x.P, 16)                   AS ItemData,
       CAST(SUBSTRING(c.MarketStore, x.P + 9, 1) AS int) / 16                    AS Section,
       CAST(SUBSTRING(c.MarketStore, x.P, 1) AS int)
         + (CAST(SUBSTRING(c.MarketStore, x.P + 7, 1) AS int) & 128) * 2          AS Number,
       (CAST(SUBSTRING(c.MarketStore, x.P + 1, 1) AS int) / 8) & 15               AS [Level],
       CAST(SUBSTRING(c.MarketStore, x.P + 7, 1) AS int) & 63                     AS Excellent,
       CAST(SUBSTRING(c.MarketStore, x.P + 16, 1) AS int)                         AS Currency,
       CAST(CAST(SUBSTRING(c.MarketStore, x.P + 24, 1) + SUBSTRING(c.MarketStore, x.P + 23, 1)
               + SUBSTRING(c.MarketStore, x.P + 22, 1) + SUBSTRING(c.MarketStore, x.P + 21, 1)
               + SUBSTRING(c.MarketStore, x.P + 20, 1) + SUBSTRING(c.MarketStore, x.P + 19, 1)
               + SUBSTRING(c.MarketStore, x.P + 18, 1) + SUBSTRING(c.MarketStore, x.P + 17, 1) AS binary(8)) AS bigint) AS Price,
       DATEADD(second,
               CAST(CAST(SUBSTRING(c.MarketStore, x.P + 28, 1) + SUBSTRING(c.MarketStore, x.P + 27, 1)
                       + SUBSTRING(c.MarketStore, x.P + 26, 1) + SUBSTRING(c.MarketStore, x.P + 25, 1) AS binary(4)) AS bigint),
               '19700101')                                 AS ListedAtUtc
FROM dbo.Character c
CROSS APPLY (SELECT TOP (50) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS r FROM sys.all_objects) n
CROSS APPLY (SELECT n.r AS r, 22 + n.r * 29 AS P) x          -- SQL is 1-based: row r starts at byte 22 + r*29
WHERE c.MarketStore IS NOT NULL
  AND SUBSTRING(c.MarketStore, 1, 1) = 0x01                                    -- version 1
  AND SUBSTRING(c.MarketStore, x.P + 16, 1) BETWEEN 0x01 AND 0x07              -- listed
GO

Rows with a price of 0 or below never get saved as listed, so Currency 1-7 is enough.

All listings, newest first (page 1, 20 per page):

SELECT Seller, [Row], ItemData, Section, Number, [Level], Excellent, Currency, Price, ListedAtUtc
FROM dbo.WEB_MarketListings
ORDER BY ListedAtUtc DESC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;

Filter (zen only, +9 or higher, excellent, cheapest first):

SELECT Seller, ItemData, [Level], Price
FROM dbo.WEB_MarketListings
WHERE Currency = 1 AND [Level] >= 9 AND Excellent <> 0
ORDER BY Price ASC;

One item type (section and number as in Item.xml):

SELECT Seller, ItemData, [Level], Currency, Price
FROM dbo.WEB_MarketListings
WHERE Section = 0 AND Number = 19
ORDER BY Currency, Price;

Character store page:

SELECT [Row], ItemData, Currency, Price, ListedAtUtc
FROM dbo.WEB_MarketListings
WHERE Seller = @name
ORDER BY [Row];

Listings count per currency (stats box):

SELECT Currency, COUNT(*) AS Listings
FROM dbo.WEB_MarketListings
GROUP BY Currency;

The view reads every character's store each time. That is fine for a few thousand characters. For a big server, cache the page for 30-60 seconds.

Show the wallet and unlisted rows only to the logged-in owner (Character.AccountID = the logged-in account). The view leaves both out.

Step 3 - Sold listings

dbo.MarketSaleLog has one row per sale:

Column	Type	Meaning
Id	bigint, identity	Grows by 1 per row
Seller / Buyer	varchar(10)	Character names
ItemData	varbinary(16)	The item sold, 16-byte DB format
Currency	tinyint	1-7, see currency codes
Price	bigint	What the buyer paid
Net	bigint	What the seller got (price minus commission)
Status	tinyint	1 sold. 0 coin sale in progress, 2 refunded, 3 stuck coin sale (GM). Only show 1
SoldAt	datetime	Time of sale, SQL Server's local clock

Add this index once for time-based pages (safe, the game does not care):

CREATE NONCLUSTERED INDEX IX_MarketSaleLog_SoldAt ON dbo.MarketSaleLog (SoldAt DESC) INCLUDE (Status, Seller, Buyer, Currency, Price);

Recent sales feed (last 20):

SELECT TOP 20 Id, SoldAt, Seller, Buyer, ItemData, Currency, Price
FROM dbo.MarketSaleLog
WHERE Status = 1
ORDER BY Id DESC;

A character's sales (the in-game Sold tab):

SELECT TOP 50 SoldAt, Buyer, ItemData, Currency, Price, Net
FROM dbo.MarketSaleLog
WHERE Seller = @name AND Status = 1
ORDER BY Id DESC;

A character's purchases:

SELECT TOP 50 SoldAt, Seller, ItemData, Currency, Price
FROM dbo.MarketSaleLog
WHERE Buyer = @name AND Status = 1
ORDER BY Id DESC;

Price history of one item type, last 30 days (zen):

SELECT CAST(SoldAt AS date) AS [Day],
       COUNT(*)   AS Sales,
       MIN(Price) AS Lowest,
       AVG(Price) AS Average
FROM dbo.MarketSaleLog
WHERE Status = 1 AND Currency = 1
  AND SoldAt >= DATEADD(day, -30, GETDATE())
  AND CAST(SUBSTRING(ItemData, 10, 1) AS int) / 16 = @section
  AND CAST(SUBSTRING(ItemData, 1, 1) AS int) + (CAST(SUBSTRING(ItemData, 8, 1) AS int) & 128) * 2 = @number
GROUP BY CAST(SoldAt AS date)
ORDER BY [Day];

SUBSTRING is 1-based, so item byte 9 is SUBSTRING(ItemData, 10, 1).

All-time sales archive

MarketSaleLog is a rolling window: each seller keeps only the last SaleLogKeep sold rows (default 50, set in Market.xml). Older ones are deleted. Short feeds are fine; all-time totals and long price history need their own table.

Use	Query MarketSaleLog directly?
Recent sales, a character's last sales, price history of busy items this week	Yes
All-time top sellers, total trade volume, long price history	No - use the archive

Install (once)

CREATE TABLE dbo.WEB_MarketSales
(
    Id       bigint        NOT NULL PRIMARY KEY,   -- same Id as MarketSaleLog
    Seller   varchar(10)   NOT NULL,
    Buyer    varchar(10)   NOT NULL,
    ItemData varbinary(16) NOT NULL,
    Currency tinyint       NOT NULL,
    Price    bigint        NOT NULL,
    Net      bigint        NOT NULL,
    SoldAt   datetime      NOT NULL
)
CREATE NONCLUSTERED INDEX IX_WEB_MarketSales_Seller ON dbo.WEB_MarketSales (Seller, Currency);
CREATE NONCLUSTERED INDEX IX_WEB_MarketSales_SoldAt ON dbo.WEB_MarketSales (SoldAt DESC);
GO
CREATE PROCEDURE dbo.WEB_MarketSalesUpdate
AS
BEGIN
    SET NOCOUNT ON;

    -- Copies every finished sale not copied yet. A coin sale can turn from Status 0 to 1
    -- after newer rows were written, so this checks every row instead of "Id > last Id".
    -- MarketSaleLog stays small (max SaleLogKeep rows per seller), so this is cheap.
    INSERT INTO dbo.WEB_MarketSales (Id, Seller, Buyer, ItemData, Currency, Price, Net, SoldAt)
    SELECT s.Id, s.Seller, s.Buyer, s.ItemData, s.Currency, s.Price, s.Net, s.SoldAt
    FROM dbo.MarketSaleLog s
    WHERE s.Status = 1
      AND NOT EXISTS (SELECT 1 FROM dbo.WEB_MarketSales a WHERE a.Id = s.Id);
END
GO

Run it once by hand to copy what is there now:

EXEC dbo.WEB_MarketSalesUpdate;

Keep it updated

Run EXEC dbo.WEB_MarketSalesUpdate every 1-5 minutes. Running it twice copies nothing twice.

Way	How
SQL Server Agent (not in Express)	New job, one step EXEC dbo.WEB_MarketSalesUpdate, schedule every 1 minute
Windows Task Scheduler	Every 1 minute: sqlcmd -S . -E -d MuOnline -Q "EXEC dbo.WEB_MarketSalesUpdate"
Website cron	Your site's scheduler runs the EXEC

A sale is lost from the archive only if the seller makes SaleLogKeep newer sales before the next run. Every 1-5 minutes is safe.

Archive queries

Top sellers by zen earned:

SELECT TOP 100 a.Seller, COUNT(*) AS Sales, SUM(a.Net) AS ZenEarned
FROM dbo.WEB_MarketSales a
JOIN dbo.Character c ON c.Name = a.Seller          -- hides deleted characters
WHERE a.Currency = 1
GROUP BY a.Seller
ORDER BY ZenEarned DESC;

Most sales (any currency): GROUP BY Seller ORDER BY COUNT(*) DESC.

Trade volume per day:

SELECT CAST(SoldAt AS date) AS [Day], Currency, COUNT(*) AS Sales, SUM(Price) AS Volume
FROM dbo.WEB_MarketSales
WHERE SoldAt >= DATEADD(day, -30, GETDATE())
GROUP BY CAST(SoldAt AS date), Currency
ORDER BY [Day], Currency;

Price history: the price history query works on WEB_MarketSales too. Drop the Status = 1 line.

Season reset: TRUNCATE TABLE dbo.WEB_MarketSales.

Important Behavior

• Open / closed is not stored. A listed row on the website may belong to a closed store. Do not show "buy" buttons: buying only happens in game.

• Search is per GameServer in game. The website shows every character's store from the database, so it can list items a player on another server cannot reach with the search window.

• Rows above MaxRows. If you lowered MaxRows in Market.xml, rows above it keep their price in the database but are not sold in game. Add AND [Row] < <MaxRows> to listing queries.

• Rule changes. If an item stops being allowed (ItemMove, TradeItemBlock*, binding), the row becomes unlisted the next time the owner opens the store. Until then it still shows as listed.

• Coin sales (Currency 5-7) appear in MarketSaleLog at Status = 0 first and turn to 1 when the item is delivered. Never count 0, 2 or 3.

• Character rename. Old sale rows keep the old name.

• Time. ListedAtUtc is UTC. SoldAt is the SQL Server machine's local time.

Common Issues

View returns nothing. No store has a priced row yet, or MarketStore does not exist: apply DataServer/SQL/Migrations/0002_market.sql.

Prices look huge or random. The bytes were read big-endian. Price, wallet and ListedAt are little-endian. Only the item serial (item bytes 3-6) is big-endian.

PHP gets a short or broken string. Read MarketStore and ItemData with PDO::SQLSRV_ENCODING_BINARY.

Top sellers lose sales over time. You are counting MarketSaleLog directly. Use the archive table.

Do not use PShopItemValue. The Market drops that vanilla table. Prices live in MarketStore.

Related pages

• Market - settings, currencies, commission, SaleLogKeep

• Website: Inscription Tooltips - extra tooltip lines for inscribed items, keyed by the item serial