Solved

PHP get months from date search query where user

Posted on 2016-09-29
40
54 Views
Last Modified: 2016-10-06
I am running a php script where a user searches for a manager between two dates to show sites operatives have visited. The output needs to show that manager with its sites  between two dates seperated by months.

For example:

Manager Martin
Dates: 01/01/2016 to 01/08/2016

Output: See image attached

Ive attached the search page. The search page currently shows the manager between dates and the sites visited by manager

Ive attached the db table of where this information is kept
siteallocation.sql
SLA.PNG
searchb.php
0
Comment
Question by:RiccardoQuest
  • 19
  • 19
  • 2
40 Comments
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 41821521
What is the question?
1
 

Author Comment

by:RiccardoQuest
ID: 41821524
The issue is i cant seem to create the output like the SLA.png from the attachments provided
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 41821645
Ok, so you want to produce the output exactly as per the SLA?

What determines a YES / NO / 1 / 2 value in the cells?
0
 

Author Comment

by:RiccardoQuest
ID: 41826176
if an operative doesn't visit the listed sites in a month the value 0/NO is displayed.. if the operative visits the site once 1/YES is displayed any more then 1 visit per month displays the actual number of visits of a site per month.

i understand the picture has the word yes which equals 1 but we are going to change this and simply have it display the number of visits per month, so 1 would appear as 1 instead of yes.
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 41826220
Ok so we will either see a 0 or a number 1 or greater.

Why are there cells that are blank - how do those get generated?
0
 

Author Comment

by:RiccardoQuest
ID: 41826275
The cells that are blank are because that site hasnt started yet they will be blank or N/A
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 41826283
So there are actually 3 values - blank, 0 or > 0?
0
 

Author Comment

by:RiccardoQuest
ID: 41826449
Well the contract start date of the site is pulled from another table which holds the sites information. It will be blank. 0 = NO, 1 and above will be numerical values on the amount of operatives visited that site within that month
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 41826722
We might have to back up here a bit. Having looked at your code there are a couple things off the bat that jump out

1. You are using the mysql library -  this has been deprecated and you should consider moving off this asap as it is not supported in later versions of php.

2. Your code organisation could use some work. Badly structured code is always going to be difficult to manage.

I would recommend creating a gig to get your code more structured.

Having said that the basic idea of what you want to do is this

SELECT COUNT(*), sitename, YEAR(timeofallocation), MONTH(timeofallocation) FROM siteallocation
WHERE userId = 1 AND timeofallocation BETWEEN '2016-01-01' AND '2016-10-03'
GROUP BY sitename, YEAR(timeofallocation), MONTH(timeofallocation);

Open in new window


You then loop through the results and you build up an array of data items. An empty array can be created based on the input data with provision for every possible value (Site : Date). When you loop through the results array you simply fill in the values you retrieved from the database.
The second part of the script then takes this merged array and uses it to create a table. No logic required because all the fields are there already.
0
 

Author Comment

by:RiccardoQuest
ID: 41826746
Thank you for this! I am aware of the coding structure not being perfect as i was having great issues with this. I will implement these changes and see how it goes. If all is well then i shall say your response is the best solution. Thank you again!!
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 41826878
Here's the information you need to understand why the PHP MySQL extension is no longer supported, and how to move to one of the supported extensions:
https://www.experts-exchange.com/articles/11177/PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 41826972
Having looked at the requirement again it appears there might be a case where a site has no visits or data for a particular manager - the query above only finds those records where a manager is linked to a site record.

It might be useful to see the site table as well (or at least a representative sample).
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 41827182
Here is a stab at what you are trying to do

Take a look at the sample here

This borrows from your code but with a bit of restructuring and using mysqli (which is very close to mysql - so there should not be a big learning curve).

Landing page searchdb.php
Note the use of includes to keep the code neat and compartmentalised
<?php
require "dbconnect.php";
?>
<!doctype html>
<html>
<head>
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css" rel="stylesheet" />
</head>
<body>
<form method="POST">
	<input type="date" name="from" /> - <input type="date" name="to" />
	<select name="selectu">
		<option value="0">Select User</option>
		<?php fn_user_list($mysqli); ?>
	</select>
	<input type="submit" value="submit" />
</form>
<?php if ($_POST) require "dbgrid.php" ?>
</body>
</html>

Open in new window


dbconnect.php
This connects to the db - and contains the function for getting users from the database for the <select>. Note the fn_user_list takes the db connection as a parameter
<?php
$server   = 'localhost';
$user     = 'user';
$password = 'password';
$db       = 'database';
// Create a new MySQLi connection

$mysqli = new mysqli($server, $user, $password, $db);

// Handle failure
if ($mysqli->connect_error) {
    die('Connect Error (' . $mysqli->connect_errno . ') '
            . $mysqli->connect_error);
}

// Function to create user <option> list
function fn_user_list($db)
{
	// HEREDOC the query 
	$query = <<< QUERY
		SELECT userId, userName FROM users
QUERY;
	// Execute it
	$result = $db->query($query);
	if ($result) {
		// Loop through the results creating options
		while($row = $result->fetch_object()) {
			echo <<< OPTION
			<option value="{$row->userName}">{$row->userName}</option>
OPTION;
		}
	}
}

Open in new window


The workhorse dbgrid.php. This code builds an array of data which is used to create the template.
It fetches the data from the database and then builds an empty array to use as a template for the data. The template contains elements for each month / year combination of FromDate to ToDate. After retrieving data for a user the template is copied and the matching year / month cells are filled in leaving the rest blank. Finally the code builds the table
<?php
// Lookup to get month name
$MONTH = array ('', 'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec');
// Get the values from post santizing with preg-replace to eliminate anything 
// we wouldn't expect to find in a date string
$from    = isset($_POST['from']) ? preg_replace('[^\d-]','', $_POST['from']) : false;
$to      = isset($_POST['to']) ? preg_replace('[^\d-]','', $_POST['to']) : false;
$selectu = isset($_POST['selectu']) ? preg_replace('[^\d-]','', $_POST['selectu']) : false;

// If POST was good build and run the query
if ($from && $to && $selectu) {
  // Get all sites and for those where the specified user has records get that information as well
  $query = <<< QUERY
    SELECT
      COUNT(*) AS `total`,
      s.sitename, 
      s.sitecode, 
      sa.year, 
      sa.month
    FROM 
      sites s LEFT JOIN (
        SELECT
          sitename,
          YEAR(timeofallocation) AS `year`, 
          MONTH(timeofallocation) AS `month` 
        FROM 
          siteallocation 
        WHERE 
          manager = '{$selectu}' AND
          timeofallocation BETWEEN '{$from}' AND '{$to}'
      ) sa ON s.sitename = sa.sitename
    GROUP BY 
      s.sitename, 
      s.sitecode, 
      sa.year, 
      sa.month
    ORDER BY
        sitecode, year, month;  
QUERY;
  // Some short cuts
  $startDate  = strtotime($from);
  $endDate    = strtotime($to);
  $startMonth = date('m', $startDate);
  $startYear  = date('Y', $startDate);
  $endMonth   = date('m', $endDate);
  $endYear    = date('Y', $endDate);

  // Build our template by looping through all months from 
  // startmonth / startyear to endmonth / endyear

  $m = $startMonth;
  $y = $startYear;
  $template = array();

  while($m < $endMonth || $y < $endYear) {
    if ($m++ > 12) {
      $y++;
      $m = 1;
    }
    if (!isset($template[$y])) {
      $template[$y] = array();
    }

    $template[$y][$m] = '';
  }

  // Initialise the data grid
  $grid = array();
  $result = $mysqli->query($query);
  if ($result) {
    $current = false;
    // Loop through results
    while($item = $result->fetch_object()) {
      // If this is a new site wrap up the existing row and start a new one
      if ($current !== $item->sitecode) {
        // we enter here on first iteration because we have not encountered a site yet
        // That is why $current starts as false so that it does not drop into the following if
        // which we only want once we have built a valid row
        if ($current) {
          // If this is not the first time around add the complete row to the grid
          $grid[] = $row;
        }
        // Update current to he current sitecode
        $current = $item->sitecode;
      
        // create the new row
        $row = new stdClass;
        $row->sitename = $item->sitename;
        $row->sitecode = $item->sitecode;
        // ... and copy in our template
        $row->data = $template;
      }
      // if there was data for the user on this month / year for this site
      // then set it otherwise skip
      if ($item->year) {
        $row->data[$item->year][$item->month] = $item->total;
      }
    }
    // remember to add the WIP row
    $grid[] = $row;
  }
}
// Build the table
?>
<table class="table">
  <thead>
    <tr>
      <th><?php echo $selectu?></th>
      <th>Site Visits</th>
<?php 
  foreach($grid[0]->data as $year => $months) {
    foreach($months as $m => $v) {
      echo "<th>Month<br/>of.</th>";
    }
  }
?>
    </tr>
    <tr>
      <th>Site Code</th>
      <th>Site Name</th>
<?php
  foreach($grid[0]->data as $year => $months) {
      foreach($months as $m => $v) {
        echo "<th>" . $MONTH[$m] . "<br/>" . $y . "</th>";
      }
  }    
?>
    </tr>
  </thead>
  <tbody>
<?php
  foreach($grid as $row) {
    echo <<< ROW
      <tr>
        <td>{$row->sitecode}</td>
        <td>{$row->sitename}</td>
ROW;
    foreach($row->data as $year => $months) {
      foreach($months as $month => $total) {
        echo "<td>{$total}</td>";
      }
    }
    echo "</tr>";
  }
?>
  </tbody>
</table>

Open in new window

0
 

Author Comment

by:RiccardoQuest
ID: 41827649
Just looking over the code ive noticed that you are using the time allocation field its actually the date field as time allocation is the time and date that the site form was published into the db. The `date` field is the one to use as the site form can be back dated. If i change the time allocation fields to datas from the code youve provided will it work?
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 41827660
In theory but looking at that column the data is bad - you have missing dates, 0000-00-00 and dates using different formats

Example

2016-09-08
27-09-2016

You will need to fix your data before you get the results you are looking for

The principle is the same though - i.e. the algorithm won't change
0
 

Author Comment

by:RiccardoQuest
ID: 41827746
The dates throughout are in the format 27-09-2016 apart from time allocation because that format is only for display of when a user wants to see when it was posted, then on that page i would just change the format to d-m-Y for display. Im going to input some new data into the `date` field so its better and then Im going to change the timeallocation to date now and see how we get on. Ill keep this thread up to date :) Thanks
0
 

Author Comment

by:RiccardoQuest
ID: 41827802
Got two invalid arguments

Warning: Invalid argument supplied for foreach() in /home/questweb/public_html/hms/dbgrid.php on line 111

Warning: Invalid argument supplied for foreach() in /home/questweb/public_html/hms/dbgrid.php on line 122
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 41827890
Yes because the data is invalid.  The principle is exactly the same - as long as the target field is a valid date it will work - if the data is bad it will not work - garbage in garbage out.
The screen shot below is of the data you sent through.
Bad DataI fixed the broken items and refreshed the sample I posted above - which now works off date - as you can see it is returning data.
Sample Screenshot
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 41827892
Updated source for dbgrid.php
<?php
$MONTH   = array ('', 'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec');

$from    = isset($_POST['from']) ? preg_replace('[^\d-]','', $_POST['from']) : false;
$to      = isset($_POST['to']) ? preg_replace('[^\d-]','', $_POST['to']) : false;
$selectu = isset($_POST['selectu']) ? preg_replace('[^\d-]','', $_POST['selectu']) : false;
//ALTERED TO USE 'date' INSTEAD OF timeofallocation
if ($from && $to && $selectu) {
  $query = <<< QUERY
    SELECT
      COUNT(*) AS `total`,
      s.sitename, 
      s.sitecode, 
      sa.year, 
      sa.month
    FROM 
      sites s LEFT JOIN (
        SELECT
          sitename,
          YEAR(`date`) AS `year`, 
          MONTH(`date`) AS `month` 
        FROM 
          siteallocation 
        WHERE 
          manager = '{$selectu}' AND
          `date` BETWEEN '{$from}' AND '{$to}'
      ) sa ON s.sitename = sa.sitename
    GROUP BY 
      s.sitename, 
      s.sitecode, 
      sa.year, 
      sa.month
    ORDER BY
        sitecode, year, month;  
QUERY;
  $startDate  = strtotime($from);
  $endDate    = strtotime($to);
  $startMonth = date('m', $startDate);
  $startYear  = date('Y', $startDate);
  $endMonth   = date('m', $endDate);
  $endYear    = date('Y', $endDate);

  $m = $startMonth;
  $y = $startYear;
  $template = array();

  while($m < $endMonth || $y < $endYear) {
    if ($m++ > 12) {
      $y++;
      $m = 1;
    }
    if (!isset($template[$y])) {
      $template[$y] = array();
    }

    $template[$y][$m] = '';
  }

  $grid = array();
  $result = $mysqli->query($query);
  if ($result) {
    $current = false;
    while($item = $result->fetch_object()) {
      if ($current !== $item->sitecode) {
        if ($current) {
          $grid[] = $row;
        }
        $current = $item->sitecode;
      
        $row = new stdClass;
        $row->sitename = $item->sitename;
        $row->sitecode = $item->sitecode;
        $row->data = $template;
      }
      if ($item->year) {
        $row->data[$item->year][$item->month] = $item->total;
      }
    }

    $grid[] = $row;
  }
  else {
    echo $mysqli->error;
  }
}

?>
<table class="table">
  <thead>
    <tr>
      <th><?php echo $selectu?></th>
      <th>Site Visits</th>
<?php 
  foreach($grid[0]->data as $year => $months) {
    foreach($months as $m => $v) {
      echo "<th>Month<br/>of.</th>";
    }
  }
?>
    </tr>
    <tr>
      <th>Site Code</th>
      <th>Site Name</th>
<?php
  foreach($grid[0]->data as $year => $months) {
      foreach($months as $m => $v) {
        echo "<th>" . $MONTH[$m] . "<br/>" . $y . "</th>";
      }
  }    
?>
    </tr>
  </thead>
  <tbody>
<?php
  foreach($grid as $row) {
    echo <<< ROW
      <tr>
        <td>{$row->sitecode}</td>
        <td>{$row->sitename}</td>
ROW;
    foreach($row->data as $year => $months) {
      foreach($months as $month => $total) {
        echo "<td>{$total}</td>";
      }
    }
    echo "</tr>";
  }
?>
  </tbody>
</table>

Open in new window

0
 

Author Comment

by:RiccardoQuest
ID: 41827950
That is using the format Y-m-d however i wish to use d-m-Y what do i need to change now to make this work? Swap month and year around from the previous doc above?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:RiccardoQuest
ID: 41828025
Ive changed the dates see attached and still get the arguement error.
siteallocation--2-.sql
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 41828056
I loaded your data into my sample - works without an issue

http://www.marcorpsa.com/ee/t1659/searchdb.php

I can't see your code so I don't know how you have implemented this - lines 111 and 112 in the error message don't match up with my code.

You need to do some debugging on your side to figure out why your implementation does not work. The sample code does work - so something in the way you have implemented it is not working

After you do a $mysqli->query() do the following
echo "MySQLi Error: " . $mysqli->error . "<br/>";
echo "Query: " . $query . "<br/>";

Open in new window

Look at the output from the above to see what is causing the problem.
0
 

Author Comment

by:RiccardoQuest
ID: 41828072
This is what i have.... Ivechanged the format of the date field from VARCHAR to DATE and this can be seen in the attached file
expert1.php
dbgrid.php
siteallocation--3-.sql
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 41828123
Works perfectly here. Imported your data and replaced the scripts with yours works without a hitch.
Did you put the debug statements in I recommended.
0
 

Author Comment

by:RiccardoQuest
ID: 41828165
MySQLi Error:
Query: SELECT COUNT(*) AS `total`, s.sitename, s.sitecode, sa.year, sa.month FROM sites s LEFT JOIN ( SELECT sitename, YEAR(`date`) AS `year`, MONTH(`date`) AS `month` FROM siteallocation WHERE manager = 'Dylon' AND `date` BETWEEN '2016-08-18' AND '2016-10-31' ) sa ON s.sitename = sa.sitename GROUP BY s.sitename, s.sitecode, sa.year, sa.month ORDER BY sitecode, year, month;
Unknown column 's.sitecode' in 'field list'

Warning: Invalid argument supplied for foreach() in /home/questweb/public_html/hms/dbgrid.php on line 98

Warning: Invalid argument supplied for foreach() in /home/questweb/public_html/hms/dbgrid.php on line 109

It should be able to find the sitecode field i dont understand why?

Could it have anything to do with the PHP Verison?
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 41828220
You will notice that the query in my script joins with a table called sites. You did not provide this table so I had to guess what it does. Obviously my interpretation does not match what you have so you need to change the query to work against your sites table.
1
 

Author Comment

by:RiccardoQuest
ID: 41828234
So i am guessing that youve added Year and Month to your sites table. Hes a snippet of my sites table attached.
sites.sql
0
 

Author Comment

by:RiccardoQuest
ID: 41828265
I changed where it sayssitecode in you code to siteid for `sites` however ...

Query: SELECT COUNT(*) AS `total`, s.sitename, s.siteid, sa.year, sa.month FROM sites s LEFT JOIN ( SELECT sitename, YEAR(`date`) AS `year`, MONTH(`date`) AS `month` FROM siteallocation WHERE manager = 'Manager2' AND `date` BETWEEN '2016-08-09' AND '2016-10-28' ) sa ON s.sitename = sa.sitename GROUP BY s.sitename, s.siteid, sa.year, sa.month ORDER BY siteid, year, month;

But i do have a result!
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 41828266
No, my sites table is simply
CREATE TABLE `sites` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sitecode` int(11) DEFAULT NULL,
  `sitename` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

Open in new window


In your table is the id for a site linked to sitecode in the siteallocations table?

If so just change the s.sitecode to s.id.
0
 

Author Comment

by:RiccardoQuest
ID: 41828280
Im going to rename `siteid` in sites to `sitecode` to save this misinterpretation. The `siteid` in `sites` is the same as `sitecode` in `siteallocation`.
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 41828286
I see you have a siteid column - which is the same as sitecode - so you can change sitecode to siteid and it should work
<?php
$MONTH   = array ('', 'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec');

$from    = isset($_POST['from']) ? preg_replace('[^\d-]','', $_POST['from']) : false;
$to      = isset($_POST['to']) ? preg_replace('[^\d-]','', $_POST['to']) : false;
$selectu = isset($_POST['selectu']) ? preg_replace('[^\d-]','', $_POST['selectu']) : false;
//ALTERED TO USE 'date' INSTEAD OF timeofallocation
if ($from && $to && $selectu) {
	$query = <<< QUERY
		SELECT
			COUNT(*) AS `total`,
			s.sitename, 
			s.siteid, 
			sa.year, 
			sa.month
		FROM 
			sites s LEFT JOIN (
				SELECT
					sitename,
					YEAR(`date`) AS `year`, 
					MONTH(`date`) AS `month` 
				FROM 
					siteallocation 
				WHERE 
					manager = '{$selectu}' AND
					`date` BETWEEN '{$from}' AND '{$to}'
			) sa ON s.sitename = sa.sitename
		GROUP BY 
			s.sitename, 
			s.siteid, 
			sa.year, 
			sa.month
		ORDER BY
				siteid, year, month;	
QUERY;
	$startDate  = strtotime($from);
	$endDate    = strtotime($to);
	$startMonth = date('m', $startDate);
	$startYear  = date('Y', $startDate);
	$endMonth   = date('m', $endDate);
	$endYear    = date('Y', $endDate);

	$m = $startMonth;
	$y = $startYear;
	$template = array();

	while($m < $endMonth || $y < $endYear) {
		if ($m++ > 12) {
			$y++;
			$m = 1;
		}
		if (!isset($template[$y])) {
			$template[$y] = array();
		}

		$template[$y][$m] = '';
	}

	$grid = array();
	$result = $mysqli->query($query);
	if ($result) {
		$current = false;
		while($item = $result->fetch_object()) {
			if ($current !== $item->siteid) {
				if ($current) {
					$grid[] = $row;
				}
				$current = $item->siteid;
			
				$row = new stdClass;
				$row->sitename = $item->sitename;
				$row->siteid = $item->siteid;
				$row->data = $template;
			}
			if ($item->year) {
				$row->data[$item->year][$item->month] = $item->total;
			}
		}

		$grid[] = $row;
	}
	else {
		echo $mysqli->error;
	}
}

?>
<table class="table">
	<thead>
		<tr>
			<th><?php echo $selectu?></th>
			<th>Site Visits</th>
<?php 
	foreach($grid[0]->data as $year => $months) {
		foreach($months as $m => $v) {
			echo "<th>Month<br/>of.</th>";
		}
	}
?>
		</tr>
		<tr>
			<th>Site Code</th>
			<th>Site Name</th>
<?php
	foreach($grid[0]->data as $year => $months) {
			foreach($months as $m => $v) {
				echo "<th>" . $MONTH[$m] . "<br/>" . $y . "</th>";
			}
	}		
?>
		</tr>
	</thead>
	<tbody>
<?php
	foreach($grid as $row) {
		echo <<< ROW
			<tr>
				<td>{$row->siteid}</td>
				<td>{$row->sitename}</td>
ROW;
		foreach($row->data as $year => $months) {
			foreach($months as $month => $total) {
				echo "<td>{$total}</td>";
			}
		}
		echo "</tr>";
	}
?>
	</tbody>
</table>

Open in new window

0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 41828320
Im going to rename `siteid` in sites to `sitecode` to save this misinterpretation. The `siteid` in `sites` is the same as `sitecode` in `siteallocation`.
Don't do that - that could have side effects for the rest of your code - see code changes above.
0
 

Author Comment

by:RiccardoQuest
ID: 41829450
Just got a question to add to this. If i wanted to bring in the contract start date so where a search between 1/06/2016 - 31/08/2016 for manager2 but contract doesn't start till 01/07/2016 is it possible to add N/A on the results for the month of 06 (June) ? Contract start date is on sites. See attached for `contractstartdates`
sites.sql
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 41829607
Yes - with changes.

A note on dates. If you do not use standard date formats you are going to find yourself running into walls because you will need to be continually doing translations on your data. The contractstartdate column in sites has a a dd/mm/yyyyy date format - which is going to complicate things.

First up - standardise your data - make your dates uniform and use UTC (yyyy-mm-dd) to store your dates.

Then you can modify your code as follows
<?php
$MONTH   = array ('', 'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec');

$from    = isset($_POST['from']) ? preg_replace('[^\d-]','', $_POST['from']) : false;
$to      = isset($_POST['to']) ? preg_replace('[^\d-]','', $_POST['to']) : false;
$selectu = isset($_POST['selectu']) ? preg_replace('[^\d-]','', $_POST['selectu']) : false;
//ALTERED TO USE 'date' INSTEAD OF timeofallocation
// ALTERED TO INCLUDE THE contractstartdate COLUMN
if ($from && $to && $selectu) {
	$query = <<< QUERY
		SELECT
			COUNT(*) AS `total`,
			s.sitename, 
			s.siteid, 
			s.contractstartdate, 
			sa.year, 
			sa.month
		FROM 
			sites s LEFT JOIN (
				SELECT
					sitename,
					YEAR(`date`) AS `year`, 
					MONTH(`date`) AS `month` 
				FROM 
					siteallocation 
				WHERE 
					manager = '{$selectu}' AND
					`date` BETWEEN '{$from}' AND '{$to}'
			) sa ON s.sitename = sa.sitename
		GROUP BY 
			s.sitename, 
			s.siteid, 
			sa.year, 
			sa.month
		ORDER BY
				siteid, year, month;	
QUERY;
	$startDate  = strtotime($from);
	$endDate    = strtotime($to);
	$startMonth = date('m', $startDate);
	$startYear  = date('Y', $startDate);
	$endMonth   = date('m', $endDate);
	$endYear    = date('Y', $endDate);

	$m = $startMonth;
	$y = $startYear;
	$template = array();

	while($m < $endMonth || $y < $endYear) {
		if ($m++ > 12) {
			$y++;
			$m = 1;
		}
		if (!isset($template[$y])) {
			$template[$y] = array();
		}

		$template[$y][$m] = '';
	}

	$grid = array();
	$result = $mysqli->query($query);
	if ($result) {
		$current = false;
		while($item = $result->fetch_object()) {
			if ($current !== $item->siteid) {
				if ($current) {
					$grid[] = $row;
				}
				$current = $item->siteid;
			
				$row = new stdClass;
				$row->sitename = $item->sitename;
				$row->siteid = $item->siteid;
				$row->data = $template;
			}
			if ($item->year) {
				// compare contract start date to first of the month for this item
				// if less than show NA otherwise the total
				if (strtotime("{$item->year}-{$item->month}-01") < strtotime($item->contractstartdate)) {
					$row->data[$item->year][$item->month] = 'N/A';
				}
				else {
					$row->data[$item->year][$item->month] = $item->total;
				}
			}
		}

		$grid[] = $row;
	}
	else {
		echo $mysqli->error;
	}
}

?>
<table class="table">
	<thead>
		<tr>
			<th><?php echo $selectu?></th>
			<th>Site Visits</th>
<?php 
	foreach($grid[0]->data as $year => $months) {
		foreach($months as $m => $v) {
			echo "<th>Month<br/>of.</th>";
		}
	}
?>
		</tr>
		<tr>
			<th>Site Code</th>
			<th>Site Name</th>
<?php
	foreach($grid[0]->data as $year => $months) {
			foreach($months as $m => $v) {
				echo "<th>" . $MONTH[$m] . "<br/>" . $y . "</th>";
			}
	}		
?>
		</tr>
	</thead>
	<tbody>
<?php
	foreach($grid as $row) {
		echo <<< ROW
			<tr>
				<td>{$row->siteid}</td>
				<td>{$row->sitename}</td>
ROW;
		foreach($row->data as $year => $months) {
			foreach($months as $month => $total) {
				echo "<td>{$total}</td>";
			}
		}
		echo "</tr>";
	}
?>
	</tbody>
</table>

Open in new window

0
 

Author Comment

by:RiccardoQuest
ID: 41829922
Ive sorted the date formats out from the previous comments about it. The format in the db will be yyyy-mm-dd however when displayed on the page for the users im converting it to d-m-y. The code works perfectly!
ive add "if($total==Null){echo "NO";}" for the values that are within the start of the contract date but have no value because of no visits. It set all values to NO.
The attachment shows the results from my search and for site Allies & Morrison it should have N/A in month August aswell and NO for October.
na.PNG
0
 

Author Comment

by:RiccardoQuest
ID: 41829934
"            foreach($row->data as $year => $months) {
                  foreach($months as $month => $total) {
                        if($total==Null){echo "<td>NO</td>";}else{ echo "<td>{$total}</td>";}
                  }
            }
            echo "</tr>";"
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 41829970
Try
if ($total == 0)
We are doing a count so there will be 0 values not null values.
0
 

Author Comment

by:RiccardoQuest
ID: 41830014
The null seemed to work however see attached where Allies and Morrison the N/A for contract start date should be on months June to September
nay.PNG
0
 
LVL 51

Accepted Solution

by:
Julian Hansen earned 500 total points
ID: 41830072
The contractstartdate is a bit of johnny come lately to the party so we need to work that in. The reason it is not working is because we have default template that is setup with 0's. If we don't have a record for a pre start contract date then it is never going to modify the template.

To fix this we are going to need to move the strtotime compare to the output rather than the populating the template

The template populate loop then changes to this
	if ($result) {
		$current = false;
		while($item = $result->fetch_object()) {
			if ($current !== $item->siteid) {
				if ($current) {
					$grid[] = $row;
				}
				$current = $item->siteid;
			
				$row = new stdClass;
				$row->sitename = $item->sitename;
				$row->siteid = $item->siteid;
				$row->data = $template;
				// We save the contract start date to use later
				$row->contractstartdate = strtotime($item->contractstartdate);
			}
			// Remove the strtotime comparison
			if ($item->year) {
				$row->data[$item->year][$item->month] = $item->total;
			}
		}

		$grid[] = $row;
	}

Open in new window

In the output code we make the following change
<?php
	foreach($grid as $row) {
		echo <<< ROW
			<tr>
				<td>{$row->siteid}</td>
				<td>{$row->sitename}</td>
ROW;
		foreach($row->data as $year => $months) {
			foreach($months as $month => $total) {
				// Check for a N/A condition in output
				if (strtotime("{$year}-{$month}-01") < $row->contractstartdate) {
					echo "<td>N/A</td>";
				}
				else {
					// Check for NO / Number output
					$total = $total > 0 ? $total : 'NO';
					echo "<td>{$total}</td>";
				}
			}
		}
		echo "</tr>";
	}
?>

Open in new window

Full listing
<?php
$MONTH   = array ('', 'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec');

$from    = isset($_POST['from']) ? preg_replace('[^\d-]','', $_POST['from']) : false;
$to      = isset($_POST['to']) ? preg_replace('[^\d-]','', $_POST['to']) : false;
$selectu = isset($_POST['selectu']) ? preg_replace('[^\d-]','', $_POST['selectu']) : false;
//ALTERED TO USE 'date' INSTEAD OF timeofallocation
if ($from && $to && $selectu) {
	$query = <<< QUERY
		SELECT
			COUNT(*) AS `total`,
			s.sitename, 
			s.siteid, 
			s.contractstartdate, 
			sa.year, 
			sa.month
		FROM 
			sites s LEFT JOIN (
				SELECT
					sitename,
					YEAR(`date`) AS `year`, 
					MONTH(`date`) AS `month` 
				FROM 
					siteallocation 
				WHERE 
					manager = '{$selectu}' AND
					`date` BETWEEN '{$from}' AND '{$to}'
			) sa ON s.sitename = sa.sitename
		GROUP BY 
			s.sitename, 
			s.siteid, 
			sa.year, 
			sa.month
		ORDER BY
				siteid, year, month;	
QUERY;
	$startDate  = strtotime($from);
	$endDate    = strtotime($to);
	$startMonth = date('m', $startDate);
	$startYear  = date('Y', $startDate);
	$endMonth   = date('m', $endDate);
	$endYear    = date('Y', $endDate);

	$m = $startMonth;
	$y = $startYear;
	$template = array();

	while($m < $endMonth || $y < $endYear) {
		if ($m++ > 12) {
			$y++;
			$m = 1;
		}
		if (!isset($template[$y])) {
			$template[$y] = array();
		}

		$template[$y][$m] = '';
	}

	$grid = array();
	$result = $mysqli->query($query);
	$contractstartdate = null;
	if ($result) {
		$current = false;
		while($item = $result->fetch_object()) {
			if ($current !== $item->siteid) {
				if ($current) {
					$grid[] = $row;
				}
				$current = $item->siteid;
			
				$row = new stdClass;
				$row->sitename = $item->sitename;
				$row->siteid = $item->siteid;
				$row->data = $template;
				$row->contractstartdate = strtotime($item->contractstartdate);
			}
			if ($item->year) {
				$row->data[$item->year][$item->month] = $item->total;
			}
		}

		$grid[] = $row;
	}
	else {
		echo $mysqli->error;
	}
}

?>
<table class="table">
	<thead>
		<tr>
			<th><?php echo $selectu?></th>
			<th>Site Visits</th>
<?php 
	foreach($grid[0]->data as $year => $months) {
		foreach($months as $m => $v) {
			echo "<th>Month<br/>of.</th>";
		}
	}
?>
		</tr>
		<tr>
			<th>Site Code</th>
			<th>Site Name</th>
<?php
	foreach($grid[0]->data as $year => $months) {
			foreach($months as $m => $v) {
				echo "<th>" . $MONTH[$m] . "<br/>" . $y . "</th>";
			}
	}		
?>
		</tr>
	</thead>
	<tbody>
<?php
	foreach($grid as $row) {
		echo <<< ROW
			<tr>
				<td>{$row->siteid}</td>
				<td>{$row->sitename}</td>
ROW;
		foreach($row->data as $year => $months) {
			foreach($months as $month => $total) {
				if (strtotime("{$year}-{$month}-01") < $row->contractstartdate) {
					echo "<td>N/A</td>";
				}
				else {
					$total = $total > 0 ? $total : 'NO';
					echo "<td>{$total}</td>";
				}
			}
		}
		echo "</tr>";
	}
?>
	</tbody>
</table>

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 41832403
1

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now