need some help

[whitemoss]

Hi guys,

right now, im designing a template page that could display monthly traffic report for some services. as what im doing now, i already make that page as what my lecturer asked for but he advised me not to use hard code.his reason was, if there was other services coming in, he didnt want me to alter that code. right now, i just display the result by using for example:if ($ApplicationCode=='SMS Ceria'), then there was a query to display that result and so on for other applications.so, everytime, there was a new services added in the databases, i should add the other IF statement.so, he didnt want my code to be like that.what he wants was, the new service just need to be added in the database only and my coding should automatically can cater that new service added. so, i've no idea on how to do that:hope anyone could help me n below is my coding:

<select name="ApplicationCode" id="ApplicationCode">
					 <? $query = "Select ServiceName FROM Application order by ApplicationCode;";
						$result = mysql_query($query);
						while ($row = mysql_fetch_array($result)) 
						{ $X = $row["ServiceName"]; ?>
						<option value="<? echo $X; ?>"> <? echo $X; ?> </option> 
						<? echo $X; ?>
						<? } ?>
					 </select>



for ($i=1;$i<=$no_of_days;$i++)
			{	
				$display_date=date("d/m/Y",$dt);
				echo "<tr>";
				echo "<td><div align=\"center\"><span class=\"style10\"><span class=\"style11\"><span class=\"style11\">$i</span></span></span></div></td>";
				echo "<td><div align=\"center\"><span class=\"style10\"><span class=\"style11\"><span class=\"style11\">$display_date</span></span></span></div></td>";

			//get total MO for SMS balance
			if ($ApplicationCode=='SMS Balance')
			{
			$queryMObalance="SELECT COUNT(*) FROM MObalance WHERE DAYOFMONTH(DateSend)=DAYOFMONTH('$date') ";
			$queryMObalance.=" AND MONTH(DateSend)=MONTH('$date') AND YEAR(DateSend)=YEAR('$date')";
			$queryMObalance.=" AND ApplicationCode=1 ";
			$resultMObalance=mysql_result(mysql_query($queryMObalance),0);
			//$rowMObalance=mysql_fetch_array($resultMObalance);
			$totalMObalance=$resultMObalance;
			$grand_totalMO=$grand_totalMO+$totalMObalance;

			echo "<td><div align=\"center\"><span class=\"style10\"><span class=\"style11\"><span class=\"style11\">$totalMObalance</span></span></span></div></td>";

			//get total MT for SMS balance

			$queryMTbalance="SELECT COUNT(*) FROM MTbalance WHERE DAYOFMONTH(DateSend)=DAYOFMONTH('$date') ";
			$queryMTbalance.=" AND MONTH(DateSend)=MONTH('$date') AND YEAR(DateSend)=YEAR('$date')";
			$queryMTbalance.=" AND ApplicationCode=1 ";
			$resultMTbalance=mysql_result(mysql_query($queryMTbalance),0);
			//$rowMTbalance=mysql_fetch_array($resultMTbalance);
			$totalMTbalance=$resultMTbalance;
			$grand_totalMT=$grand_totalMT+$totalMTbalance;

			echo "<td><div align=\"center\"><span class=\"style10\"><span class=\"style11\"><span class=\"style11\">$totalMTbalance</span></span></span></div></td>";

			$day=$day+1;
			$dt = mktime(0, 0, 0, $Month  ,$day, $Year);
			$date=date("Y-m-d",$dt);
			echo "</tr>";
			}

			//MO for SMS Recharge
			else if ($ApplicationCode=='SMS Recharge')
			{
			$queryMOrecharge="SELECT COUNT(*) FROM MOrecharge WHERE DAYOFMONTH(DateSend)=DAYOFMONTH('$date') ";
			$queryMOrecharge.=" AND MONTH(DateSend)=MONTH('$date') AND YEAR(DateSend)=YEAR('$date')";
			$queryMOrecharge.=" AND ApplicationCode=2 ";
			$resultMOrecharge=mysql_result(mysql_query($queryMOrecharge),0);
			//$rowMOrecharge=mysql_fetch_array($resultMOrecharge);
			$totalMOrecharge=$resultMOrecharge;
			$grand_totalMO=$grand_totalMO+$totalMOrecharge;

			echo "<td><div align=\"center\"><span class=\"style10\"><span class=\"style11\"><span class=\"style11\">$totalMOrecharge</span></span></span></div></td>";

			//get total MT for SMS Recharge

			$queryMTrecharge="SELECT COUNT(*) FROM MTrecharge WHERE DAYOFMONTH(DateSend)=DAYOFMONTH('$date') ";
			$queryMTrecharge.=" AND MONTH(DateSend)=MONTH('$date') AND YEAR(DateSend)=YEAR('$date')";
			$queryMTrecharge.=" AND ApplicationCode=2 ";
			$resultMTrecharge=mysql_result(mysql_query($queryMTrecharge),0);
			//$rowMTrecharge=mysql_fetch_array($resultMTrecharge);
			$totalMTrecharge=$resultMTrecharge;
			$grand_totalMT=$grand_totalMT+$totalMTrecharge;

			echo "<td><div align=\"center\"><span class=\"style10\"><span class=\"style11\"><span class=\"style11\">$totalMTrecharge</span></span></span></div></td>";


			$day=$day+1;
			$dt = mktime(0, 0, 0, $Month  ,$day, $Year);
			$date=date("Y-m-d",$dt);
			echo "</tr>";
			}

else
	{
		$curr_month=date("m");
		$curr_year=date("Y");
		$curr_day=date("d");

		$day=1;
		$dt = mktime(0, 0, 0, $curr_month  ,$day, $curr_year);
		$date=date("Y-m-d",$dt);
		$s_date=date("Y-m-d",$dt);
		$no_of_days=$curr_day;
		//echo "no of days".$no_of_days;
		//$grand_totalMO=0;$grand_totalMT=0; //$grand_totalUser=0;
		//$totalMO=0; $totalMT=0; $total_user=0
}
echo "<tr>";
			echo "<td><div align=\"center\"><span class=\"style7\"><span class=\"style11\"><span class=\"style11\"></span></span></span></div></td>";
			echo "<td><div align=\"center\"><span class=\"style7\"><span class=\"style11\"><span class=\"style11\">TOTAL</span></span></span></div></td>";
			echo "<td><div align=\"center\"><span class=\"style7\"><span class=\"style11\"><span class=\"style11\">$grand_totalMO</span></span></span></div></td>";
			echo "<td><div align=\"center\"><span class=\"style7\"><span class=\"style11\"><span class=\"style11\">$grand_totalMT</span></span></span></div></td>";
?>

[bpat1434]

Well, here's what I'd tell you to do:
In the <select> item, don't make the value the text that is displayed. Use the ApplicationCode. Then you can take all your queries, and combine all the commonalities and use variables to make it very general.

<select name="ApplicationCode" id="ApplicationCode">
	 <? $query = "Select ApplicationCode, ServiceName FROM Application order by ApplicationCode;";
	 $result = mysql_query($query);
	 while ($row = mysql_fetch_array($result)) 
	 	 { $X = $row["ServiceName"];
	 	 $y = $row["ApplicationCode"]; ?>
	 <option value="<? echo $y; ?>"> <? echo $X; ?> </option> 
	 <? } ?>
</select>

<!-- Do your other stuff here -->

<!-- General Query -->
<?php
// Be sure to put this in a different page that the form posts to
$id = $_POST['ApplicationCode'];
switch($id){
    case 1:
        $table = 'MObalance';
        break;
    case 2:
        $table = 'MObalance';
        break;
    case 3:
        $table = 'MOrecharge';
        break;
    case 4:
        $table = 'MOrecharge';
        break;
}

$querybalance="SELECT COUNT(*) FROM ".$table." WHERE DAYOFMONTH(DateSend)=DAYOFMONTH('$date') ";
$querybalance.=" AND MONTH(DateSend)=MONTH('$date') AND YEAR(DateSend)=YEAR('$date')";
$querybalance.=" AND ApplicationCode=1 ";
$resultbalance=mysql_result(mysql_query($querybalance),0);
//$rowbalance=mysql_fetch_array($resultbalance);
$totalbalance=$resultbalance;
$grand_total=$grand_total+$totalbalance;

That's closer to what you want.

~Brett

[whitemoss]

ok..i got what r u trying to say..but..fyi, there are 2 types of tables for each services..for example, for SMS Balance, there are MObalance table and MTbalance table.and when came out with the output, it should display both data from both table side by side. so my output should look like this:

  Date                Total MO           Total MT

---

01/01/2005 2 2

for my database, application table consist of ApplicationCode, ServiceName, MOtablename and MTtablename.my lecturer gave me an opinion on how to avoid the use of hard coding by using all the fields in this table.but i've no idea on how to us it.besides that, if i use switch case as u told me in previous post, i still need to add on the case whenever the new service coming in.

thanks in advance..

[whitemoss]

i already can make the result side by side as what i want..which means both Total MO and total MT. but as i said before, it still hard coded..still, i can add the other case for the swith case whenever the new service coming in..hope u guys can help me in finding the ways on how to resolve this problem..really hopeless..i was thinking on using array but i was not familiar enough on array..however i tried to do it, but as i expected, it didnt work.. 🙁