While Loop Not Working

[Tenaciousmug]

It's only displaying the first result and not the others. What am I doing wrong?

I'm selecting the items the user occupies.

Then I'm selecting the name and image of that item from the item table.

 

echo "<table cellspacing=\"0\" class=\"news\" align=\"center\">";
echo "<tr>";
$sql = "SELECT * FROM useritems WHERE userid='".$_SESSION['userid']."' LIMIT $offset, $rowsperpage"; //selects all the users items to whoever is logged in
$result = mysqli_query($cxn,$sql) or die(mysqli_erro($cxn));
$imagecount = 0;
while ($row = mysqli_fetch_assoc($result)) //while there are still results
{
	extract($row);

	$quantity = $row['quantity'];
	$itemid = $row['itemid'];

	$sql = "SELECT * FROM items WHERE itemid='".$itemid."'"; //selecting the name and image of the item that is displaying
	$result = mysqli_query($cxn, $sql) or die(mysqli_error($cxn));
	$row = mysqli_fetch_assoc($result);

	$name = $row['name'];
	$image = $row['image'];

	if ($imagecount%5 == 0) //after 5 items have been listed, start a new line
	{
		echo "</tr>";
		echo "<tr>";
	}
}

echo "<td width=\"120px\" align=\"center\">";
echo "<img src=\"http://www.elvonica.com/".$image."\"><br>";
echo $name." (".$quantity.")";
echo "</td>";

$imagecount++; //after one item has been displayed, redo the loop if there are still results
}
echo "</table>";

[Psycho]

Why are you using extract($row) and then defining variables based upon the variable $row?!!!

 

Assuming $row contains an index named "quantity", the extract function will automatically create a variable called $quantity with the value associated with that index. You then redefine $quantity using $row['quantity']. That makes no sense.

 

Second, why are you running a query in your loop? Just do a join in the first query to get all the records you need at one time.

 

As to your particular issue, I suspect there is a problem with one of the two values you are using on the LIMIT clause. But, you don't show how those are defined, so I can't help you there. But, try echoing the query to the page to ensure it has the values you expect.

[wildteen88]

You are overwriting the variables you defined for your first query ($sql, $result and $row) when you go to run your second query:

		$sql = "SELECT * FROM items WHERE itemid='".$itemid."'"; //selecting the name and image of the item that is displaying
	$result = mysqli_query($cxn, $sql) or die(mysqli_error($cxn));
	$row = mysqli_fetch_assoc($result);

 

However performing a second query from the results of your first query is not recommended. This is an inefficient way of getting data from your database as it takes up server resources. What you can do is use an alternative query (called a join) which will grab the necessary data from both your useritems table and items table using just one query.

 

[Psycho]

Here is a rewrite of your code that should produce the results you are after - but I did not test it as I don't have your database. Note, I created it so that you can separate the logic (PHP) from the output (HTML).

 

<?php

    //selects all the users items to whoever is logged in
    $sql = "SELECT useritems.quantity, items.name, items.image
            FROM useritems
            JOIN items USING(itemid)
            WHERE userid='{$_SESSION['userid']}'
            LIMIT {$offset}, {$rowsperpage}";
    $result = mysqli_query($cxn, $sql) or die(mysqli_erro($cxn));
    $imagecount = 0;
    $tableOutput = '';
    while ($row = mysqli_fetch_assoc($result)) //while there are still results
    {
        $imagecount++;
        $quantity = $row['quantity'];
        $name     = $row['name'];
        $image    = $row['image'];

        //Open new row
        if($imagecount%5==1)
        {
            $tableOutput .= "<tr>\n";
        }

        //Create TD
        $tableOutput .= "<td width=\"120px\" align=\"center\">";
        $tableOutput .= "<img src=\"http://www.elvonica.com/{$image}\"><br>";
        $tableOutput .= "{$name} ({$quantity})";
        $tableOutput .= "</td>\n";

        //Close current row
        if ($imagecount%5==0) //after 5 items have been listed, start a new line
        {
            $tableOutput .= "</tr>\n";
	}
}
    //Close last row  if needed
    if($imagecount%5!=0)
    {
        $tableOutput .= "</tr>\n";
    }

?>

<table cellspacing="0" class="news" align="center">
<?php echo $tableOutput; ?>
</table>

[Tenaciousmug]

Mjdamato, you're a genius. (:

I totally forgot about the JOIN... now I need to fix all my other codes.

Thanks so much!