> For the complete documentation index, see [llms.txt](https://axiomemu.gitbook.io/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://axiomemu.gitbook.io/docs/guides/website-market.md).

# 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](/docs/data/plugins/market.md) 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](#all-time-sales-archive)) |
| Top sellers           | Sales count and earnings per seller                                                       | [Archive](#all-time-sales-archive)                              |

{% hint style="info" %}
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.
{% endhint %}

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

{% tabs %}
{% tab title="Node.js / TypeScript" %}

```ts
// 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.
{% endtab %}

{% tab title="PHP" %}

```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.
{% endtab %}
{% endtabs %}

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

```sql
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):

```sql
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):

```sql
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`):

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

**Character store page**:

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

**Listings count per currency** (stats box):

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

{% hint style="info" %}
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.
{% endhint %}

{% hint style="warning" %}
Show the **wallet** and **unlisted rows** only to the logged-in owner (`Character.AccountID` = the logged-in account). The view leaves both out.
{% endhint %}

## 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](#step-1-read-the-store-blob)                                         |
| `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):

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

**Recent sales feed** (last 20):

```sql
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):

```sql
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**:

```sql
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):

```sql
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)

```sql
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:

```sql
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`                                            |

{% hint style="warning" %}
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.
{% endhint %}

### Archive queries

**Top sellers by zen earned**:

```sql
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**:

```sql
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](#step-3-sold-listings) 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

{% hint style="warning" %}
**View returns nothing.** No store has a priced row yet, or `MarketStore` does not exist: apply `DataServer/SQL/Migrations/0002_market.sql`.
{% endhint %}

{% hint style="warning" %}
**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.
{% endhint %}

{% hint style="warning" %}
**PHP gets a short or broken string.** Read `MarketStore` and `ItemData` with `PDO::SQLSRV_ENCODING_BINARY`.
{% endhint %}

{% hint style="warning" %}
**Top sellers lose sales over time.** You are counting `MarketSaleLog` directly. Use the archive table.
{% endhint %}

{% hint style="warning" %}
**Do not use `PShopItemValue`.** The Market drops that vanilla table. Prices live in `MarketStore`.
{% endhint %}

## Related pages

* [Market](/docs/data/plugins/market.md) - settings, currencies, commission, `SaleLogKeep`
* [Website: Inscription Tooltips](/docs/guides/website-inscription-tooltips.md) - extra tooltip lines for inscribed items, keyed by the item serial