group by

[SkyRanger]

I am trying to figure out how to do a group by location and only show 1 location per group.

 

 

example:

 

LocationName

Item1

Item2

Item3

 

Location2

item1

item2

item3

 

etc.

 

 

This is what I have so far:

 

$query = "SELECT  * FROM `cinvent` where `oname`='$client' group by 'iroom'";
$result = $mysqli->query($query) or die($mysqli->error.__LINE__);

while($row = $result->fetch_assoc()){
?>            
            <table align="center" style="width: 95%">
 <tr>
  <td colspan="5"><b><u><?php  echo [b]Locationname - not sure what to put here yet[/b] ?></u></b></td>
 </tr>
 <tr border="1">
  <td class="auto-style3" style="width: 50; height: 50">
  <div class="thumbnail-item">
 <a href="#"><img src="images/camera.png" class="thumbnail"/></a>
 <div class="tooltip">
  <img src="members/<?php echo $loggedin; ?>/inventory/<?php echo $row['pic']; ?>" alt="" width="200" />
  <span class="overlay"></span>
 </div> 
</div>
  </td>
  <td class="auto-style3"><?php echo $row['iname']; ?></td>
  <td class="auto-style3"><?php echo $row['bname']; ?></td>
  <td class="auto-style3"><?php echo $row['iserial']; ?></td>
  <td class="auto-style3"><?php echo $row['evalue']; ?></td>
 </tr>
   </table>
<?php
}
?>

[kicken]

If you want to display all the rows from the database, but have them display in a grouped format then using 'GROUP BY' in the sql query is not the proper method.  What you need to do is group them from within the PHP either by sorting them into array groups then outputting that array, or tracking the last location and only display that part when it is changed.

 

For example:

$sql = 'SELECT locationName, itemName FROM items ORDER BY locationName';
$results = $db->query($sql);

$lastLocation=null;
echo '<ul>';
while ($row=$results->fetch()){
    if ($lastLocation !== $row['locationName']){
       if ($lastLocation !== null){ echo '</ul></li>'; }
       echo '<li>'.$row['locationName'].'<ul>';
    }
    echo '<li>'.$row['itemName'].'</li>';
}
echo '</ul></li></ul>';

 

[SkyRanger]

Perfect, that is what I need. Thank you. Is there a way to do this in the html method that I have or will i have to do it in the <li>? Or can I just change the <ul> and <li> to <tr> and <td> or will this not work?

[kicken]

You can use whatever HTML you want to create the grouping structure, just figure out what you need to output and when you need to do it.  Then integrate that into the proper spots in the PHP code.

 

[SkyRanger]

Thank Kicken

[SkyRanger]

ok, so i put in the script. but for some reason it will only output 1 row. Can anybody see where the problem is because I can't

 

$query = "SELECT  * FROM `cinvent` where `oname`='$client' group by 'iroom'";
$results = $mysqli->query($query) or die($mysqli->error.__LINE__);

?>            
            <table align="center" style="width: 700px">
            <?php
$lastLocation=null;
echo '<tr>';
while ($row=$results->fetch_assoc()){
   if ($lastLocation !== $row['iroom']){
      if ($lastLocation !== null){ echo '</u></b></td>'; }
      echo '<td colspan=\"5\"><b><u>'.$row['iroom'].'</b></td></tr>';
   }             ?>

   <tr border="1">
  <td class="auto-style5" style="width: 50; height: 50">
  <div class="thumbnail-item"><a href="#"><img border="0" src="images/camera.png" class="thumbnail"/></a>
 <div class="tooltip">
  <img src="members/<?php echo $loggedin; ?>/inventory/<?php echo $row['pic']; ?>" alt="" width="200" />
  <span class="overlay"></span>
 </div> 
</div>
  </td>
  <td class="auto-style5"><?php echo $row['iname']; ?></td>
  <td class="auto-style5"><?php echo $row['bname']; ?></td>
  <td class="auto-style5"><?php echo $row['iserial']; ?></td>
  <td class="auto-style5"><?php echo $row['evalue']; ?></td>
 </tr>
 <?php
}
?>
   </table>

[SkyRanger]

Anybody. I go over this script run echos on query but still nothing, I am stumped

[PFMaBiSmAd]

The code needs to save the new $row['iroom'] value into $lastLocation so that just a change in the value will result in closing the previous section/starting a new section -

$lastLocation = $row['iroom'];

[SkyRanger]

Thanks PFMaBiSmAd. Unfortionatly I consider myself to be still a real noob to php. Would I just have to change:

 

$lastLocation = null;
to
$lastLocation = $row['iroom'];

 

If so I changed that with no change to the output. Still only displays 1 row.

[kicken]

No, you'd change this part:

 

if ($lastLocation !== $row['iroom']){
   if ($lastLocation !== null){ echo '</u></b></td>'; }
   echo '<td colspan=\"5\"><b><u>'.$row['iroom'].'</b></td></tr>';
   }

 

To include that line, like so:

if ($lastLocation !== $row['iroom']){
   if ($lastLocation !== null){ echo '</u></b></td>'; }
   echo '<td colspan=\"5\"><b><u>'.$row['iroom'].'</b></td></tr>';
   $lastLocation = $row['iroom'];
}

 

I forgot that step in my example.  Whenever the location changes, you need to record the new one so that in future loops it will know when it changes again.

 

 

[SkyRanger]

No, that didn't work for some reason. Still same problem.

[SkyRanger]

bump - anybody have an idea of what the problem is. It is still only showing 1 row

[PFMaBiSmAd]

You still have a GROUP BY term in your query, so of course you only get one consolidated row. That's the opposite of what kicken stated and showed in his example query statement -

 

using 'GROUP BY' in the sql query is not the proper method.

[SkyRanger]

Thank you PFMaBiSmAd. I honestly over looked that. Fixed the problem by removing grouped by and changed it to order by and it works perfectly. Thank you and thank you Kicken for your awesome help.

[SkyRanger]

Had to mark this as unsolved. Not sure what is going on here but each entry comes out on a new line ex:

 

Sorry screwed this part up

Title1

Item1

Title2

item1

Title1

item2

 

Title# = iroom

 

Any idea of why this is going this?

 

$mysqli = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);

if (mysqli_connect_errno())
{
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}

$client = $session->username;

$query = "SELECT * FROM `cinvent` where `oname`='$client' order by 'iroom'";
$results = $mysqli->query($query) or die($mysqli->error.__LINE__);


?>
<table align="center" style="width: 700px">
<?php
$lastLocation=null;
echo '<tr>';
while ($row=$results->fetch_assoc()){
if ($lastLocation !== $row['iroom']){
if ($lastLocation !== null){ echo '</u></b></td>'; }
echo '<td colspan=\"5\"><b><u>'.$row['iroom'].'</b></td></tr>';
$lastLocation = $row['iroom'];
} ?>

			<tr border="1">
					<td class="auto-style5"><?php echo $row['iname']; ?></td>
					<td class="auto-style5"><?php echo $row['bname']; ?></td>
					<td class="auto-style5"><?php echo $row['iserial']; ?></td>
					<td class="auto-style5"><?php echo $row['evalue']; ?></td>
				</tr>
				<?php
}
?>


			</table>

Edited January 28, 2013 by SkyRanger

[SkyRanger]

Anybody have an Idea of my my output is not coming out properly. ie: grouping the iroom rows together.

[kicken]

One thing I can tell you is that your code generates invalid HTML. You should fix that and see if you still have a problem.

 

Given

iroom     |    iname    
-------------------------
Room 1   |    Name 1
Room 1   |    Name 2
Room 2   |    Name 1
Room 2   |    Name 2

 

You would end up with HTML looking like:

<table align="center" style="width: 700px">
       <tr>
               <td colspan="5">
                       <b><u>Room 1</b>
               </td>
       </tr>
       <tr border="1">
               <td class="auto-style5">Name 1</td>
       </tr>
       <tr border="1">
               <td class="auto-style5">Name 2</td>
       </tr>

       </u></b></td>
               <td colspan="5">
                       <b><u>Room 2</b>
               </td>
       </tr>
       <tr border="1">
               <td class="auto-style5">Name 1</td>
       </tr>
       <tr border="1">
               <td class="auto-style5">Name 2</td>
       </tr>
</table>

[SkyRanger]

Yeah cleaned up the html code but still having the same problem were it is not grouping the iroom

 

Room1

Item 1

Room2

Item1

Room1

item2

Room2

item2

 

$query = "SELECT  * FROM `cinvent` where `oname`='$client' order by 'iroom'";
$results = $mysqli->query($query) or die($mysqli->error.__LINE__);



?>            
            <table align="center" style="width: 700px">
            <?php
$lastLocation=null;
echo '<tr>';
while ($row=$results->fetch_assoc()){
if ($lastLocation !== $row['iroom']){
  if ($lastLocation !== null){ echo '<tr>'; }
  echo '<td colspan=\"4\"><b><u>'.$row['iroom'].'</u></b></td></tr>';
  $lastLocation = $row['iroom'];
}            ?>

   <tr border="1">
  <td class="auto-style5"><?php echo $row['iname']; ?></td>
  <td class="auto-style5"><?php echo $row['bname']; ?></td>
  <td class="auto-style5"><?php echo $row['iserial']; ?></td>
  <td class="auto-style5"><?php echo $row['evalue']; ?></td>
 </tr>
 <?php
}
?>



   </table>

Edited January 29, 2013 by SkyRanger

[kicken]

Check your query results, make sure it is ordered'd correctly.  The code depends on all the room's of the same type being together in the result-set.  The sample output you're posting shows them alternating.

 

edit: Just noticed:

... order by 'iroom'";

 

iroom should not be in quotes.

 

Edited January 29, 2013 by kicken

[SkyRanger]

Thanks kicken, removed the quotes and it worked perfectly.

[MCD]

You Guys Rock .... I spent two weeks trying to get a straight and simple answer for an identical problem, that everyone I asked tried to answer with a different solution and some extra variant. They apparently didn't grasp that non-techie people, particularly designers are image/layout oriented. I don't expect an IT guy to do a drawing like Picasso for me but if he asks me to make one, I will. So why when I ask many IT people a qusetion needing very simple formatting (understanably limited by certain code language structures) do I not get a straight forward solution?

 

If code samples are posted (that are regular requirements on many web sites) with their actual expected output, and respondents understand the layout needs in web page graphics, then it makes it so much easier for those who have to stretch their skills to be able to get a working solution. I even think if you organized the site/list into a body that charged an arbitary amount for a simple set of cut-n-paste codes it would go a long way to being very saleable and wet the appetites of novices and newcomers to learn more, otherwise frustration and depression can make you walk away from a solution.

 

Spot on with this thread, even though other purists may argue that there are better coding solutions - but it works!

 

<?php freaks

$Thanks to all who contributed ==->toSolution

Echo . '[YOU ROCK]' .

?>