echoing only 1 row from database when values are similar.

[seany123]

what im trying to do is create a page which shows rows from a database... but where there are duplicates i only want it to show one of the rows etc.

 

here the table structure of the 2 tables that are involved.

 

 

--

-- Table structure for table `blueprint_items`

--

 

CREATE TABLE IF NOT EXISTS `blueprint_items` (

  `id` int(11) NOT NULL auto_increment,

  `item_id` int(11) NOT NULL,

  `name` varchar(255) collate latin1_general_ci NOT NULL default '',

  `description` text collate latin1_general_ci NOT NULL,

  `type` enum('weapon','armour','shoe','Consumable','pet','rare','parts','other') collate latin1_general_ci NOT NULL default 'weapon',

  `Value` int(11) NOT NULL default '0',

  `shopcost` int(11) NOT NULL default '0',

  `strengthbonus` int(11) NOT NULL default '0',

  `defensebonus` int(11) NOT NULL default '0',

  `speedbonus` int(11) NOT NULL default '0',

  `powerbonus` int(11) NOT NULL default '0',

  `torquebonus` int(11) NOT NULL default '0',

  `weightbonus` int(11) NOT NULL default '0',

  `img` varchar(255) collate latin1_general_ci NOT NULL,

  `features` varchar(255) collate latin1_general_ci NOT NULL,

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=96 ;

 

--

-- Table structure for table `items`

--

 

CREATE TABLE IF NOT EXISTS `items` (

  `id` int(11) NOT NULL auto_increment,

  `player_id` int(11) NOT NULL default '0',

  `item_id` int(11) NOT NULL default '0',

  `status` enum('equipped','unequipped') collate latin1_general_ci NOT NULL default 'unequipped',

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=146 ;

 

 

okay so for what im trying to achieve to work im using the following queries....

 

<?php
$query = $db->execute("select * from items where `player_id`=?", array($player->id));
while($item = $query->fetchrow())
{
$query2 = $db->execute("select * from `blueprint_items` where `id`=?",array($item['item_id']));
$item2 = $query2->fetchrow();

$query3 = $db->execute("select * from `items` where `player_id`=? and `item_id`=?", array($player->id, $item['item_id']));

echo $query3->recordcount();
?>

 

using the above echo... it will tell me the amount of rows in the `items` table with the specified values are duplicates...

 

but what im now having a problem with is knowing how to only show 1 row from the duplicated rows rather than all of them still.

 

 

 

 

[sasa]

try to do all work with one query

somehing like

SELECT i.*, b.*, COUNT(i.id) FROM items i 
LEFT JOIN blueprint_items b ON i.iteem_id=b.id
WHERE player_id=? 
GROUP BY i.item_id

not tested

[seany123]

try to do all work with one query

somehing like

SELECT i.*, b.*, COUNT(i.id) FROM items i 
LEFT JOIN blueprint_items b ON i.iteem_id=b.id
WHERE player_id=? 
GROUP BY i.item_id

not tested

 

but that doesn't fix my actual problem.

 

i wanna some how limit the amout of duplicate rows selected to 1

[Mark Baker]

If they're exact duplicates across all the columns being returned, then SELECT DISTINCT

[sasa]

are you try this?

if you can use phpmyadmin with your database try to use  this SQL

SELECT i.*, b.*, COUNT(i.id) AS amount FROM items i 
LEFT JOIN blueprint_items b ON i.item_id=b.id
WHERE i.player_id=1 
GROUP BY i.item_id

[seany123]

are you try this?

if you can use phpmyadmin with your database try to use  this SQL

SELECT i.*, b.*, COUNT(i.id) AS amount FROM items i 
LEFT JOIN blueprint_items b ON i.item_id=b.id
WHERE i.player_id=1 
GROUP BY i.item_id

 

I don't think i can.

 

If they're exact duplicates across all the columns being returned, then SELECT DISTINCT

 

im making a inventory page for my game... im trying to show the users all their items... but say if they had 50 of the same item.. i dont wanna show all the items so instead show only 1 then echo somthing like Quantity: 50 (which would be the recordcount)

 

 

 

 

[seany123]

Any other ideas? please?

[seany123]

anyone?

[onthespot]

Cant you just use GROUP BY?

[kickstart]

Any other ideas? please?

 

The entry from Sasa looks basically right to me. It is grouping by item Id and giving you a count, so should only give you one row per item for that person.

 

Should really group by all the non aggregate columns, so try this which is essentially the same but more standard SQL:-

 

SELECT a.name, a.someOtherField, b.ItemCount
(SELECT item_id, count(id) AS ItemCount FROM items WHERE player_id = $whatever GROUP BY item_id)a
INNER JOIN blueprint_items b
ON a.item_id = b.item_id

 

All the best

 

Keith