Pull Unique ID's from Database

[racko]

Hi folks,

 

Need some help with a function I'm trying to write a function that pulls data from an MS Access DB.

 

There are a bunch of locations which show items that are due on January, February, etc... I need to show one location per line, with the Grand totals showing which is not due and which is past due. I have the totals part working, but I can only get one location to show up, even though there are around 20 - 30.

[MatthewJ]

It's a lot easier to help if you actually post the code

[Mchl]

Either you're fetching only one row from the result set, or your query is not grouping the results (read on GROUP BY clause).

[racko]

I thought so 

 

Ok Here are three of the functions I'm working with to try and get it going...

function locationCheck(){
extract($GLOBALS);

$rs = $conn->Execute("Select * From Location WHERE account = '" . $_SESSION['account'] . "'");
$number=0;	

	while (!$rs->EOF) {
		$fv = $rs->Fields("Location");
		$Location[$count]=$fv->value;
		$count++;
		$rs->MoveNext();
		$number++;
	}
	return $number;
	$rs->Close();
}

function ListLocations (){
extract($GLOBALS);

$rs = $conn->Execute("Select * From Location WHERE account = '" . $_SESSION['account'] . "'");

$number=locationCheck();
$loc = 0;
while ($loc <= $number){
	while (!$rs->EOF) {
		$fv = $rs->Fields("Location");
		$Location[$count]=$fv->value;
//			echo "<tr /><td  align=\"center\" />". $Location[$count]."</td />";
		return $Location[$count];				
		$count++;
		$rs->MoveNext();

	}
$loc++;
}
	$rs->Close();

}

 

function getSummary(){
extract($GLOBALS);

$number=locationCheck();
$loc = 0;

while ($loc <= $number){
$rs = $conn->Execute("Select * From Hose WHERE Customer = '" . $_SESSION['account'] . "' AND (Status > '0' OR Status <> 'scrap') AND Location = '" . ListLocations() . "'");


$count = 0;



while (!$rs->EOF) {

//	$fv = $rs->Fields("Location");
//	$Location[$count]=$fv->value;

//		echo "<td />" . $Location[$count] . "</td />";	


	$fv = $rs->Fields("Visual");
	$Visual[$count]=$fv->value;
	$due_date = strtotime(date("Y-m-d", strtotime($Visual[$count])) . "+".getVisualRetest()."month");
	$date = date("m", $due_date);
	$date_year = date("Y", $due_date);
	if ($date == "01"){
		if ($date_year < date("Y") || $date <= date("m")){
			$jan_count++;
		}else {
			$jan_due++;
		}

	}if ($date == "02"){
			if ($date_year < date("Y") || $date <= date("m")){
				$feb_count++;
			}else {
				$feb_due++;
			}



	}if ($date == "03"){

			if ($date_year < date("Y") || $date <= date("m")){
				$mar_count++;
			}else {
				$mar_due++;
			}


	}if ($date == "04"){
			 if ($date_year < date("Y") || $date <= date("m")){
				$apr_count++;
			 }else {
				$apr_due++;
		     }

	}if ($date == "05"){
			  if ($date_year < date("Y") || $date <= date("m")){
				$may_count++;
			  }else {
				$may_due++;
		      }

	}if ($date == "06"){
			  if ($date_year < date("Y") || $date <= date("m")){
				$jun_count++;
		   	  }else {
				$jun_due++;
			  }


	}if ($date == "07" && $Visual[$count] != "-"){
			  if ($date_year < date("Y") || $date <= date("m")){
				$jul_count++;
			  }else {
				$jul_due++;
			  }
	}		

	if ($date == "08"){
			  if ($date_year < date("Y") || $date <= date("m")){
				$aug_count++;
			  }else {
				$aug_due++;
			  }

	}if ($date == "09"){
			  if ($date_year < date("Y") || $date <= date("m")){
				$sep_count++;
			  }else {
				$sep_due++;
			  }


	}if ($date == "10"){
			  if ($date_year < date("Y") || $date <= date("m")){
				$oct_count++;
			  }else {
				$oct_due++;
			  }


	}if ($date == "11"){
			  if ($date_year < date("Y") || $date <= date("m")){
				$nov_count++;
			  }else {
				$nov_due++;
			  }


	}if ($date == "12"){
			  if ($date_year < date("Y") || $date <= date("m")){
				$dec_count++;
 			  }else {
				$dec_due++;
			  }


	}


	$count++;
	$rs->MoveNext();
}
$rs->Close();
$total_past=$jan_count+$feb_count+$mar_count+$apr_count+$may_count+$jun_count+$jul_count+$aug_count+$sep_count+$oct_count+$nov_count+$dec_count;
$total_due=$jan_due+$feb_due+$mar_due+$apr_due+$may_due+$jun_due+$jul_due+$aug_due+$sep_due+$oct_due+$nov_due+$dec_due;

		$grand_total=$total_due+$total_past;
		$percentage_due = $total_due/$grand_total*100;
		$percentage_past = $total_past/$grand_total*100;

		$zero_decimal_due = number_format($percentage_due);
		$zero_decimal_past = number_format($percentage_past);


		echo "<tr /><td  align=\"center\" />".ListLocations ()."</td />";
		echo "<td  align=\"center\" bgcolor=\"#00FF00\" />".$total_due."</td />";
		echo "<td  align=\"center\" bgcolor=\"#FF0000\" />".$total_past."</td />";
		echo "<td  align=\"center\" />".$grand_total."</td />";
		echo "<td  align=\"center\" bgcolor=\"#FF0000\" />".$zero_decimal_past."%</td />";

		echo "<td  align=\"center\" bgcolor=\"#00FF00\" />".$zero_decimal_due."%</td /></tr >";


$loc++;

}

}

 

I know I should use case statements instead of all these if's but they work

 

 

 

 

[racko]

I've removed the first while loop from the third function since all it did was print out the same location ID for the total number of locations...

[racko]

I think the problem is with the return on the ListLocations() function, when I just echo  $Location[$count] all the locations list, but the return function only returns the first variable in the list of locations. Even though it's in the while loop???

 

How do I get return to actually return everything 

 

I get the feeling I'm talking to myself at this point  :'(

[Mchl]

Once return is called function stops execution and returns to scope one level above. You can't return more than one value from a function.

[MatthewJ]

Loop them into an array and return the array

[racko]

I just read on google you can't return more then one thing with return...

 

as for the looping array mine only returns array and nothing else..

 

I'm still playing around with the group by clause and it doesn't like me very much but I'll keep plugging away at it

[racko]

Ok, so I'm guessing this is turning into an sql problem (though I'm using an access db )

 

I've done the Group by and it almost works, problem is this:

 

$rs = $conn->Execute("Select Location, Customer, Visual FROM hose WHERE Customer = '" . $_SESSION['account'] . "' AND (Status <> '0' AND Status <> 'scrap') GROUP BY Location, Customer, Visual order by Location ASC");

 

Only way I could get it to work was by adding all the selects in the group by, and if I exclude them, which I can because I need them, I get a PHP Fatal Error...

 

 

[Mchl]

What fatal error? PHP fatal errors can't be caused by running SQL in Access, so it must be something with your PHP syntax

[racko]

What fatal error? PHP fatal errors can't be caused by running SQL in Access, so it must be something with your PHP syntax

 

I get this error:

 

PHP Fatal error:  Uncaught exception 'com_exception' with message '<b>Source:</b> Microsoft JET Database Engine<br/><b>Description:</b> You tried to execute a query that does not include the specified expression 'Customer' as part of an aggregate function.'

 

When I change my code to this:

$rs = $conn->Execute("Select Location, Customer, Visual FROM hose WHERE Customer = '" . $_SESSION['account'] . "' AND (Status <> '0' AND Status <> 'scrap') GROUP BY Location order by Location ASC");

 

 

[racko]

I'm beginning to think this won't work period. Since I need to calculate things that are past due from today and back, and things that are not due from today on. Using group doesn't give me the option to add things up using this technique, for lack of a better word.

 

Here is an image of what I'm trying to calculate, again any ideas would be great

 

 

[attachment deleted by admin]