Link to home
Start Free TrialLog in
Avatar of Bruce Gust
Bruce GustFlag for United States of America

asked on

I can not figure this out...!

Here's my URL: http://cascsapws1v.nss.vzwnet.com/e2e/EPC_IMS_Report_Card_bg.php?sdate=2015-09-02&edate=2015-09-06®ion=CAROLINAS%2FTENNESSEE%2CCENTRAL+TEXAS%2CFLORIDA&toc=rank&x=45&y=9

Notice the "region..."

OK, now here comes my sql:

	$q2 = "SELECT TIME1, REGION, 

(100*SUM(DNS_FAILURE)/SUM(DNS_ATTEMPTS)) AS DNS_FAILURE_RATE,
(SUM(`DNS_Latency(ms)`)/SUM(DNS_LATENCY_ATTEMPTS)) AS AVG_DNS_LATENCY,
(100*SUM(UL_PACKECTS_RETRANSMITTED)/SUM(NETWORK_UPLINK_PACKETS)) AS TCP_UL_RETX,
(100*SUM(DL_PACKECTS_RETRANSMITTED)/SUM(NETWORK_DOWNLINK_PACKETS)) AS TCP_DL_RETX,
(SUM(CLIENT_ROUND_TRIP_TIME + SERVER_ROUND_TRIP_TIME)/(1000*SUM(SUCCESSFUL_TCP_FLOWS))) AS `Avg_CombNetwk_RTT(ms)`,
(SUM(CLIENT_ROUND_TRIP_TIME)/(1000*SUM(SUCCESSFUL_TCP_FLOWS))) AS `Avg_Client_RTT(ms)`,
(SUM(SERVER_ROUND_TRIP_TIME)/(1000*SUM(SUCCESSFUL_TCP_FLOWS))) AS `Avg_Server_RTT(ms)`,

(100*SUM(ATTACH_FAILURES)/SUM(ATTACH_ATTEMPTS)) AS ATTACH_FAILURE_RATE,
(SUM(`Attach_Latency(ms)`)/SUM(ATTACH_ATTEMPTS_FOR_LATENCY)) AS `Avg_Attach_Latency(ms)`,
(100*SUM(PDN_FAILURES)/SUM(PDN_ATTEMPTS)) AS PDN_FAILURE_RATE,
(SUM(`PDN_Latency(ms)`)/SUM(PDN_ATTEMPTS_FOR_LATENCY)) AS `Avg_PDN_Latency(ms)`,
(100*SUM(S6A_FAILURES)/SUM(S6A_ATTEMPTS)) AS S6A_FAILURE_RATE,
(100*SUM(S6B_FAILURES)/SUM(S6B_ATTEMPTS)) AS S6B_FAILURE_RATE,
(100*SUM(ERAB_FAILURES)/SUM(ERAB_ATTEMPTS)) AS ERAB_FAILURE_RATE,
(100*SUM(INITCTXTSETUP_FAILURES)/SUM(INITCTXTSETUP_ATTEMPTS)) AS INITCTXTSETUP_FAILURE_RATE,
(100*SUM(SERVICEREQUEST_FAILURES)/SUM(SERVICEREQUEST_ATTEMPTS)) AS SERVICEREQUEST_FAILURE_RATE,

(100*SUM(SESSION_EFFECTIVE)/SUM(CALL_ATTEMPTS)) AS SEER,
(100*SUM(CALL_DROPS)/SUM(CALL_SETUPCOMPLETES)) AS CALL_DROP_RATE,
(100*SUM(CALL_COMPLETION)/SUM(CALL_ATTEMPTS)) AS SCR,
(100*SUM((EXCELLENT_BIN+GOOD_BIN))/SUM(MOS_VALID_SESSIONS)) AS ACQ,
(100*SUM(LOST_PACKETS)/SUM(SENT_PACKETS)) AS RTP_PACKETLOSS,
(SUM(AVG_PACKET_JITTER)/SUM(SESSION_ATTEMPTS)) AS AVG_RTP_PACKETJITTER,
(SUM(CALL_POST_DIAL_DELAY)/SUM(CALL_ATTEMPTS)) AS AVG_SIP_POSTDIALDELAY,
(100*SUM(ONE_WAY_SESSIONS)/SUM(SESSION_ATTEMPTS)) AS RTP_ONEWAYSESSION,
(100*SUM(`Total_Gap_Length(msec)`)/SUM(`Stream_Duration(msec)`)) AS RTP_GAP_DURATION

FROM epc_rc3 WHERE";

Open in new window


...and then I grab the data in my "region" from the URL:

 
$region_array= (explode(",", $_GET['region']));
echo"<br>";
$region_count= count($region_array);
//echo $region_array[0];
echo "<br>";
$the_region_code="(REGION=";
for($i=0; $i<=$region_count; $i++)
{
	$the_region_code.="'";
	$the_region_code.=$region_array[$i];
	if($i<($region_count-1))
	{
		$the_region_code.="'";
		$the_region_code.=" OR REGION=";
	}	
}
$the_region_code.=")";

Open in new window


...and then I finish up my sql:

$q2.=$the_region_code;
$q2.=" AND left(TIME1,10) between '".$sdate."' and '".$edate."' group by region order by region";

Open in new window


When I echo just the $the_region_code, I get this:

(REGION='CAROLINAS/TENNESSEE' OR REGION='CENTRAL TEXAS' OR REGION='FLORIDA')

Perfect!

But when I echo my sql statement, I get this:

SELECT TIME1, REGION, (100*SUM(DNS_FAILURE)/SUM(DNS_ATTEMPTS)) AS DNS_FAILURE_RATE, (SUM(`DNS_Latency(ms)`)/SUM(DNS_LATENCY_ATTEMPTS)) AS AVG_DNS_LATENCY, (100*SUM(UL_PACKECTS_RETRANSMITTED)/SUM(NETWORK_UPLINK_PACKETS)) AS TCP_UL_RETX, (100*SUM(DL_PACKECTS_RETRANSMITTED)/SUM(NETWORK_DOWNLINK_PACKETS)) AS TCP_DL_RETX, (SUM(CLIENT_ROUND_TRIP_TIME + SERVER_ROUND_TRIP_TIME)/(1000*SUM(SUCCESSFUL_TCP_FLOWS))) AS `Avg_CombNetwk_RTT(ms)`, (SUM(CLIENT_ROUND_TRIP_TIME)/(1000*SUM(SUCCESSFUL_TCP_FLOWS))) AS `Avg_Client_RTT(ms)`, (SUM(SERVER_ROUND_TRIP_TIME)/(1000*SUM(SUCCESSFUL_TCP_FLOWS))) AS `Avg_Server_RTT(ms)`, (100*SUM(ATTACH_FAILURES)/SUM(ATTACH_ATTEMPTS)) AS ATTACH_FAILURE_RATE, (SUM(`Attach_Latency(ms)`)/SUM(ATTACH_ATTEMPTS_FOR_LATENCY)) AS `Avg_Attach_Latency(ms)`, (100*SUM(PDN_FAILURES)/SUM(PDN_ATTEMPTS)) AS PDN_FAILURE_RATE, (SUM(`PDN_Latency(ms)`)/SUM(PDN_ATTEMPTS_FOR_LATENCY)) AS `Avg_PDN_Latency(ms)`, (100*SUM(S6A_FAILURES)/SUM(S6A_ATTEMPTS)) AS S6A_FAILURE_RATE, (100*SUM(S6B_FAILURES)/SUM(S6B_ATTEMPTS)) AS S6B_FAILURE_RATE, (100*SUM(ERAB_FAILURES)/SUM(ERAB_ATTEMPTS)) AS ERAB_FAILURE_RATE, (100*SUM(INITCTXTSETUP_FAILURES)/SUM(INITCTXTSETUP_ATTEMPTS)) AS INITCTXTSETUP_FAILURE_RATE, (100*SUM(SERVICEREQUEST_FAILURES)/SUM(SERVICEREQUEST_ATTEMPTS)) AS SERVICEREQUEST_FAILURE_RATE, (100*SUM(SESSION_EFFECTIVE)/SUM(CALL_ATTEMPTS)) AS SEER, (100*SUM(CALL_DROPS)/SUM(CALL_SETUPCOMPLETES)) AS CALL_DROP_RATE, (100*SUM(CALL_COMPLETION)/SUM(CALL_ATTEMPTS)) AS SCR, (100*SUM((EXCELLENT_BIN+GOOD_BIN))/SUM(MOS_VALID_SESSIONS)) AS ACQ, (100*SUM(LOST_PACKETS)/SUM(SENT_PACKETS)) AS RTP_PACKETLOSS, (SUM(AVG_PACKET_JITTER)/SUM(SESSION_ATTEMPTS)) AS AVG_RTP_PACKETJITTER, (SUM(CALL_POST_DIAL_DELAY)/SUM(CALL_ATTEMPTS)) AS AVG_SIP_POSTDIALDELAY, (100*SUM(ONE_WAY_SESSIONS)/SUM(SESSION_ATTEMPTS)) AS RTP_ONEWAYSESSION, (100*SUM(`Total_Gap_Length(msec)`)/SUM(`Stream_Duration(msec)`)) AS RTP_GAP_DURATION FROM epc_rc3 WHERE(REGION='CAROLINAS/TENNESSEE' OR REGION='CENTRAL TEXAS' OR REGION='FLORIDA') AND left(TIME1,10) between '2015-09-02' and '2015-09-06' group by region order by regionSELECT TIME1,AREA, REGION, (100*SUM(DNS_FAILURE)/SUM(DNS_ATTEMPTS)) AS DNS_FAILURE_RATE, (SUM(`DNS_Latency(ms)`)/SUM(DNS_LATENCY_ATTEMPTS)) AS AVG_DNS_LATENCY, (100*SUM(UL_PACKECTS_RETRANSMITTED)/SUM(NETWORK_UPLINK_PACKETS)) AS TCP_UL_RETX, (100*SUM(DL_PACKECTS_RETRANSMITTED)/SUM(NETWORK_DOWNLINK_PACKETS)) AS TCP_DL_RETX, (SUM(CLIENT_ROUND_TRIP_TIME + SERVER_ROUND_TRIP_TIME)/(1000*SUM(SUCCESSFUL_TCP_FLOWS))) AS `Avg_CombNetwk_RTT(ms)`, (SUM(CLIENT_ROUND_TRIP_TIME)/(1000*SUM(SUCCESSFUL_TCP_FLOWS))) AS `Avg_Client_RTT(ms)`, (SUM(SERVER_ROUND_TRIP_TIME)/(1000*SUM(SUCCESSFUL_TCP_FLOWS))) AS `Avg_Server_RTT(ms)`, (100*SUM(ATTACH_FAILURES)/SUM(ATTACH_ATTEMPTS)) AS ATTACH_FAILURE_RATE, (SUM(`Attach_Latency(ms)`)/SUM(ATTACH_ATTEMPTS_FOR_LATENCY)) AS `Avg_Attach_Latency(ms)`, (100*SUM(PDN_FAILURES)/SUM(PDN_ATTEMPTS)) AS PDN_FAILURE_RATE, (SUM(`PDN_Latency(ms)`)/SUM(PDN_ATTEMPTS_FOR_LATENCY)) AS `Avg_PDN_Latency(ms)`, (100*SUM(S6A_FAILURES)/SUM(S6A_ATTEMPTS)) AS S6A_FAILURE_RATE, (100*SUM(S6B_FAILURES)/SUM(S6B_ATTEMPTS)) AS S6B_FAILURE_RATE, (100*SUM(ERAB_FAILURES)/SUM(ERAB_ATTEMPTS)) AS ERAB_FAILURE_RATE, (100*SUM(INITCTXTSETUP_FAILURES)/SUM(INITCTXTSETUP_ATTEMPTS)) AS INITCTXTSETUP_FAILURE_RATE, (100*SUM(SERVICEREQUEST_FAILURES)/SUM(SERVICEREQUEST_ATTEMPTS)) AS SERVICEREQUEST_FAILURE_RATE, (100*SUM(SESSION_EFFECTIVE)/SUM(CALL_ATTEMPTS)) AS SEER, (100*SUM(CALL_DROPS)/SUM(CALL_SETUPCOMPLETES)) AS CALL_DROP_RATE, (100*SUM(CALL_COMPLETION)/SUM(CALL_ATTEMPTS)) AS SCR, (100*SUM((EXCELLENT_BIN+GOOD_BIN))/SUM(MOS_VALID_SESSIONS)) AS ACQ, (100*SUM(LOST_PACKETS)/SUM(SENT_PACKETS)) AS RTP_PACKETLOSS, (SUM(AVG_PACKET_JITTER)/SUM(SESSION_ATTEMPTS)) AS AVG_RTP_PACKETJITTER, (SUM(CALL_POST_DIAL_DELAY)/SUM(CALL_ATTEMPTS)) AS AVG_SIP_POSTDIALDELAY, (100*SUM(ONE_WAY_SESSIONS)/SUM(SESSION_ATTEMPTS)) AS RTP_ONEWAYSESSION, (100*SUM(`Total_Gap_Length(msec)`)/SUM(`Stream_Duration(msec)`)) AS RTP_GAP_DURATION FROM epc_rc3 WHERE (REGION = 'CAROLINAS/TENNESSEE,CENTRAL TEXAS,FLORIDA') AND left(TIME1,10) between '2015-09-02' and '2015-09-06' group by region order by region

Open in new window


Notice the "WHERE (REGION = 'CAROLINAS/TENNESSEE,CENTRAL TEXAS,FLORIDA') " at the end.

What am I doing wrong? Why doesn't "$the_region_code" render properly at the end of my sql string?

Thoughts?
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Server not found when I tried the link.  Is that the correct URL?
SOLUTION
Avatar of Paul MacDonald
Paul MacDonald
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Bruce Gust

ASKER

Paul and Ray...

As always, your willingness to weigh in on these dilemmas is most appreciated.

The problem turned out to be something I didn't even reference on the question, but was brought to light my mind based on your collective input.

I have a couple of "if" statements following that place in the code where I suspected a problem. The "if" statements change the sql statement, so even if it's initially correct, because of a bogus if clause I had later in the page, that correct sql statement was changed into the mess I was contending with.

Your confirmation of the code that I had in place inspired me to look elsewhere for a flaw and I found it down the road with that "if" statement that didn't look especially suspect until after the two of you confirmed the soundness of what I had in place.

In short...BAM! Problem solved.

Thank you!
Very happy to have helped,  however circuitously.  Thank you for the points.
The if() statement may not have been invented by the devil, but it's possible.  And variable name collisions were definitely invented by the devil.  Some ways around these common programming problems appear when we start thinking in terms of object-oriented design patterns.  Mastery of these things consumes a couple of years of college, but you can get s flavor of the ideas from some of the articles here at E-E and the references and links.

https://www.experts-exchange.com/articles/12293/AntiPHPatterns-and-AntiPHPractices.html
https://www.experts-exchange.com/articles/18210/Software-Design-Dependencies.html
https://www.experts-exchange.com/articles/18329/SOLID-Design-in-PHP-Applications.html
https://www.experts-exchange.com/articles/19999/PHP-Design-Avoiding-Globals-with-Dependency-Injection.html