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?
Bruce GustPHP DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
Server not found when I tried the link.  Is that the correct URL?
Paul MacDonaldDirector, Information SystemsCommented:
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?
Ray PaseurCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Learn More!
Bruce GustPHP DeveloperAuthor 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 SystemsCommented:
Very happy to have helped,  however circuitously.  Thank you for the points.
Ray PaseurCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.