Inventories

Napster

I have a php game site were people can sign up and each person has an invetory. For that I use a while loop. And I have 2 tables. One table has all the inventory info, like the username of the person with the item, the itemid and the name of the item. THe other table has all the item info, the pic url, item name, description etc. Now I use the while loop on the first table but I cant figure out how to get the item info and match it up with the correct item.

dustbuster

Alright, let's assume you have an $item_number variable.
Let's also assume that you have a table structure like this:

inventory_info:
used_item_id
user_name
...

item:
item_id
pic
url
...

To access all the info:

$item_number=2;

$query="SELECT * FROM inventory_info, item WHERE item.item_id='$item_number' AND inventory_info.used_item_id=item.item_id";
$result=mysql_query($query) or die("Invlaid Query: ".mysql_error());

while($row=mysql_fetch_assoc($result)){
foreach ($row as $key => $val){
echo $key.": ".$val."<br>";
}
}

HTH

NOTE:
This should probably have been posted in the database section first though.

Napster

i dont get what u mean, but I will post what i have for my inventory.

el_items ( THE table for item info )
name = item name
description
price
pic ( theu url of it )
itemnb ( item nb )
price

el_inventory
username ( person who owns the item )
name ( name of the item )
itemid

now my script is

<?php

include"jocey.inc.php";
include "header.php";

$username = $_COOKIE['user'];

$connection = mysql_connect($host, $user,$password)
or die ("Couldn't connect to server.");

mysql_select_db($database, $connection)
or die ("Couldn't select database.");                      //20

$result = mysql_query("SELECT * FROM el_inventory WHERE username='$username' ");

?>

<table border=0> 
<?php
while ($row = mysql_fetch_array($result,MYSQL_ASSOC)) { 
echo '<tr><td>'; 
echo $row[name]; 
echo '</td></tr>'; 
} 
?>
</table>

<?php include"footer.php"; ?>

no this only gets all the items for that person who is logged in. For example, the user Fareon has thre items which are as follows Axe, Coif, Chainmail now using the names ( which are in variables, how can I get the right item info ( picture etc ) from the DB and then get it to match to the right item?

dustbuster

If you change your structure as follows you should be able to do this.

el_items ( THE table for item info )
item_id <= NEW
name = item name
description
price
pic ( theu url of it )
itemnb ( item nb ) <= Does this mean itemid or item number? If it means itemid or item number change to be more meaningful
price

el_inventory
username ( person who owns the item )
name ( name of the item )
itemid

<?php 

include"jocey.inc.php"; 
include "header.php"; 

$username = $_COOKIE['user']; 

$connection = mysql_connect($host, $user,$password) 
or die ("Couldn't connect to server."); 

mysql_select_db($database, $connection) 
or die ("Couldn't select database.");                      //20 

//#################################
//NEW CODE
//#################################

$result = mysql_query("SELECT * FROM el_inventory, el_items WHERE el_inventory.username='$username'  AND el_items.item_id= el_inventory.itemid"); 
?> 

<table border=0> 
<?php 
while ($row = mysql_fetch_assoc($result,MYSQL_ASSOC)) {  

echo '<tr><td>';  

//#################################
//NEW CODE
//#################################
foreach ($row as $key=> $val){
// This will simply print out the column name and its value
echo $key.": ".$val."<br>";
}
echo '</td></tr>';  

}  

?> 
</table> 

<?php include"footer.php"; ?>

Now if you don't want to change your database naming convention AND the itemnb column in el_items really is the item id corresponding to itemid in el_inventory...

meaning

el_items ->itemnb = el_inventory->itemid

use this

$result = mysql_query("SELECT * FROM el_inventory, el_items WHERE el_inventory.username='$username'  AND el_items.itemnb= el_inventory.itemid");

HTH

Napster

itemnb was the actual item number and item id was the id of each item, no 2 itemid are the same even if the item name is the same

dustbuster

Then you should use the second query I posted in place of the one in the code I modified. Everytime you do the mysql_fetch call it will put the results (from one row) of both tables.

You should consider using mysql_fetch_assoc instead of mysql_fetch_array as it doesn't include the numerical array indexes and thus saves space.

Napster

Would it work if I went like this

$result = mysql_query("SELECT * FROM el_inventory, el_items WHERE el_inventory.username='$username'  AND el_items.name= el_inventory.name");

Napster

my idea worked now i am trying to get 3 items a row, on the screen. But if I go

while ($row = mysql_fetch_array($result,MYSQL_ASSOC)) {  

echo '<tr><td>';  

echo $row[name];  

echo '</td><td>';  

echo $row[name];  

echo '</td><td>';   

echo $row[name];  

echo '</td></tr>';  

}

It will show the same 3 items per row.

dustbuster

The reason is that you don't understand how the mysql access works.

Take the following code (be aware that I don't have any connection information)

//1 
$query="SELECT * FROM el_inventory, el_items WHERE el_inventory.username='$username'  AND el_items.name= el_inventory.name";

//2 
$result=mysql_query($query);

//3 
while ($row=mysql_fetch_assoc($result){
echo "<tr><td>"
echo $row['name'];
echo "</td></tr>\n";
}

//1 is your query. Nothing new here.

//2 This part goes to the database and executes the query and returns $result. $result is not an array it is a resource identifier.

//3 uses the resource identifier ($result) to get the information you need, one row at a time. This while loop will do this until there are no more row which match the criteria.

This is of course a high level view of what is happening. If you wish to know more about how the whole process works from a technical point of view you can post in the database forum and get some information there.

Back to your orignal problem. The code I posted above should solve it.

Napster

You way did not work, I am sorry. I know that with mysql it will only let you do one item per line. Now I have seen other sites like mine, they have inventories and they have three items per line.

dustbuster

What do you mean by 'it didn't work'?

Did you get an error?
Did your script print out a bunch of garbage?

I just realized that on my previous post I had a typo

while ($row=mysql_fetch_assoc($result){

Should be
while ($row=mysql_fetch_assoc($result)){

Napster

npo, I fixed the script, but for items, they have to be stacked on top of each other, if they are side by side, they are just dupliactes

dustbuster

If you can post you current code and an example of what you want the results to look like we can go from there.

Napster

Here is my current code for my inventory

<?php

include"jocey.inc.php";
include "header.php";

$username = $_COOKIE['user'];

$connection = mysql_connect($host, $user,$password)
or die ("Couldn't connect to server.");

mysql_select_db($database, $connection)
or die ("Couldn't select database.");                      //20

$result = mysql_query("SELECT * FROM el_inventory, el_items WHERE el_inventory.username='$username'  AND el_items.name= el_inventory.name");

?>
<b>Click on an item that you wish to use. Then, a page with information and a list of available actions will be shown.  You
will be able to trade it, give it away and much much more. </b>
<br><br><br>
<table border=0> 
<center>
<?php
while ($row = mysql_fetch_assoc($result)){ 
echo "<tr><td>"; 
echo $row['name']; 
echo "</td><td>";
echo "<a href='packaction.php?name=$row[name]&id=$row[itemid]'><img src='$row[pic]'></img></a>"; 
echo "</td></tr>";

}
?>
</table>
<BR><BR><BR><BR><BR><BR>
<?php include"footer.php"; ?>

Now, lets say v w x, y and z are all items in my inventory. Right now it will display them like this

v
w
x
y
z

Now I want them to be displayed like this

v w x
y z

Or so there is three items a row ( the row on the screen )

if I add more of these codes

echo "<tr><td>"; 
echo $row['name']; 
echo "</td><td>";
echo "<a href='packaction.php?name=$row[name]&id=$row[itemid]'><img src='$row[pic]'></img></a>"; 
echo "</td></tr>";
[code=php]

it will make the page look like this

v  v  v
w w w
x  x  x
y  y  y
z  z  z

Or a duplicate for every code i add.  I dont want that though.

dustbuster

<?php 

include"jocey.inc.php"; 
include "header.php"; 

$username = $_COOKIE['user']; 

$connection = mysql_connect($host, $user,$password) 
or die ("Couldn't connect to server."); 

mysql_select_db($database, $connection) 
or die ("Couldn't select database.");                      //20 

$result = mysql_query("SELECT * FROM el_inventory, el_items WHERE el_inventory.username='$username'  AND el_items.name= el_inventory.name"); 

?> 
<b>Click on an item that you wish to use. Then, a page with information and a list of available actions will be shown.  You 
will be able to trade it, give it away and much much more. </b> 
<br><br><br> 
<table border=0> 
<center> 
<?php

$items_per_row=3;//number of items per row
$item_count=0;

while ($row = mysql_fetch_assoc($result)){  

  if ($item_count<=0){
    echo "<tr>";  

  }
    echo "<td>";  

    echo $row['name'];
    echo "</td>";
    echo "<td>";
    echo "<a href='packaction.php?name=$row[name]&id=$row[itemid]'><img src='$row[pic]'></img></a>";  

    echo "</td>";

  $item_count++;
  if ($item_count >=$items_per_row){//we have all the items per row
    echo "</tr>";
    $item_count=0;

  }
} 
?> 
</table> 
<BR><BR><BR><BR><BR><BR> 
<?php include"footer.php"; ?>

[NOTE]I changed the code [/NOTE]

Napster

Thank you very much, I dont know what i would do without you

dustbuster

The code I posted will only print the last </tr> if you have the correct number of items.

After the while loop is finished add:

if ($item_count !=0){
echo "</tr>";
}

This will ensure that the last </tr> is printed.