Creating array from database row entries in specific column of database and using for a foreach loop

Question

I have a database with multiple entries. One specific column of entries I am interested in is called sample_group. If there are 10 rows of entries in sample_group, I am trying to create a array of the unique entries using PHP.

For example, the entries would be "food", "food", "water", "food", "swabs", "swabs", "swabs", "food", "water", "water"and after creating an array should be: array("food", "water", "swabs").

Here is my attempted code, it sort of works but the array has an empty entry at the end:

 $sql3 = "SELECT * FROM samples_database WHERE order_id=$order_id;";
  $result3 = mysqli_query($conn, $sql3); 
$group_array = '';
  while ($input = mysqli_fetch_array($result3)) {
    $group_array .= $input['sample_group'] . ',';
  }
  $group_array2 = array_filter(array_unique(explode(',', $group_array)));

And then the foreach loop:

foreach ($group_array2 as $group) {
//do something
}

Can anybody please push me in the right direction?

why not push to array within the while loop and check if value exists already? — treyBake, Mar 01 '19 at 10:48
No, it is just one word such as "food" in each row of the database in that column. — Roelof Coertze, Mar 01 '19 at 10:49
@roelofco are you only using the `sample_group` value from the query or are you doing something with the other data too? — Nick, Mar 01 '19 at 10:52

Nick · Accepted Answer · 2019-03-01T10:59:23.740

6

It would be much simpler if you made $group_array an actual array:

$group_array = array();
while ($input = mysqli_fetch_array($result3)) {
    $group_array[] = $input['sample_group'];
}
$group_array = array_unique($group_array);
foreach ($group_array as $group) {
    //do something
}

edited Mar 01 '19 at 10:59

answered Mar 01 '19 at 10:48

Nick

138,499
22
57
95

After the array was made, is it better to use a while or foreach loop? – Roelof Coertze Mar 01 '19 at 10:52
@roelofco a foreach is generally my preferred solution – Nick Mar 01 '19 at 10:53
For some reason, and probably not bacause of the array creation, the `foreach` loop is not working. I have a SQL query in the `foreach` loop and the `$group` is a filter variable... Could I be missing a step? – Roelof Coertze Mar 01 '19 at 11:01
@roelofco you could put a `print_r($group_array);` before the `foreach` to verify that it contained what you expected; if it was empty it probably means the query failed for some reason. Otherwise, without seeing the code inside the `foreach` loop it's hard to tell what might be the problem – Nick Mar 01 '19 at 11:04
this is what I am getting `Array ( [0] => Water [1] => Food [2] => Swab ) 1` – Roelof Coertze Mar 01 '19 at 11:07
@roelofco that's good - the first part of the code is working properly. It sounds like the code in the `foreach` loop has some problems. I suggest you ask another question to get some help on that. – Nick Mar 01 '19 at 11:12
Thank you, I am going to accept your answer, because it is consistent with the rest of my code, however in the future I will apply the suggestions and method of the answer by @Tim Biegeleisen. – Roelof Coertze Mar 01 '19 at 11:14

Tim Biegeleisen · Answer 2 · 2019-03-01T11:01:28.627

5

I would suggest just doing a SELECT DISTINCT on the MySQL side:

$sql = "SELECT DISTINCT sample_group FROM samples_database WHERE order_id = ?";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("i", $order_id);
$stmt->execute();
$result = $stmt->get_result();
if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $group_array[] = $row['sample_group'];
    }
}

Note: You should ideally use a prepared statement when building your SQL query, and avoid concatenation. I have attempted to provide code which uses a prepared statement.

Also, I replaced your SELECT * (all columns) with just SELECT sample_group. The reason for this is that for one thing, selecting all columns when your script only needs one of them wastes network bandwidth, and might hurt performance. Another reason is that SELECT * makes it ambiguous about which columns your script really needs to use.

edited Mar 01 '19 at 11:01

answered Mar 01 '19 at 10:49

Tim Biegeleisen

502,043
27
286
360

This is good as long as OP doesn't want any of the other values in his original query – Nick Mar 01 '19 at 10:50
1

@Nick You're right, and I know that...just trying to avoid a `SELECT *` which is generally an evil thing. – Tim Biegeleisen Mar 01 '19 at 10:51
Indeed, there should be a `SELECT *` hammer! :-) – Nick Mar 01 '19 at 10:51
Pardon my ignorance, but why would `SELECT *` be bad? – Roelof Coertze Mar 01 '19 at 10:56
@roelofco I don't reckon you to be ignorant at all actually. `SELECT *` is generally frowned upon, because then it isn't clear _which_ columns your PHP script was actually asking for. Sometimes, you get away with it, but perhaps later on the table definition might change, and then the script would break. – Tim Biegeleisen Mar 01 '19 at 10:57
I see, so in case of two databases having the same column names or similar? – Roelof Coertze Mar 01 '19 at 10:58
It's more just about not being clear what you want to select, and "everything" may change at some point. Also, it wastes bandwidth to select every column when you only want to use one of them. I will update my answer now. – Tim Biegeleisen Mar 01 '19 at 11:00

Creating array from database row entries in specific column of database and using for a foreach loop

2 Answers2