Apply GROUP BY into query

[gin]

MySQL server version: 5.1.36

 

Note: This is a followup on this topic: http://www.phpfreaks.com/forums/index.php?topic=337378.0

I've started this new topic on their advice that I should change the SQL rather than the PHP.

 

The setup:

My system keeps quote data spread over several MySQL tables. Sometimes a job involves 2 or more quotes, and I need to display them in a combined manner so we can see how much in total was quoted for each item for the whole job.

 

What I want:

Quote 1

Admin charges: $500

Courier charges: $200

Quote 2

Courier charges: $300

Despatch: $400

 

Output

Admin charges: $500

Courier charges: $500  <-- Combined total

Despatch: $400

 

What I have:

I have an array of related quote IDs. I also have a $mod date, because prices of quoted items change, and we have to see older quotes exactly as they were. Below is an example.

$quotes[0]['id'] = 1;
$quotes[0]['mod'] = '2011-01-01';
$quotes[1]['id'] = 2;
$quotes[1]['mod'] = '2011-01-05';

 

I have the MySQL query to correctly extract the data for one quote. It works fine.

$query = 'SELECT d.item_id, price, jobrep_name AS sec_name, i.name AS name FROM quote_details d
LEFT JOIN quotes_section s ON d.sec_id = s.id
LEFT JOIN quote_items i ON d.item_id = i.item_id
WHERE quote_id=\''.$q['id'].'\' AND ( (date_start<="'.$q['mod'].'" AND (date_end>="'.$q['mod'].'" OR date_end="0000-00-00")) OR
(date_start IS NULL AND date_end IS NULL) ) ORDER BY s.sort, i.sort';

 

I then placed the MySQL query in a PHP loop. This also works fine.

foreach ($quotes as $q) {
// $query (as above) is here
if ($data = $db->read($query))	// class to connect to & read from MySQL
	$qdata[] = $data;
}

 

My results from above:

$qdata[0][0][item_id] = 'admin';
$qdata[0][0][price] = 500;
$qdata[0][0][sec_name] = 'Administration';
$qdata[0][0][name] = 'Administrative charges';

$qdata[0][1][item_id] = 'courier';
$qdata[0][1][price] = 200;
$qdata[0][1][sec_name] = 'Administration';
$qdata[0][1][name] = 'Courier charges';

$qdata[1][0][item_id] = 'courier';
$qdata[1][0][price] = 300;
$qdata[1][0][sec_name] = 'Administration';
$qdata[1][0][name] = 'Courier charges';

$qdata[1][1][item_id] = 'despatch';
$qdata[1][1][price] = 400;
$qdata[1][1][sec_name] = 'Administration';
$qdata[1][1][name] = 'Despatch';

 

What I need:

I need to GROUP the items together such that they appear as below.

$data[0][item_id] = 'admin';
$data[0][price] = 500;
$data[0][sec_name] = 'Administration';
$data[0][name] = 'Administrative charges';

$data[1][item_id] = 'courier';
$data[1][price] = 500;			// see the subtotal?
$data[1][sec_name] = 'Administration';
$data[1][name] = 'Courier charges';

$data[2][item_id] = 'despatch';
$data[2][price] = 400;
$data[2][sec_name] = 'Administration';
$data[2][name] = 'Despatch';

 

The trouble is that since each ID has a different mod date, is this even possible? I'm also having a hard time wrapping my brain around the GROUP BY to accomplish this. Any advice much appreciated!

 

EDIT: And just to make life harder, each quote also has a $currency. Quotes with foreign currency need to be converted back into the local currency before any SUM happens. The conversion rate is also somewhere in a MySQL table, and the exact rate is again dependent on the $mod date.

[mikosiko]

I will like to see your tables schema (structure) for better advice....

 

for now... just a few thoughts:

- I don't see the reason behind your array of quotes... if well designed, your quote model should hold the supporting information (the $mod date per example).

 

- Don't see either the necessity for your foreach loop when you just can make 1 single select to recover all the information for all the quotes (again with a well designed model...)

 

- Not clear to which table the column "price" belong, but most likely it should be in a "product-price-validdate" kind of table instead of in the quote (or quote details) itself.

 

- I have also the impression that the "quotes" table should be included in the SELECT that you shown (probably adding the field quote_id and mod_date to the list of selected field, eliminating completely the quote array usage), and allowing you to GROUP BY the quote_id

 

only knowing your complete relevant model and the supporting code will allow the forum members to give you better advice.. but imho go forward with that arrays structure doesn't make too much sense (at least to me).

[gin]

Hi mikosiko, below is my table schema. I will appreciate any insight given.

 

These first two tables consist of actual quote data. The `quotes` tables has the 1-per-quote data, such as the quote ID & comments. The `quote_details` table contains the items in the quote. There will be multiple lines of details, linked to a quote via the quote_id.

CREATE TABLE `quotes` (
  `id` varchar(7) NOT NULL,
  `username` varchar(15) NOT NULL,
  `modified` datetime NOT NULL,
  `ptitle` varchar(50) NOT NULL,
  `qtype` tinyint(1) NOT NULL,
  `letter_id` smallint(4) NOT NULL,
  `usd_conv` float NOT NULL,
  `foreign_per` smallint(3) NOT NULL,
  `external_per` smallint(3) NOT NULL,
  `extprod_per` smallint(3) NOT NULL,
  `total` int(9) NOT NULL,
  `package` int(9) NOT NULL,
  `gtotal` int(9) NOT NULL,
  `gpackage` int(9) NOT NULL,
  `comments` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

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

CREATE TABLE `quote_details` (
  `quote_id` varchar(7) NOT NULL,
  `item_id` varchar(20) NOT NULL,
  `sec_id` varchar( NOT NULL,
  `desc` varchar(100) NOT NULL,
  `price` int(9) NOT NULL,
  `quant` float NOT NULL,
  PRIMARY KEY (`quote_id`,`item_id`,`sec_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

Next are the setup tables. These are used when setting up a blank quote form for a user to fill in.

 

The `quotes_default` contains details, like the conversion rate, foreign percentages, etc. that apply to the quote at the time the quote is made. They can be overwritten by the user (and the new value saved in the `quotes` table), but often they are not (and therefore that column in the `quotes` table is left blank). The defaults are changed periodically, so which set of defaults apply to a quote depends on when a quote was last modified (`quotes`.mod).

CREATE TABLE `quotes_default` (
  `date` date NOT NULL,
  `usd_conv` float NOT NULL,
  `foreign_per` smallint(3) NOT NULL,
  `external_per` smallint(3) NOT NULL,
  `extprod_per` smallint(3) NOT NULL,
  `terms` varchar(1500) NOT NULL,
  PRIMARY KEY (`date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

The items that can be quoted are separated into 11 sections. The only reason there are separate item & section tables is due to the possibility of a section being renamed and/or re-sorted (as in display order). The jobrep_name column is for job reports, which have different names for each section (and different items in each section, yegads).

CREATE TABLE `quotes_section` (
  `id` varchar( NOT NULL,
  `name` varchar(25) NOT NULL,
  `sort` tinyint(2) NOT NULL,
  `jobrep_name` varchar(14) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

There are >100 items that a user can quote, each with a default rate. Occasionally, the rates are updated, or the item removed, which is where the date_start and date_end come into it -- I put a date_end on the outdated info, then enter a insert row with updated info. We've also got 4 different types of quotes, which display different sets of items, which is the reason for the boolean type* fields.

CREATE TABLE `quote_items` (
  `id` int(9) unsigned NOT NULL AUTO_INCREMENT,
  `sort` tinyint(3) unsigned NOT NULL,
  `date_start` date NOT NULL,
  `date_end` date NOT NULL,
  `item_id` varchar(20) NOT NULL,
  `sec_id` varchar( NOT NULL,
  `type1` tinyint(1) NOT NULL,
  `type2` tinyint(1) NOT NULL,
  `type3` tinyint(1) NOT NULL,
  `type4` tinyint(1) NOT NULL,
  `name` varchar(255) NOT NULL,
  `rate` mediumint(7) NOT NULL,
  `cost` decimal(9,2) NOT NULL,
  `unit` varchar(10) NOT NULL,
  `ts_map` varchar(14) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

 

How I got that query (in the top post)

At it's most basic, I have to pull from the `quotes` and `quote_details` (joined by quotes.id) tables to get all the user-entered data. I also have to check `quotes_default` (joined by quotes.mod date) for default conversion rates, etc. The `quotes_section`(joined by quote_details.sec_id) and `quote_items` (joined by quote_details.item_id) tables are pulled purely for display purposes; the order in which the items are displayed, and the full name of the section and item - which has changed in the past, even when the rates didn't change. There's no choice in this. Users need to view a quote exactly as it was sent to a client, even years later.

 

Looking forward to any comments.

[mikosiko]

I just read the previous topic, and there, in Reply #7 PFMABismad gave you a good advice.... just follow it.... try the suggested modified select with Group by and SUM in phpmyadmin or whatever tool you use to validate your select and work from there

[gin]

Progress! Some, anyway.

 

I've managed the GROUP BY, and now I'm now trying to combine one query to another.

 

This is a working query

SELECT d.item_id, price FROM quote_details d
LEFT JOIN quotes_section s ON d.sec_id = s.id
LEFT JOIN quote_items i ON d.item_id = i.item_id
LEFT JOIN quotes q ON d.quote_id = q.id
WHERE quote_id="12-3" AND ( (date_start<=DATE(modified) AND (date_end>=DATE(modified) OR date_end="0000-00-00")) OR
(date_start IS NULL AND date_end IS NULL) )
ORDER BY s.sort, i.sort

Output:
=============
$data[0][item_id] = 'admin';
$data[0][price] = 500;

 

I want to add the below to the above somehow. The $mod variable you see is the same as the DATE(modified) from the query above.

SELECT usd_conv FROM quotes_default WHERE date<="'.$mod.'" ORDER BY date DESC LIMIT 1

 

I've tried the following, but it gives me additional sets of data. The trouble is that the second query is limited by LIMIT 1, and I don't know how to cobble that in with the JOIN.

SELECT q.id, d.item_id, price, def.usd_conv FROM quote_details d
LEFT JOIN quotes_section s ON d.sec_id = s.id
LEFT JOIN quote_items i ON d.item_id = i.item_id
LEFT JOIN quotes q ON d.quote_id = q.id
LEFT JOIN quotes_default def ON def.date <= DATE(modified)     // <-- New addition here
WHERE quote_id="12-3" AND ( (date_start<=DATE(modified) AND (date_end>=DATE(modified) OR date_end="0000-00-00")) OR
(date_start IS NULL AND date_end IS NULL) )
ORDER BY s.sort, i.sort

Output:
=============
$data[0][item_id] = 'admin';
$data[0][price] = 500;
$data[0][usd_conv] = 2.9;

$data[1][item_id] = 'admin';     // <-- I don't want this set of data!
$data[1][price] = 500;
$data[1][usd_conv] = 2.8;

[fenway]

Sorry, I don't follow.

[gin]

Okay, looking at post #4 above, the first query only selects the item_id & price fields. I also want to select the usd_conv field (as in the second query); however, this field necessitates JOINing in another table. Using the second query as an example, I only want the usd_conv "WHERE date <= DATE(modified)"; however, there are lots of results, and I only wanted  the latest one so I did a "ORDER BY date DESC LIMIT 1".

 

So now I'm trying to combine the two, but I don't know how to get the latest usd_conv. That means I get more than one set of data in my third query. This is because there is more than 1 usd_conv when applying "WHERE date <= DATE(modified)". What I really need is "WHERE date <= DATE(modified), but just the latest one", but I don't know how.

 

Hoping that makes sense.

[fenway]

Ah, I see -- you'll need to use a GROUP BY to find the set you want, and then join back.  LIMIT 1 was an easy workaround that limits functionality.

[gin]

Thanks for all your help, I've finally gotten it!

 

For other people who might have similar problems, here's my solution:

SELECT q.id, d.item_id, price, def.usd_conv FROM quote_details d
LEFT JOIN quotes_section s ON d.sec_id = s.id
LEFT JOIN quote_items i ON d.item_id = i.item_id
LEFT JOIN quotes q ON d.quote_id = q.id
LEFT JOIN (SELECT * FROM quotes_default ORDER BY date DESC) def ON def.date <= DATE(modified)
WHERE quote_id="12-3" AND ( (date_start<=DATE(modified) AND (date_end>=DATE(modified) OR date_end="0000-00-00")) OR
(date_start IS NULL AND date_end IS NULL) )
GROUP BY item_id
ORDER BY s.sort, i.sort

 

There's a nested query on the 5th line, which ensures I'm using the latest data, as otherwise the GROUP BY would just pickup the first (ie. oldest) row.