Hello, I'm new here, but am really impressed with the positive attitude! I was wondering if someone could give me some help or point me in the right direction. I have a query that returns the following fields:
city
state
name
serial
category
using this code
[PHP]
$p = '';
$result = mysql_query("SE LECT city, state, title, serial, category FROM serials ORDER BY serial, category");
while($row = mysql_fetch_ass oc($result))
{
if($row['serial'] == $p)
{
echo "<br />";
$p = $row['serial'];
}else{
echo "<br />";
echo "<br />";
$p = $row['serial'];
}
echo $row["city"];
echo $row["state"];
echo $row["title"];
echo $row["serial"];
echo $row["category"];
}
?>
[/PHP]
This generates the following: -
Toronto Ontario Chef 12345 ABC
-
Detroit Michigan Cook 12345 DBF
-
-
Toronto Ontario Cook 12353 ABC
-
-
Los Angeles California Waiter 12355 DGG
-
-
NYC New York Janitor 12382 DGG
-
-
Miami Florida Cook 12777 FGH
-
Fort Worth Texas Usher 12777 QWE
-
-
Dallas Texas Cook 22222 FGH
-
-
NYC New York Chef 33333 JKL
-
Detroit Michigan Janitor 33333 DBF
-
I'm not too concerned about the formatting of the results right now.
What I would like to do is to group like results together. Naturally the <serialnum> values are grouped together and broken up by the double line break. What I would like to also group like 'categories' together with the <serialnum> groupings.
Ideally the data above would be represented like this: -
Toronto Ontario Chef 12345 ABC
-
Detroit Michigan Cook 12345 DBF
-
Toronto Ontario Cook 12353 ABC
-
Detroit Michigan Janitor 33333 DBF
-
NYC New York Chef 33333 JKL
-
-
Los Angeles California Waiter 12355 DGG
-
NYC New York Janitor 12382 DGG
-
-
Miami Florida Cook 12777 FGH
-
Fort Worth Texas Usher 12777 QWE
-
Dallas Texas Cook 22222 FGH
-
So because the <category> field on line 3 matches the <category> field of line 1, it is grouped with the first grouping. Likewise, although the <serial> field of line 4 doesn't match either of lines 1,2,3 the <category> field of line 4 matches that of line 2. Lastly, since the <serial> field of line 5 matches the <serial> field of line 4, it is grouped in the first grouping.
Does this make sense? Sorry, reading it back it doesn't read that well. Let me know if I can make any further clarification.
Most importantly, thanks in advance for any direction that you can provide!
Jake
9 1838 pbmods 5,821
Recognized Expert Expert
Heya, Jake. Welcome to TSDN!
Looking at this part of your code: -
if($row['serial'] == $p)
-
{
-
echo "<br />"; // Note: similar line in else...
-
$p = $row['serial']; // Note: similar line in else...
-
}
-
else
-
{
-
echo "<br />";
-
echo "<br />"; // Similar line 1
-
$p = $row['serial']; // Similar line 2
-
}
-
Notice that you're really only doing anything if the current value *doesn't* match the previous value. Have a look at this: -
$lastSerial = '';
-
$lastCat = '';
-
-
.
-
.
-
.
-
// Note that we always echo at least one <br />.
-
echo '<br />';
-
-
// If the current row s/b in a new section, add a <br />.
-
if(! (($row['serial'] == $lastSerial) || ($row['category'] == $lastCategory)))
-
{
-
echo '<br />';
-
}
-
-
$lastSerial = $row['serial'];
-
$lastCat = $row['cat'];
-
Thanks for the warm welcome pbmods!
That definitely cleaned up the code, thank you.
The main thing I'd like it to do is like to put the results into groups based on either variable, <serial> or <category>. So in the first 'text' example above, line 16 should be placed up in the first grouping because the category 'DBF' is already present in the first grouping (line 2).
Hmmm still not too sure I made my question very clear.
Thanks for the quick response, it's great here!
Jake
Hope this is allowed, but I just wanted to bump this and see if anyone had even heard of something like this being capable?
Thanks again!
Jake
jx2 228
New Member
well its exist solution for that :-)
but you actuly asked two questions pbmods answer you one of them
the other answer is here
it probably doesnt solve all problems but you get what you want to
look at "GROUP BY WITH ROLLUP"
pbmods 5,821
Recognized Expert Expert
Heya, Jake.
Bumping is allowed on this site. NB as a result, we are extra-hard on double-posting (posting the same problem twice in the same forum).
I'm not sure I understand entirely what you're looking for. If you look at the example you provided: -
Toronto Ontario Chef 12345 ABC
-
Detroit Michigan Cook 12345 DBF
-
Toronto Ontario Cook 12353 ABC
-
Detroit Michigan Janitor 33333 DBF
-
NYC New York Chef 33333 JKL
-
-
Los Angeles California Waiter 12355 DGG
-
NYC New York Janitor 12382 DGG
-
-
Miami Florida Cook 12777 FGH
-
Fort Worth Texas Usher 12777 QWE
-
Dallas Texas Cook 22222 FGH
-
There's a definite pattern here; between any two rows in the same section, either the serial number is the same, or the category is the same. It seems like (and correct me if I'm wrong) MySQL is returning the results in the proper order, so the logical way to check to see if we need to start a new section would be to compare the current row with the previous one and see if both the serial and the category are different.
Would there be a situation where at least one of the criteria matches, but we should start a new section anyway? And/or are there circumstances where even though neither the serial nor the category matches, but we should still keep the two rows in the same section?
jx2 - Thanks for your reply. As I understand the GROUP BY parameter (including GROUP BY WITH ROLLUP) it will only work if there is an aggregate function in the query. Since I want all lines to be echo'd I don't want to have an aggregate function.
pbmods - Yes and No, but mostly yes. :)
There's a definite pattern here; between any two rows in the same section, either the serial number is the same, or the category is the same.
Yes, thats exactly right. In the following example (as used previously) there are 3 groups, lets call them A, B, C. In group A line 4 doesn't have either the same serial or category as the line directly above it. It does however have the same category as another record from that that group, line 2. -
Toronto Ontario Chef 12345 ABC
-
Detroit Michigan Cook 12345 DBF
-
Toronto Ontario Cook 12353 ABC
-
Detroit Michigan Janitor 33333 DBF
-
NYC New York Chef 33333 JKL
-
-
Los Angeles California Waiter 12355 DGG
-
NYC New York Janitor 12382 DGG
-
-
Miami Florida Cook 12777 FGH
-
Fort Worth Texas Usher 12777 QWE
-
Dallas Texas Cook 22222 FGH
Similarly, in Group C, line 12 doesn't have the same serial or category as the line above it, but the category matches that of line 10.
It seems like (and correct me if I'm wrong) MySQL is returning the results in the proper order
Not quite. The results are set to ORDER BY serial, which means that they'll all be grouped together, but the category column is not grouped together.
If I just use the following code as I currently am I could get 5000 results, and while they're sorted by serial, result/line 126 could have the same category as result 3456. Because line 126 has the same category as line 3456 they should be grouped together.
[PHP]
$result = mysql_query("SE LECT city, state, title, serial, category FROM serials ORDER BY serial, category");
[/PHP]
Would there be a situation where at least one of the criteria matches, but we should start a new section anyway?
No.
And/or are there circumstances where even though neither the serial nor the category matches, but we should still keep the two rows in the same section?
No.
I just can't seem to wrap my head around how you'd group results based on either of two variables.
Thanks soooo much for your help so far!
pbmods 5,821
Recognized Expert Expert
Heya Jake.
Ok. I see what you're getting at now; I was mistaking the name column for the category column.
Possibly part of the misunderstandin g is that in MySQL, the phrase 'GROUP BY' has a slightly different meaning.
I think in your case, you'll need to manually parse your results and organize them that way: -
$_groups = array();
-
$_idx = array();
-
while($_row = mysql_fetch_assoc($_res))
-
{
-
if(isset($_idx['serial'][$_row['serial']]))
-
{
-
$_groups[$_idx['serial'][$_row['serial']]][] = $_row;
-
$_idx['category'][$_row['category']] = $_idx['serial'][$_row['serial']];
-
}
-
elseif(isset($_idx['category'][$_row['category']]))
-
{
-
$_groups[$_idx['category'][$_row['category']]][] = $_row;
-
$_idx['serial'][$_row['serial']] = $_idx['category'][$_row['category']];
-
}
-
else
-
{
-
$_groups[][] = $_row;
-
end($_groups);
-
$_idx['serial'][$_row['serial']] = key($_groups);
-
$_idx['category'][$_row['category']] = key($_groups);
-
}
-
}
-
-
foreach($_groups as $_entries)
-
{
-
foreach($_entries as $entry)
-
{
-
foreach($entry as $val)
-
{
-
echo "$val\t";
-
}
-
echo '<br />';
-
}
-
echo '<br />';
-
}
-
Phew! Ok, so what's going on here?
What we're doing is creating a big multi-dimensional array. As we go through, we check to see if we've already processed an entry with the same serial or category. If we have, we assign it to the same group as the last one. Otherwise, we create a new group and update our index.
Going back to everybody's favorite example: -
Toronto* * *Ontario* * *Chef* * 12345* ABC
-
Detroit* * *Michigan* * Cook* * 12345* DBF
-
Toronto* * *Ontario* * *Cook* * 12353* ABC
-
Detroit* * *Michigan* * Janitor 33333* DBF
-
NYC* * * * *New York* * Chef* * 33333* JKL
-
*
-
Los Angeles California* Waiter* 12355* DGG
-
NYC* * * * *New York* * Janitor 12382* DGG
-
*
-
Miami* * * *Florida* * *Cook* * 12777* FGH
-
Fort Worth* Texas* * * *Usher* *12777* QWE
-
Dallas* * * Texas* * * *Cook* * 22222* FGH
-
We end up with a $_groups that looks something like this (abbreviated for space): -
Array
-
(
-
[0] => Array
-
(
-
[0] => Array
-
(
-
[City] => Toronto
-
...
-
[Serial] => 12345
-
[Cat] => ABC
-
)
-
[1] => Array
-
(
-
[City] => Detroit
-
...
-
[Serial] => 12345
-
[Cat] => DBF
-
)
-
.
-
.
-
.
-
)
-
[1] => Array
-
(
-
[0] => Array
-
(
-
[City] => Los Angeles
-
...
-
[Serial] => 12355
-
[Cat] => DGG
-
)
-
[1] => Array
-
(
-
[City] => NYC
-
...
-
[Serial] => 12382
-
[Cat] => DGG
-
)
-
)
-
.
-
.
-
.
-
)
-
Once we've done all that hard work, the output part is rather trivial....
Yes Yes Yes!
Thank you sooo much pbmods! I've tested this code on a big db and it worked flawlessly. I'm going to spend some time and make sure I understand exactly whats going on.
Thanks so much for all your help, not only in this thread, but also in everyones threads!
Jake
pbmods 5,821
Recognized Expert Expert
Heya, Jake.
Glad to hear you got it working! Good luck with your project, and if you ever need anything, post back anytime :)
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Debbie Davis |
last post by:
Hi there,
SQL 2000
I have the following query:
SELECT sponsor, COUNT(sponsor) * 2 AS total FROM Referrals
GROUP BY sponsor
Works great, returns the sponsor and the total * 2 of their referrals
|
by: kristofera |
last post by:
I am trying to do a distinct grouping of some nodes sorted by a numeric
value but for some reason the distinct (preceding-sibling filter) is
applied to the result as if not sorted. If I don't use the
preceding-sibling filter, the nodes are properly sorted. Is this a bug
in the xslt processor I'm using (.net framework 1.1) or is this correct
behaviour?
Any alternative solutions that will show me the lowest priced item in
each category?...
|
by: Andreas Håkansson |
last post by:
Seeing how my previous post seem to have fallen between the
cracks, I thought I would have a second, more direct, go at it.
So my question is "Is it possible to group (Muenchian method)
over multiple nodes?" I will use an example to try to explain what
I need to do and what I have for data. The example might not be
very realistic but it's much easier than to try and explain using the
scenario I have =P
Suppose I had a list of...
|
by: ahaque38 |
last post by:
Hello.
Using A2K SP3, I am having the following problem with a report using
"Sorting and Grouping".
I have recently added a grouping in the reports for
"Category2<>'CONTRACTS'".
I have reports at the plan (overall totals), department and division
levels which have sorting and grouping implemented with this new
|
by: Mike MacSween |
last post by:
tblCourses one to many to tblEvents.
A course may have an intro workshop (a type of event), a mid course
workshop, a final exam. Or any combination. Or something different in the
future.
At the moment the printed output is usually going to Word. It's turning into
an unholy mess, because I'm having to prepare umpteen different Word
templates, and the queries that drive them, depending on what events a
course has.
| |
by: Gargamil |
last post by:
I've written a function to calculate a seriies of parameters based upon some
variables. I'd like to be able to return all the parameters to a query.
Now all the parameters are interrelated and cannot be calculated seperately.
I suppose the question is, can I return multiple results from a function to
a query? Or am I limited to duplicating the query multiple times and
returnig one parameter each time?
g
|
by: craig.buchinski |
last post by:
Ok, i have a Access Database (which is used with my ESRI Arcmap) and i
have written a query for a report to pull out our streetlights and
group them by billing wattage. The problem is that it is counting the
number of lights and summing them like i have asked, but in some cases
there are more then one row with a different sum but the same billing
wattage. Here is my SQL query. I'm trying to group by billing wattage
so all the unique...
|
by: Shiriah |
last post by:
I feel that this is a rather trivial concept that I cannot quite wrap my head around to make work in PHP. I consider myself an intermediate user in SQL (both MSSQL and MySQL) and somewhat novice in PHP, but I am well versed in programming in general between multiple languages (VB/Java/Python/ColdFusion). By saying this, I'm letting you know not to be afraid to use complex terminology / concepts.
Here is what I am attempting to do:
I have...
|
by: kevenj |
last post by:
Hello,
I'm a bit of a newcomer to PHP and am having an issue I can't overcome.
I've read some great posts on the issue of grouping results on this forum 1 and can happily display results under a common variable. The problem occurs as I want to display the common variable after the grouped results.
I'm attempting to write out the results of a query for journal articles in a citation format that lists authors before the journal title...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |