PHP Odd Question (Not really sure what this is called..?)

[Stalingrad]

Hi all! I've been workign a lot lately on this issue and have come accross it yet again. I will show you the example of the code that grabs the data and display it, then also two images. One image of what I am getting (No erros in this), and also an Image of what I am tryign to accomplish. I'm sorry I can't be more specific, but I have googled everything under the sun and am stumped as to what the function or even php/mysql code is called for this.. anyway, here it goes!

 

I am displaying some images. They display great. Theo nly problem is, I wnat them to display in a more advanced way now that I have covered many nice things on my website. I can show a list of images, but let's say I have an image that is displayed twice (the same image. I want to, instead of displaying the image itself twice again, just display the image and underneath it put a x 2. Here is my code for grabbing and displaying the images... keep in mind, yes my variable names are rediculous and my coding seems unorginzed but I am 100% self-taught for 11 years now...

 

$getid = $_GET['uitemid'];

$action = $_GET['action'];

if(!isset($getid)) {

$col = "4";

echo "<table border=0>";

echo "<tr>";



$query = "SELECT * FROM uitems WHERE username='$showusername' AND location='1'";

$iquery = mysql_query($query);

while($irow = mysql_fetch_array($iquery)) {

$uitemid = $irow['uitemid'];

$theitemid = $irow['theitemid'];

$loc = $irow['location'];

$price = $irow['price'];

$uzer = $irow['username'];





$squery = "SELECT * FROM items WHERE itemid='$theitemid'";

$querys = mysql_query($squery);

while($row = mysql_fetch_array($querys)) {

$name = $row['name'];

$image = $row['image'];

$desc = $row['desc'];

$rare = $row['rarity'];

$shop = $row['shop'];

$type = $row['type'];

$coins = $row['coins'];

$uses = $row['uses'];



echo "<td><center>$name<br><a href=?uitemid=$uitemid><img src=images/items/$image></a></center></td>";



$col--;

if(!$col) {

 echo "</tr><tr>";

   $col=4;

}



}

}

}

 

Here are the images, The first one is what I am getting, and the second is what I want to accomplish...

 

before and after I named them, basically. Before is what I am getting, and after is what I want... so yeah.

 

Anyway, basically, what way should I go about with my code to make this happen? I don't use the JOIN MySQL because it never works for me, I just loop it, and it always works fine. Is there a way to do this with my code? Thanks guys!

[requinix]

It's mostly a change to your query. In SQL a GROUP BY allows you to "combine" similar items (however you want to define that) and lets you use aggregate functions like COUNT() to give you information about each grouping.

$query = "SELECT *, COUNT(1) AS count FROM uitems WHERE username='$showusername' AND location='1' GROUP BY theitemid";

That will group items together according to the theitemid, and return back a column named "count" containing the number of items in each group. In your example it will be 2 for the gem and 1 for everything else; if it's not 1 then display the x? alongside the item.

 

 

As for the JOIN, you really should learn to use it. Using nested queries like you're doing now is almost always a Bad Thing. Also you should avoid using SELECT *s unless you actually do want all of the information in the table (which you don't here).

 

Try this:

SELECT u.uitemid, i.name, i.image
FROM uitems u
JOIN items i ON u.theitemid = i.itemid
WHERE u.username = '$showusername' AND u.location = 1
GROUP BY i.theitemid

[edit] With this you only need the one loop. [/edit]

 

But there's a problem. I think the uitemid is unique to each uitem? GROUP BY won't let you get more than one value for each group of rows: you'll get some uitemid for one of the two gems and you can't know which one it was for. So that impacts the link you output.

Edited March 6, 2013 by requinix

[Stalingrad]

The query works fine for displaying as for the uitem thing, but thank you for the help. I will try what you said when I get back home from work. I appreciate the help, and will let you know how it goes. I will post later, thank you! I will also look into the JOIN. I've had trouble in the past with JOIN and just became discouraged. I take the long route with it by running 4930294 loops, lol. >_< I run so many loops with the while array,it's no even funny. But I will try the GROUP BY.