MySQL Database Help

[SieRobin]

Ok so I'm creating a browser based RPG, and I'm making an inventory, obviously for your weapons and armor. I have everything set up correctly, just one problem.. when it goes to set the added effects into the database, if you have more than one weapon or armor, it puts both weapon or armor effects into the database. As I don't think I'm stupid, I pretty much know the reason why, just need an answer on how to do it differently is the problem I suppose. I'm going to provide the code for your inventory below, don't mind how it's scrambled around, I've been testing it alot, and just randomly guessing on how to fix it, but I can't so, I came here.

[code]<?php
session_start();
include 'connect.php';  
?>
<title>Feudal Age - Inventory</title>
<link rel="stylesheet" href="style.css" type="text/css">
<?php
include "navigate.php";
if (isset($_SESSION['player']))
  {
    $player=$_SESSION['player'];
    $userstats="SELECT * from users where playername='$player'";
    $userstats2=mysql_query($userstats) or die("Could not retrieve the users stats.");
    $userstats3=mysql_fetch_array($userstats2);
    $inventory="SELECT * from inventory where UID='$userstats3[ID]' order by IID";
    $inventory2=mysql_query($inventory) or die("Could not get inventory.");
    
    print "<table class='table'><tr class='headline'><td colspan='4'><center>Inventory</center></td></tr>
    <tr><td class='mainrowb' width='40px'>Type</td><td class='mainrowb'>Item Name</td><td class='mainrowb' width='100px'>Sell</td><td class='mainrowb' width='100px'>Status</td></tr>";
    while ($inventory3=mysql_fetch_array($inventory2)) {
    $uinventory="SELECT * from market where ID='$inventory3[IID]'";
    $uinventory2=mysql_query($uinventory) or die("Could not get inventory.");
    $uinventory3=mysql_fetch_array($uinventory2);
    print "<tr class='mainrow'>";
    if ($uinventory3['type']=="weapon") {
    print "<td><font color='red' face='webdings' size='2'>•</font></td>";
    }
    elseif ($uinventory3['type']=="shield") {
    print "<td><font color='white' face='webdings' size='2'>d</font></td>";
    }
    elseif ($uinventory3['type']=="armor") {
    print "<td><font color='green' face='webdings' size='2'>€</font></td>";
    }
    $sellp=$uinventory3['cost']*.6;
    $sell=$_GET['sell'];
    $item=$_GET['item'];
    print "<td>$uinventory3[name]</td>";
    print "<td><a href='inventory.php?sell=$sellp&item=$inventory3[IID]'>$sellp</a> Gold</td>";
    if (isset($sell)) {
    mysql_query("UPDATE users set gold=gold+'$sell' where ID='$userstats3[ID]'");
    mysql_query("DELETE from inventory where UID='$userstats3[ID]' AND IID='$item'");
    print "<script type='text/javascript'>
    onload = function () {location.reload('inventory.php')}
    </script>";
    }
    if ($inventory3['status']==0) {
    print "<td><a href='inventory.php?equip=$inventory3[IID]&type=$uinventory3[type]&user=$userstats3[ID]'>Equip</a> | Unequip</td></tr>";
    } else {
    print "<td>Equip | <a href='inventory.php?unequip=$inventory3[IID]&type=$uinventory3[type]&user=$userstats3[ID]'>Unequip</a></td></tr>";
    }
    $equip=$_GET['equip'];
    $unequip=$_GET['unequip'];
    $user=$_GET['user'];
    $type=$_GET['type'];
    if (isset($equip)) {
    if ($userstats3['iwstatus']==1&&$type=="weapon") {
    print "<table class='table'><tr class='headline'><td colspan='4'><center>Inventory</center></td></tr>
    <tr class='mainrow'><td><center>You can not equip more than one weapon, please return to your <a href='inventory.php'>inventory</a> to unequip your current weapon.</td></tr></table>";
    exit;
    }
    if ($userstats3['iastatus']==1&&$type=="armor") {
    print "<table class='table'><tr class='headline'><td colspan='4'><center>Inventory</center></td></tr>
    <tr class='mainrow'><td><center>You can not equip more than one armor, please return to your <a href='inventory.php'>inventory</a> to unequip your current armor.</td></tr></table>";
    exit;
    }
    if ($userstats3['isstatus']==1&&$type=="shield") {
    print "<table class='table'><tr class='headline'><td colspan='4'><center>Inventory</center></td></tr>
    <tr class='mainrow'><td><center>You can not equip more than one shield, please return to your <a href='inventory.php'>inventory</a> to unequip your current shield.</td></tr></table>";
    exit;
    } else {
    mysql_query("UPDATE inventory set status=1 where UID='$userstats3[ID]' AND IID='$equip'");
    mysql_query("UPDATE users set istr=istr+'$uinventory3[dmg]', iarm=iarm+'$uinventory3[arm]', iagil=iagil+'$uninventory3[aagil]', idex=idex+'$uninventory3[adex]' where ID='$userstats3[ID]'");
    if ($type=="weapon") {
    mysql_query("UPDATE users set iwstatus=1 where ID='$userstats3[ID]'"); }
    elseif ($type=="armor") {
    mysql_query("UPDATE users set iastatus=1 where ID='$userstats3[ID]'"); }
    elseif ($type=="shield") {
    mysql_query("UPDATE users set isstatus=1 where ID='$userstats3[ID]'"); }
    print "<script type='text/javascript'>
    onload = function () {location.reload('inventory.php')}
    </script>";
     }
    }
    if (isset($unequip)) {
    mysql_query("UPDATE inventory set status=0 where UID='$userstats3[ID]' AND IID='$unequip'");
    if ($type=="weapon") {
    mysql_query("UPDATE users set iwstatus=0, istr=istr-'$uinventory3[dmg]', iarm=iarm-'$uinventory3[arm]', iagil=iagil-'$uninventory3[aagil]', idex=idex-'$uninventory3[adex]' where ID='$userstats3[ID]'");
    }
    elseif ($type=="armor") {
    mysql_query("UPDATE users set iastatus=0, istr=istr-'$uinventory3[dmg]', iarm=iarm-'$uinventory3[arm]', iagil=iagil-'$uninventory3[aagil]', idex=idex-'$uninventory3[adex]' where ID='$userstats3[ID]'");
    }
    elseif ($type=="shield") {
    mysql_query("UPDATE users set isstatus=0, istr=istr-'$uinventory3[dmg]', iarm=iarm-'$uinventory3[arm]', iagil=iagil-'$uninventory3[aagil]', idex=idex-'$uninventory3[adex]' where ID='$userstats3[ID]'");
    }
    print "<script type='text/javascript'>
    onload = function () {location.reload('inventory.php')}
    </script>";
    }
   }
   print "</table>";
  }
?>[/code]

Thank you for the help in advanced.. if I threw you off anymore, and you're not sure what I'm talking about, please ask.

[AndyB]

You might not have much luck asking people to wade through that script to find what might be happening that isn't what you want.

Why not point to some specific lines in the script so we can learn just where your problem is.

[.josh]

well, i could be wrong, seeing as how(as you pointed out) your code is a complete mess, but it seems as though you have your inventory update queries inside your initial while statement

[SieRobin]

[code]} else {
    mysql_query("UPDATE inventory set status=1 where UID='$userstats3[ID]' AND IID='$equip'");
    mysql_query("UPDATE users set istr=istr+'$uinventory3[dmg]', iarm=iarm+'$uinventory3[arm]', iagil=iagil+'$uninventory3[aagil]', idex=idex+'$uninventory3[adex]' where ID='$userstats3[ID]'");
    if ($type=="weapon") {
    mysql_query("UPDATE users set iwstatus=1 where ID='$userstats3[ID]'"); }
    elseif ($type=="armor") {
    mysql_query("UPDATE users set iastatus=1 where ID='$userstats3[ID]'"); }
    elseif ($type=="shield") {
    mysql_query("UPDATE users set isstatus=1 where ID='$userstats3[ID]'"); }
    print "<script type='text/javascript'>
    onload = function () {location.reload('inventory.php')}
    </script>";
     }
    }
    if (isset($unequip)) {
    mysql_query("UPDATE inventory set status=0 where UID='$userstats3[ID]' AND IID='$unequip'");
    if ($type=="weapon") {
    mysql_query("UPDATE users set iwstatus=0, istr=istr-'$uinventory3[dmg]', iarm=iarm-'$uinventory3[arm]', iagil=iagil-'$uninventory3[aagil]', idex=idex-'$uninventory3[adex]' where ID='$userstats3[ID]'");
    }
    elseif ($type=="armor") {
    mysql_query("UPDATE users set iastatus=0, istr=istr-'$uinventory3[dmg]', iarm=iarm-'$uinventory3[arm]', iagil=iagil-'$uninventory3[aagil]', idex=idex-'$uninventory3[adex]' where ID='$userstats3[ID]'");
    }
    elseif ($type=="shield") {
    mysql_query("UPDATE users set isstatus=0, istr=istr-'$uinventory3[dmg]', iarm=iarm-'$uinventory3[arm]', iagil=iagil-'$uninventory3[aagil]', idex=idex-'$uninventory3[adex]' where ID='$userstats3[ID]'");
    }[/code]

Yes I know sorry for the mess, but above is pretty much the queries to put the item stats into the database. Now the variables involved like $uninventory3 are actually this..

[code]    $uinventory="SELECT * from market where ID='$inventory3[IID]'";
    $uinventory2=mysql_query($uinventory) or die("Could not get inventory.");
    $uinventory3=mysql_fetch_array($uinventory2);[/code]

And $inventory3 is actually set through the while loop so it would print out what's in the users inventory, but this is how it looks.

[code]$inventory="SELECT * from inventory where UID='$userstats3[ID]' order by IID";
    $inventory2=mysql_query($inventory) or die("Could not get inventory.");
    
    print "<table class='table'><tr class='headline'><td colspan='4'><center>Inventory</center></td></tr>
    <tr><td class='mainrowb' width='40px'>Type</td><td class='mainrowb'>Item Name</td><td class='mainrowb' width='100px'>Sell</td><td class='mainrowb' width='100px'>Status</td></tr>";
    while ($inventory3=mysql_fetch_array($inventory2)) {[/code]

Correct it is a mess, but I usually clean it up when it works correctly lol.

I actually figured it out :] Crayon pointed me in the right direction with the while loop statement. I found out that yes I had wrapped the while loop around my queries as well, which was a bad mistake hehe.

Thank you again :D

[SieRobin]

I actually ran into another problem with this. As though it doesn't put ALL of the weapon/armor's effects into the database, but it will only put the first weapon/armor it finds into the database for the effects. So say it's an armor piece, and it's the last piece in line, then basically it will add the first weapon or armor in line..

How could I make it so when it adds the effects, it actually pulls the right information? I know it has to do something with the query, just I can't figure a way around it.