Avatar of Bruce Gust
Bruce Gust
Flag 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?
PHP

Avatar of undefined
Last Comment
Ray Paseur

8/22/2022 - Mon
Ray Paseur

Server not found when I tried the link.  Is that the correct URL?
SOLUTION
Paul MacDonald

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Ray Paseur

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
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!
Paul MacDonald

Very happy to have helped,  however circuitously.  Thank you for the points.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Ray Paseur

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