PHP - Help Me To Sort My List In Grouping

Hey everyone,

I have a database for projects and each entry has one or more project type category that it falls into. The values are separated with a semi-colon to make the distinction. I've used the following code to output the data onto the page:

Code: [Select]
$sql = "SELECT project_type FROM projects ORDER BY project_type";
$result = mysql_query($sql);


while ($rs = mysql_fetch_array($result)) {
$database_value = $rs['project_type'];
$array = explode(';', $database_value);

foreach($array as $key => $value){
echo " ".$value."<br />"; 
}


}
This outputs the following:

Code: [Select]
Agriculture
Agriculture
Agriculture
Agriculture
Agriculture
Agriculture
Agriculture
Agriculture
Export Development
Agriculture
Impact Assessments
Impact Studies
Agriculture
M & E
Agriculture
Private Sector Development
Corporate and Public Governance
Corporate and Public Governance
Corporate and Public Governance
In PHP, how can I group these values together, similar to SQL's GROUP BY method? I've tried grouping it in the query but some of the data goes lost because it must be exploded first.

Can anybody help?


Thanks in advance!

Karen

Often in the very beginning of index.php, I will define a bunch of constants.  To make sure I can quickly identify them as being one of my defined constants, I will often include some sort of prefix.

define ('xzy_somecontant',123);
define ('xzy_anothercontant',321);

Sometimes I have a bunch of constants that are related;

define ('xzy_id_for_page1',123);
define ('xzy_id_for_page2',231);
define ('xzy_id_for_page3',312);

It would be nice to somehow group them into say "xzy_page_ids", and then access them by some index such as "page1" or "1" (or whatever makes sense for the given naming structure).   Is this possible?  Is there another defacto way of doing so such as a static class or something?   Thanks

hi, i have a field for price that gets returned.

is there a way to do ORDER BY 'category' so that i can have tally at the bottom of each category that adds all the prices together?

Hello,   I was wondering if maybe someone could help me on this. I am having trouble with this query. I need to send an email to the users but group all the records related to that user instead of sending one email per record to the user. I hope this makes sense. Here is the code I have so far. All records are being displayed but how can I get all the records for each user (infant_specialist field) send one email and then go to the next user and send the other email and so on.   Thank you for your help

$conn = mysqli_connect('localhost','root','','dbname');

$sql = "SELECT case_no, client_name, infant_specialist.full_name, infant_specialist.is_email FROM ies_clients INNER JOIN infant_specialist ON ies_clients.infant_specialist = infant_specialist.is_id WHERE (date_closed IS NULL OR date_closed >=  DATE_FORMAT(NOW() ,'%Y-%m-01')) ORDER BY client_name ASC";
$q = mysqli_query($conn,$sql);

//echo mysqli_num_rows($q) . '<br/>';

while($row = mysqli_fetch_array($q) ){
	
	echo $row['client_name'] . ' --- ' , $row['full_name'] . ' --- ' . $row['is_email'] . '<br/>';
}

Edited by stivenbr, 07 October 2014 - 02:05 PM.

Hello all,

Need a little help with grouping and summing using an array. I can't do this directly from the database because the vendor package we are using (Tivoli Data Warehouse) stores date in a proprietary format as a CHAR in the database and in GMT so I have to get the data first and then manipulate the date to get it human readable and in the right EST date.

I have my query (and my date conversion) from the database returning the data in a format that looks like this:

The reason each date has the service repeated is because it was selected from the database by hour (needed to convert Tivoli's weird timestamp and use of GMT)

Service TX_Count Date
-------------------------------------------------------------------------------------------
Service1 23451 2010-01-01
Service1 93874 2010-01-01
Service1 82363 2010-01-01
Service1 56245 2010-01-02
Service1 73453 2010-01-02
Service1 18965 2010-01-02

I have successfully gotten the data in to an array and I can group by date and then sum the tx_count, or I can group the services and sum the tx_count.

What I really need to be able to do is to get the transaction counts by service by day so that I would get something like this as a result

Service1 2010-01-01 199688
Service1 2010-01-02 148663

So I need to group by service and then date and then sum it all out of the array.

Any advice and direction is appreciated

Can someone tell me the best way to group elements in an XML file?

Code: [Select]
<item>
             <title>Event 1</title>
             <dc:date>2010-09-25T01:00:00+01:00</dc:date>
             <dc:date>2010-10-02T01:00:00+01:00</dc:date>
             <dc:date>2010-10-09T01:00:00+01:00</dc:date>
</item>

<item>
             <title>Event 2</title>
             <dc:date>2010-09-25T01:00:00+01:00</dc:date>
             <dc:date>2010-10-02T01:00:00+01:00</dc:date>
             <dc:date>2010-10-09T01:00:00+01:00</dc:date>
</item>
Above is an example of what im trying to do and below is what i want the output to be

Code: [Select]

   25th September 2010

                   Event 1
                   Event 2

   2nd October 2010

                    Event 1
                    Event 2


And so on, can anyone point me in the right direction on where to start looking to do something like this in PHP.

Thanks

Garry

I have multiple arrays containing data for each entry.

What I am trying to do is group each item in the array by the date which is also in an array.

Where do I start??

Code: [Select]
array
  'pubDate' => string 'Mon, 15 Feb 10 00:00:00 +0000' (length=29)
  'title' =>
    object(SimpleXMLElement)[6]
  'description' =>
    object(SimpleXMLElement)[7]
  'guid' => string 'http://www.dailyinfo.co.uk/events.php?colname=Events&period=7&eventday=25&eventmonth=9&eventyear=2010#72741' (length=107)
  'link' => string 'http://www.dailyinfo.co.uk/events.php?colname=Events&period=7&eventday=25&eventmonth=9&eventyear=2010#72741' (length=107)

array
  'date' =>
    array
      0 => string '2010-09-25T01:00:00+01:00' (length=25)
      1 => string '2010-10-02T01:00:00+01:00' (length=25)

-------------------------------------------------------------------------------------------------------------------

array
  'pubDate' => string 'Fri, 26 Feb 10 00:00:00 +0000' (length=29)
  'title' =>
    object(SimpleXMLElement)[5]
  'description' =>
    object(SimpleXMLElement)[8]
  'guid' => string 'http://www.dailyinfo.co.uk/events.php?colname=Events&period=7&eventday=1&eventmonth=10&eventyear=2010#73266' (length=107)
  'link' => string 'http://www.dailyinfo.co.uk/events.php?colname=Events&period=7&eventday=1&eventmonth=10&eventyear=2010#73266' (length=107)

array
  'date' =>
    array
      0 => string '2010-09-25T01:00:00+01:00' (length=25)
      1 => string '2010-09-30T01:00:00+01:00' (length=25)

-------------------------------------------------------------------------------------------------------------------

How can i group the data by year to build the graph

I need the data to be like so

2012
0 - total customers
0 - total customers
0 - total customers
0 - total customers
0 - total customers
9 - total customers
5 - total customers
3 - total customers
5 - total customers

however i only have this data

Code: [Select]
total_customers month year
1 Aug 2011
9 Oct 2011
8 Nov 2011
4 Dec 2011
4 Jan 2012
so i need to make it show 0 for the months i dont have data for

Code: [Select]
<div id="dash_chart" class="portlet x9">

<div class="portlet-header">
<h4>Customer Growth</h4>

<ul class="portlet-tab-nav">
<li class="portlet-tab-nav-active"><a href="#tab1" rel="tooltip" title="Customers">Customers </a></li>
<li class=""><a href="#tab2" rel="tooltip" title="Sales over last 48 hours.">Sales </a></li>
</ul>
</div> <!-- .portlet-header -->

<div class="portlet-content">
<div id="tab1" class="portlet-tab-content portlet-tab-content-active">
               
                <?php 

echo '<table class="stats" title="area" width="100%" cellpadding="0" cellspacing="0">
<caption>Customer Base Growth</caption>
<thead>
                <tr>';

$monthNames = Array('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');

$i = 0;

foreach ($monthNames as $month){

echo '<th>'.$month.'</th>'."\n";

}

echo '</tr>
  </thead>';

                      $q = "SELECT count(cus_id) as total_customers, DATE_FORMAT(sign_date, '%b') as month, YEAR(sign_date) as year FROM customers 
  GROUP BY YEAR(sign_date), MONTH(sign_date) ORDER BY sign_date ASC";
$r = @mysqli_query ($dbc, $q);

$data = array();

if (mysqli_affected_rows($dbc) >= 1) {

echo '<tbody>
<tr>
   <th>2011</th>';

while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {


}

}

echo '</tr>';

?>
                            <tr>
<th>2012</th>
<td>3</td>
<td>4</td>
<td>2</td>
<td>0</td>
<td>0</td>
                                <td>0</td>
<td>0</td>
<td>3</td>
<td>5</td>
                                <td>3</td>
                                <td>9</td>
</tr>
</tbody>
</table>

grouping - i need to add an option where i can select ALL of the branches
-then my table will show all list of employees' leaves from all the branches..
-to make it easier to view all the leave details when i select ALL..
-instead of having to preview all the branches 1 by 1.
-do i need to modify my view on my database or just modify my codes..what codes should i use.
-Please tell me where and what do i need to change.



Code: [Select]
<h3><FORM name ="reportbutton" method="post" align="left" action="index.php?view=report">&nbsp;<strong>Leave Application Listing </strong>| &nbsp;<Input type = "Submit" Name = "Submit1" Class="button" VALUE = "Back To Report">&nbsp;&nbsp;</h3> </FORM>
<br><br>
<form name="leavebutton" method="post" action="index.php?view=report_leave">
<?
$b=$_GET['b'];
$m=$_GET['m'];
$y=$_GET['y'];

if  ($b <> '' && $m <> '' && $y <> '') {

$branch=$b;
$leavemonth=$m;
$leaveyear=$y;
}
else
{
$branch=$_POST['branch'];
$leavemonth=$_POST['leavemonth'];
$leaveyear=$_POST['leaveyear'];
}



$connection=mysql_connect("$server", "$username", "$password")
or die("Could not establish connection");
mysql_select_db($database_name, $connection)
or die ("Could not select database");


   
    $query="SELECT * from tblworkgroup";
    $result=mysql_query($query);?>


        &nbsp;&nbsp;Branch&nbsp;
        <select name="branch" >       
        <? while($row=mysql_fetch_array($result)) { ?>
    <option value= "<?=$row['WorkGroupID']?>" <? if ($branch==$row['WorkGroupID']){ echo 'selected'; } ?> ><?=$row['WorkGroupName']?></option>
        <? } ?>
        </select>
           </td>
          </tr>
       <br><br><br>
  &nbsp;&nbsp;Month &nbsp;&nbsp;
<select name="leavemonth" >
<option value = "1" <? if ($leavemonth==1){ echo 'selected'; } ?>>January</option>
<option value = "2" <? if ($leavemonth==2){ echo 'selected'; } ?>>February</option>
<option value = "3" <? if ($leavemonth==3){ echo 'selected'; } ?>>March</option>
<option value = "4" <? if ($leavemonth==4){ echo 'selected'; } ?>>April</option>
<option value = "5" <? if ($leavemonth==5){ echo 'selected'; } ?>>May</option>
<option value = "6" <? if ($leavemonth==6){ echo 'selected'; } ?>>June</option>
<option value = "7" <? if ($leavemonth==7){ echo 'selected'; } ?>>July</option>
<option value = "8" <? if ($leavemonth==8){ echo 'selected'; } ?>>August</option>
<option value = "9" <? if ($leavemonth==9){ echo 'selected'; } ?>>September</option>
<option value = "10" <? if ($leavemonth==10){ echo 'selected'; } ?>>October</option>
<option value = "11" <? if ($leavemonth==11){ echo 'selected'; } ?>>November</option>
<option value = "12" <? if ($leavemonth==12){ echo 'selected'; } ?>>December</option>
</select>

&nbsp;&nbsp;
Year &nbsp;&nbsp;
<select name="leaveyear" >
<option value = "2010" <? if ($leaveyear==2010) { echo 'selected';} ?>>2010</option>
<option value = "2011" <? if ($leaveyear==2011) { echo 'selected';} ?>>2011</option>
<option value = "2012" <? if ($leaveyear==2012) { echo 'selected';} ?>>2012</option>
<option value = "2013" <? if ($leaveyear==2013) { echo 'selected';} ?>>2013</option>
<option value = "2014" <? if ($leaveyear==2014) { echo 'selected';} ?>>2014</option>
<option value = "2015" <? if ($leaveyear==2015) { echo 'selected';} ?>>2015</option>
<option value = "2016" <? if ($leaveyear==2016) { echo 'selected';} ?>>2016</option>
<option value = "2017" <? if ($leaveyear==2017) { echo 'selected';} ?>>2017</option>

</select>


  &nbsp;&nbsp;

<input type='submit' name='submit' value='Preview' Class="button" onclick='return validate()'>
   
   </div>

</form>


<br /><br />

<div id="box" valign="top">
                <h3>&nbsp;<strong>Leave Application Details</strong>&nbsp;&nbsp</h3>
<br><br>
<FORM name="printbutton" method="post" align="left" action="report_print.php" target="_blank">
<input type="hidden" name="leavemonth" value="<?php echo $leavemonth; ?>">
<input type="hidden" name="leaveyear" value="<?php echo $leaveyear; ?>">
<input type="hidden" name="branch" value="<?php echo $branch;?>">
<Input type = "Submit" Name = "Submit1" Class="button" VALUE = "Print Leave Details">
</form>
                <table width="80%" align="center" >
<thead>
<tr>
<th width="700px" align="left">Name</a></th>
                           
<th width="500px" align="center"></a>Application</th>

<th width="500px" align="center"></a>Date</th>
                           
<th width="500x" align="right"></a>Reason</th>
</tr>
</thead>
<tbody>
<?
/*echo "branch:".$branch.'<br />';
echo "leavemonth:".$leavemonth.'<br />';
echo "leaveyear:".$leaveyear.'<br />';*/
//--------------------------------------------------
if ($leavemonth =='' ||  $leaveyear =='' || $branch == '') {
// one value missing, so don't display
}
else
{
// start display data
$rs = mysql_query( " call vwleavereport('$leavemonth', '$leaveyear', $branchid, 0);");
$query="SELECT *,
MONTH(tblleaveapplication.DateFrom) as DFMonth,
MONTH(tblleaveapplication.DateTo) as DTMonth,
YEAR(tblleaveapplication.DateFrom) as DFYear
FROM `tblleaveapplication`
LEFT JOIN `tblemployee`
ON tblleaveapplication.employeeid = tblemployee.id
WHERE WorkGroupID = '".$branch."'
AND (
(MONTH(tblleaveapplication.DateFrom)=".$leavemonth." AND YEAR(tblleaveapplication.DateFrom)=".$leaveyear.")
OR
(MONTH(tblleaveapplication.DateTo)=".$leavemonth." AND YEAR(tblleaveapplication.DateFrom)=".$leaveyear.")
)
";

$result = mysql_query($query);
while($row=mysql_fetch_array($result))
{
echo '<tr>';
echo '<td>'.$row['EmployeeName'].'</td>';
echo '<td>'.$row['DateFrom'].'</td>';
echo '<td>'.$row['DateTo'].'</td>';
echo '<td>'.$row['Purpose'].'</td>';
echo '</tr>';
}

}
?>

</table>

Hi everyone.
I need to display a list of departments and a total of occurrences of each department in mysql/php.
eg.
There are 3 Administration types.
There are 5 Government types.
etc.
there is no error when i run the code below, but nothing displays happens either. i'd appreciate anyone's help. Thanks.

Code: [Select]
<?php
$query = mysql_query("SELECT CompanyName, CompanyBranch, DateRecorded, DateClosed, Year(DateRecorded) AS Year, CaseOwner.UsersDepartment
FROM records LEFT JOIN CaseOwner ON CaseOwner = CaseOwner.ProperName
GROUP BY CompanyName, CompanyBranch, DateRecorded, DateClosed, CaseOwner.UsersDepartment
ORDER BY CaseOwner.UsersDepartment") or die("SELECT Error: ".mysql_error());
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
echo "There are ". $row['COUNT(CaseOwner.UsersDepartment)'] ." ". $row['CaseOwner.UsersDepartment'] ." types.";
echo "<br />";
}
?>

I was wondering if this is possible. I have a list of criteria each with their own checkboxes, and I want to query a database when one or more of the checkboxes is submitted. So if 3 are checked, I want to query the database to find those 3 items. If one only is checked, I want to query the dataabse for one item.

any ideas greatly appreciated thank you.

this is basically a criteria based search

I'm trying to dynamically group a range of rows using PHPSpreadsheet and having a lot of trouble thinking through the logic. My code so far looks like this:

$worksheet = $spreadsheet->getActiveSheet();
// Get the highest row number and column letter referenced in the worksheet
$highestRow = $worksheet->getHighestRow(); // e.g. 10
$highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
// Increment the highest column letter
$highestColumn++;

$job_num_chk = '';
$grp_start_row = '';

//format the inserted records
for ($row = 2; $row <= $highestRow; ++$row) {
	//$spreadsheet->getActiveSheet()->getRowDimension($row)->setRowHeight(53);
	for ($col = 'A'; $col != $highestColumn; ++$col) {
		
		$spreadsheet->getActiveSheet()->getStyle($col . $row)->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);
		$spreadsheet->getActiveSheet()->getStyle($col . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
		
		if ($col == 'B') {
			$cur_job_num = $spreadsheet->getActiveSheet()->getCell($col . $row)->getValue(); //get current job number
			
			//check if job number is different
			if ( $job_num_chk !== $cur_job_num ) {
				$spreadsheet->getActiveSheet()->insertNewRowBefore($row, 1); //insert dividing row
				
				//set dividing row to black
				$spreadsheet->getActiveSheet()->getStyle('A'.$row.':N'.$row)->getFill()
					->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
					->getStartColor()->setARGB('000000');
				
				$job_num_chk = $cur_job_num; //update job number
			}
			
		}

	}
	
}

This line: 

$grp_start_row = '';

Is where I'm hitting a mental wall...here's what the spreadsheet looks like so far:

I want to be able to group the rows 101-105 in that example like below:

I need to be able to determine where the start row is and the end row is in order to use the grouping feature (https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes/#groupoutline-a-row)

Thanks everyone.

I am using two seperate queries to calculate a streak, but the queries must be grouped together to find the actual streak?

Query 1:

SELECT COUNT(matchID) as streak, clan1 FROM webs_cup_matches WHERE ladID='17' AND clan1='2630' AND score1 > score2 AND confirmscore='1' AND einspruch='0' GROUP BY clan1 ORDER BY streak DESC LIMIT 1
Query 2:
SELECT COUNT(matchID) as streak, clan2 FROM webs_cup_matches WHERE ladID='17' AND clan2='2630' AND score1 < score2 AND confirmscore='1' AND einspruch='0' GROUP BY clan2 ORDER BY streak DESC LIMIT 1

is it possible someone can join these queries together?

Hi, I would like to know if there's a way to quickly collect and sort form data, then use that data in an if statement. I've tried for a while now and i just can't seem to figure it out.



I would like to do this with radio buttons. Hopefully this will make it clear:
Code:
Code: [Select]
<?php

if (isset($_POST['submit'])) 
{
// I don't know how to do this but maybe something like

// if $_POST values == "Yes"
// put them into an array
// then count how many are in the array, e.g. 2 if 2 "Yes" radio buttons are selected

$count = count($array) . " Yes values selected";
echo $count;

//else if do the same for "No" values

// hopefully this is understandable
}

?>

<!-- i want to take these values and group them, so all the "Yes" values selected together and the "no" values together -->
<form method="post" action="">

<input type="radio" name="first" value="Yes" />
<input type="radio" name="first" value="No" />

<input type="radio" name="second" value="Yes" />
<input type="radio" name="second" value="No" />

<input type="submit" name="submit">
</form>

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=314247.0