...

View Full Version : Order primary query by secondary query?



q1h
01-09-2012, 02:11 AM
Hi, I have the following query set up:


$query1 = mysql_query("SELECT category FROM table GROUP BY category ORDER BY date ASC");

while ( $data1 = mysql_fetch_array( $query1 ) ) {

$category = $data1['category'];

echo '<p>' . $category . '</p>';

$query2 = mysql_query("SELECT * FROM table WHERE category = '$category' ORDER BY date ASC");

while( $data2 = mysql_fetch_array( $query2 ) ) {

echo '<p>' . $data2['data'] . '</p>';

}

}

I'm grouping my data into categories and then sorting by a date (soonest first, using strtotime). My issue is that the sub-query sorts correctly, but I want the primary query to show the category of whichever date is earliest first. Right now, it's sorting the categories alphabetically. Do I need some kind of date average? Thanks ...

q1h
01-09-2012, 02:30 AM
What?

Old Pedant
01-09-2012, 04:41 AM
Well, first of all, you shouldn't be using two queries.

Try to never never never do one query inside a loop on another query. You are killing your performance, possibly by an order of magnitude or more.

So...



SELECT SQ.category, SQ.firstDate, T.list, T.of, T.fields
FROM ( SELECT category, MIN(`date`) AS firstDate
FROM table
GROUP BY category ) AS SQ,
table AS T
WHERE T.category = SQ.category
ORDER BY SQ.firstDate ASC, T.`date` ASC


(1) Never use SELECT *, especially when working with multiple tables (even when one is a pseudo-table created by a subquery, as here). It's not really a good idea to ever user SELECT *, but it's especially bad when using multiple tables.

(2) date is a builtin function in MySQL (and in many other DBs). To ensure you don't confuse your field named date with the function, put back ticks (`...` -- the character that shares a key witn ~ ) around the name.

Old Pedant
01-09-2012, 04:48 AM
Oh, and I don't code in PHP, but you would then use that single query something like this:


$sql = "SELECT SQ.category, SQ.firstDate, T.list, T.of, T.fields
FROM ( SELECT category, MIN(`date`) AS firstDate
FROM table
GROUP BY category ) AS SQ,
table AS T
WHERE T.category = SQ.category
ORDER BY SQ.firstDate ASC, T.`date` ASC";

$query = mysql_query( $sql ) or die( mysql_error );

$categpry = "";
while ( $data = mysql_fetch_array( $query ) )
{
$cur_category = $data["category"];
if ( $cur_category != $category )
{
echo "<h3>" . $cur_category . "</h3>\n";
$category = $cur_category
}
echo "<p>" . $data["...whatever field(s)..."] . "</p>";
}


As I said, I don't use PHP, so use that only as a guideline. The "trick" is to monitor the category field and output a new category only when it changes.

q1h
01-09-2012, 05:59 AM
Thanks, Old Pedant, that worked!

One thing, though - I was trying to keep the category dependencies together and simply show the block of whichever one contained the earliest date first. I tried a couple variations of your code, but I can't seem to get the dependencies together ...

Old Pedant
01-09-2012, 06:29 AM
Sorry, no idea what you mean by "category dependencies."

The only dependencies I saw in your original code was that you wanted all whatever-they-are that are in the same category grouped together.

If you mean you have subcategories, then probably all you need to do is put them into your ORDER BY clause in place of using `date`.

q1h
01-09-2012, 04:02 PM
I mean that I'm trying to get all the data from one category to display together under the category heading, with the category headings sorted by date, and the data itself sorted by date. Thanks ...

Old Pedant
01-09-2012, 08:55 PM
Well, so far as I can tell--without having your database in front of me--that query *should* be doing exactly that.

Here is some sample data:


+----------+---------------------+--------------------------+
| category | date | data |
+----------+---------------------+--------------------------+
| animals | 2011-01-01 00:00:00 | aardvarks come first |
| people | 2010-12-01 00:00:00 | adam came first |
| animals | 2011-01-15 00:00:00 | koalas are in the middle |
| people | 2011-06-01 00:00:00 | old pedant is still here |
| animals | 2011-02-02 00:00:00 | zebras come last |
+----------+---------------------+--------------------------+

and this is what I get from that query:


+----------+---------------------+---------------------+--------------------------+
| category | firstDate | date | data |
+----------+---------------------+---------------------+--------------------------+
| people | 2010-12-01 00:00:00 | 2010-12-01 00:00:00 | adam came first |
| people | 2010-12-01 00:00:00 | 2011-06-01 00:00:00 | old pedant is still here |
| animals | 2011-01-01 00:00:00 | 2011-01-01 00:00:00 | aardvarks come first |
| animals | 2011-01-01 00:00:00 | 2011-01-15 00:00:00 | koalas are in the middle |
| animals | 2011-01-01 00:00:00 | 2011-02-02 00:00:00 | zebras come last |
+----------+---------------------+---------------------+--------------------------+

which sure seems to me to be what you asked for.

What am I missing?

q1h
01-12-2012, 01:43 AM
I think it must have been my PHP that messed up the results - the raw mysql data looks like what you have. Thanks for all your help!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum