0

I have used below code. I'm new to PHP and Mysql...

Am I doing something wrong in loop.?

abc table has 70562 records ip2 table has 3833421 records..

Any suggestion for improvement.? 

$sql = "SELECT * FROM abc where ip_country IS NULL";

$query = mysqli_query($con, $sql) or die (mysqli_error($con));

while ($row = mysqli_fetch_array($query)) {
    $id = $row["subid"];
    $ip = $row["ip"];

    //Convert Ip to Number 
    $ipnumberconversion = ip2long($ip);

    //Select Ip From Ip Table
    $sql2 = "UPDATE abc as a ,
                    ip2 as b
            SET a.ip_state = b.REGION,
                a.ip_postalcode = b.ZIPCODE,
                a.ip_city = b.CITY, 
                a.ip_country = b.COUNTRY_NAME  
            WHERE a.subid = '$id' AND b.IP_FROM <= $ipnumberconversion AND b.IP_TO >= $ipnumberconversion";
    $query2 = mysqli_query($con, $sql2) or die (mysqli_error($con));
 }
rf jm
  • 33
  • 1
  • 5
  • 1
    `ip2long()` http://php.net/manual/en/function.ip2long.php exists, and even for mysql https://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_inet-aton - you could use a SQL join instead of a loop to connect the two tables. Or in other words: Yes, your code is very inefficient. – Sven Jul 15 '15 at 21:46
  • looks like a one off thing? if so does it matter –  Jul 15 '15 at 21:48
  • exactly what's wrong with this code? high cpu usage isn't a programming "problem"... it's a design issue. – Marc B Jul 15 '15 at 21:48
  • hardware is cheaper than labour, well that's what i tell my boss ;-) –  Jul 15 '15 at 21:49
  • Updated based on @Sven suggestions – rf jm Jul 16 '15 at 13:59

1 Answers1

-2

If you trying this on localhost, its normal. The biggest part of CPU usage cause the mysql process(concretely updating the values in table).

There are many possibilities. Low CPU performance, bad settings of MySQL,etc

Michael Novotný
  • 27
  • 4