PHP MySQL displaying Problem...

[Stalingrad]

Hi! I'm coding something, but I can't grab the data from my database. I'm wanting to select all of the items from a table where they belong to a certain user. At the same time, I'm trying to grab the image, name, description, etc. of the item that the user has. Like... for example;

I have a user with two different items named "Apple" and "Banana". In the user items table, it stores the primary keyed id, the id of the user, the itemid from the items table. In the "items" table, it has all of the basic information from the item: name, description, images, etc.

 

Here is my code: I'm doing it wrong...:

<?php
session_start();
include("config.php");
if(isset($_SESSION["user"])) {
echo "<div id=ban>My Pet Game</div><div id=nav>$lshownav<br></div><div id=content>";

$query = "SELECT * FROM uitems WHERE userid='$uid' AND location='1'"; 	 
$result = mysql_query($query);
while($row = mysql_fetch_array($result)) {
$itemid = $row['theitemid'];
$uitemid = $row['uitemid'];
$loc  = $row['location'];
}
$itemquery = "SELECT * FROM items"; 	 
$itemresult = mysql_query($itemquery);
while($itemrow = @mysql_fetch_array($itemresult)) {
$theiid = $itemrow['itemid'];
$iname = $itemrow['name'];
$iimage  = $itemrow['image'];
$idesc = $itemrow['description'];
$itype = $itemrow['type'];
$irarity = $itemrow['rarity'];
}
echo "<a href=?act=view&uitemid=$uitemid><img src=items/$iimage></a><br>$iname";
}

if(!isset($_SESSION["user"])) {
echo "<div id=ban>My Pet Game</div><div id=nav>$nlshownav<br></div><div id=content>Please <a href=login.php>Login</a> or <a href=register.php>Register</a> to Continue.</div>";
}
?>

 

Can somebody please help me get the correct code? Thanks in advance.

[Kevin.Arvixe]

Is $uid ever set? If this is all of the code, it does not appear so.

 

Also, can you change one line for me: 

$result = mysql_query($query);

 

to

 

$result = mysql_query($query) or die(mysql_error());

 

And please provide me with the results

[Stalingrad]

Yes, the $uid is set. It is set in my config file.

[Kevin.Arvixe]

And as far as this goes? $result = mysql_query($query) or die(mysql_error());

 

 

Also, what is the expected outcome?

 

What is actually being displayed?

[Stalingrad]

Nothing yet. It's just grabbing the user's item information.

[Kevin.Arvixe]

And this:

 

Also, what is the expected outcome?

 

What is actually being displayed?

 

 

Also:

 

while($itemrow = @mysql_fetch_array($itemresult)) {
    $theiid = $itemrow['itemid'];
    $iname = $itemrow['name'];
    $iimage  = $itemrow['image'];
    $idesc = $itemrow['description'];
    $itype = $itemrow['type'];
    $irarity = $itemrow['rarity'];
}
echo "<a href=?act=view&uitemid=$uitemid><img src=items/$iimage></a><br>$iname";
}

 

Exam this bit...

 

The while statement goes and sets $iimage to $itemrow['image'];

Then resets $iimage to the new $itemrow['image']

Then does it again... then again... until all of the rows are complete.

AFTER $iimage is set to the final row, you do an echo. So this is only echo'ing the final row.

Put your echo inside of the while statement (if you want each row to be echo'd)

[Stalingrad]

Thanks! I'll try that... but, what would the statement be?

[Kevin.Arvixe]

while($itemrow = @mysql_fetch_array($itemresult)) {

    $theiid = $itemrow['itemid'];

    $iname = $itemrow['name'];

    $iimage  = $itemrow['image'];

    $idesc = $itemrow['description'];

    $itype = $itemrow['type'];

    $irarity = $itemrow['rarity'];

echo "<a href=?act=view&uitemid=$uitemid><img src=items/$iimage></a><br>$iname";

}

}

[Stalingrad]

Thanks, I'll try that.

 

[Stalingrad]

Ahh, that's grabbing the items from the items table. I want to grab the items from the user items table (uitems), but have the properties that the items table has for the id of the items in the uitems table.

[Kevin.Arvixe]

$query = "SELECT theitemid, uitemid, location, image FROM uitems LEFT JOIN items ON userid='$uid' AND location='1'";	 
$result = mysql_query($query);
while($row = mysql_fetch_array($result)) {
$itemid = $row['theitemid'];
$uitemid = $row['uitemid'];
$loc  = $row['location'];
echo "<a href=?act=view&uitemid=$uitemid><img src=items/$iimage></a><br>$iname";
}

 

Looks like in your first query, you are doing this. I moved the echo to the top query and changed the query aswell... Im not incredibly strong w/ JOIN statements, and I dont have a MySQL box around to toy with, so you may get a syntax error, but essentially you will want to try out multiple JOIN statements to display the items image w/o having to query the database twice.

[Stalingrad]

Ahh, it's still not working. I have no clue why. o: Thank you VERY much for all of your help. =D

[Kevin.Arvixe]

Keep exploring the join statement though. Really, this is your answer. I wish i had a server infront of me, but I dont.

 

SELECT theitemid, uitemid, location, image

FROM uitems

WHERE userid='$uid' AND location='1'

LEFT JOIN items ON items.itemid = uitems.uitemid

 

 

Try that?