Have an account? Sign in
Login  Register  Facebook
Web Development Problems
MySQL Database Query Problem
I need your help!!!.

I need to query a table in my database that has record of goods sold. I want the query to detect a particular product and also calculate the quantity sold. The product are 300 now, but it would increase in the future.

Below is a sample of my DB Table
#----------------------------
# Table structure for litorder
#----------------------------
CREATE TABLE `litorder` (
  `id` int(10) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL default '',
  `address` varchar(50) NOT NULL default '',
  `xdate` date NOT NULL default '0000-00-00',
  `ref` varchar(20) NOT NULL default '',
  `code1` varchar(50) NOT NULL default '',
  `code2` varchar(50) NOT NULL default '',
  `code3` varchar(50) NOT NULL default '',
  `code4` varchar(50) NOT NULL default '',
  `code5` varchar(50) NOT NULL default '',
  `code6` varchar(50) NOT NULL default '',
  `code7` varchar(50) NOT NULL default '',
  `code8` varchar(50) NOT NULL default '',
  `code9` varchar(50) NOT NULL default '',
  `code10` varchar(50) NOT NULL default '',
  `code11` varchar(50) character set latin1 collate latin1_bin NOT NULL default '',
  `code12` varchar(50) NOT NULL default '',
  `code13` varchar(50) NOT NULL default '',
  `code14` varchar(50) NOT NULL default '',
  `code15` varchar(50) NOT NULL default '',
  `product1` varchar(100) NOT NULL default '0',
  `product2` varchar(100) NOT NULL default '0',
  `product3` varchar(100) NOT NULL default '0',
  `product4` varchar(100) NOT NULL default '0',
  `product5` varchar(100) NOT NULL default '0',
  `product6` varchar(100) NOT NULL default '0',
  `product7` varchar(100) NOT NULL default '0',
  `product8` varchar(100) NOT NULL default '0',
  `product9` varchar(100) NOT NULL default '0',
  `product10` varchar(100) NOT NULL default '0',
  `product11` varchar(100) NOT NULL default '0',
  `product12` varchar(100) NOT NULL default '0',
  `product13` varchar(100) NOT NULL default '0',
  `product14` varchar(100) NOT NULL default '0',
  `product15` varchar(100) NOT NULL default '0',
  `price1` int(10) NOT NULL default '0',
  `price2` int(10) NOT NULL default '0',
  `price3` int(10) NOT NULL default '0',
  `price4` int(10) NOT NULL default '0',
  `price5` int(10) NOT NULL default '0',
  `price6` int(10) NOT NULL default '0',
  `price7` int(10) NOT NULL default '0',
  `price8` int(10) NOT NULL default '0',
  `price9` int(10) NOT NULL default '0',
  `price10` int(10) NOT NULL default '0',
  `price11` int(10) NOT NULL default '0',
  `price12` int(10) NOT NULL default '0',
  `price13` int(10) NOT NULL default '0',
  `price14` int(10) NOT NULL default '0',
  `price15` int(10) NOT NULL default '0',
  `quantity1` int(10) NOT NULL default '0',
  `quantity2` int(10) NOT NULL default '0',
  `quantity3` int(10) NOT NULL default '0',
  `quantity4` int(10) NOT NULL default '0',
  `quantity5` int(10) NOT NULL default '0',
  `quantity6` int(10) NOT NULL default '0',
  `quantity7` int(10) NOT NULL default '0',
  `quantity8` int(10) NOT NULL default '0',
  `quantity9` int(10) NOT NULL default '0',
  `quantity10` int(10) NOT NULL default '0',
  `quantity11` int(10) NOT NULL default '0',
  `quantity12` int(10) NOT NULL default '0',
  `quantity13` int(10) NOT NULL default '0',
  `quantity14` int(10) NOT NULL default '0',
  `quantity15` int(10) NOT NULL default '0',
  `amount1` int(10) NOT NULL default '0',
  `amount2` int(10) NOT NULL default '0',
  `amount3` int(10) NOT NULL default '0',
  `amount4` int(10) NOT NULL default '0',
  `amount5` int(10) NOT NULL default '0',
  `amount6` int(10) NOT NULL default '0',
  `amount7` int(10) NOT NULL default '0',
  `amount8` int(10) NOT NULL default '0',
  `amount9` int(10) NOT NULL default '0',
  `amount10` int(10) NOT NULL default '0',
  `amount11` int(10) NOT NULL default '0',
  `amount12` int(10) NOT NULL default '0',
  `amount13` int(10) NOT NULL default '0',
  `amount14` int(10) NOT NULL default '0',
  `amount15` int(10) NOT NULL default '0',
  `totalNaira` double(20,0) NOT NULL default '0',
  `totalDollar` int(20) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 4096 kB; InnoDB free: 4096 kB; InnoDB free: 409';
#----------------------------
# Records for table litorder
#----------------------------


insert  into litorder values 
(27, 'Sanyaolu Fisayo', '14 Adegboyega Street Palmgrove Lagos', '2010-05-31', '', 'DL 001', 'DL 002', 'DL 003', '', '', '', '', '', '', '', '', '', '', '', '', 'AILMENT & PREVENTION DVD- ENGLISH', 'AILMENT & PREVENTION DVD- HAUSA', 'BEAUTY CD', '', '', '', '', '', '', '', '', '', '', '', '', 800, 800, 3000, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 16, 16, 20, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 12800, 12800, 60000, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '85600', 563), 
(28, 'Irenonse Esther', 'Lagos,Nigeria', '2010-06-01', '', 'DL 005', 'DL 008', 'FC 004', '', '', '', '', '', '', '', '', '', '', '', '', 'GET HEALTHY DVD', 'YOUR FUTURE DVD', 'FOREVER FACE CAP (YELLOW)', '', '', '', '', '', '', '', '', '', '', '', '', 1000, 900, 2000, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 2, 2, 3, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 2000, 1800, 6000, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '9800', 64), 
(29, 'Kalu Lekway', 'Lagos, Nigeria', '2010-06-01', '', 'DL 001', 'DL 003', '', '', '', '', '', '', '', '', '', '', '', '', '', 'AILMENT & PREVENTION DVD- ENGLISH', 'BEAUTY CD', '', '', '', '', '', '', '', '', '', '', '', '', '', 800, 3000, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 3, 6, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 2400, 18000, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '20400', 133), 
(30, 'Dele', 'Ilupeju', '2010-06-02', '', 'DL 001', 'DL 003', '', '', '', '', '', '', '', '', '', '', '', '', '', 'AILMENT & PREVENTION DVD- ENGLISH', 'BEAUTY CD', '', '', '', '', '', '', '', '', '', '', '', '', '', 800, 3000, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 10, 10, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 8000, 30000, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '38000', 250);
Started: September 17, 2011 Latest Activity: September 17, 2011 mysql php
4 Answers
Why do you have code, price, quantity, amount repeated 15 times in your table? try to look at Database Normalization :

Database Normalization Basics

Posted: xtremex
In: September 17, 2011

Since the table is not normalized, i think the query won't be neat. But if you have to do it the hard way then i think this might work

select product1,quantity1 from table name where product1 = '<>' union select product2,quantity2 from table name where product2 = '<>'

Make the above 15 times for every column in the table. But overtime as more products are added this query will become slow. So the best course is to normalize this table. That will surely save you tons of hours in future.

Posted: is_set
In: September 17, 2011

I'm not sure which 'quantity' you need, as there are 12 different 'quantity' fields in your table. Here may be something akin to what you're looking for:
SELECT COUNT(quantity1) AS TotalAmnt
FROM litorder
WHERE product1 = 'yourProduct'
GROUP BY product1

You will want to try to normalize your database as well, to make queries like this easier in the future.

Posted: Go
In: September 17, 2011

I\'m not exactly sure what your question is, but your table design seems to be your problem. Any time you have columns foo1, foo2, foo3, etc... that should be a sign to you that your design might need modification.

I suggest you look at database normalization and the Wikipedia page on First Normal Form. In particular you could look at the section about repeating groups across columns and how to resolve it.

Posted: MacOS
In: September 17, 2011

Your Answer

xDo you want to answer this question? Please login or create an account to post your answer