Inserting Country Selection into MySQL PHP-CodePudding

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?

CodePudding user response：

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."'");
...
?>

CodePudding user response：

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;
}