Market Logs

[Samurai Legend]

I have Item Market, Coin Market, Steps Market and Gem Market. I need the best possible advice on how to log when users add, remove and sell their stuff and log it correctly. I know how to code but I need the best possible concept. I want the logs to be in one table. Thank you.

[Sim]

Table named sell_log

Following fields

sellID auto increment, primary key int(7)

sellUserID int(7)

sellFeature varchar(10) think 10 enough

sellType varchar(7)

sellBuyerID int(7) not null default 0

I guess these 2 fields to.

sellPrice int(9)

sellDate int (12)

Then add insert query after every add, remove, sell, ect.

sellType would be 'add' 'removed' 'sold', ect

sellFeature would be 'item' 'coin' 'steps' 'gem', ect

 

 

 

[Samurai Legend]

The markets are much complicated, e.g. users can add 1000 coins or items etc, users have the options to buy x coins, items etc from the 1000 coins. 

Now I want all the logs to link

Edited May 17, 2021 by Samurai Legend

[Sim]

That table structure should work just fine. Maybe add one more field?

sellPriceType varchar(6)

Be coins or money

And Maybe

sellTypeID int(11)

TypeID would reference item IDs or any other IDs needed.

All other fields I mentioned stores data on what/where/who/when.

 

You need to relook at the sellType and sellFeature I already mentioned. 

 

I know for a fact with those two additional tables, it will cover all. I would bet on it.

[Samurai Legend]

Here I have three tables. Adding x to the market, removing x to the market and buying x at the market

[SQL]CREATE TABLE `marketaddlogs` (
  `malID` int(11) NOT NULL AUTO_INCREMENT,
  `malUID` int(11) NOT NULL,
  `malCAT` enum('item','coins','steps','gems') NOT NULL DEFAULT 'item',
  `malUSER` int(11) NOT NULL,
  `malIP` varchar(255) NOT NULL DEFAULT '',
  `malITEM` int(11) NOT NULL,
  `malQTY` int(11) NOT NULL,
  `malPRICE` int(11) NOT NULL,
  `malCURRENCY` enum('money','coins','steps','gems') NOT NULL DEFAULT 'money',
  `malTIME` int(11) NOT NULL,
  PRIMARY KEY (`malID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `marketremovelogs` (
  `mlrID` int(11) NOT NULL AUTO_INCREMENT,
  `mlrUID` int(11) NOT NULL,
  `mlrCAT` enum('item','coins','steps','gems') NOT NULL DEFAULT 'item',
  `mlrUSER` int(11) NOT NULL,
  `mlrIP` varchar(255) NOT NULL DEFAULT '',
  `mlrITEM` int(11) NOT NULL,
  `mlrQTY` int(11) NOT NULL,
  `mlrTIME` int(11) NOT NULL,
  PRIMARY KEY (`mlrID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `marketbuylogs` (
  `mblID` int(11) NOT NULL AUTO_INCREMENT,
  `mblUID` int(11) NOT NULL,
  `mblCAT` enum('item','coins','steps','gems') NOT NULL DEFAULT 'item',
  `mblBUYER` int(11) NOT NULL,
  `mblSELLER` int(11) NOT NULL,
  `mblIPBUYER` varchar(255) NOT NULL,
  `mblIPSELLER` varchar(255) NOT NULL DEFAULT '',
  `mblITEM` int(11) NOT NULL,
  `mblQTY` int(11) NOT NULL,
  `mblPRICE` int(11) NOT NULL,
  `mblCURRENCY` enum('money','coins','steps','gems') NOT NULL DEFAULT 'money',
  `mblTIME` int(11) NOT NULL,
  PRIMARY KEY (`mblID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
[/SQL]

Adding in the Market - 

[php]

            $m = $db->insert_id();
            $db->query(
                "INSERT INTO `marketaddlogs`
                VALUES (NULL, $m, 'item', $userid, '{$ir['lastip']}', {$r['inv_itemid']}, {$_POST['QTY']}, {$_POST['price']}, '{$_POST['currency']}', " . time() . ")");

[/php]

Buying in the Market - 

[php]

            $q2 = $db->query("
                         SELECT * 
                         FROM `marketaddlogs` 
                         WHERE `malID` = {$_GET['ID']}");
            $info = $db->fetch_row($q2);
            $q3 = $db->query("
                         SELECT `lastip`
                         FROM `users` 
                         WHERE `userid` = {$r['imADDER']}");
            $ip = $db->fetch_row($q3);
            $db->query(
                "INSERT INTO `marketbuylogs`
                VALUES (NULL, {$info['malID']}, 'item', $userid, {$r['imADDER']}, '{$ir['lastip']}', '{$ip['lastip']}', {$r['imITEM']}, {$r['imQTY']}, {$final_price}, 'money', " . time() . ")");
[/php]

Removing in the Market - 

[php]

                    SELECT * 
                    FROM `marketaddlogs` 
                    WHERE `malUID` = {$_GET['ID']}");
    $info = $db->fetch_row($q2);
    $db->query(
            "INSERT INTO `marketremovelogs`
            VALUES 
            (NULL, {$info['malID']}, 'item', $userid, '{$ir['lastip']}', {$r['imITEM']}, {$info['malQTY']}, " . time() . ")");

[/php]

 

Staff Logs , I need to join the three tables together and get good amount of information e.g. what is being sold, removed, added. If there is an IP buying etc.

Now I have an error join two tables. Please someone help

QUERY ERROR: Unknown column 'mrl.mrlUID' in 'on clause'
Query was SELECT * FROM `marketaddlogs` AS `mal` JOIN `marketremovelogs` AS `mrl` ON `mal`.`malUID` = `mrl`.`mrlUID` WHERE `mal`.`malCAT` = 'item' ORDER BY `malTIME` DESC LIMIT 0, 100

[php]

function view_item_market_logs()
{
    global $db, $ir, $c, $h, $userid;
    echo "<h3>Item Market Logs</h3><hr />";
    if (!isset($_GET['st']))
    {
        $_GET['st'] = 0;
    }
    $st = abs(intval($_GET['st']));
    $app = 100;
    $q = $db->query("SELECT COUNT(`malID`)
                     FROM `marketaddlogs`");
    $itemmarket = $db->fetch_single($q);
    $db->free_result($q);
    if ($itemmarket == 0) {
        ?>There hasn't been any processes for the item market.<?php
        return;
    }
    $pages = ceil($itemmarket / $app);
    echo 'Pages:&nbsp;';
    for ($i = 1; $i <= $pages; $i++)
    {
        $s = ($i - 1) * $app;
        echo ($s == $st) ? '<b>' . $i . '</b>&nbsp;'
                : '<a href="staff_logs.php?action=itemmarketlogs&st=' . $s . '">'
                        . $i . '</a>&nbsp;';
        echo ($i % 25 == 0) ? '<br />' : '';
    }
    echo "
    <br />
    <table width='100%' cellspacing='1' cellpadding='1' class='table'>
            <tr>
                <th>Transcript</th>
            </tr>
       ";
    $q =
            $db->query(
                    "SELECT * 
                     FROM `marketaddlogs` AS `mal`
                     JOIN `marketremovelogs` AS `mrl`
                     ON `mal`.`malUID` = `mrl`.`mrlUID`
                     WHERE `mal`.`malCAT` = 'item' 
                     ORDER BY `malTIME` DESC
                     LIMIT $st, $app");
    while ($r = $db->fetch_row($q)) {

        echo "
        <tr>
            <td>" . date('F j Y, g:i:s a', $r['malTIME']) . ": " . username($r['malUSER']) . " added {$r['malITEM']} x{$r['malQTY']} to the Item Market for the price of {$r['malPRICE']} ({$r['malCURRENCY']}).
        </tr>
           ";
    }
    $db->free_result($q);
    echo "
    </table>
    <br />
    Pages:&nbsp;
       ";
    for ($i = 1; $i <= $pages; $i++)
    {
        $s = ($i - 1) * $app;
        echo ($s == $st) ? '<b>' . $i . '</b>&nbsp;'
                : '<a href="staff_logs.php?action=itemmarketlogs&st=' . $s . '">'
                        . $i . '</a>&nbsp;';
        echo ($i % 25 == 0) ? '<br />' : '';
    }
    $mypage = floor($_GET['st'] / 100) + 1;
    stafflog_add("Looked at the Item Market Logs (Page $mypage)");
}

[/php]

Got the error fixed. Now it shows ddd

May 17 2021, 4:24:23 am: Shades http://samuraiconflict.test/images/staff.gif added 1 x2 to the Item Market for the price of 2 (money).
May 17 2021, 4:26:55 am: N/A removed x from the Item Market

 

How do I not make it show the item removed bit when there isn't no data?

If anyone can help me improve to show it in a better format it will be a big help!

Why is it when I buy something again from the same listing it comes up as a new row?

[AdamHull]

Your query is malformed 

 "SELECT * 
                     FROM `marketaddlogs` AS `mal`
                     JOIN `marketremovelogs` AS `mrl`
                     ON `mal`.`UID` = `mrl`.`UID`
                     WHERE `mal`.`CAT` = 'item' 
                     ORDER BY `mal.TIME` DESC
                     LIMIT $st, $app"

You on have to use mrl.name

You do not need to put mrl infront after the. 

[Sim]

12 hours ago, Samurai Legend said:

8 hours ago, Samurai Legend said:

Here I have three tables. 

. I want the logs to be in one table. Thank you.

Thanks.