如何让这个脚本缩短？

Question

$time = date("G");

$location = unserialize(file_get_contents('http://www.geoplugin.net/php.gp?ip='.$_SERVER['REMOTE_ADDR']));

$countrycode = $location['geoplugin_countryCode'];

    switch($time){
    case 0:
    $conn->query("UPDATE link SET visits_at_00 = visits_at_00 + 1 WHERE id = '$id'");
    break;
    case 1:
    $conn->query("UPDATE link SET visits_at_01 = visits_at_01 + 1 WHERE id = '$id'");
    break;
    case 2:
    $conn->query("UPDATE link SET visits_at_02 = visits_at_02 + 1 WHERE id = '$id'");
    break;
    case 3:
    $conn->query("UPDATE link SET visits_at_03 = visits_at_03 + 1 WHERE id = '$id'");
    break;
    case 4:
    $conn->query("UPDATE link SET visits_at_04 = visits_at_04 + 1 WHERE id = '$id'");
    break;
    case 5:
    $conn->query("UPDATE link SET visits_at_05 = visits_at_05 + 1 WHERE id = '$id'");
    break;
    case 6:
    $conn->query("UPDATE link SET visits_at_06 = visits_at_06 + 1 WHERE id = '$id'");
    break;
    case 7:
    $conn->query("UPDATE link SET visits_at_07 = visits_at_07 + 1 WHERE id = '$id'");
    break;
    case 8:
    $conn->query("UPDATE link SET visits_at_08 = visits_at_08 + 1 WHERE id = '$id'");
    break;
    case 9:
    $conn->query("UPDATE link SET visits_at_09 = visits_at_09 + 1 WHERE id = '$id'");
    break;
    case 10:
    $conn->query("UPDATE link SET visits_at_10 = visits_at_10 + 1 WHERE id = '$id'");
    break;
    case 11:
    $conn->query("UPDATE link SET visits_at_11 = visits_at_11 + 1 WHERE id = '$id'");
    break;
    case 12:
    $conn->query("UPDATE link SET visits_at_12 = visits_at_12 + 1 WHERE id = '$id'");
    break;
    case 13:
    $conn->query("UPDATE link SET visits_at_13 = visits_at_13 + 1 WHERE id = '$id'");
    break;
    case 14:
    $conn->query("UPDATE link SET visits_at_14 = visits_at_14 + 1 WHERE id = '$id'");
    break;
    case 15:
    $conn->query("UPDATE link SET visits_at_15 = visits_at_15 + 1 WHERE id = '$id'");
    break;
    case 16:
    $conn->query("UPDATE link SET visits_at_16 = visits_at_16 + 1 WHERE id = '$id'");
    break;
    case 17:
    $conn->query("UPDATE link SET visits_at_17 = visits_at_17 + 1 WHERE id = '$id'");
    break;
    case 18:
    $conn->query("UPDATE link SET visits_at_18 = visits_at_18 + 1 WHERE id = '$id'");
    break;
    case 19:
    $conn->query("UPDATE link SET visits_at_19 = visits_at_19 + 1 WHERE id = '$id'");
    break;
    case 20:
    $conn->query("UPDATE link SET visits_at_20 = visits_at_20 + 1 WHERE id = '$id'");
    break;
    case 21:
    $conn->query("UPDATE link SET visits_at_21 = visits_at_21 + 1 WHERE id = '$id'");
    break;
    case 22:
    $conn->query("UPDATE link SET visits_at_22 = visits_at_22 + 1 WHERE id = '$id'");
    break;
    case 23:
    $conn->query("UPDATE link SET visits_at_23 = visits_at_23 + 1 WHERE id = '$id'");
    break;
    }

    switch($countrycode){
    case "US":
    $conn->query("UPDATE link SET visits_from_us = visits_from_us + 1 WHERE id = '$id'");
    break;
    case "DE":
    $conn->query("UPDATE link SET visits_from_de = visits_from_de + 1 WHERE id = '$id'");
    break;
    case "FR":
    $conn->query("UPDATE link SET visits_from_fr = visits_from_fr + 1 WHERE id = '$id'");
    break;
    case "MX":
    $conn->query("UPDATE link SET visits_from_mx = visits_from_mx + 1 WHERE id = '$id'");
    break;
    case "TR":
    $conn->query("UPDATE link SET visits_from_tr = visits_from_tr + 1 WHERE id = '$id'");
    break;
    case "IT":
    $conn->query("UPDATE link SET visits_from_it = visits_from_it + 1 WHERE id = '$id'");
    break;
    case "RU":
    $conn->query("UPDATE link SET visits_from_ru = visits_from_ru + 1 WHERE id = '$id'");
    break;
    case "ES":
    $conn->query("UPDATE link SET visits_from_es = visits_from_es + 1 WHERE id = '$id'");
    break;
    case "CN":
    $conn->query("UPDATE link SET visits_from_cn = visits_from_cn+ 1 WHERE id = '$id'");
    break;
    case "AU":
    $conn->query("UPDATE link SET visits_from_au = visits_from_au + 1 WHERE id = '$id'");
    break;
    case "IN":
    $conn->query("UPDATE link SET visits_from_in = visits_from_in + 1 WHERE id = '$id'");
    break;
    case "EN":
    $conn->query("UPDATE link SET visits_from_en = visits_from_en + 1 WHERE id = '$id'");
    break;
    case "CA":
    $conn->query("UPDATE link SET visits_from_ca = visits_from_ca + 1 WHERE id = '$id'");
    break;
    case "SA":
    $conn->query("UPDATE link SETvisits_from_sa = visits_from_sa + 1 WHERE id = '$id'");
    break;
    }

First, I know that this looks crappy, and I have to prepare statements and validate the data, and all that. Anyway I want first to build a basic content, and I want to make this way shorter, now I know how to make a short script with multiple that contains the same steps, but here I don't know where to start, and what to do. Please help me to get some ideas and examples.

Answer 1

Instead of switch you can validate the value of $time and $countrycode and then use it to generate proper query.

Here's an example of how it could look like. Of course I didn't run it, so it may not work just by copy&pasting it.

$time = date("G");

$location = unserialize(file_get_contents('http://www.geoplugin.net/php.gp?ip='.$_SERVER['REMOTE_ADDR']));

$countrycode = $location['geoplugin_countryCode'];

//validation 0<=time<=23
if(0<=$time && $time <= 23) {
    //modify $time to format with leading zero for digits.
    $index = str_pad($time, 2, '0', STR_PAD_LEFT);
    // use it for the query.
    $conn->query("UPDATE link SET visits_at_$index = visits_at_$index + 1 WHERE id = '$id'");
}

// array of all country codes. 
// I've added only three, and you should add here the rest of valid codes
$availableCountryCodes = array('US', 'DE', 'FR'); 
//validate if the country code is in the array, which means it's valid
if(in_array($countrycode, $availableCountryCodes)) {   
    // make it lower case as it's the required format for the query 
    $lowerCountryCode = strtolower($countrycode);
    // use it for the query
    $conn->query("UPDATE link SET visits_from_$lowerCountryCode = visits_from_$lowerCountryCode + 1 WHERE id = '$id'");
}

EDIT:

As @okante noted in the comment it can be optimized to make it one UPDATE query as you're updating the same row twice.

$time = date("G");

$location = unserialize(file_get_contents('http://www.geoplugin.net/php.gp?ip='.$_SERVER['REMOTE_ADDR']));

$countrycode = $location['geoplugin_countryCode'];

$fields = array();

//validation 0<=time<=23
if(0<=$time && $time <= 23) {
    //modify $time to format with leading zero for digits.
    $index = str_pad($time, 2, '0', STR_PAD_LEFT);
    // add to $fields array for later use
    $fields[] = "visits_at_$index = visits_at_$index + 1";
}

// array of all country codes. 
// I've added only three, and you should add here the rest of valid codes
$availableCountryCodes = array('US', 'DE', 'FR'); 
//validate if the country code is in the array, which means it's valid
if(in_array($countrycode, $availableCountryCodes)) {   
    // make it lower case as it's the required format for the query 
    $lowerCountryCode = strtolower($countrycode);
    // add to $fields array for later use
    $fields[] = "visits_from_$lowerCountryCode = visits_from_$lowerCountryCode + 1";
}

// if there's somehting to update
if($fields) {
    // concat the update parts with a comma
    $concatedFields = implode(', ', $fields);
    // and the final query
    $conn->query("UPDATE link SET $concatedFields WHERE id = '$id'");
}