I can not figure this out...!

Bruce Gust
Bruce Gust used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2016

Commented:
Server not found when I tried the link.  Is that the correct URL?
Paul MacDonaldDirector, Information Systems
Commented:
SELECT TIME1, REGION, ...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 region

SELECT TIME1,AREA, REGION, ...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

...so the first statement is correct, but the second one isn't.  Can you verify what $region_count is for both passes?
Most Valuable Expert 2011
Top Expert 2016
Commented:
Please see:
http://iconoun.com/demo/temp_brucegust.php?sdate=2015-09-02&edate=2015-09-06®ion=CAROLINAS%2FTENNESSEE%2CCENTRAL+TEXAS%2CFLORIDA&toc=rank&x=45&y=9

Here is the code:
<?php // demo/temp_brucegust.php
/**
 * http://www.experts-exchange.com/questions/28712815/I-can-not-figure-this-out.html
 *
 * http://cascsapws1v.nss.vzwnet.com/e2e/EPC_IMS_Report_Card_bg.php?sdate=2015-09-02&edate=2015-09-06&region=CAROLINAS%2FTENNESSEE%2CCENTRAL+TEXAS%2CFLORIDA&toc=rank&x=45&y=9
 */
error_reporting(E_ALL);
echo '<pre>';

var_dump($_GET);

$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.=")";

var_dump($region_array);
var_dump($region_count);
var_dump($the_region_code);

Open in new window

The output shows this:
array(6) {
  ["sdate"]=>
  string(10) "2015-09-02"
  ["edate"]=>
  string(10) "2015-09-06"
  ["region"]=>
  string(41) "CAROLINAS/TENNESSEE,CENTRAL TEXAS,FLORIDA"
  ["toc"]=>
  string(4) "rank"
  ["x"]=>
  string(2) "45"
  ["y"]=>
  string(1) "9"
}




Notice:  Undefined offset: 3 in /home/iconoun/public_html/demo/temp_brucegust.php on line 21

array(3) {
  [0]=>
  string(19) "CAROLINAS/TENNESSEE"
  [1]=>
  string(13) "CENTRAL TEXAS"
  [2]=>
  string(7) "FLORIDA"
}
int(3)
string(76) "(REGION='CAROLINAS/TENNESSEE' OR REGION='CENTRAL TEXAS' OR REGION='FLORIDA')"

Open in new window

The only conclusion I can draw from seeing the interaction of your GET request and the snippet that creates $the_region_code is that somewhere in your script you are either using the wrong variable to create the query, or you are overwriting $the_region_code with the contents of $_GET['region'].

I do not see the confusion in the code posted here, but here is my recommendation.  Get a code scanner, like a "find" or "find in files" and look for every occurrence of the string region.  Somewhere that is wrong.
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Bruce GustPHP Developer

Author

Commented:
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 MacDonaldDirector, Information Systems

Commented:
Very happy to have helped,  however circuitously.  Thank you for the points.
Most Valuable Expert 2011
Top Expert 2016

Commented:
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.

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial