How could I loop through this data, yet not flip to another row after every recordset?

I apologize if the title of my question makes you wonder if I've got monkeys flying out of my nose, but see if this doesn't better explain what I'm trying to get my head around...

Here's a sample of my data:
 
id     title    start_cell     row_number
1      a          25                1
2      b          43                1
3      c           74               1
4     d           13                2

The code that I'm using is building a table on my page that consists of 84 cells. So, here comes my data as a digital entity called $results.

$body="<tr>"

foreach ($results as $row)
{
   //now I'm going through 83 cells, checking to see if there's any data that matches the row I'm on and the cell that I'm looking at
  for ($y=0; $y<=83; $y++)
 {
    $body.="<td
    if($row['DataDisplay_Row']==1 AND $the_cell==$y)
    {
    //write something in the cell
    }
    $body.=">";
$body.="</tr><tr>"
}

What I've got results in table consisting of four rows rather than two. Do you smell what I'm cooking? How can I arrange my code so I only get two rows?
brucegustPHP DeveloperAsked:
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.

Julian HansenCommented:
So what you are saying is that you want to loop through each record and add its value to its appropriate cell in the table. Not all cells have values so you have to skip a few.

My question is - does each cell in the table have to exist as a cell or could you use a colspan to fill in a bunch of empty cells?

Also your code seems to be looping through all rows but only outputting values for Row 1 - is that correct or do you need a solution that will output all rows?

I am assuming you order your data by row then column?

My next question is why are you writing your data inside the <td > element
i.e. <td datahere >

Another question - why are you not doing your <tr> output in the loop like

for( ... ) 
   echo "<tr>";
   // cell logic here
   echo "</tr>";
}

Open in new window


Seems your way ends up with an orphan row at the end?
Are these attributes you are writing or data?
0
brucegustPHP DeveloperAuthor Commented:
So what you are saying is that you want to loop through each record and add its value to its appropriate cell in the table. Not all cells have values so you have to skip a few.

My question is - does each cell in the table have to exist as a cell or could you use a colspan to fill in a bunch of empty cells?

Also your code seems to be looping through all rows but only outputting values for Row 1 - is that correct or do you need a solution that will output all rows? Yes!

I am assuming you order your data by row then column? Just like what I have in my example - yes!

My next question is why are you writing your data inside the <td > element
i.e. <td datahere > That was a mistake. I do have to do some formatting depending on the nature of the data, but for the sake of the logic I'm asking your input on, never mind that.

Another question - why are you not doing your <tr> output in the loop like

Not ruling anything out, Julian. I've played with a couple of different configurations, but the challenge SEEMS to be that as I loop through each successive row of my database / 84 individual cells, at this point I've yet to figure out how to start another row on my actual webpage ONLY after I've exhausted the data that potentially exists on that row. In other words, in my example, I've got four rows of data. But on my webpage, I should only have two. That's my quandary.


Seems your way ends up with an orphan row at the end? No

Are these attributes you are writing or data? I will be writing data and attributes. The challenge is just looping through the database and going through multiple rows of data, but, like I've got in my example, just two rows versus four.

What do you think...?
0
Julian HansenCommented:
Based on that then this should work - bear in mind this is untested as I don't have access to your data.
Assumptions
1. Your table works off 0 based cell data - this is based on your for loop that starts from 0. If this is not he case you need to increment accordingly
2. You don't want to use colspan to span groups of empty cells
3. We use the data as presented
4. All rows are represented in the database - if they are not we will need to include code to handle that at the point indicated
5. That data is sane - we don't have to check for cells > 84
define('NCOLS', 84);
$body = '<tr>';
// KEEP TRACK OF WHAT ROW WE ARE ON
$current_row = 0;
$col = 0;

foreach ($results as $row) {
  // WE NEED TO START ANOTHER ROW
  if ($current_row != $row['row_number']) {
    if ($current_row != 0) {
      // FINISH OFF THE ROW;
      while($col++ < NCOLS) {
        $body .= '<td></td>';
      }
      $body .= '</tr><tr>';
    }
    // MOVE OUR ROW MARKER
    $current_row = $row['row_number'];

    // RESET COL COUNT
    $col = 0;
    // TODO: HANDLE MISSED ROWS HERE
  }
  
  // HERE WE SHOULD BE GOOD TO GO WITH OUTPUT OF CELLS
  // LOOP THROUGH BLANKS
  while($col++ < $row['start_cell']) {
    $body .= '<td></td>';
  }
  
  // HERE IS WHERE WE OUTPUT OUR DATA
  $body .= "<td>{$row['title']}</td>";
  
  // NOTHING MORE TO DO THE NEXT ITERATION WILL DO THE WORK
}
// LASTLY WE FINISH OF THE LAST ROW AND CLOSE IT
while($col++ < NCOLS) {
   $body .= '<td></td>';
}
$body .= '</tr>';

Open in new window

As I said untested code but the logic should be mostly there.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

brucegustPHP DeveloperAuthor Commented:
Hey, Julian!

I've been kicking the tires on this thing most of the day and I'm still coming up short.

First off, here's my code in its entirety...

<?php
//ERROR REPORTING
ini_set('display_errors',1);
ini_set('display_startup_errors',1);
error_reporting(E_ALL); 
require_once('projectpageclass.php');
require_once('model_class.php');

$new_page= new ProjectPage;
$page_title="South Area Network Development Project List";
$new_date=new DateCalc;

$current_row="";

if(!isset($_GET['year']))
{
	$this_year=date("Y");
}
else
{
	$this_year=$_GET['year'];
}
$the_date=$this_year;
$the_date.="-01-01";

$year_one_calc = date('c', strtotime("$the_date - 1 year"));
$year_one=date("Y", strtotime($year_one_calc)); 

$year_two=$this_year;

$year_three_calc = date('c', strtotime("$the_date + 1 year"));
$year_three=date("Y", strtotime($year_three_calc)); 

$dayNumber = date("z") + 1; 

include('mssql_db_cred.php');

$mssql_pdo = new PDO("dblib:host=".$mssql_cred_data['server'].";dbname=".$mssql_cred_data['dbname'],$mssql_cred_data['user'],$mssql_cred_data['pw']); 
$mssql_pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
if(!$mssql_pdo)
{
	echo "no connection";
}

include("gant_header.php");

//here's the sql that gives you all of your content

//this is putting the dates that are being displayed on the calendar into a code friendly format
$day = date('w');
$week_start = date('c', strtotime('-'.$day.' days'));
$this_week = date('c', strtotime($week_start));

/*$week_one_data = date('c', strtotime("$this_week - 6 weeks"));
$week_one=date("m/d/Y", strtotime($week_one_data));

$week_twelve_data = date('c', strtotime("$this_week + 5 weeks"));
$week_twelve=date("m/d/Y", strtotime($week_twelve_data));*/

//I used this next chunk of code to test for the "sound-ness" of my code in that there wasn't any data between September and December of this year
//$week_one= date("m/d/Y", strtotime("-10 months"));
//echo $week_one;
//$week_twelve= date("m/d/Y", strtotime("-6 months"));
//echo $week_twelve;

$week_one='01/01/2015';
$week_twelve='05/31/2015';

$query = "EXECUTE stp_Select_REPORT_DevelopeProjects_Gantt :DataDisplay_Start, :DataDisplay_End";
$stpro = $mssql_pdo->prepare($query);
$stpro->bindParam(':DataDisplay_Start', $week_one, PDO::PARAM_STR);
$stpro->bindParam(':DataDisplay_End', $week_twelve, PDO::PARAM_STR);

// call the stored procedure
$stpro->execute();

$results = $stpro->fetchALL(PDO::FETCH_ASSOC);

/*
$number=1;
foreach($results as $row)
{
	echo $number.') '.$row['str_ProjectName'].' | '.$row['dte_DateStarted'].'<br>';
	$number=$number+1;
}
*/

$start_row=0;
$new_row=0;


$current_row_number=1;
$col=0;
$number_cols=84;
$number=1;

$body.="<tr>";

/*this is golden - this is your template, right here
foreach($results as $row)
{
	if($current_row_number<>$row['DataDisplay_Row'])
	{
		$body.="</tr><tr>";
	}
	$body.="<td>";
	$body.=$row['DataDisplay_Row'];
	$body.="</td>";
	$current_row_number=$row['DataDisplay_Row'];
}
*/	

foreach($results as $row)
{
	if($current_row_number<>$row['DataDisplay_Row'])
	{
		$body.="</tr><tr>";
	}


	$body.="<td>";
	if($row['DataDisplay_EndCell']=="" OR $row['DataDisplay_EndCell']<0)
	{
		continue;
	}
		if($row['DataDisplay_StartCell']=="")
		{
			$the_cell=$row['DataDisplay_EndCell'];
		}
		else
		{
			$the_cell=$row['DataDisplay_StartCell'];
		}
	$body.=$the_cell;
	$body.="</td>";
	
	$current_row_number=$row['DataDisplay_Row'];
}	
$body.="
	<!-- this last row is just for aesthetics so there's a little room at the bottom of the page. It also gurantees a certain with for all of the \"daily\" cells -->";
	include("spacer_single.php");
	$body.="
</table><br><br>";
		
$new_page->setBody($body);

echo $new_page->display();

?>

Open in new window


Secondly, here's the data in spreadsheet form (attached)...

And finally, here's the way my page currently appears:

Bottom line is that I'm making progress and your suggestions helped put some of those wheels in motion. Problem is, while I've been able to effectively reduce 58 rows down to the authentic 19, there's still the problem of iterating through 84 cells. Right now, I've just got it running where I'm going through however many values there might be per row.

What am I missing?
dev_data.xlsx
screenshot.JPG
0
Julian HansenCommented:
Your code does not quite follow the sample I posted. Here is a working sample of that code
Here is the source that generates that
<!-- SOURCE LISTING ACTIVE -->
<!doctype html>
<html>
<head>
<title>Title</title>
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css" rel="stylesheet" />
<link href="css/custom.css" rel="stylesheet" />
<style type="text/css">
td {
	border-left: 1px solid #dedede;
	border-right: 1px solid #dedede;
}
</style>
</head>
<body>
<?php
$host = 'server';
$user = 'user';
$password = 'password';
$database = 'database';

$mysqli = new mysqli($host,$user,$password,$database);
$query = "select * from ee1249 order by row_number, start_cell";
$result = $mysqli->query($query);
$results = $result->fetch_all(MYSQLI_ASSOC);

define('NCOLS', 84);
$body = '<tr>';
// KEEP TRACK OF WHAT ROW WE ARE ON
$current_row = 0;
$col = 0;

foreach ($results as $row) {
  // WE NEED TO START ANOTHER ROW
  if ($current_row != $row['row_number']) {
    if ($current_row != 0) {
      // FINISH OFF THE ROW;
      while($col++ < NCOLS) {
        $body .= '<td></td>';
      }
      $body .= "</tr>\n<tr>";
    }
    // MOVE OUR ROW MARKER
    $current_row = $row['row_number'];

    // RESET COL COUNT
    $col = 0;
    // TODO: HANDLE MISSED ROWS HERE
  }
  
  // HERE WE SHOULD BE GOOD TO GO WITH OUTPUT OF CELLS
  // LOOP THROUGH BLANKS
  while($col++ < $row['start_cell']) {
    $body .= '<td></td>';
  }
  
  // HERE IS WHERE WE OUTPUT OUR DATA
  $body .= "<td>{$row['title']}</td>";
  
  // NOTHING MORE TO DO THE NEXT ITERATION WILL DO THE WORK
}
// LASTLY WE FINISH OF THE LAST ROW AND CLOSE IT
while($col++ < NCOLS) {
   $body .= '<td></td>';
}
$body .= "</tr>\n";
?>
<table class="table table-border table-striped">
<?php echo $body; ?>
</table>
</body>
</html>
<?php 
function fnDump($obj)
{
  echo "<pre>" . print_r($obj, true) . "</pre>";
}
?>

Open in new window

Take a look at your results loop
foreach($results as $row)
{
        // THIS IS NOT THE SAME AS IN THE SAMPLE
	if($current_row_number<>$row['DataDisplay_Row'])
	{
		$body.="</tr><tr>";
	}

Open in new window

Here is the data for the sample
id   title  start_cell  row_number
1    a      25          1
2    b      43          1
3    c      74          1
4    d      13          2
5    e      21          2
6    f      39          2
7    g      46          2
8    h      83          2
9    i       0          3
10   j      35          3
11   k      62          3
12   l      69          3

Open in new window

The code in the sample is important - you need to check for inequality and not equal to 0
You also don't have the catchup code for blank cells.
Try matching the sample and we can take it from there
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
brucegustPHP DeveloperAuthor Commented:
Homerun, Julian! Homerun!

I've been working for weeks on this project and I can now see the finish line! Thanks so much!
0
Julian HansenCommented:
You are welcome.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.