I need help!!

[seany123]

yeah the player with with id 1 is me and yeah i have about 6 items.

[kickstart]

Hi

 

There is a GROUP BY player_id missing from the subselect.

 

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0003 sec)
SQL query:
SELECT bi.img AS blueprintimg, bi.id AS blueprintid, bi.name AS blueprintname, i.id AS itemid
FROM players p
JOIN (
SELECT player_id, MAX( id ) AS id, COUNT( id ) AS num_items
FROM items
WHERE player_id =1
GROUP BY player_id
)items_sub ON p.id = items_sub.player_id
JOIN items i ON items_sub.id = i.id
JOIN blueprint_items bi ON i.id = bi.item_id
WHERE p.id =1

 

However can you post the definitions for the 3 tables along with a couple of example lines (there is an export function in phpmyadmin)

 

All the best

 

Keith

[fenway]

Agreed... it's getting silly to obtain each piece of information bit by bit.

[seany123]

Hi

 

There is a GROUP BY player_id missing from the subselect.

 

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0003 sec)
SQL query:
SELECT bi.img AS blueprintimg, bi.id AS blueprintid, bi.name AS blueprintname, i.id AS itemid
FROM players p
JOIN (
SELECT player_id, MAX( id ) AS id, COUNT( id ) AS num_items
FROM items
WHERE player_id =1
GROUP BY player_id
)items_sub ON p.id = items_sub.player_id
JOIN items i ON items_sub.id = i.id
JOIN blueprint_items bi ON i.id = bi.item_id
WHERE p.id =1

 

However can you post the definitions for the 3 tables along with a couple of example lines (there is an export function in phpmyadmin)

 

All the best

 

Keith

 

i dont know what you want when you say about example lines... but here is what i got from the 3 tables....

 

 

-- phpMyAdmin SQL Dump
-- version 2.11.9.5
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 16, 2009 at 11:05 PM
-- Server version: 5.0.81
-- PHP Version: 5.2.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `maf10000_db1`
--

-- --------------------------------------------------------

--
-- 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 ;

--
-- Dumping data for table `blueprint_items`
--

INSERT INTO `blueprint_items` (`id`, `item_id`, `name`, `description`, `type`, `Value`, `shopcost`, `strengthbonus`, `defensebonus`, `speedbonus`, `powerbonus`, `torquebonus`, `weightbonus`, `img`, `features`) VALUES
(1, 1, 'Awake Pill', 'An item that restores your awake when used.', 'Consumable', 0, 0, 0, 0, 0, 0, 0, 0, 'http://www.futurederm.com/wp-content/uploads/2008/03/031408-pill.jpg', '- refill awake 100% '),
(2, 2, 'Hammer', 'A weapon used to attack other mobsters.', 'weapon', 1000, 1500, 3, 0, 0, 0, 0, 0, 'http://z.about.com/d/homerepair/1/0/s/-/-/-/hammer.jpg', '- Strength bonus 3%'),
(3, 0, 'Baseball Bat', 'A weapon used to attack other mobsters.', 'weapon', 2000, 3000, 5, 0, 0, 0, 0, 0, 'http://www.midwaypartyrental.com/images/products/baseball_bat.jpg', '- Strength bonus 5%'),
(4, 0, 'Crow Bar', 'A weapon used to attack other mobsters.', 'weapon', 10000, 15000, 10, 0, 0, 0, 0, 0, 'http://www.steritool.com/images/product/10294.jpg', '- Strength bonus 10%'),
(5, 0, 'Kitchen Knife', 'A weapon used to attack other mobsters.', 'weapon', 20000, 30000, 15, 0, 0, 0, 0, 0, 'http://womenofcaliber.files.wordpress.com/2009/07/self-defense-kitchen-knife1.jpg', '- Stength bonus 15%'),
(6, 0, 'Axe', 'A weapon used to attack other mobsters.', 'weapon', 160000, 240000, 25, 0, 0, 0, 0, 0, 'http://mafiakillerz.com/img/items/NONO.png', '- Stength bonus 25%'),
(7, 0, 'Chainsaw', 'A weapon used to attack other mobsters.', 'weapon', 320000, 480000, 35, 0, 0, 0, 0, 0, 'http://www.howdididoit.com/wp-content/uploads/2008/01/chainsaw.jpg', '- Stength bonus 35%'),
(8, 0, 'Rags', 'Armour used to defend yourself from other mobster attacks.', 'armour', 1000, 1500, 0, 3, 0, 0, 0, 0, '', '- Defense bonus 3%'),
(9, 0, 'Padded Shirt', 'A piece of armour used to defend yourself from other citizen attacks.', 'armour', 2000, 3000, 0, 5, 0, 0, 0, 0, '', '- Defense bonus 5%'),
(10, 0, 'Leather Vest', 'A piece of armour used to defend yourself from other citizen attacks.', 'armour', 10000, 15000, 0, 10, 0, 0, 0, 0, '', '- Defense bonus 10%'),
(11, 0, 'Chainmail', 'A piece of armour used to defend yourself from other citizen attacks.', 'armour', 20000, 30000, 0, 15, 0, 0, 0, 0, '', '- Defense bonus 15%'),
(12, 0, 'Platebody', 'A piece of armour used to defend yourself from other citizen attacks.', 'armour', 160000, 240000, 0, 25, 0, 0, 0, 0, '', '- Defense bonus 25%'),
(13, 0, 'Bullet Proof Vest', 'A piece of armour used to defend yourself from other citizen attacks.', 'armour', 320000, 480000, 0, 35, 0, 0, 0, 0, '', '- Defense bonus 35%'),
(14, 0, 'Meat Cleaver', 'A weapon used to attack other mobsters.', 'weapon', 100000, 160000, 20, 0, 0, 0, 0, 0, '', '- Strength bonus 20%'),
(15, 0, 'Sledgehammer', 'A weapon used to attack other mobsters.', 'weapon', 240000, 360000, 30, 0, 0, 0, 0, 0, '', '- Strength bonus 30%'),
(16, 0, 'Wrench', 'A weapon used to attack other mobsters.', 'weapon', 20000, 30000, 13, 0, 0, 0, 0, 0, '', '- Strength bonus 13%'),
(17, 0, 'Machete', 'A weapon used to attack other mobsters.', 'weapon', 80000, 120000, 18, 0, 0, 0, 0, 0, '', '- Strength bonus 18%'),
(18, 0, 'Medi-Kit (25%)', 'An item that heals yourself when used (HP).', 'Consumable', 10000, 15000, 0, 0, 0, 0, 0, 0, 'http://mafiakillerz.com/img/items/18.jpg', '- HP Restore: 25%'),
(19, 0, 'Medi-Kit (50%)', 'An item that heals yourself when used (HP).', 'Consumable', 15000, 22500, 0, 0, 0, 0, 0, 0, 'http://mafiakillerz.com/img/items/19.jpg', '- HP Restore: 50%'),
(20, 0, 'Medi-Kit (75%)', 'An item that heals yourself when used (HP).', 'Consumable', 20000, 30000, 0, 0, 0, 0, 0, 0, 'http://mafiakillerz.com/img/items/20.jpg', '- HP Restore: 75%'),
(21, 0, 'Medi-Kit (100%)', 'An item that heals yourself when used (HP).', 'Consumable', 25000, 37500, 0, 0, 0, 0, 0, 0, 'http://mafiakillerz.com/img/items/21.jpg', '- HP Restore: 100%'),
(22, 0, 'Police Revolver', 'A weapon used to attack other mobsters.', 'weapon', 320000, 480000, 36, 0, 0, 0, 0, 0, '', '- Strength bonus: 36%'),
(23, 0, '1911 Colt', 'A weapon used to attack other mobsters.', 'weapon', 500000, 750000, 40, 0, 0, 0, 0, 0, '', '- Strength bonus: 40%'),
(24, 0, 'Sawn-off Shotgun', 'A weapon used to attack other mobsters.', 'weapon', 620000, 930000, 43, 0, 0, 0, 0, 0, '', '- Strength bonus: 43%'),
(25, 0, 'Pump Action Shotgun', 'A weapon used to attack other mobsters.', 'weapon', 650000, 975000, 45, 0, 0, 0, 0, 0, '', '- Strength bonus: 45%'),
(26, 0, '1927 Thompson', 'A weapon used to attack other mobsters.', 'weapon', 900000, 1350000, 50, 0, 0, 0, 0, 0, '', '- Strength bonus: 50%'),
(27, 0, 'Medical Certificate (25%)', 'An item that reduces your stay in hospital when used.', 'Consumable', 30000, 45000, 0, 0, 0, 0, 0, 0, '', '- Hospital time: - 25%'),
(28, 0, 'Medical Certificate (50%)', 'An item that reduces your stay in hospital when used.', 'Consumable', 45000, 67500, 0, 0, 0, 0, 0, 0, '', '- Hospital time: - 50%'),
(29, 0, 'Medical Certificate (75%)', 'An item that reduces your stay in hospital when used.', 'Consumable', 60000, 90000, 0, 0, 0, 0, 0, 0, '', '- Hospital time: - 75%'),
(30, 0, 'Medical Certificate (100%)', 'An item that reduces your stay in hospital when used.', 'Consumable', 75000, 112500, 0, 0, 0, 0, 0, 0, '', '- Hospital time: - 100%'),
(31, 0, 'Interceptor Body Armour', 'A piece of armour used to defend yourself from other mobster attacks.', 'armour', 650000, 975000, 0, 45, 0, 0, 0, 0, '', '- Defense bonus: 45%'),
(32, 0, 'Modular Tactical Vest', 'A piece of armour used to defend yourself from other mobster attacks.', 'armour', 500000, 750000, 0, 40, 0, 0, 0, 0, '', '- Defense bonus: 40%'),
(33, 0, 'Luger P08', 'A weapon used to attack other mobster.', 'weapon', 280000, 420000, 33, 0, 0, 0, 0, 0, '', '- Defense bonus: 33%'),
(34, 0, 'Jehrico 941F', 'A weapon used to attack other mobsters.', 'weapon', 470000, 705000, 38, 0, 0, 0, 0, 0, '', '- Strength bonus: 38%'),
(35, 0, 'Browning Hi-Power', 'A weapon used to attack other mobsters.', 'weapon', 540000, 810000, 42, 0, 0, 0, 0, 0, '', '- Strength bonus: 42%'),
(36, 0, 'CMP-150', 'A weapon used to attack other mobster.', 'weapon', 400000, 600000, 37, 0, 0, 0, 0, 0, '', '- Strength bonus: 37%'),
(37, 0, 'MP40', 'A weapon used to attack other mobsters.', 'weapon', 490000, 735000, 39, 0, 0, 0, 0, 0, '', '- Strength bonus: 39%'),
(38, 0, 'MP5', 'A weapon used to attack other mobsters.', 'weapon', 520000, 780000, 41, 0, 0, 0, 0, 0, '', '- Strength bonus: 41%'),
(39, 0, 'DW-P5', 'A weapon used to attack other mobsters.', 'weapon', 630000, 945000, 44, 0, 0, 0, 0, 0, '', '- Strength bonus: 44%'),
(40, 0, 'Gewehr 3', 'A weapon used to attack other mobsters.', 'weapon', 800000, 1200000, 48, 0, 0, 0, 0, 0, '', '- Strength bonus: 48%'),
(41, 0, 'RCP90', 'A weapon used to attack other mobsters.', 'weapon', 1200000, 1800000, 54, 0, 0, 0, 0, 0, '', '- Strength bonus: 54%'),
(42, 0, 'FN SCAR', 'A weapon used to attack other mobsters.', 'weapon', 1400000, 2100000, 57, 0, 0, 0, 0, 0, '', '- Strength bonus: 57%'),
(43, 0, 'Flak Jacket', 'A piece of armour used to defend yourself from other mobster attacks.', 'armour', 800000, 1200000, 0, 50, 0, 0, 0, 0, '', '- Defense bonus: 50%'),
(44, 0, 'Panzer 1918', 'A piece of armour used to defend yourself from other mobster attacks.', 'armour', 240000, 360000, 0, 30, 0, 0, 0, 0, '', '- Defense bonus: 30%'),
(45, 0, 'Cannabis', 'A drug that boosts your stats temporarily.', 'Consumable', 28000, 42000, 5, 10, 5, 0, 0, 0, '', '- Strength bonus: 5%, Defense bonus: 10%, Speed bonus: 5% '),
(46, 0, 'Speed', 'A drug that boosts your stats temporarily.', 'Consumable', 56000, 84000, 10, 10, 20, 0, 0, 0, '', '- Strength bonus: 10%, Defense bonus: 10%, Speed bonus: 20% '),
(47, 0, 'LSD', 'A drug that boosts your stats temporarily.', 'Consumable', 80000, 120000, 25, 20, 15, 0, 0, 0, '', '- Strength bonus: 25%, Defense bonus: 20%, Speed bonus: 15% '),
(48, 0, 'Cocaine', 'A drug that boosts your stats temporarily.', 'Consumable', 108000, 162000, 25, 25, 30, 0, 0, 0, '', '- Strength bonus: 25%, Defense bonus: 25%, Speed bonus: 30% '),
(49, 0, 'Heroin', 'A drug that boosts your stats temporarily.', 'Consumable', 140000, 210000, 35, 35, 30, 0, 0, 0, '', '- Strength bonus: 35%, Defense bonus: 35%, Speed bonus: 30% '),
(50, 0, 'Acura V6', 'A V6 engine to power your car.', 'parts', 40000, 60000, 0, 0, 0, 240, 220, 200, '', '- Power bonus: 240, Torque bonus: 220, Weight bonus: 200 '),
(51, 0, 'Subaru Opposed-4', 'An Opposed-4 engine to power your car.', 'parts', 28000, 42000, 0, 0, 0, 200, 160, 140, '', '- Power bonus: 200, Torque bonus: 160, Weight bonus: 140 '),
(52, 0, 'Nissan I6', 'An I6 engine to power your car.', 'parts', 16000, 24000, 0, 0, 0, 130, 160, 140, '', '- Power bonus: 130, Torque bonus: 160, Weight bonus: 140 '),
(53, 0, 'Ford Small Block V8', 'A Small Block V8 to power your car.', 'parts', 53000, 80000, 0, 0, 0, 160, 230, 200, '', '- Power bonus: 160, Torque bonus: 230, Weight bonus: 200 '),
(54, 0, 'Ford Big Block V8', 'A Big Block V8 to power your car.', 'parts', 0, 0, 0, 0, 0, 270, 340, 260, '', '- Power bonus: 270, Torque bonus: 340, Weight bonus: 260 '),
(55, 0, 'Ford V6', 'A V6 engine to power your car.', 'parts', 21000, 32000, 0, 0, 0, 100, 170, 150, '', '- Power bonus: 100, Torque bonus: 170, Weight bonus: 150 '),
(56, 0, 'Stock Exhaust', 'A stock exhaust for your car.', 'parts', 8000, 12000, 0, 0, 0, 15, 0, 5, '', '- Power bonus: 15, Torque bonus: 0, Weight bonus: 5 '),
(57, 0, 'Dual Exhausts', 'Dual exhausts for your car.', 'parts', 13000, 20000, 0, 0, 0, 30, 0, 15, '', '- Power bonus: 30, Torque bonus: 0, Weight bonus: 15 '),
(58, 0, 'Performance Exhaust', 'A performance exhaust for your car.', 'parts', 16000, 24000, 0, 0, 0, 35, 0, 5, '', '- Power bonus: 35, Torque bonus: 0, Weight bonus: 5 '),
(59, 0, 'Quad Exhausts', 'Quad Exhausts for your car.', 'parts', 0, 0, 0, 0, 0, 60, 0, 30, '', '- Power bonus: 60, Torque bonus: 0, Weight bonus: 30 '),
(60, 0, 'Small Turbocharger', 'A small turbocharger for your car.', 'parts', 23000, 35000, 0, 0, 0, 50, 90, 20, '', '- Power bonus: 50, Torque bonus: 90, Weight bonus: 20 '),
(61, 0, 'Large Turbocharger', 'A large turbocharger for your car.', 'parts', 40000, 60000, 0, 0, 0, 80, 140, 30, '', '- Power bonus: 80, Torque bonus: 140, Weight bonus: 30 '),
(62, 0, 'Stock Suspension', 'Stock Suspension for your car.', 'parts', 22000, 34000, 0, 0, 0, 0, 0, 250, '', '- Power bonus: 0, Torque bonus: 0, Weight bonus: 250 '),
(63, 0, 'Racing Suspension', 'Racing Suspension for your car.', 'parts', 43000, 65000, 0, 0, 0, 0, 0, 120, '', '- Power bonus: 0, Torque bonus: 0, Weight bonus: 120 '),
(64, 0, 'Small Supercharger', 'A small supercharger for your car.', 'parts', 35000, 50000, 0, 0, 0, 80, 140, 35, '', '- Power bonus: 80, Torque bonus: 140, Weight bonus: 35 '),
(65, 0, 'Large Supercharger', 'A large supercharger for your car.', 'parts', 0, 0, 0, 0, 0, 170, 230, 60, '', '- Power bonus: 170, Torque bonus: 230, Weight bonus: 60 '),
(66, 0, 'Dual Performance Exhausts', 'Dual Performance Exhausts for your car.', 'parts', 0, 0, 0, 0, 0, 45, 0, 15, '', '- Power bonus: 45, Torque bonus: 0, Weight bonus: 15 '),
(67, 0, 'Subaru Flat-4', 'A Flat-4 engine to power your car.', 'parts', 40000, 60000, 0, 0, 0, 210, 260, 120, '', '- Power bonus: 210, Torque bonus: 260, Weight bonus: 120 '),
(68, 0, 'Mazda Double Rotary', 'A Double Rotary Engine to power your car.', 'parts', 80000, 120000, 0, 0, 0, 230, 260, 180, '', ''),
(69, 0, 'Strength Up (+5)', 'Increases your pet''s strength!', 'pet', 80000, 120000, 5, 0, 0, 0, 0, 0, '', ''),
(70, 0, 'Defense Up (+5)', 'Increases your pet''s defense!', 'pet', 80000, 120000, 0, 5, 0, 0, 0, 0, '', ''),
(71, 0, 'Speed Up (+5)', 'Increases your pet''s speed!', 'pet', 80000, 120000, 0, 0, 5, 0, 0, 0, '', ''),
(72, 0, 'HP Up (+5)', 'Increases your pet''s HP!\r\n', 'pet', 120000, 180000, 0, 0, 0, 0, 0, 0, '', ''),
(73, 0, 'Christmas Hat (2007)', 'A rare item from the Christmas period of 2009!', 'rare', 0, 0, 0, 0, 0, 0, 0, 0, '', ''),
(74, 0, 'Christmas Cracker (2007)', 'A rare item from the Christmas period of 2007!', 'rare', 0, 0, 0, 0, 0, 0, 0, 0, '', ''),
(75, 0, 'Colt King Cobra', 'A weapon used to attack other mobsters.\r\n', 'weapon', 1000000, 1500000, 52, 0, 0, 0, 0, 0, '', ''),
(76, 0, 'AK-47', 'A weapon used to attack other mobsters.', 'weapon', 1900000, 2850000, 59, 0, 0, 0, 0, 0, '', ''),
(77, 0, 'RPG-7', 'A weapon used to attack other mobsters.', 'weapon', 2800000, 4200000, 65, 0, 0, 0, 0, 0, '', ''),
(78, 0, 'DragonSkin Tactical Vest', 'A piece of armour used to defend yourself from other mobster attacks.', 'armour', 1200000, 1800000, 0, 54, 0, 0, 0, 0, '', ''),
(79, 0, 'Camouflage Vest', 'A piece of armour used to defend yourself from other mobster attacks.', 'armour', 1500000, 2250000, 0, 57, 0, 0, 0, 0, '', ''),
(80, 0, 'Easter Egg', 'A rare item from the Easter period of 2009!', 'rare', 0, 0, 0, 0, 0, 0, 0, 0, '', ''),
(81, 0, 'Chevrolet 7.0L LS7', 'A 7.0L LS7 to power your car.', 'parts', 0, 0, 0, 0, 0, 331, 380, 275, '', ''),
(82, 0, 'Butterfly Knife', 'A weapon used to attack other citizens.', 'weapon', 360000, 540000, 37, 0, 0, 0, 0, 0, '', ''),
(83, 0, 'M4 Carbine', 'A weapon used to attack other mobsters.', 'weapon', 2300000, 3450000, 61, 0, 0, 0, 0, 0, '', ''),
(84, 0, 'HK USP45 Silenced', 'A weapon used to attack other mobsters.', 'weapon', 1400000, 2100000, 54, 0, 0, 0, 0, 0, '', ''),
(85, 0, 'STF Liquid Armour', 'A piece of armour used to defend yourself from other citizen mobsters.', 'armour', 2800000, 4200000, 0, 65, 0, 0, 0, 0, '', ''),
(86, 0, 'Moccasins', 'Shoes used to increase your speed in battle.', 'shoe', 5000, 7500, 0, 0, 3, 0, 0, 0, '', ''),
(87, 0, 'Thongs', 'Shoes used to increase your speed in battle.', 'shoe', 25000, 37500, 0, 0, 6, 0, 0, 0, '', ''),
(88, 0, 'Steel Sandals', 'Shoes used to increase your speed in battle.', 'shoe', 100000, 150000, 0, 0, 10, 0, 0, 0, '', ''),
(89, 0, 'Cowboy Boots', 'Shoes used to increase your speed in battle.', 'shoe', 160000, 240000, 0, 0, 16, 0, 0, 0, '', ''),
(90, 0, 'Aussie Work Boots', 'Shoes used to increase your speed in battle.', 'shoe', 240000, 360000, 0, 0, 19, 0, 0, 0, '', ''),
(91, 0, 'Walking Shoes', 'Shoes used to increase your speed in battle.', 'shoe', 350000, 525000, 0, 0, 26, 0, 0, 0, '', ''),
(92, 0, 'Sneakers', 'Shoes used to increase your speed in battle.', 'weapon', 900000, 1200000, 0, 0, 34, 0, 0, 0, '', ''),
(93, 0, 'Runners', 'Shoes used to increase your speed in battle.', 'shoe', 1600000, 2400000, 0, 0, 40, 0, 0, 0, '', ''),
(94, 0, 'Track Shoes', 'Shoes used to increase your speed in battle.', 'shoe', 2400000, 3600000, 0, 0, 48, 0, 0, 0, '', ''),
(95, 0, 'Stilettos', 'Shoes used to increase your speed in battle.', 'shoe', 1200000, 1800000, 0, 0, 37, 0, 0, 0, '', '');

-- --------------------------------------------------------

--
-- 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 ;

--
-- Dumping data for table `items`
--

INSERT INTO `items` (`id`, `player_id`, `item_id`, `status`) VALUES
(9, 1, 1, 'equipped'),
(10, 1, 2, 'equipped'),
(11, 1, 1, 'unequipped'),
(12, 1, 1, 'unequipped'),
(13, 1, 2, 'unequipped'),
(14, 1, 2, 'unequipped'),
(15, 1, 1, 'unequipped'),
(16, 1, 1, 'unequipped'),
(17, 1, 18, 'unequipped'),
(18, 1, 3, 'unequipped'),
(19, 60, 1, 'unequipped'),
(20, 60, 1, 'unequipped'),
(21, 60, 1, 'unequipped'),
(22, 60, 1, 'unequipped'),
(23, 60, 1, 'unequipped'),
(24, 60, 1, 'unequipped'),
(25, 60, 1, 'unequipped'),
(26, 60, 1, 'unequipped'),
(27, 60, 1, 'unequipped'),
(28, 60, 1, 'unequipped'),
(29, 60, 1, 'unequipped'),
(30, 60, 1, 'unequipped'),
(31, 60, 1, 'unequipped'),
(32, 60, 1, 'unequipped'),
(33, 60, 1, 'unequipped'),
(34, 60, 1, 'unequipped'),
(35, 60, 1, 'unequipped'),
(36, 60, 1, 'unequipped'),
(37, 60, 1, 'unequipped'),
(38, 60, 1, 'unequipped'),
(39, 60, 1, 'unequipped'),
(40, 60, 1, 'unequipped'),
(41, 60, 1, 'unequipped'),
(42, 60, 1, 'unequipped'),
(43, 60, 1, 'unequipped'),
(44, 60, 1, 'unequipped'),
(45, 60, 1, 'unequipped'),
(46, 60, 1, 'unequipped'),
(47, 60, 1, 'unequipped'),
(48, 60, 1, 'unequipped'),
(49, 60, 1, 'unequipped'),
(50, 60, 1, 'unequipped'),
(51, 60, 1, 'unequipped'),
(52, 60, 1, 'unequipped'),
(53, 60, 1, 'unequipped'),
(54, 60, 1, 'unequipped'),
(55, 60, 1, 'unequipped'),
(56, 60, 1, 'unequipped'),
(57, 60, 1, 'unequipped'),
(58, 60, 1, 'unequipped'),
(59, 60, 1, 'unequipped'),
(60, 60, 1, 'unequipped'),
(61, 60, 1, 'unequipped'),
(62, 60, 1, 'unequipped'),
(63, 60, 1, 'unequipped'),
(64, 60, 1, 'unequipped'),
(65, 60, 1, 'unequipped'),
(66, 60, 1, 'unequipped'),
(67, 60, 1, 'unequipped'),
(68, 60, 1, 'unequipped'),
(69, 60, 1, 'unequipped'),
(70, 60, 1, 'unequipped'),
(71, 60, 1, 'unequipped'),
(72, 60, 1, 'unequipped'),
(73, 60, 1, 'unequipped'),
(74, 60, 1, 'unequipped'),
(75, 60, 1, 'unequipped'),
(76, 60, 1, 'unequipped'),
(77, 60, 1, 'unequipped'),
(78, 60, 1, 'unequipped'),
(79, 60, 1, 'unequipped'),
(80, 60, 1, 'unequipped'),
(81, 60, 1, 'unequipped'),
(82, 60, 1, 'unequipped'),
(83, 60, 1, 'unequipped'),
(84, 60, 1, 'unequipped'),
(85, 60, 1, 'unequipped'),
(86, 60, 1, 'unequipped'),
(87, 60, 1, 'unequipped'),
(88, 60, 1, 'unequipped'),
(89, 60, 1, 'unequipped'),
(90, 60, 1, 'unequipped'),
(91, 60, 1, 'unequipped'),
(92, 60, 1, 'unequipped'),
(93, 60, 1, 'unequipped'),
(94, 60, 1, 'unequipped'),
(95, 60, 1, 'unequipped'),
(96, 1, 1, 'unequipped'),
(97, 1, 1, 'unequipped'),
(98, 1, 1, 'unequipped'),
(99, 1, 1, 'unequipped'),
(100, 1, 1, 'unequipped'),
(101, 1, 1, 'unequipped'),
(102, 1, 1, 'unequipped'),
(103, 1, 1, 'unequipped'),
(104, 1, 1, 'unequipped'),
(105, 1, 1, 'unequipped'),
(106, 1, 1, 'unequipped'),
(107, 1, 1, 'unequipped'),
(108, 1, 1, 'unequipped'),
(109, 1, 1, 'unequipped'),
(110, 1, 1, 'unequipped'),
(111, 1, 1, 'unequipped'),
(112, 1, 1, 'unequipped'),
(113, 1, 1, 'unequipped'),
(114, 1, 1, 'unequipped'),
(115, 1, 1, 'unequipped'),
(116, 1, 1, 'unequipped'),
(117, 1, 1, 'unequipped'),
(118, 1, 1, 'unequipped'),
(119, 1, 1, 'unequipped'),
(120, 1, 1, 'unequipped'),
(121, 1, 1, 'unequipped'),
(122, 1, 1, 'unequipped'),
(123, 1, 1, 'unequipped'),
(124, 1, 1, 'unequipped'),
(125, 1, 1, 'unequipped'),
(126, 1, 1, 'unequipped'),
(127, 1, 1, 'unequipped'),
(128, 1, 1, 'unequipped'),
(129, 1, 1, 'unequipped'),
(130, 1, 1, 'unequipped'),
(131, 1, 1, 'unequipped'),
(132, 1, 1, 'unequipped'),
(133, 1, 1, 'unequipped'),
(134, 1, 1, 'unequipped'),
(135, 1, 1, 'unequipped'),
(136, 1, 1, 'unequipped'),
(137, 1, 1, 'unequipped'),
(138, 1, 1, 'unequipped'),
(139, 1, 1, 'unequipped'),
(140, 1, 1, 'unequipped'),
(141, 1, 1, 'unequipped'),
(142, 1, 1, 'unequipped'),
(143, 1, 1, 'unequipped'),
(144, 1, 1, 'unequipped'),
(145, 1, 1, 'unequipped');

-- --------------------------------------------------------

--
-- Table structure for table `players`
--

CREATE TABLE IF NOT EXISTS `players` (
  `id` int(11) NOT NULL auto_increment,
  `rm` varchar(11) collate latin1_general_ci NOT NULL default '0',
  `username` varchar(255) collate latin1_general_ci NOT NULL default '',
  `password` varchar(255) collate latin1_general_ci NOT NULL default '',
  `email` varchar(255) collate latin1_general_ci NOT NULL default '',
  `xaccess` tinyint(4) NOT NULL default '0',
  `registered` int(11) NOT NULL default '0',
  `last_active` int(11) NOT NULL default '0',
  `ip` varchar(255) collate latin1_general_ci NOT NULL default '',
  `level` int(11) NOT NULL default '1',
  `maxlevel` int(11) NOT NULL default '500',
  `points` bigint(255) NOT NULL default '0',
  `money` bigint(255) NOT NULL default '0',
  `bank` int(11) NOT NULL default '0',
  `maxbank` int(11) NOT NULL default '30000000',
  `hp` int(11) NOT NULL default '100',
  `maxhp` int(11) NOT NULL default '100',
  `exp` int(11) NOT NULL default '0',
  `maxexp` int(11) NOT NULL default '494',
  `energy` int(11) NOT NULL default '10',
  `maxenergy` int(11) NOT NULL default '10',
  `nerve` int(11) NOT NULL default '10',
  `maxnerve` int(11) NOT NULL default '10',
  `awake` int(11) NOT NULL default '100',
  `maxawake` int(11) NOT NULL default '100',
  `strength` bigint(255) NOT NULL default '10',
  `defense` bigint(255) NOT NULL default '10',
  `speed` bigint(255) NOT NULL default '10',
  `total` bigint(255) NOT NULL default '30',
  `interest` tinyint(1) NOT NULL default '0',
  `kills` int(11) NOT NULL default '0',
  `deaths` int(11) NOT NULL default '0',
  `searchdowntown1` int(11) NOT NULL default '1',
  `searchdowntown2` int(11) NOT NULL default '1',
  `luckydip` int(11) NOT NULL default '1',
  `Players_Job_ID` int(11) NOT NULL default '0',
  `P_Job_Skill` int(11) NOT NULL default '0',
  `ref` int(11) NOT NULL default '0',
  `dead_time_remaining` int(11) NOT NULL default '0',
  `weapon_used_id` int(11) NOT NULL default '0',
  `killed_by_id` int(11) NOT NULL default '0',
  `city_id` int(11) NOT NULL default '1',
  `gender` varchar(255) collate latin1_general_ci NOT NULL default 'Unknown',
  `house_id` int(11) NOT NULL default '0',
  `style` varchar(255) collate latin1_general_ci NOT NULL default '',
  `crimes_failed` int(11) NOT NULL default '0',
  `crimes_sucess` int(11) NOT NULL default '0',
  `staff` int(11) NOT NULL default '0',
  `investment_id` varchar(11) collate latin1_general_ci NOT NULL default '0',
  `investment_time` int(11) NOT NULL default '0',
  `prison` int(11) NOT NULL default '0',
  `prison_caught` int(11) NOT NULL default '0',
  `prison_bust` int(11) NOT NULL default '0',
  `hospital` int(11) NOT NULL default '0',
  `crimes_money` bigint(11) NOT NULL default '0',
  `notepad` text collate latin1_general_ci NOT NULL,
  `recover` varchar(255) collate latin1_general_ci NOT NULL default '',
  `ncolor` int(11) NOT NULL default '0',
  `ncolorid` varchar(20) collate latin1_general_ci NOT NULL default 'CC0000',
  `banned` varchar(11) collate latin1_general_ci NOT NULL default '0',
  `signature` text collate latin1_general_ci NOT NULL,
  `battle_money` bigint(11) NOT NULL default '0',
  `battle_won` bigint(11) NOT NULL default '0',
  `battle_lost` bigint(11) NOT NULL default '0',
  `quote` varchar(50) collate latin1_general_ci NOT NULL default 'No Quote',
  `rating` int(11) NOT NULL default '0',
  `avatar` varchar(255) collate latin1_general_ci NOT NULL default '',
  `gang_id` int(11) default NULL,
  `gang_upgrade` int(1) NOT NULL default '0',
  `gang_edit` int(1) NOT NULL default '0',
  `gang_vault` int(1) NOT NULL default '0',
  `gang_members` int(1) NOT NULL default '0',
  `gang_mail` int(1) NOT NULL default '0',
  `gang_crime` int(1) NOT NULL default '0',
  `gang_ranks` int(1) NOT NULL default '0',
  `battle_total` int(11) NOT NULL default '0',
  `lottery` int(1) NOT NULL default '0',
  `validated` int(11) NOT NULL default '0',
  `validkey` varchar(255) collate latin1_general_ci NOT NULL,
  `update` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `searchdowntown` (`searchdowntown1`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci PACK_KEYS=1 AUTO_INCREMENT=1018 ;

[kickstart]

Hi

 

Try this minor change:-

 

SELECT bi.img AS blueprintimg, bi.id AS blueprintid, bi.name AS blueprintname, i.id AS itemid

FROM players p

JOIN (

SELECT player_id, MAX( id ) AS id, COUNT( id ) AS num_items

FROM items

WHERE player_id =1

GROUP BY player_id

)items_sub ON p.id = items_sub.player_id

JOIN items i ON items_sub.id = i.id

JOIN blueprint_items bi ON i.item_id = bi.item_id

WHERE p.id =1

 

All the best

 

Keith

[seany123]

Hi

 

Try this minor change:-

 

SELECT bi.img AS blueprintimg, bi.id AS blueprintid, bi.name AS blueprintname, i.id AS itemid

FROM players p

JOIN (

SELECT player_id, MAX( id ) AS id, COUNT( id ) AS num_items

FROM items

WHERE player_id =1

GROUP BY player_id

)items_sub ON p.id = items_sub.player_id

JOIN items i ON items_sub.id = i.id

JOIN blueprint_items bi ON i.item_id = bi.item_id

WHERE p.id =1

 

All the best

 

Keith

 

using that on myadmin SQL its came back with 1 entry which seems to be working.... now i need to integrate it with my php page.

[seany123]

anyone know how i can integrate it into the php page ive already posted above?

[fenway]

anyone know how i can integrate it into the php page ive already posted above?

that's no longer a mysql question...

[seany123]

well the whole time its been a SQL/PHP question i been back and forth about 3 times now!

[MadTechie]

Once the SQL is giving you the desired results then this would be solved,

as for outputting the results that's PHP,

to make them look visually appalling  that HTML/CSS

 

theirs no point using PHP on a query that doesn't work, thus this is/was a SQL problem.

[seany123]

can a mod please move this to the php section... because i dont know if i can explain this all again.

[MadTechie]

Create a new thread with a summary of the new problem in PHP help, (include a link here if you need)