Read mysqli query result into multi-dimensional array

Question

I have a code to read data from MySQL database based on user input sequence, and I want all the result to be stored into a multi-dimensional array to be used in later part. However, the code will only store the last array but not every array. Is there anything wrong with my code?

for($i = 0 ; $i < sizeof($up_gene) ; $i ++){
    $geneName = $up_gene[$i];
    $mysql = "SELECT * FROM clmap WHERE GeneID = '$geneName'";
    $result = mysqli_query($con, $mysql);
    $row = mysqli_fetch_array($result , MYSQLI_NUM);
    $up_temp[$i] = $row;
}

Data in my database would be as followed:

GeneID  | 17_AAG | HUTYG | A_770897 | AICAR | ...
-------------------------------------------------
FR2L1   | 1390   | 6746  | 4316     | 1051  | ...
ALDH1A1 | 288    | 7622  | 12       | 98867 | ...

# other rows is similar to above #

If I input a list of GeneID for example FR2L1, ALDH1A1, ABAT, AAAS, the output array will only have value for last array which in this case is $up_temp[3], the rest from $up_temp[0] to $up_temp[2] are empty. There are millions of rows in the database, but I will only need to select user's input GeneID.

Output of print_r($up_temp[3])

Array ( [0] => F2RL1 [1] => 1390 [2] => 6764 [3] => 4316 [4] => 1051 [5] => 11654 [6] => 1871 [7] => 8863 [8] => 1245 [9] => 6438 [10] => 12693 [11] => 7525 [12] => 10372 [13] => 10625 [14] => 7114 [15] => 12180 [16] => 2760 [17] => 375 [18] => 12525 [19] => 2828 [20] => 6938 [21] => 5926 [22] => 8906 [23] => 9372 [24] => 6341 [25] => 1427 [26] => 3858 [27] => 1194 [28] => 3510 [29] => 2462 [30] => 3301 [31] => 1909 [32] => 3816 [33] => 8901 [34] => 5361 [35] => 2711 [36] => 5294 [37] => 6138 [38] => 10272 [39] => 9893 [40] => 1205 [41] => 10868 [42] => 3016 [43] => 1601 [44] => 315 [45] => 2577 [46] => 6226 [47] => 274 [48] => 1573 [49] => 2681 [50] => 1447 [51] => 5375 [52] => 1381 [53] => 6405 [54] => 11033 [55] => 7915 [56] => 8293 [57] => 3446 [58] => 231 [59] => 9772 [60] => 4684 [61] => 432 [62] => 1815 [63] => 10904 [64] => 41 [65] => 4647 [66] => 3238 [67] => 5488 [68] => 2340 [69] => 4925 [70] => 2513 [71] => 911 [72] => 3736 [73] => 6593 [74] => 1441 [75] => 3098 [76] => 2976 [77] => 4665 [78] => 8056 [79] => 10717 [80] => 53 [81] => 4159 [82] => 9920 [83] => 5673 [84] => 12146 [85] => 2072 [86] => 4753 [87] => 9678 [88] => 6811 [89] => 7519 [90] => 10708 [91] => 1215 [92] => 6837 [93] => 6817 [94] => 6085 [95] => 7160 [96] => 9059 [97] => 7729 [98] => 5330 [99] => 179 [100] => 1020 [101] => 8672 [102] => 9971 [103] => 7189 [104] => 2307 [105] => 2849 [106] => 4897 [107] => 1229 [108] => 11588 [109] => 1923 [110] => 12715 [111] => 2099 [112] => 12035 [113] => 4062 [114] => 1416 [115] => 547 [116] => 1915 [117] => 7689 [118] => 6641 [119] => 11312 [120] => 7079 [121] => 8564 [122] => 2156 [123] => 539 [124] => 4850 [125] => 10205 [126] => 1756 [127] => 1266 [128] => 3468 [129] => 7341 [130] => 8809 [131] => 11949 [132] => 10126 [133] => 3436 [134] => 8066 [135] => 3116 [136] => 4155 [137] => 1637 [138] => 1717 [139] => 2045 [140] => 7511 )

Output of print_r($up_temp[0]) to print_r($up_temp[2]) : empty

UPDATE: it turns out that my $up_gene variable are not properly entered, just realized there are space behind each variables which causes MySQL query return empty for first few variables. The code above do works as expected.

mysqli_fetch_array as mysqli_fetch_assoc($result,$result)

Sundar
– Sundar

2016-01-13 12:46:47 +00:00
Commented Jan 13, 2016 at 12:46 — Sundar
– Sundar, Commented Jan 13, 2016 at 12:46

Philipp Dahse · Accepted Answer · 2016-01-13 13:12:09Z

2

Try to do it like this:

$up_temp = array();
$sql = "select * from clmap where GeneID in(".implode(",",$up_gene).");";
$result = mysqli_query($con, $sql);
while($row = mysqli_fetch_array($result , MYSQLI_NUM)){
    $up_temp[] = $row;
}

Implode the array $up_gene so that all ids are a comma seperated string. Then use mysql in to fetch all rows that have one of you ids. At least run a while loop and store the $row arrays into the multi-dimensional array $up_temp

answered Jan 13, 2016 at 13:12

Philipp Dahse

7483 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Woody Over a year ago

turns out to be my $up_gene variable problem. Your code help me to improve my code, so I accept yours as the answer. Thanks.

Collectives™ on Stack Overflow

Read mysqli query result into multi-dimensional array

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related