Inserting Country Selection into MySQL PHP

Question

I'm currently developing a site that provides specific news events to users. The purpose of the site is to allow users to select which countries they would like to view news from and see this.

On the user profile page I have provided a checkbox list of all of these countries (which are retrieved from a table called countries from MySQL).

The input name for the checkbox is countries[] as there are multiple countries the user can select from. When the user submits the form and I retrieve the data it is displayed as an array which is correct:

array(252) { [0]=> string(1) "1" [1]=> string(1) "2" [2]=> string(1) "3" [3]=> string(1) "4" [4]=> string(1) "5" [5]=> string(1) "6" [6]=> string(1) "7" [7]=> string(1) "8" [8]=> string(1) "9" [9]=> string(2) "10" [10]=> string(2) "11" [11]=> string(2) "12" [12]=> string(2) "13" [13]=> string(2) "14" [14]=> string(2) "15" [15]=> string(2) "16" [16]=> string(2) "17" [17]=> string(2) "18" [18]=> string(2) "19" [19]=> string(2) "20" [20]=> string(2) "21" [21]=> string(2) "22" [22]=> string(2) "23" [23]=> string(2) "24" [24]=> string(2) "25" [25]=> string(2) "26" [26]=> string(2) "27" [27]=> string(2) "28" [28]=> string(2) "29" [29]=> string(2) "30" [30]=> string(2) "31" [31]=> string(2) "32" [32]=> string(2) "33" [33]=> string(2) "34" [34]=> string(2) "35" [35]=> string(2) "36" [36]=> string(2) "37" [37]=> string(2) "38" [38]=> string(2) "39" [39]=> string(2) "40" [40]=> string(2) "41" [41]=> string(2) "42" [42]=> string(2) "43" [43]=> string(2) "44" [44]=> string(2) "45" [45]=> string(2) "46" [46]=> string(2) "47" [47]=> string(2) "48" [48]=> string(2) "49" [49]=> string(2) "50" [50]=> string(2) "51" [51]=> string(2) "52" [52]=> string(2) "53" [53]=> string(2) "54" [54]=> string(2) "55" [55]=> string(2) "56" [56]=> string(2) "57" [57]=> string(2) "58" [58]=> string(2) "59" [59]=> string(2) "60" [60]=> string(2) "61" [61]=> string(2) "62" [62]=> string(2) "63" [63]=> string(2) "64" [64]=> string(2) "65" [65]=> string(2) "66" [66]=> string(2) "67" [67]=> string(2) "68" [68]=> string(2) "69" [69]=> string(2) "70" [70]=> string(2) "71" [71]=> string(2) "72" [72]=> string(2) "73" [73]=> string(2) "74" [74]=> string(2) "75" [75]=> string(2) "76" [76]=> string(2) "77" [77]=> string(2) "78" [78]=> string(2) "79" [79]=> string(2) "80" [80]=> string(2) "81" [81]=> string(2) "82" [82]=> string(2) "83" [83]=> string(2) "84" [84]=> string(2) "85" [85]=> string(2) "86" [86]=> string(2) "87" [87]=> string(2) "88" [88]=> string(2) "89" [89]=> string(2) "90" [90]=> string(2) "91" [91]=> string(2) "92" [92]=> string(2) "93" [93]=> string(2) "94" [94]=> string(2) "95" [95]=> string(2) "96" [96]=> string(2) "97" [97]=> string(2) "98" [98]=> string(2) "99" [99]=> string(3) "100" [100]=> string(3) "101" [101]=> string(3) "102" [102]=> string(3) "103" [103]=> string(3) "104" [104]=> string(3) "105" [105]=> string(3) "106" [106]=> string(3) "107" [107]=> string(3) "108" [108]=> string(3) "109" [109]=> string(3) "110" [110]=> string(3) "111" [111]=> string(3) "112" [112]=> string(3) "113" [113]=> string(3) "114" [114]=> string(3) "115" [115]=> string(3) "116" [116]=> string(3) "117" [117]=> string(3) "118" [118]=> string(3) "119" [119]=> string(3) "120" [120]=> string(3) "121" [121]=> string(3) "122" [122]=> string(3) "123" [123]=> string(3) "124" [124]=> string(3) "125" [125]=> string(3) "126" [126]=> string(3) "127" [127]=> string(3) "128" [128]=> string(3) "129" [129]=> string(3) "130" [130]=> string(3) "131" [131]=> string(3) "132" [132]=> string(3) "133" [133]=> string(3) "134" [134]=> string(3) "135" [135]=> string(3) "136" [136]=> string(3) "137" [137]=> string(3) "138" [138]=> string(3) "139" [139]=> string(3) "140" [140]=> string(3) "141" [141]=> string(3) "142" [142]=> string(3) "143" [143]=> string(3) "144" [144]=> string(3) "145" [145]=> string(3) "146" [146]=> string(3) "147" [147]=> string(3) "148" [148]=> string(3) "149" [149]=> string(3) "150" [150]=> string(3) "151" [151]=> string(3) "152" [152]=> string(3) "153" [153]=> string(3) "154" [154]=> string(3) "155" [155]=> string(3) "156" [156]=> string(3) "157" [157]=> string(3) "158" [158]=> string(3) "159" [159]=> string(3) "160" [160]=> string(3) "161" [161]=> string(3) "162" [162]=> string(3) "163" [163]=> string(3) "164" [164]=> string(3) "165" [165]=> string(3) "166" [166]=> string(3) "167" [167]=> string(3) "168" [168]=> string(3) "169" [169]=> string(3) "170" [170]=> string(3) "171" [171]=> string(3) "172" [172]=> string(3) "173" [173]=> string(3) "174" [174]=> string(3) "175" [175]=> string(3) "176" [176]=> string(3) "177" [177]=> string(3) "178" [178]=> string(3) "179" [179]=> string(3) "180" [180]=> string(3) "181" [181]=> string(3) "182" [182]=> string(3) "183" [183]=> string(3) "184" [184]=> string(3) "185" [185]=> string(3) "186" [186]=> string(3) "187" [187]=> string(3) "188" [188]=> string(3) "189" [189]=> string(3) "190" [190]=> string(3) "191" [191]=> string(3) "192" [192]=> string(3) "193" [193]=> string(3) "194" [194]=> string(3) "195" [195]=> string(3) "196" [196]=> string(3) "197" [197]=> string(3) "198" [198]=> string(3) "199" [199]=> string(3) "200" [200]=> string(3) "201" [201]=> string(3) "202" [202]=> string(3) "203" [203]=> string(3) "204" [204]=> string(3) "205" [205]=> string(3) "206" [206]=> string(3) "207" [207]=> string(3) "208" [208]=> string(3) "209" [209]=> string(3) "210" [210]=> string(3) "211" [211]=> string(3) "212" [212]=> string(3) "213" [213]=> string(3) "214" [214]=> string(3) "215" [215]=> string(3) "216" [216]=> string(3) "217" [217]=> string(3) "218" [218]=> string(3) "219" [219]=> string(3) "220" [220]=> string(3) "221" [221]=> string(3) "222" [222]=> string(3) "223" [223]=> string(3) "224" [224]=> string(3) "225" [225]=> string(3) "226" [226]=> string(3) "227" [227]=> string(3) "228" [228]=> string(3) "229" [229]=> string(3) "230" [230]=> string(3) "231" [231]=> string(3) "232" [232]=> string(3) "233" [233]=> string(3) "234" [234]=> string(3) "235" [235]=> string(3) "236" [236]=> string(3) "237" [237]=> string(3) "238" [238]=> string(3) "239" [239]=> string(3) "240" [240]=> string(3) "241" [241]=> string(3) "242" [242]=> string(3) "243" [243]=> string(3) "244" [244]=> string(3) "245" [245]=> string(3) "246" [246]=> string(3) "247" [247]=> string(3) "248" [248]=> string(3) "249" [249]=> string(3) "250" [250]=> string(3) "251" [251]=> string(3) "252" }

PHP Function Code:

public static function postPreferences($selected_countries) {

    //$selected_countries is the raw $POST data for countries[]
    //Request::post('countries'))

    $database = DatabaseFactory::getFactory()->getConnection();

    $sql = "INSERT INTO user_settings (user_id, country_selection) 
            VALUES (:user_id, :country_selection)";
                
    $query = $database->prepare($sql);
        
    $query->execute(array(':user_id' => Session::get('user_id'),
        ':country_selection' => $selected_countries));

    if ($query->rowCount() == 1) {
        return true;
    }
        
    return false;
}

This isn't working for me, and I suspect it has something to do with the fact that I am trying to input an array into another array in the execute function.

Any help will be appreciated. I am also open to a better way of doing this if there are any suggestions :)

EDIT:

As others have suggested, implode works and stores the value in the database as the following:

1,2,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252

However, is there a more efficient way of storing this much data?

Is `country_selection` supposed to be a comma-separated list of countries? That's a bad idea, but in that case you should be inserting `implode(',', $selected_countries)` — Barmar, Apr 11 '22 at 22:07
@Barmar how would you suggest to do this in a much better way? Considering that there are so many countries that are selected which I want to attribute to the user — John Paul, Apr 11 '22 at 22:12

Andrew Larsen · Answer 1 · 2022-04-12T00:08:17.547

EDIT: Updated my answer as my last code example didn't expect a comma separated string from db.

(code is not tested and might not work as expected)

<?php

public static function postPreferences($selected_countries) {

    $database = DatabaseFactory::getFactory()->getConnection();

    // Get existing selections
    $sqlExisting = 'SELECT country_selection FROM user_settings WHERE user_id = :user_id';
    $queryExisting = $database->prepare($sqlExisting);
    $queryExisting->execute([
        ':user_id' => Session::get('user_id')
    ]);

    $existingSelections = null;

    if ($queryExisting->rowCount() > 0) {
        $existingSelections = $queryExisting->fetchRows(); // dont know this DatabaseFactory, just an example, expects an associative array
    }

    // Insert
    if ($existingSelections === null && is_array($selected_countries) && count($selected_countries) > 0) {
        $sqlInsert = 'INSERT INTO user_settings (user_id, country_selection)VALUES(:user_id, :country_selection)';
        $queryInsert = $database->prepare($sqlInsert);
        $queryInsert->execute([
            ':user_id' => Session::get('user_id'),
            ':country_selection' => implode(',', $country_selection)
        ]);

    // Update
    } else if (is_array($existingSelections) && count($existingSelections) === 1 && $existingSelections[0]['country_selection'] !== implode(',', $country_selection)) {
        $sqlUpdate = 'UPDATE user_settings SET country_selection = :country_selection WHERE user_id = :user_id';
        $queryUpdate = $database->prepare($sqlUpdate);
        $queryUpdate->execute([
            ':user_id' => Session::get('user_id'),
            ':country_selection' => (count($country_selection) === 0 ? null : implode(',', $country_selection))
        ]);
    }

    return true;
}

I don't think his `user_settings` table has a row per country. — Barmar, Apr 11 '22 at 23:05

Riga · Answer 2 · 2022-04-11T22:38:07.317

You can improve the following example depending on your usage.

Database registration is unnecessary if the user will select a country each time.

If the user is not going to choose a country constantly, you can use both alternatives to the database, depending on preference.

Alternative 1


<?php

$selected_countries = implode(',',$selected_countries);

// INSERT OR UPDATE OR ONLY POST AFTER

$user_countries = "1,2,3,4";
$query = "SELECT * FROM news where country_id IN (".$user_countries.")";
...
?>

Alternative 2


<?php

foreach($selected_countries as $country_id)
{
    // You may need to delete or update the previous country selections of the user in order to avoid duplicate registration here.
    
    $query = "INSERT user_selected_countries (user_id,country_id) values ('1','".$country_id."')";
}

// INSERT OR UPDATE AFTER

$user_id = $_SESSION['user_id'];

$query = "SELECT n.* FROM news n INNER JOIN user_selected_countries uc ON n.country_id=uc.country_id where uc.user_id='".$user_id."'");
...
?>

Inserting Country Selection into MySQL PHP

2 Answers2