masher Posted November 1, 2012 Share Posted November 1, 2012 I have the code below. It runs through some CDR files and creates a custom call report per customer request. This portion of the code updates filed in the database so when it calls on the actual reporting part of the script (the ssh towards teh bottom calls on it) it has all the info it needs, such as weather a calls was inbound or outbound ect. The script works as planed but is very slow. It could take u p to 30 minutes to run the sript. I have tested on 2 different servers and it is slow on both. I set up mysql slow query loggin and no query takes more then 1 second. I am not a very good PHP programmer and i was hoping some could point out what I am doing wrong to make the script so slow. Thanks alot! Sorry for the all the grammatical and spelling errors! <html> <form action="NAME.PHP" method="post"> Start Time: <input type="text" name="start"><br> End Time: <input type="text" name="end"> Location: <select name="sv_loc"> <option value="jtown">Johnstown</option> <option value="gahanna">Gahanna</option> <option value="canal">Canal</option> </select> <input type="submit" value="Submit"> </form> </html> <?php mysql_connect( 'lDOMAINt' , 'USER' , 'PASSWORD'); if(!mysql_select_db('DATABASE')) die(mysql_error()); date_default_timezone_set('America/New_York'); // Set to true to display query and argument info // $debug_data = false; $debug_data = true; $result = mysql_query("SELECT * FROM [color=#fff][background=#004]TABLE[/background][/color] WHERE calldate between '$_POST[start]' and '$_POST[end]' "); while($row = mysql_fetch_array($result)){ $sql_insert = "UPDATE TABLE SET In_out = 'in' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert) or die("Insertion Failed: In_out" . mysql_error()); if ($row['src'] == 5101) { $sql_insert1 = "UPDATE TABLE SET In_out = 'out' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert1) or die("Insertion Failed: In_out2" . mysql_error()); $sql_insert2 = "UPDATE [color=#fff][background=#004]TABLE[/background][/color] SET ext_name = '5101' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert2) or die("Insertion Failed: In_out2" . mysql_error()); $sql_insert3 = "UPDATE TABLE SET svc_loc = 'gahanna' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert3) or die("Insertion Failed: In_out2" . mysql_error()); } if ($row['src'] == 5102) { $sql_insert1 = "UPDATE TABLE SET In_out = 'out' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert1) or die("Insertion Failed: In_out2" . mysql_error()); $sql_insert2 = "UPDATE TABLE SET ext_name = '5102' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert2) or die("Insertion Failed: In_out2" . mysql_error()); $sql_insert3 = "UPDATE TABLE SET svc_loc = 'gahanna' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert3) or die("Insertion Failed: In_out2" . mysql_error()); } if ($row['src'] == 5103) { $sql_insert1 = "UPDATE TABLE SET In_out = 'out' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert1) or die("Insertion Failed: In_out2" . mysql_error()); $sql_insert2 = "UPDATE TABLE SET ext_name = '5103' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert2) or die("Insertion Failed: In_out2" . mysql_error()); $sql_insert3 = "UPDATE TABLE SET svc_loc = 'gahanna' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert3) or die("Insertion Failed: In_out2" . mysql_error()); } if ($row['src'] == 5104) { $sql_insert1 = "UPDATE TABLE SET In_out = 'out' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert1) or die("Insertion Failed: In_out2" . mysql_error()); $sql_insert2 = "UPDATE TABLE SET ext_name = '5104' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert2) or die("Insertion Failed: In_out2" . mysql_error()); $sql_insert3 = "UPDATE TABLE SET svc_loc = 'gahanna' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert3) or die("Insertion Failed: In_out2" . mysql_error()); } if ($row['src'] == 5105) { $sql_insert1 = "UPDATE TABLE SET In_out = 'out' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert1) or die("Insertion Failed: In_out2" . mysql_error()); $sql_insert2 = "UPDATE TABLE SET ext_name = '5105' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert2) or die("Insertion Failed: In_out2" . mysql_error()); $sql_insert3 = "UPDATE TABLE SET svc_loc = 'gahanna' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert3) or die("Insertion Failed: In_out2" . mysql_error()); } if ($row['src'] == 5201) { $sql_insert1 = "UPDATE TABLE SET In_out = 'out' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert1) or die("Insertion Failed: In_out2" . mysql_error()); $sql_insert2 = "UPDATE TABLE SET ext_name = '5201' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert2) or die("Insertion Failed: In_out2" . mysql_error()); $sql_insert3 = "UPDATE TABLE SET svc_loc = 'jtown' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert3) or die("Insertion Failed: In_out2" . mysql_error()); } if ($row['src'] == 5202) { $sql_insert1 = "UPDATE TABLE SET In_out = 'out' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert1) or die("Insertion Failed: In_out2" . mysql_error()); $sql_insert2 = "UPDATE TABLE SET ext_name = '5202' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert2) or die("Insertion Failed: In_out2" . mysql_error()); $sql_insert3 = "UPDATE TABLE SET svc_loc = 'jtown' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert3) or die("Insertion Failed: In_out2" . mysql_error()); } if ($row['src'] == 5203) { $sql_insert1 = "UPDATE TABLE SET In_out = 'out' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert1) or die("Insertion Failed: In_out2" . mysql_error()); $sql_insert2 = "UPDATE TABLE SET ext_name = '5203' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert2) or die("Insertion Failed: In_out2" . mysql_error()); $sql_insert3 = "UPDATE TABLE SET svc_loc = 'jtown' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert3) or die("Insertion Failed: In_out2" . mysql_error()); } if ($row['src'] == 5204) { $sql_insert1 = "UPDATE TABLE SET In_out = 'out' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert1) or die("Insertion Failed: In_out2" . mysql_error()); $sql_insert2 = "UPDATE TABLE SET ext_name = '5204' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert2) or die("Insertion Failed: In_out2" . mysql_error()); $sql_insert3 = "UPDATE TABLE SET svc_loc = 'jtown' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert3) or die("Insertion Failed: In_out2" . mysql_error()); } if ($row['src'] == 5301) { $sql_insert1 = "UPDATE TABLE SET In_out = 'out' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert1) or die("Insertion Failed: In_out2" . mysql_error()); $sql_insert2 = "UPDATE TABLE SET ext_name = '5301' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert2) or die("Insertion Failed: In_out2" . mysql_error()); $sql_insert3 = "UPDATE TABLE SET svc_loc = 'canal' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert3) or die("Insertion Failed: In_out2" . mysql_error()); } if ($row['src'] == 5302) { $sql_insert1 = "UPDATE TABLE SET In_out = 'out' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert1) or die("Insertion Failed: In_out2" . mysql_error()); $sql_insert2 = "UPDATE TABLE SET ext_name = '5302' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert2) or die("Insertion Failed: In_out2" . mysql_error()); $sql_insert3 = "UPDATE TABLE SET svc_loc = 'canal' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert3) or die("Insertion Failed: In_out2" . mysql_error()); } if ($row['src'] == 5303) { $sql_insert1 = "UPDATE TABLE SET In_out = 'out' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert1) or die("Insertion Failed: In_out2" . mysql_error()); $sql_insert2 = "UPDATE TABLE SET ext_name = '5303' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert2) or die("Insertion Failed: In_out2" . mysql_error()); $sql_insert3 = "UPDATE TABLE SET svc_loc = 'canal' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert3) or die("Insertion Failed: In_out2" . mysql_error()); } if ($row['src'] == 5304) { $sql_insert1 = "UPDATE TABLE SET In_out = 'out' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert1) or die("Insertion Failed: In_out2" . mysql_error()); $sql_insert2 = "UPDATE TABLE SET ext_name = '5304' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert2) or die("Insertion Failed: In_out2" . mysql_error()); $sql_insert3 = "UPDATE TABLE SET svc_loc = 'canal' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert3) or die("Insertion Failed: In_out2" . mysql_error()); } if ($row['src'] == 5305) { $sql_insert1 = "UPDATE TABLE SET In_out = 'out' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert1) or die("Insertion Failed: In_out2" . mysql_error()); $sql_insert2 = "UPDATE TABLE SET ext_name = '5305' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert2) or die("Insertion Failed: In_out2" . mysql_error()); $sql_insert3 = "UPDATE TABLE SET svc_loc = 'canal' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert3) or die("Insertion Failed: In_out2" . mysql_error()); } if ($row['src'] == 5306) { $sql_insert1 = "UPDATE TABLE SET In_out = 'out' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert1) or die("Insertion Failed: In_out2" . mysql_error()); $sql_insert2 = "UPDATE TABLE SET ext_name = '5306' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert2) or die("Insertion Failed: In_out2" . mysql_error()); $sql_insert3 = "UPDATE TABLE SET svc_loc = 'canal' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert3) or die("Insertion Failed: In_out2" . mysql_error()); } if ($row['src'] == 5310) { $sql_insert1 = "UPDATE TABLE SET In_out = 'out' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert1) or die("Insertion Failed: In_out2" . mysql_error()); $sql_insert2 = "UPDATE TABLE SET ext_name = '5310' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert2) or die("Insertion Failed: In_out2" . mysql_error()); $sql_insert3 = "UPDATE TABLE SET svc_loc = 'canal' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($sql_insert3) or die("Insertion Failed: In_out2" . mysql_error()); } $get_ext = substr("$row[dstchannel]", 4, 4); if (strpos($get_ext,'53') !== false) { $svc_loc = "canal"; $sql_insert4 = "UPDATE TABLE SET svc_loc = '$svc_loc' WHERE dstchannel = '$row[dstchannel]'"; mysql_query($sql_insert4) or die("Insertion Failed: Local" . mysql_error()); $sql_insert5 = "UPDATE TABLE SET ext_name = '$get_ext' WHERE dstchannel = '$row[dstchannel]'"; mysql_query($sql_insert5) or die("Insertion Failed: Local" . mysql_error()); } if (strpos($get_ext,'51') !== false) { $svc_loc = "gahanna"; $sql_insert4 = "UPDATE TABLE SET svc_loc = '$svc_loc' WHERE dstchannel = '$row[dstchannel]'"; mysql_query($sql_insert4) or die("Insertion Failed: Local" . mysql_error()); $sql_insert5 = "UPDATE TABLE SET ext_name = '$get_ext' WHERE dstchannel = '$row[dstchannel]'"; mysql_query($sql_insert5) or die("Insertion Failed: Local" . mysql_error()); } if (strpos($get_ext,'52') !== false) { $svc_loc = "jtown"; $sql_insert4 = "UPDATE TABLE SET svc_loc = '$svc_loc' WHERE dstchannel = '$row[dstchannel]'"; mysql_query($sql_insert4) or die("Insertion Failed: Local" . mysql_error()); $sql_insert5 = "UPDATE TABLE SET ext_name = '$get_ext' WHERE dstchannel = '$row[dstchannel]'"; mysql_query($sql_insert5) or die("Insertion Failed: Local" . mysql_error()); } } include('Net/SSH2.php'); $ssh = new Net_SSH2('localhost'); if (!$ssh->login('******', '********')) { exit('Login Failed'); } echo $ssh->exec("php /path/to/php/file/file.php $_POST[sv_loc] $_POST[start] $_POST[end]"); ?> Quote Link to comment https://forums.phpfreaks.com/topic/270158-slow-php-mysql/ Share on other sites More sharing options...
Muddy_Funster Posted November 1, 2012 Share Posted November 1, 2012 your.....running.....queries.......inside.......a.......loop.....!!! Quote Link to comment https://forums.phpfreaks.com/topic/270158-slow-php-mysql/#findComment-1389299 Share on other sites More sharing options...
masher Posted November 1, 2012 Author Share Posted November 1, 2012 Don't i have to loop through each row to update multiple rows? Quote Link to comment https://forums.phpfreaks.com/topic/270158-slow-php-mysql/#findComment-1389307 Share on other sites More sharing options...
Psycho Posted November 1, 2012 Share Posted November 1, 2012 No, you do not. And you absolutely do not need to run multiple queries to update different fields of the same record(s). I've gone through your code and think it can all be accomplished with just three queries - and no loops. As I don't have your DB and really don't know what you are trying to accomplish, this is not tested. There are other problems I did not address either <html> <form action="NAME.PHP" method="post"> Start Time: <input type="text" name="start"><br> End Time: <input type="text" name="end"> Location: <select name="sv_loc"> <option value="jtown">Johnstown</option> <option value="gahanna">Gahanna</option> <option value="canal">Canal</option> </select> <input type="submit" value="Submit"> </form> </html> <?php mysql_connect( 'lDOMAINt' , 'USER' , 'PASSWORD'); if(!mysql_select_db('DATABASE')) { die(mysql_error()); } date_default_timezone_set('America/New_York'); // Set to true to display query and argument info // $debug_data = false; $debug_data = true; $query = "UPDATE TABLE SET In_out = 'in' WHERE calldate between '$_POST[start]' and '$_POST[end]'"; mysql_query($query) or die("First update failed: " . mysql_error()); $query = "UPDATE TABLE SET In_out = 'out', ext_name = src, svc_loc = CASE WHEN src IN (5101, 5102, 5103, 5104, 5105) THEN 'gahanna' WHEN src IN (5201, 5202, 5203, 5204) THEN 'jtown' WHEN src IN (5301, 5302, 5303, 5304, 5305, 5306, 5310) THEN 'canal' END WHERE calldate between '$_POST[start]' and '$_POST[end]' AND src IN (5101, 5102, 5103, 5104, 5105, 5201, 5202, 5203, 5204, 5301, 5302, 5303, 5304, 5305, 5306, 5310)"; mysql_query($query) or die("Second update failed: " . mysql_error()); $query = "UPDATE TABLE SET ext_name = SUBSTR(dstchannel, 5, 4), svc_loc = CASE WHEN FIND_IN_SET('51', SUBSTR(dstchannel, 5, 4)) THEN 'gahanna' WHEN FIND_IN_SET('52', SUBSTR(dstchannel, 5, 4)) THEN 'jtown' WHEN FIND_IN_SET('53', SUBSTR(dstchannel, 5, 4)) THEN 'canal' ELSE svc_loc END WHERE calldate between '$_POST[start]' and '$_POST[end]'"; mysql_query($query) or die("Third update failed: " . mysql_error()); include('Net/SSH2.php'); $ssh = new Net_SSH2('localhost'); if (!$ssh->login('******', '********')) { exit('Login Failed'); } echo $ssh->exec("php /path/to/php/file/file.php $_POST[sv_loc] $_POST[start] $_POST[end]"); ?> Quote Link to comment https://forums.phpfreaks.com/topic/270158-slow-php-mysql/#findComment-1389312 Share on other sites More sharing options...
PFMaBiSmAd Posted November 1, 2012 Share Posted November 1, 2012 (edited) There's three things that are slowing your code down. 1) You have a wall of repetitious hard-coded logic (all the if(){} statements.) Using a data structure to simplify the logic will speed up the code because there will be less of it to run each pass through the program. 2) You are updating multiple values in any one row by using multiple queries. Using one query to update all the values at once will reduce the execution time of your script to about 1/3 of what it is now. 3) You are running queries inside of a loop. To fix item #1, you need to use a data driven design. What this means is you have a data structure somewhere (array, database query) that defines the data/limits/key-values ... that your code will use. For example, if you have an array that associates the 'src' values (5101, 5102,... = gahanna, 5201, 5202, ... = jtown, ...), all that massive wall of if(){} blocks of code simply becomes - <?php $lookup = array(); // a 'lookup' array of the keys/values $lookup[5101] = 'gahanna'; // other values here... $lookup[5201] = 'jtown'; // other values here... $lookup[5301] = 'canal'; // other values here.... ... if(isset($lookup[$row['src']])){ // The $row['src'] value exists in the $lookup array // $lookup[$row['scr']] is the correct svc_loc value to use in your query } To fix item #2, just use one UPDATE query - UPDATE your_table SET column1 = 'value1', column2 = 'value2', column3='value3' WHERE your_where_condition. Combining #1 and #2, your EXISTING query logic would look like - <?php $lookup = array(); // a 'lookup' array of the keys/values $lookup[5101] = 'gahanna'; // other values here... $lookup[5201] = 'jtown'; // other values here... $lookup[5301] = 'canal'; // other values here.... ... $result = mysql_query("SELECT * FROM TABLE WHERE calldate between '$_POST[start]' and '$_POST[end]' "); while($row = mysql_fetch_array($result)){ // unconditionally set In_out to 'in' (it is set to 'out' later if 'src' is in a specific set of values) $query = "UPDATE TABLE SET In_out = 'in' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($query) or die("Update Failed: $query" . mysql_error()); if(isset($lookup[$row['src']])){ // The $row['src'] value exists in the $lookup array $query = "UPDATE TABLE SET In_out = 'out', ext_name = '{$row['src']}', svc_loc = '{$lookup[$row['src']]}' WHERE uniqueid = '$row[uniqueid]'"; mysql_query($query) or die("Update Failed: $query" . mysql_error()); } $get_ext = substr("$row[dstchannel]", 4, 4); if (strpos($get_ext,'51') !== false) { $svc_loc = "gahanna"; $query = "UPDATE TABLE SET svc_loc = '$svc_loc', ext_name = '$get_ext' WHERE dstchannel = '$row[dstchannel]'"; mysql_query($query) or die("Update Failed: $query" . mysql_error()); } if (strpos($get_ext,'52') !== false) { $svc_loc = "jtown"; $query = "UPDATE TABLE SET svc_loc = '$svc_loc', ext_name = '$get_ext' WHERE dstchannel = '$row[dstchannel]'"; mysql_query($query) or die("Update Failed: $query" . mysql_error()); } if (strpos($get_ext,'53') !== false) { $svc_loc = "canal"; $query = "UPDATE TABLE SET svc_loc = '$svc_loc', ext_name = '$get_ext' WHERE dstchannel = '$row[dstchannel]'"; mysql_query($query) or die("Update Failed: $query" . mysql_error()); } } I don't have time right now (edit: but Psycho did, see his post above), but since the code has been reduced, it is easier to see what it actually is doing (see the forest for the trees), making it easier to eliminate the queries inside of the loop. Edited November 1, 2012 by PFMaBiSmAd Quote Link to comment https://forums.phpfreaks.com/topic/270158-slow-php-mysql/#findComment-1389316 Share on other sites More sharing options...
masher Posted November 1, 2012 Author Share Posted November 1, 2012 Thank you for the help. I truely appreciate it. I knew there had to be a better way. I am still an infant when it comes to mysql and php. Your script worked about 80% and i am able to fix it from here. It was executed very fast though:) Quote Link to comment https://forums.phpfreaks.com/topic/270158-slow-php-mysql/#findComment-1389318 Share on other sites More sharing options...
masher Posted November 1, 2012 Author Share Posted November 1, 2012 This is the new code. Works 100% and is blazing fast. Thanks guys. I had to add an other query and also the FIND_IN_SET was looking for a literal '51' or '52' or '53'. When the actual response was something like '5101' so it was failing there. I just changed the SUBSTR to only out put the first 2 numbers "SUBSTR(dstchannel, 5, 2 )" and that fixed it right up. Once again thanks so much guys. <html> <form action="NAME.PHP" method="post"> Start Time: <input type="text" name="start"><br> End Time: <input type="text" name="end"> Location: <select name="sv_loc"> <option value="jtown">Johnstown</option> <option value="gahanna">Gahanna</option> <option value="canal">Canal</option> </select> <input type="submit" value="Submit"> </form> </html> <?php mysql_connect( 'lDOMAIN' , 'USER' , 'PASSWORD'); if(!mysql_select_db('DATABASE')) { die(mysql_error()); } date_default_timezone_set('America/New_York'); // Set to true to display query and argument info // $debug_data = false; $debug_data = true; $query = "UPDATE table SET In_out = 'in' WHERE calldate between '$_POST[start]' and '$_POST[end]'"; mysql_query($query) or die("First update failed: " . mysql_error()); $query = "UPDATE table SET ext_name = CASE WHEN src IN (5101, 5102, 5103, 5104, 5105) THEN src WHEN src IN (5201, 5202, 5203, 5204) THEN src WHEN src IN (5301, 5302, 5303, 5304, 5305, 5306, 5310) THEN src END WHERE calldate between '$_POST[start]' and '$_POST[end]' AND src IN (5101, 5102, 5103, 5104, 5105, 5201, 5202, 5203, 5204, 5301, 5302, 5303, 5304, 5305, 5306, 5310)"; mysql_query($query) or die("Second update failed: " . mysql_error()); $query = "UPDATE table SET In_out = 'out', ext_name = src, svc_loc = CASE WHEN src IN (5101, 5102, 5103, 5104, 5105) THEN 'gahanna' WHEN src IN (5201, 5202, 5203, 5204) THEN 'jtown' WHEN src IN (5301, 5302, 5303, 5304, 5305, 5306, 5310) THEN 'canal' END WHERE calldate between '$_POST[start]' and '$_POST[end]' AND src IN (5101, 5102, 5103, 5104, 5105, 5201, 5202, 5203, 5204, 5301, 5302, 5303, 5304, 5305, 5306, 5310)"; mysql_query($query) or die("Second update failed: " . mysql_error()); $query = "UPDATE table SET ext_name = SUBSTR(dstchannel, 5, 4), svc_loc = CASE WHEN FIND_IN_SET('51', SUBSTR(dstchannel, 5, 2)) THEN 'gahanna' WHEN FIND_IN_SET('52', SUBSTR(dstchannel, 5, 2)) THEN 'jtown' WHEN FIND_IN_SET('53', SUBSTR(dstchannel, 5, 2)) THEN 'canal' ELSE svc_loc END WHERE calldate between '$_POST[start]' and '$_POST[end]' and In_out like 'in' "; mysql_query($query) or die("Third update failed: " . mysql_error()); include('Net/SSH2.php'); $ssh = new Net_SSH2('localhost'); if (!$ssh->login('******', '********')) { exit('Login Failed'); } echo $ssh->exec("php /path/to/php/file/file.php $_POST[sv_loc] $_POST[start] $_POST[end]"); ?> Quote Link to comment https://forums.phpfreaks.com/topic/270158-slow-php-mysql/#findComment-1389351 Share on other sites More sharing options...
masher Posted November 9, 2012 Author Share Posted November 9, 2012 Maybe you guys could help me with this also. This code runs slowly too. Same issue with the Loops? How to overcome? Thanks! <?php $con = mysql_connect("HOST","USER","PASSWORD"); if (!$con) { die('Could not connect: ' . mysql_error()); } $TABLE = $argv[1]; mysql_select_db("DATABASE", $con); $result = mysql_query("SELECT * FROM $TABLE WHERE lastapp like '%dial%' and dcontext like 'bus_ywca' and lastdata like '%voip1%' "); while($row = mysql_fetch_array($result)) { $get_three = substr("$row[src]", 0, 3); $get_six = substr("$row[src]", 3, 3); $get_three1 = substr("$row[dst]", 0, 3); $get_six1 = substr("$row[dst]", 3, 3); $request_url = "http://localcallingguide.com/xmlrcdist.php?npa1=$get_three&nxx1=$get_six&npa2=$get_three1&nxx2=$get_six1"; $xml = simplexml_load_file($request_url) or die("feed not loading"); $local = $xml->rcdist->islocal; if ($local == "N") { $sql_insert = "UPDATE $database SET local = '$local' WHERE idkwtf = '$row[idkwtf]'"; mysql_query($sql_insert) or die("Insertion Failed: Local" . mysql_error()); } echo $row['src']; echo " "; echo $row['dst']; echo " "; echo $row['start']; echo " "; Echo "done"; Echo "</br>"; } ?> Quote Link to comment https://forums.phpfreaks.com/topic/270158-slow-php-mysql/#findComment-1391359 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.