MySQL GROUP BY

If I have a MySQL Query like:

SELECT * FROM Table WHERE field!=NULL GROUP BY FIELD;

And if my returns are:
1,1,1,1 2,2,2,2,2,3,3,5,5,7,7,7,7
What do I have to do to add a break and and echo between each GROUP?
Does that make sense?  I'm not really sure how to ask the question.
LVL 8
rgranlundAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
No, it does not make sense because you will not get that from MySQL.  You will get a row at a time.  If you are seeing that it is because you are formatting it that way.

However, you might try this:
$cfield = '';
/* associative and numeric array */
while($row = mysqli_fetch_array($result, MYSQLI_BOTH)) {
  if($cfield != $row['field']) {
    echo "<br>";
    $cfield = $row['field'];
    }
  echo $cfield;
  }

Open in new window

0
Chris StanyonWebDevCommented:
The results you've show don't look grouped to me. We'd need to see the data structure (and possibly data) as well as your correct SQL statement, but as Dave has said, when you group records, then you get one record per group, and you loop through the recordset exactly the same as normal. When you group records though, be aware that the resulting dataset might not make a lot of sense (if you select all fields and group on one).

Also, your query should probably look more like this:

SELECT field1, field2 FROM yourTable WHERE field1 IS NOT NULL GROUP BY field2

Open in new window

0
Ray PaseurCommented:
Please post your test data set, so we can load it into a table and run a query against the table, thanks.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

PortletPaulfreelancerCommented:
I suggest you post the actual code that includes the query.
In my experience if you attempt to summarize a query to make it easier for us, overall it will be harder to solve (and take more time)

I'm not suggesting 20,000 lines of unfamiliar code, but enough of what you use for us to inspect the issue (& please use the code button in the toolbar for code snippets).
0
Ray PaseurCommented:
@PortletPaul: Would you agree that what we need from @rgranlund is the SSCCE?
0
PortletPaulfreelancerCommented:
@Ray_Paseur: I most certainly agree.

>>I'm not really sure how to ask the question.
a "Short, Self Contained, Correct (Compilable), Example" (SCCE) will assist

Consider that the only facts we know about the problem are written on this page. Right now we have just these 2 facts:
SELECT * FROM Table WHERE field!=NULL GROUP BY FIELD;
returns are: 1,1,1,1 2,2,2,2,2,3,3,5,5,7,7,7,7
Given that SQL does not natively concatenate rows into comma separated strings there has to be some facts missing (and using "!=NULL" isn't correct).

A short extract of the related PHP code (that contains the query and handles the returned result) will undoubtedly help us provide a solution.
0
rgranlundAuthor Commented:
@ ET ALL, thank you for the information and further interest in helping me out.

The following MySQL query grabs names and dates from a DB.  What I would like to is add a break at each new day
<?php
	$sql->query("SELECT * FROM schedule WHERE YEARWEEK(schedule_date) = YEARWEEK('$requestDate')
      ORDER BY schedule_date ASC"); 
				$row = $sql->fetch_all();
				
	foreach($row as $r){
		echo $r['first_name'].' '.$r['date'].'<br />';
		}
?>

//  This is a var_dump sample

array(28) { [0]=> array(6) { ["first_name"]=> string(7) "Shannon" ["last_name"]=> string(6) "Chavez" ["date"]=> string(10) "2013-08-25"  } 
[1]=> array(6) { ["first_name"]=> string(6) "Coleen" ["date"]=> string(10) "2013-08-25" } 
[2]=> array(6) { ["first_name"]=> string(6) "Jacque" ["date"]=> string(10) "2013-08-25" } 
[3]=> array(6) { ["first_name"]=> string(7) "Theresa"  ["date"]=> string(10) "2013-08-26"  } 
[4]=> array(6) { ["first_name"]=> string(5) "James" ["date"]=> string(10) "2013-08-26"  } 
[5]=> array(6) { ["first_name"]=> string(7) "Gregory"  ["date"]=> string(10) "2013-08-26"  } 
[6]=> array(6) { ["first_name"]=> string(5) "Sonja"  ["date"]=> string(10) "2013-08-26" } 
[7]=> array(6) { ["first_name"]=> string(6) "Jacque" ["date"]=> string(10) "2013-08-26" } 
[8]=> array(6) { ["first_name"]=> string(4) "Jill" ["date"]=> string(10) "2013-08-27" } 
[9]=> array(6) { ["first_name"]=> string(7) "Theresa" ["date"]=> string(10) "2013-08-27" } 

//  SAMPLE RETURN

SUNDAY 2013-08-25
Sharron 
Coleen
Jacque


MONDAY 2013-08-26
Theresa
James
Gregory
Sonja
Jacque


TUESDAY 2013-08-27
Jill
Theresa

Open in new window


I want to add a break at each day and insert the Weekday and date ie... MONDAY, TUESDAY, etc...
0
Ray PaseurCommented:
See if this helps point it in the right direction.
http://www.laprbass.com/RAY_temp_rgranlund.php

<?php // RAY_temp_rgranlund.php
error_reporting(E_ALL);
echo '<pre>';


// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28232093.html#a39472217


// SOME NORMALIZED TEST DATA
$arr = array
( 0=> array( "first_name" =>  "Shannon", "date"=> "2013-08-25" )
, 1=> array( "first_name" =>  "Coleen",  "date"=> "2013-08-25" )
, 2=> array( "first_name" =>  "Jacque",  "date"=> "2013-08-25" )
, 3=> array( "first_name" =>  "Theresa", "date"=> "2013-08-26" )
, 4=> array( "first_name" =>  "James",   "date"=> "2013-08-26" )
, 5=> array( "first_name" =>  "Gregory", "date"=> "2013-08-26" )
, 6=> array( "first_name" =>  "Sonja",   "date"=> "2013-08-26" )
, 7=> array( "first_name" =>  "Jacque",  "date"=> "2013-08-26" )
, 8=> array( "first_name" =>  "Jill",    "date"=> "2013-08-27" )
, 9=> array( "first_name" =>  "Theresa", "date"=> "2013-08-27" )
)
;

// STARTING DATE
$old = '?';

// WITH EACH ROW OF THE RESULTS SET
foreach ($arr as $sub)
{
    // IF DATE CHANGES, CREATE A NEW OUTPUT ELEMENT
    if ($sub['date'] != $old)
    {
        echo PHP_EOL;
        echo '<b>' . $sub['date'] . '</b>';
        echo PHP_EOL;
        $old = $sub['date'];
    }
    echo $sub['date'];
    echo $sub['first_name'];
    echo PHP_EOL;
}

Open in new window

HTH, ~Ray
0
Ray PaseurCommented:
To get weekday from the ISO-8601 DATETIME string value, use strtotime() and date('D') like this on line 34:

<?php // RAY_temp_rgranlund.php
error_reporting(E_ALL);
echo '<pre>';


// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28232093.html#a39472217


// SOME NORMALIZED TEST DATA
$arr = array
( 0=> array( "first_name" =>  "Shannon", "date"=> "2013-08-25" )
, 1=> array( "first_name" =>  "Coleen",  "date"=> "2013-08-25" )
, 2=> array( "first_name" =>  "Jacque",  "date"=> "2013-08-25" )
, 3=> array( "first_name" =>  "Theresa", "date"=> "2013-08-26" )
, 4=> array( "first_name" =>  "James",   "date"=> "2013-08-26" )
, 5=> array( "first_name" =>  "Gregory", "date"=> "2013-08-26" )
, 6=> array( "first_name" =>  "Sonja",   "date"=> "2013-08-26" )
, 7=> array( "first_name" =>  "Jacque",  "date"=> "2013-08-26" )
, 8=> array( "first_name" =>  "Jill",    "date"=> "2013-08-27" )
, 9=> array( "first_name" =>  "Theresa", "date"=> "2013-08-27" )
)
;

// STARTING DATE
$old = '?';

// WITH EACH ROW OF THE RESULTS SET
foreach ($arr as $sub)
{
    // IF DATE CHANGES, CREATE A NEW OUTPUT ELEMENT
    if ($sub['date'] != $old)
    {
        echo PHP_EOL;
        echo '<b>' . date('D', strtotime($sub['date'])) . '</b>';
        echo PHP_EOL;
        $old = $sub['date'];
    }
    echo $sub['date'];
    echo $sub['first_name'];
    echo PHP_EOL;
}

Open in new window

Best regards, ~Ray
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.