• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 92
  • Last Modified:

Replace column value into another column value in the report

$sql = "select site ,name,
        (select rc_code from tbl_rc where name=nov.name) as rc_code,
        SUM(cpu_count) as vcpu,
        SUM(memory_size_ingb) AS mem_gb,
        SUM(disk_space_size_ingb) as storage_gb,
        SUM(backup) as backup,
        COUNT(date)as daycount,
        (SUM(cpu_count)*(select vcpu from price_tag where site=nov.site)+SUM(memory_size_ingb)*(select memory from price_tag where site=nov.site)+SUM(disk_space_size_ingb)*(select storage from price_tag where site=nov.site)+SUM(backup)*(select backup from price_tag where site=nov.site))*(count(date)/$d) as 'total'        
FROM nov where site = '".$site."'  and month(date) = '".$mdate."' and year(date) = '".$ydate."'
GROUP BY site,name
having rc_code<90000";

Open in new window

I want to generate the report by replace the RC code where the value of 81001 in report is referring to table of m8100x where the column of MRC code is same with the RC code , so if the RC code in the report is 81001 then it display column of RC Code 1 where the value is 3001 and column of RC Code 2 where the value is 1820 but it will not display the column that contain value of 0 .Then if the RC code in the report is 81002 then it will display column of RC Code 1 where the value is 3001,RC Code 2 where the value 5708 , RC Code 3 where the value is 7751 , RC Code 4 is 6001 and RC Code 5 is 5273.... etc
Now I need to add what MySQL statement to make all of RC Code from 81001 until 81008 is replacing the value with the RC Code 1 - RC Code xx in the report.

 

My Report Look like this
I hope my final Report can look like this
0
Scott Yong
Asked:
Scott Yong
  • 6
  • 6
1 Solution
 
NerdsOfTechTechnology ScientistCommented:
First, the design of the table looks off. For best practice, it should be redesigned as a 1:M relationship. This will solve a lot of the issues you are encountering now and in the future (e.g. what happens if a server has 11 rc codes later on?).

Next, to achieve what you are asking from your data presently designed, you would likely need PHP to loop through the raw resultset, rather than a specialized and messy SQL statement (which would probably use UNIONs or CROSS JOIN).

Simply loop through the row results, filter out zeros, and output the information.

Therefore, in the short-term, I recommend that you post your PHP code here, which facilitates the HTML output rather than the SQL statement code portion, so we can modify to achieve the results you seek.
1
 
Scott Yong Author Commented:
<?php
include 'config.php';
require 'rccode_config.php';
require 'm8_config.php';
?>
<!DOCTYPE html>
<html>
<head>
<title>Utility System</title>

<style type="text/css">

header {    
    padding: 10px;
 margin-left:-10px;  
 margin-top:-10px;  
background: 	#778899;
 width:100%;
 height:25px;
 text-align: center;
 font-size:25px;
 color: white;
 font-weight: bold;
   word-spacing: 5px;
    font-family: Arial, "Helvetica Neue", Helvetica, sans-serif;
     text-shadow: -1px 0 black, 0 1px black, 1px 0 black, 0 -1px black;
}

 button{
    background-color:white ;
    border: 1px solid;
    border-color:  #000000 ;
    text-decoration: none;
    font-weight: bold;
    color: #000000;


    text-align: center;
    width: 8%;
	margin: 1px;
 }
.container {
    padding: 16px;
}
.footer{
    padding: 5px;
	 margin-left:-10px;  
	 	 margin-right:-10px; 
		 	 text-align: left;

}
body{
	 text-align: center;
}


label{
	 font-size:25px;
	     font-weight: bold;

}
</style>
</head>
<body>
<div>
<header> Summary Report </header>
</div>


 <div class="container" style="background-color:#f1f1f1">

<?php
if(isset($_POST['gen_submit']))
{
$counter=1;
	$site = $_POST['site'];
$mdate = $_POST['month'];
$ydate = $_POST['year'];
$d=cal_days_in_month(CAL_GREGORIAN,$mdate,$ydate);
$sql = "select site ,name,
        (select rc_code from tbl_rc where name=nov.name) as rc_code,
        SUM(cpu_count) as vcpu,
        SUM(memory_size_ingb) AS mem_gb,
        SUM(disk_space_size_ingb) as storage_gb,
		SUM(backup) as backup,
        COUNT(date)as daycount,
        (SUM(cpu_count)*(select vcpu from price_tag where site=nov.site)+SUM(memory_size_ingb)*(select memory from price_tag where site=nov.site)+SUM(disk_space_size_ingb)*(select storage from price_tag where site=nov.site)+SUM(backup)*(select backup from price_tag where site=nov.site))*(count(date)/$d) as 'total'		
FROM nov where site = '".$site."'  and month(date) = '".$mdate."' and year(date) = '".$ydate."'
GROUP BY site,name
having rc_code<80000";
if($result = mysqli_query($db, $sql)){
    if(mysqli_num_rows($result) > 0){
		echo "<strong><font color=black font face='arial' size='3pt'>Month : $mdate / $ydate</font></strong>";
		echo "     <br /><br />";
        echo "<table width='100%' style = 'border-collapse:collapse'>";
            echo "<tr>";
				echo "<th style = 'width:20px;border:1px solid black' align = 'center'>Item No.</th>";
				echo "<th style = 'width:20px;border:1px solid black' align = 'center'>RC Code</th>";
	            echo "<th style = 'width:20px;border:1px solid black' align = 'center'>Host Name</th>";
				echo "<th style = 'width:20px;border:1px solid black' align = 'center'>Site</th>";
                echo "<th style = 'width:20px;border:1px solid black' align = 'center'>Day</th>";				
                echo "<th style = 'width:20px;border:1px solid black' align = 'center'>vCPU</th>";
                echo "<th style = 'width:20px;border:1px solid black' align = 'center'>vRAM</th>";
                echo "<th style = 'width:20px;border:1px solid black' align = 'center'>Storage</th>";
				echo "<th style = 'width:20px;border:1px solid black' align = 'center'>Backup</th>";
                echo "<th style = 'width:20px;border:1px solid black' align = 'center'>Amount</th>";				
            echo "</tr>";
        while($row = mysqli_fetch_array($result)){
            echo "<tr>";
                echo "<td style = 'width:20px;border:1px solid black' align = 'center'>" . $counter. "</td>";				
                echo "<td style = 'width:20px;border:1px solid black' align = 'center'>" . $row['rc_code'] . "</td>";	
                echo "<td style = 'width:20px;border:1px solid black' align = 'center'>" . $row['name'] . "</td>";				
                echo "<td style = 'width:20px;border:1px solid black' align = 'center'>" . $row['site'] . "</td>";	
				echo "<td style = 'width:20px;border:1px solid black' align = 'center'>" . $row['daycount'] . "</td>";				
                echo "<td style = 'width:20px;border:1px solid black' align = 'center'>" . $row['vcpu'] . "</td>";
                echo "<td style = 'width:20px;border:1px solid black' align = 'center'>" . $row['mem_gb'] . "</td>";
                echo "<td style = 'width:20px;border:1px solid black' align = 'center'>" . $row['storage_gb'] . "</td>";
				echo "<td style = 'width:20px;border:1px solid black' align = 'center'>" . $row['backup'] . "</td>";
				echo "<td style = 'width:20px;border:1px solid black' align = 'center'>" . number_format($row['total'],2) . "</td>";				
            echo "</tr>";
			$counter++;	
        }
        echo "</table>";
        // Free result set
        mysqli_free_result($result);
    } else{
        echo "No records matching your query were found.";
    }
} else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($db);
}
}
    ?>                  
                    
                   
    

</div>
	<div class="footer" style="background-color:#778899">
		   <form action="generate_index.php" method="post" enctype="multipart/form-data">
   <button type="submit" name="btn-return" style="display: inline;">&laquo; Return</button>
    </form>
   <form action="index.php" method="post" enctype="multipart/form-data">
   <button type="submit" name="btn-main" style="display: inline;">Main Page</button>
   </div>
    </form>
</body>
</html>

Open in new window

There is my php code
0
 
Scott Yong Author Commented:
Simply loop through the row results, filter out zeros, and output the information.

I actually can doing the loop and i dont know how to take the column from the table m8100x ..
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
NerdsOfTechTechnology ScientistCommented:
SQL change was needed. The previous query was made into a subquery and LEFT JOIN'ed to m8100x. I also fixed some math order of operations issues by using some extra parentheses.

$sql = "
 SELECT * FROM(
	 SELECT site ,name,
    (SELECT rc_code FROM tbl_rc WHERE name=nov.name) AS rc_code,
     SUM(cpu_count) AS vcpu,
     SUM(memory_size_ingb) AS mem_gb,
     SUM(disk_space_size_ingb) AS storage_gb,
     SUM(backup) AS backup,
     COUNT(date) AS daycount,
     (
	  (SUM(cpu_count) * (SELECT vcpu FROM price_tag WHERE site=nov.site)) 
	+ (SUM(memory_size_ingb) * (SELECT memory FROM price_tag WHERE site=nov.site))
	+ (SUM(disk_space_size_ingb) * (SELECT storage FROM price_tag WHERE site=nov.site))
	+ (SUM(backup)*(SELECT backup FROM price_tag WHERE site=nov.site))
	* (COUNT(date)/$d)
	 ) AS 'total'        
	FROM nov 
	WHERE site = '".$site."' AND MONTH(date) = '".$mdate."' AND YEAR(date) = '".$ydate."'
	GROUP BY site, name
	HAVING rc_code<90000
 ) s1
 LEFT JOIN m8100x m1 ON
 s1.rc_code = m1.mrc_code";

Open in new window


Next, your while loop changes by adding another inner loop when there is a match found in the joined table (using count column as the way to determine such a match and the maxim for the inner loop; if no match is found the output will revert to the rc_code in the nov table):
while($row = mysqli_fetch_array($result)){
	$f = 0; // reset flag for null count from joined table
	if($row['count']==''){
		$rc = 1;
		$f = 1;	// null count use rc_code from nov instead, for loop below once.
	}else{
		$rc = $row['count'];	// count exists, for loop through all rc_code_x.
	}	
	
	for ($i = 1; $i <= $rc; $i++) {
		echo "<tr>";
			echo "<td style = 'width:20px;border:1px solid black' align = 'center'>" . $counter. "</td>";		
		if($f){
			echo "<td style = 'width:20px;border:1px solid black' align = 'center'>" . $row["rc_code"] . "</td>";
		}else{
			echo "<td style = 'width:20px;border:1px solid black' align = 'center'>" . $row["rc_code_{$i}"] . "</td>";
		}
			echo "<td style = 'width:20px;border:1px solid black' align = 'center'>" . $row['name'] . "</td>";				
			echo "<td style = 'width:20px;border:1px solid black' align = 'center'>" . $row['site'] . "</td>";	
			echo "<td style = 'width:20px;border:1px solid black' align = 'center'>" . $row['daycount'] . "</td>";				
			echo "<td style = 'width:20px;border:1px solid black' align = 'center'>" . $row['vcpu'] . "</td>";
			echo "<td style = 'width:20px;border:1px solid black' align = 'center'>" . $row['mem_gb'] . "</td>";
			echo "<td style = 'width:20px;border:1px solid black' align = 'center'>" . $row['storage_gb'] . "</td>";
			echo "<td style = 'width:20px;border:1px solid black' align = 'center'>" . $row['backup'] . "</td>";
			echo "<td style = 'width:20px;border:1px solid black' align = 'center'>" . number_format($row['total'],2) . "</td>";				
		echo "</tr>";
		$counter++;	
    }
}

Open in new window

1
 
Scott Yong Author Commented:
Thank its work , Thank for help sir.
0
 
Scott Yong Author Commented:
$f = 0;
sir this one i not so understand , can explain some more?
0
 
NerdsOfTechTechnology ScientistCommented:
I reset
$f = 0
(AKA boolean FALSE) EACH row iteration. It is used as a 'flag' that tells the script that a JOINED table match either occurs ($f STAYS = 0) or does not ($f = 1).

It's sort of inverted but I rushed the coding. You could invert the logic throughout the code... I might repost to make the flag easier to understand.

Therefore, if rc_code matches a JOINED table mrc_code record, $row['count'] will be set as something (not empty) and is used as the iteration limit for the nested for loop (on non-match the loop is iterated only once). This condition is tested here:

	if($row['count']==''){   // NOT MATCHED to joined table, iterate below loop ONCE. 
		$rc = 1;
		$f = 1;
	}else{   // MATCHED to joined table, iterate below loop from ONE TO N TIMES (N set by count).
		$rc = $row['count'];   
	}

Open in new window

0
 
NerdsOfTechTechnology ScientistCommented:
Re-posted with corrected flag logic (before it was inverted); this makes more sense I think. ADDITIONAL NOTES ADDED:

while($row = mysqli_fetch_array($result)){
	$f = 0; 				// MATCH FLAG. reset MATCH flag to FALSE
	
	if($row['count']==''){			// NOT MATCHED to joined table, iterate below loop ONCE. 
		$rc = 1;			// set N iteration limit to 1
		
	}else{					// MATCHED to joined table, iterate below loop from ONE TO N TIMES (N set by count).
		$rc = $row['count'];		// set N iteration limit to count   
		$f = 1;				// set flag to MATCHED			
	}	
	
	for ($n = 1; $n <= $rc; $n++) {		// loop from 1 to N (iteration limit) then stop. If the N limit is set to 1 (NOT MATCHED) this runs only once.
		echo "<tr>";
			echo "<td style = 'width:20px;border:1px solid black' align = 'center'>" . $counter. "</td>";		
		if(!$f){			// if flag NOT set to MATCHED print rc_code
			echo "<td style = 'width:20px;border:1px solid black' align = 'center'>" . $row["rc_code"] . "</td>";
		}else{				// if flag IS set to MATCHED print rc_code_N
			echo "<td style = 'width:20px;border:1px solid black' align = 'center'>" . $row["rc_code_{$n}"] . "</td>";
		}
			echo "<td style = 'width:20px;border:1px solid black' align = 'center'>" . $row['name'] . "</td>";				
			echo "<td style = 'width:20px;border:1px solid black' align = 'center'>" . $row['site'] . "</td>";	
			echo "<td style = 'width:20px;border:1px solid black' align = 'center'>" . $row['daycount'] . "</td>";				
			echo "<td style = 'width:20px;border:1px solid black' align = 'center'>" . $row['vcpu'] . "</td>";
			echo "<td style = 'width:20px;border:1px solid black' align = 'center'>" . $row['mem_gb'] . "</td>";
			echo "<td style = 'width:20px;border:1px solid black' align = 'center'>" . $row['storage_gb'] . "</td>";
			echo "<td style = 'width:20px;border:1px solid black' align = 'center'>" . $row['backup'] . "</td>";
			echo "<td style = 'width:20px;border:1px solid black' align = 'center'>" . number_format($row['total'],2) . "</td>";				
		echo "</tr>";
		$counter++;	
    }
}

Open in new window

1
 
Scott Yong Author Commented:
Sir , if i add other column like percentage in the back after rc_code_10 will it affect the rc_code row?
0
 
NerdsOfTechTechnology ScientistCommented:
No effect; since the change does neither affect count nor rc_code_1 through rc_code_10.
1
 
Scott Yong Author Commented:
NerdsOfTech i have facing a sorting problem .. the rc code in loop not sorting it , should i create new question to ask ?
0
 
NerdsOfTechTechnology ScientistCommented:
Yes, please ask another question as sorting is a separate issue. Include the SQL in question and a link to this question for reference. It should be very easy to sort from this subquery using ORDER BY outside of the subquery.
0
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.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now