[SOLVED] GROUP BY problem.

[Chevy]

The GROUP BY I am using does not seem to be working.

 

                $trader = mysql_query("SELECT * FROM `trades` WHERE `item1`='{$searchitem['id']}' OR `item2`='{$searchitem['id']}' OR `item3`='{$searchitem['id']}' OR `item4`='{$searchitem['id']}' OR `item5`='{$searchitem['id']}' OR `item6`='{$searchitem['id']}' GROUP BY `id`");
                $tradebarray = mysql_fetch_array($trader);

                if ($tradebarray['id'] == NULL){
                    echo '';
                }
                else {
                 echo '<a href="trades.php?type='.$tradebarray['owner'].'"><b>Lot #'.$tradebarray['id'].'</b></a> owned by <a href="profile.php?user='.$tradebarray['owner'].'"><b>'.$tradebarray['owner'].'</b>
</a><br>';
                }

 

And it just displays this:

 

 

Lot #8 owned by Chevy

Lot #9 owned by Test

Lot #8 owned by Chevy

Lot #8 owned by Chevy

Lot #8 owned by Chevy

Lot #8 owned by Chevy

Lot #8 owned by Chevy

Lot #9 owned by Test

[fenway]

First, echo the actual query, without PHP variables.

Second, post the table structure.

Third, getting back "*" from a GROUP BY makes no sense.

[Chevy]

When I echo'ed the $trader I got Resource id for about 8 itemid's...

 

This is the table structure for trades:

 

CREATE TABLE `trades` (

  `id` int(12) NOT NULL auto_increment,

  `owner` varchar(255) NOT NULL default '',

  `time` varchar(255) NOT NULL default '',

  `item1` int(12) NOT NULL default '0',

  `item2` int(12) NOT NULL default '0',

  `item3` int(12) NOT NULL default '0',

  `item4` int(12) NOT NULL default '0',

  `item5` int(12) NOT NULL default '0',

  `item6` int(12) NOT NULL default '0',

  `wanted` int(12) NOT NULL default '0',

  `description` text NOT NULL,

  PRIMARY KEY  (`id`)

)

 

[fenway]

Well, that's one out of three... not $trader, the string that you're passing to mysql_query().

[Chevy]

This is what it out puts 0_o it is weird...

 

SELECT `id`,`owner` FROM `trades` WHERE `item1`='15' OR `item2`='15' OR `item3`='15' OR `item4`='15' OR `item5`='15' OR `item6`='15' GROUP BY `id`

SELECT `id`,`owner` FROM `trades` WHERE `item1`='19' OR `item2`='19' OR `item3`='19' OR `item4`='19' OR `item5`='19' OR `item6`='19' GROUP BY `id`

SELECT `id`,`owner` FROM `trades` WHERE `item1`='23' OR `item2`='23' OR `item3`='23' OR `item4`='23' OR `item5`='23' OR `item6`='23' GROUP BY `id`

SELECT `id`,`owner` FROM `trades` WHERE `item1`='24' OR `item2`='24' OR `item3`='24' OR `item4`='24' OR `item5`='24' OR `item6`='24' GROUP BY `id`

SELECT `id`,`owner` FROM `trades` WHERE `item1`='25' OR `item2`='25' OR `item3`='25' OR `item4`='25' OR `item5`='25' OR `item6`='25' GROUP BY `id`

SELECT `id`,`owner` FROM `trades` WHERE `item1`='28' OR `item2`='28' OR `item3`='28' OR `item4`='28' OR `item5`='28' OR `item6`='28' GROUP BY `id`

SELECT `id`,`owner` FROM `trades` WHERE `item1`='33' OR `item2`='33' OR `item3`='33' OR `item4`='33' OR `item5`='33' OR `item6`='33' GROUP BY `id`

SELECT `id`,`owner` FROM `trades` WHERE `item1`='35' OR `item2`='35' OR `item3`='35' OR `item4`='35' OR `item5`='35' OR `item6`='35' GROUP BY `id`

[Chevy]

The query it is getting $searchitem['id'] is:

 

mysql_query("SELECT  `id` FROM `useritems` WHERE `itemid`='$itemiid' AND `location`='Gone'");

 

 

Table Structure for useritems:

 

CREATE TABLE `useritems` (

  `id` int(12) NOT NULL auto_increment,

  `itemid` varchar(12) NOT NULL default '',

  `owner` varchar(255) NOT NULL default '',

  `location` enum('Inventory','Safe Box','Gone') NOT NULL default 'Inventory',

  `amount` int(12) NOT NULL default '0',

  `used` int(2) NOT NULL default '0',

  PRIMARY KEY  (`id`)

)

[fenway]

Sounds like you should be combining this into a single query -- your GROUP BY is working, but not across the for loop!

[Chevy]

Hm, okay...want to lead me in the direction of that? xD

[fenway]

Try using a JOIN.

[Chevy]

Ah okay, ill look at that

 

I never really have used JOIN before...

[Chevy]

Ah okay, ill look at that

 

I never really have used JOIN before...

 

Yea I am stumped, I do not know how to do this

[fenway]

There are some stickies on basic JOIN syntax with examples.  Basically, you're running 2 queries -- one to get the uids, and then the group by one.  You don't need to do this in 2 separate steps.

[Chevy]

I know why I am not getting this...

 

The first query goes into the while loop...like so:

 

$uitem_select = mysql_query("SELECT `id` FROM `useritems` WHERE `itemid`='$itemiid' AND `location`='Gone'");

         while ($searchitem = mysql_fetch_array($uitem_select)){
                  
                $trader = mysql_query("SELECT `id`,`owner` FROM `trades` WHERE `item1`='$searchitem[id]' OR `item2`='$searchitem[id]' OR `item3`='$searchitem[id]' OR `item4`='$searchitem[id]' OR `item5`='$searchitem[id]' OR `item6`='$searchitem[id]' GROUP BY `id`") or die(mysql_error());
                $tradebarray = mysql_fetch_array($trader);

                if ($tradebarray['id'] == NULL){
                    echo '';
                }
                else {
                 echo 'ID: '.$tradebarray[id].'<br>';
                }
           
         }

[fenway]

Try this (untested);

 

SELECT t.id, t.owner FROM trades AS t
INNER JOIN 
( SELECT id FROM useritems WHERE itemid='$itemiid' AND location='Gone' ) AS u
ON ( t.item1=u.id OR t.item2=u.id OR t.item3=u.id OR t.item4=u.id OR t.item4=u.id OR t.item4=u.id )
GROUP BY t.id

[Chevy]

And this would be for the $uitem_select or $trader?

[fenway]

The outer loop.

[Chevy]

Oh wow, thank you so much fenway!

 

You are a really great help I wish I was better at MySQL haha.

 

Have a nice day, and thanks again Works perfectly!