| Group sort syntax? [message #169795] |
Mon, 27 September 2010 04:27  |
GarryJones
Messages: 21
Registered: September 2010
Karma: 0
|
Junior Member |
|
|
I have the following table from which I am reading with php.
ItemNumber, ItemName
349, bat
346, bat
346, bun
349, ball
417, hutch
350, jib
349, bridge
350, jig
346, ball
350, poll
350, numb
The Item numbers that someone wants to see are read into an array
called items.
If the array "items" contains is 346,349,350 (ie someone wants to see
everything except for item number 417
I need to be able to extract this data and group the items into a
table with table headings based on each value in the array
I think this gets the data for me
$sql = 'SELECT * FROM resmal
WHERE ItemNumber IN (' . implode(', ', $items) . ')';
$test=mysql_query($sql);
But how do I obtain this following result? I am unsure of the syntax.
I know how to write the html data but I need to be able to fetch the
data for each different item number in turn and group them,
ITEM NUMBER 346
bat
ball
bun
ITEM NUMBER 349
bat
ball
bridge
ITEM NUMBER 350
jib
jig
poll
numb
Any help Greatly appreciated
Garry Jones, Sweden
|
|
|
|
| Re: Group sort syntax? [message #169796 is a reply to message #169795] |
Mon, 27 September 2010 05:50  |
Denis McMahon
Messages: 634
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 27/09/10 05:27, GarryJones wrote:
> I have the following table from which I am reading with php.
>
> ItemNumber, ItemName
> 349, bat
> 346, bat
> 346, bun
> 349, ball
> 417, hutch
> 350, jib
> 349, bridge
> 350, jig
> 346, ball
> 350, poll
> 350, numb
>
> The Item numbers that someone wants to see are read into an array
> called items.
>
> If the array "items" contains is 346,349,350 (ie someone wants to see
> everything except for item number 417
>
> I need to be able to extract this data and group the items into a
> table with table headings based on each value in the array
>
> I think this gets the data for me
>
> $sql = 'SELECT * FROM resmal
> WHERE ItemNumber IN (' . implode(', ', $items) . ')';
> $test=mysql_query($sql);
>
> But how do I obtain this following result? I am unsure of the syntax.
> I know how to write the html data but I need to be able to fetch the
> data for each different item number in turn and group them,
perhaps add an 'ORDER BY ItemNumber,ItemName' clause to your select?
then, in your php, I think this (not tested):
<?php
$ItemNumber = -1; // assumes that this will never be -1 in database
$firsta = true;
$firstb = true;
echo "<p>";
while ($row = mysql_fetch_assoc($test)) {
if ($firsta) { // only want '<br>'s before elements
// other than the first one
$firsta = false;
}
else {
echo "<br>";
}
if ($ItemNumber != $row['ItemNumber']) // new number grouping
if ($firstb) { // only want '<br>'s before elements
// other than the first one
$firstb = false;
}
else {
echo "<br>";
}
$ItemNumber = $row['ItemNumber'];
echo "ITEM NUMBER {$row['ItemNumber']}<br>";
}
echo "{$row['ItemName']}";
}
echo </p>
?>
Rgds
Denis McMahon
|
|
|
|
| Re: Group sort syntax? [message #169800 is a reply to message #169796] |
Mon, 27 September 2010 10:09 |
Captain Paralytic
Messages: 204
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 27 Sep, 06:50, Denis McMahon <denis.m.f.mcma...@googlemail.com>
wrote:
> On 27/09/10 05:27, GarryJones wrote:
>
>
>
>
>
>> I have the following table from which I am reading with php.
>
>> ItemNumber, ItemName
>> 349, bat
>> 346, bat
>> 346, bun
>> 349, ball
>> 417, hutch
>> 350, jib
>> 349, bridge
>> 350, jig
>> 346, ball
>> 350, poll
>> 350, numb
>
>> The Item numbers that someone wants to see are read into an array
>> called items.
>
>> If the array "items" contains is 346,349,350 (ie someone wants to see
>> everything except for item number 417
>
>> I need to be able to extract this data and group the items into a
>> table with table headings based on each value in the array
>
>> I think this gets the data for me
>
>> $sql = 'SELECT * FROM resmal
>> WHERE ItemNumber IN (' . implode(', ', $items) . ')';
>> $test=mysql_query($sql);
>
>> But how do I obtain this following result? I am unsure of the syntax.
>> I know how to write the html data but I need to be able to fetch the
>> data for each different item number in turn and group them,
>
> perhaps add an 'ORDER BY ItemNumber,ItemName' clause to your select?
>
> then, in your php, I think this (not tested):
>
> <?php
> $ItemNumber = -1; // assumes that this will never be -1 in database
> $firsta = true;
> $firstb = true;
> echo "<p>";
> while ($row = mysql_fetch_assoc($test)) {
>
> if ($firsta) { // only want '<br>'s before elements
> // other than the first one
> $firsta = false;
> }
> else {
> echo "<br>";
> }
>
> if ($ItemNumber != $row['ItemNumber']) // new number grouping
>
> if ($firstb) { // only want '<br>'s before elements
> // other than the first one
> $firstb = false;
> }
> else {
> echo "<br>";
> }
>
> $ItemNumber = $row['ItemNumber'];
> echo "ITEM NUMBER {$row['ItemNumber']}<br>";
> }
> echo "{$row['ItemName']}";}
>
> echo </p>
> ?>
>
> Rgds
>
> Denis McMahon
Or simplified (and without the missing { - is that possible???)
<?php
$ItemNumber = '';
echo "<p>";
while ($row = mysql_fetch_object($test)) {
if (!$ItemNumber)
echo "<br>";
if ($ItemNumber != $row->ItemNumber) {
$ItemNumber = $row->ItemNumber;
echo "ITEM NUMBER {$row->ItemNumber}<br>";
}
echo "{$row->ItemName}";
}
echo </p>
?>
|
|
|
|
| Re: Group sort syntax? [message #169801 is a reply to message #169800] |
Mon, 27 September 2010 10:35 |
Denis McMahon
Messages: 634
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 27/09/10 11:09, Captain Paralytic wrote:
> On 27 Sep, 06:50, Denis McMahon <denis.m.f.mcma...@googlemail.com>
> wrote:
>> On 27/09/10 05:27, GarryJones wrote:
>>
>>
>>
>>
>>
>>> I have the following table from which I am reading with php.
>>
>>> ItemNumber, ItemName
>>> 349, bat
>>> 346, bat
>>> 346, bun
>>> 349, ball
>>> 417, hutch
>>> 350, jib
>>> 349, bridge
>>> 350, jig
>>> 346, ball
>>> 350, poll
>>> 350, numb
>>
>>> The Item numbers that someone wants to see are read into an array
>>> called items.
>>
>>> If the array "items" contains is 346,349,350 (ie someone wants to see
>>> everything except for item number 417
>>
>>> I need to be able to extract this data and group the items into a
>>> table with table headings based on each value in the array
>>
>>> I think this gets the data for me
>>
>>> $sql = 'SELECT * FROM resmal
>>> WHERE ItemNumber IN (' . implode(', ', $items) . ')';
>>> $test=mysql_query($sql);
>>
>>> But how do I obtain this following result? I am unsure of the syntax.
>>> I know how to write the html data but I need to be able to fetch the
>>> data for each different item number in turn and group them,
>>
>> perhaps add an 'ORDER BY ItemNumber,ItemName' clause to your select?
>>
>> then, in your php, I think this (not tested):
>>
>> <?php
>> $ItemNumber = -1; // assumes that this will never be -1 in database
>> $firsta = true;
>> $firstb = true;
>> echo "<p>";
>> while ($row = mysql_fetch_assoc($test)) {
>>
>> if ($firsta) { // only want '<br>'s before elements
>> // other than the first one
>> $firsta = false;
>> }
>> else {
>> echo "<br>";
>> }
>>
>> if ($ItemNumber != $row['ItemNumber']) { // new number grouping
Note to op - the missing '{' was the now inserted one in the above line!
>> if ($firstb) { // only want '<br>'s before elements
>> // other than the first one
>> $firstb = false;
>> }
>> else {
>> echo "<br>";
>> }
>>
>> $ItemNumber = $row['ItemNumber'];
>> echo "ITEM NUMBER {$row['ItemNumber']}<br>";
>> }
>> echo "{$row['ItemName']}";}
>>
>> echo </p>
>> ?>
>>
>> Rgds
>>
>> Denis McMahon
>
> Or simplified (and without the missing { - is that possible???)
>
> <?php
> $ItemNumber = '';
> echo "<p>";
> while ($row = mysql_fetch_object($test)) {
> if (!$ItemNumber)
> echo "<br>";
> if ($ItemNumber != $row->ItemNumber) {
> $ItemNumber = $row->ItemNumber;
> echo "ITEM NUMBER {$row->ItemNumber}<br>";
> }
> echo "{$row->ItemName}";
> }
> echo </p>
> ?>
Yeah, I was trying to prevent 2 spurious <br> before the first item,
which might make his list rather well spaced from the preceeding item.
Rgds
Denis McMahon
|
|
|
|
| Re: Group sort syntax? [message #169803 is a reply to message #169796] |
Mon, 27 September 2010 12:08 |
Denis McMahon
Messages: 634
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 27/09/10 06:50, Denis McMahon wrote:
If he wants to use ul / li for these items:
This code:
<?php
// -- data and usort cmp for testing starts here --
function cmp($a, $b)
{
if ($a['ItemNumber'] == $b['ItemNumber'] &&
$a['ItemName'] == $b['ItemName']) return 0;
if ($a['ItemNumber'] < $b['ItemNumber']) return -1;
if ($a['ItemNumber'] == $b['ItemNumber'] &&
$a['ItemName'] < $b['ItemName']) return -1;
return 1;
}
// data
$data[] = array('ItemNumber'=> 349, 'ItemName' => 'bat');
$data[] = array('ItemNumber'=> 346, 'ItemName' => 'bat');
$data[] = array('ItemNumber'=> 346, 'ItemName' => 'bun');
$data[] = array('ItemNumber'=> 349, 'ItemName' => 'ball');
$data[] = array('ItemNumber'=> 417, 'ItemName' => 'hutch');
$data[] = array('ItemNumber'=> 350, 'ItemName' => 'jib');
$data[] = array('ItemNumber'=> 349, 'ItemName' => 'bridge');
$data[] = array('ItemNumber'=> 350, 'ItemName' => 'jig');
$data[] = array('ItemNumber'=> 346, 'ItemName' => 'ball');
$data[] = array('ItemNumber'=> 350, 'ItemName' => 'poll');
$data[] = array('ItemNumber'=> 350, 'ItemName' => 'numb');
usort($data, "cmp");
// -- data and usort cmp for testing ends here --
// -- removed sql below for test --
/*
$sql = 'SELECT * FROM resmal
WHERE ItemNumber IN (' . implode(', ', $items) . ')
ORDER BY ItemNumber,ItemName';
$test=mysql_query($sql);
*/
// -- removed sql above for test --
$ItemNumber = -1; // assumes that this will never be -1 in database
echo "<ul>\n";
// while ($row = mysql_fetch_assoc($test)) { // -- removed while for testing
foreach ($data as $row) { // -- added foreach for testing
if ($ItemNumber != $row['ItemNumber']) { // new number grouping
if ($ItemNumber != -1) echo " </ul>\n </li>\n"; // not on
// first group, close last group sublist and containing li
$ItemNumber = $row['ItemNumber'];
echo " <li>ITEM NUMBER {$row['ItemNumber']}\n <ul>\n"; // new
// group and sublist
}
echo " <li>{$row['ItemName']}</li>\n"; // sublist member
}
echo " </li>\n</ul>\n"; // after last group
?>
generated this html:
<ul>
<li>ITEM NUMBER 346
<ul>
<li>ball</li>
<li>bat</li>
<li>bun</li>
</ul>
</li>
<li>ITEM NUMBER 349
<ul>
<li>ball</li>
<li>bat</li>
<li>bridge</li>
</ul>
</li>
<li>ITEM NUMBER 350
<ul>
<li>jib</li>
<li>jig</li>
<li>numb</li>
<li>poll</li>
</ul>
</li>
<li>ITEM NUMBER 417
<ul>
<li>hutch</li>
</li>
</ul>
To use this for your application, you probably need to:
(a) remove or comment all code between
// -- data and usort cmp for testing starts here --
and
// -- data and usort cmp for testing ends here --
(b) uncomment the code between
// -- removed sql below for test --
and
// -- removed sql above for test --
(c) remove the comment at the start of the line that has
// -- removed while for testing
(d) remove or comment the line that has
// -- added foreach for testing
(I couldn't be bothered to create and populate a database for the test,
so I loaded the data table into a 2d array and sorted it with usirt to
emulate the sql collation and sort.)
Rgds
Denis McMahon
|
|
|
|
| Re: Group sort syntax? [message #169805 is a reply to message #169803] |
Mon, 27 September 2010 12:28 |
Captain Paralytic
Messages: 204
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 27 Sep, 13:08, Denis McMahon <denis.m.f.mcma...@googlemail.com>
wrote:
> $ItemNumber = -1; // assumes that this will never be -1 in database
I'm not sure why you always use -1. Since items have an item number,
we can be sure that no item has no item number. So by changing this
line to:
$ItewmNumber = '';
we no longer have to rely on an assumption about what the item numbers
look like.
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]