how to combine same inventory items in a text based role playing game to one row

Question

I run a text based games and in the inventory i get rows like this:

Mini-Rocket Launcher x10000
Mini-Rocket Launcher x10000
Mini-Rocket Launcher x10000
Mini-Rocket Launcher x10000
Mini-Rocket Launcher x10000
Mini-Rocket Launcher x10000

I am wondering how to make it so the items are combined to show a row like this

Mini-Rocket Launcher x10000000

just one single row per item. here is the page code:

    <?php

    session_start();
    require "global_func.php";
    if ($_SESSION['loggedin'] == 0)
    {
        header("Location: login.php");
        exit;
    }
    $userid = $_SESSION['userid'];
    require "header.php";
    $h = new headers;
    $h->startheaders();
    include "mysql.php";
    global $c;
    $is =
            mysql_query(
                    "SELECT u.*,us.* FROM users u LEFT JOIN userstats us ON                 u.userid=us.userid WHERE u.userid=$userid",
                    $c) or die(mysql_error());
    $ir = mysql_fetch_array($is);
    check_level();
    $fm = money_formatter($ir['money']);
    $cm = money_formatter($ir['crystals'], '');
    $lv = date('F j, Y, g:i a', $ir['laston']);
    $h->userdata($ir, $lv, $fm, $cm);
    $h->menuarea();
    function item_give($user, $itemid, $qty, $notid=0)
    {
      if($notid > 0)
      {
        $q=mysql_query("SELECT * FROM inventory WHERE inv_userid={$user} and                 inv_itemid={$itemid} AND inv_id != {$notid}");
              }
              else
              {
        $q=mysql_query("SELECT * FROM inventory WHERE inv_userid={$user} and         inv_itemid={$itemid}");
      }
      if(mysql_num_rows($q) > 0)
      {
        $r=mysql_fetch_array($q);
        mysql_query("UPDATE inventory SET inv_qty=inv_qty+{$qty} WHERE inv_id=        {$r['inv_id']}");
      }
      else
      {
        mysql_query("INSERT INTO inventory (inv_itemid, inv_userid, inv_qty) VALUES         ({$itemid}, {$user}, {$qty})");
      }
    }

    $inv =
            mysql_query(
                    "SELECT iv.*,i.*,it.* FROM inventory iv LEFT JOIN items i ON         iv.inv_itemid=i.itmid LEFT JOIN itemtypes it ON i.itmtype=it.itmtypeid WHERE iv.inv_userid=        {$userid} ORDER BY i.itmtype ASC, i.itmname ASC",
                    $c);
    if (mysql_num_rows($inv) == 0)
    {
        print "<b>You have no items!</b>";
    }
    else
    {
        print
                "<font color=white><b>Your items are listed below.</b><br />
    <table width=100%><tr style='background-color:gray;'><font color=white>        <th>Item</th><th><font color=white>Sell Value</th><font color=white><th>Total Sell         Value</th><font color=white><th>Links</th></tr></font>";
        $lt = "";
        while ($i = mysql_fetch_array($inv))
        {
            if ($lt != $i['itmtypename'])
            {
                $lt = $i['itmtypename'];
                print
                        "\n<tr style='background: gray;'><th colspan=4>{$lt}</th>        </tr>";
            }
            print "<tr><td><font color=white>{$i['itmname']}</font>";
            if ($i['inv_qty'] > 1)
            {
                print "&nbsp;<font color=white>x{$i['inv_qty']}</font>";
            }
            print "</td><td><font color=white>\${$i['itmsellprice']}</td><td></font>";
            print $i['itmsellprice'] * $i['inv_qty'];
            print
                            "</td><font color=white><td>[<a href='iteminfo.php?ID=        {$i['itmid']}'><font color=white>Info</a>] [<a href='itemsend.php?ID={$i['inv_id']}'><font         color=white>Send</a>] [<a href='itemsell.php?ID={$i['inv_id']}'><font color=white>Sell</a>]                 [<a href='imadd.php?ID={$i['inv_id']}'><font color=white>Add To Market</a>]</font>";
            if ($i['itmtypename'] == 'Food' || $i['itmtypename'] == 'Medical')
    {
                print " [<a href='itemuse.php?ID={$i['inv_id']}'><font         color=white>Use</a>]</font>";
            }
            if ($i['itmname'] == 'Nuclear Bomb')
            {
                print " [<a href='nuclearbomb.php'><font color=white>Use</a>]</font>";
            }
            print "</td></tr>";
        }
        print "</table>";
    }
    $h->endpage();

Can any one help?

score 1 · Answer 1 · answered Mar 10 '13 at 03:43

1

You could use a GROUP BY in your SQL query.

Something along the lines of:

SELECT iv.*,i.*,it.*,SUM(iv.inv_qty)
FROM inventory iv
    LEFT JOIN items i
        ON iv.inv_itemid=i.itmid
    LEFT JOIN itemtypes it
        ON i.itmtype=it.itmtypeid
WHERE iv.inv_userid={$userid}
GROUP BY i.itmname
ORDER BY i.itmtype ASC, i.itmname ASC

Note the SUM(iv.inv_qty) and GROUP BY i.itmname parts. I don't know your exact table schema(s), but this is the best I could come up with from the information given in your OP.

Hope that helps

answered Mar 10 '13 at 03:43

Will Warren

1,294
15
33

where would I put this? – user2152928 Mar 11 '13 at 19:33
It would replace this line: `$inv = mysql_query("SELECT iv.*,i.*,it.* FROM inventory iv LEFT JOIN items i ON iv.inv_itemid=i.itmid LEFT JOIN itemtypes it ON i.itmtype=it.itmtypeid WHERE iv.inv_userid={$userid} ORDER BY i.itmtype ASC, i.itmname ASC", $c);` – Will Warren Mar 12 '13 at 00:13

how to combine same inventory items in a text based role playing game to one row

1 Answers1