1) I have checked and not found/read anything on SQL Min() and IN implode() together on the web yet.
2) This question is not about how to get a SQL Min() value from a table/standard SQL query, or Get a SQL Min() value from an array. I know how to do that. I also know how to do it with Left Joining tables.
This question is about getting a seperate SQL Min() value for each item in the array when using IN implode().
PS: I have the intention of porting this script to SQL PDO when I go live for security.
The problem
I have an array of id's (INT) that I get from a Sql script on the same page as this one.
The id's are input into the script below using implode(). The implode works fine all the names and the row colums for each member imploded member appears. When I add the SQL Min() to get the min amount EACH member has contributed, I get one record with the min() amount found for ALL the imploded() members combined.
$memberid_arr = array('1','2','3');
$sql = "SELECT * FROM members WHERE memberid IN (".implode(',',$memberid_arr).") AND amount =( select min(amount) FROM members ";
$result = $conn->query($sql);
if ($conn->query($sql) === FALSE) {
echo"error";
die();
} else if ($result->num_rows > 0) {
while($row = $result->fetch_array()) {
$name = "". $row['name']."";
$month = "". $row['month']."";
$amount = "". $row['amount']."";
$member_arr[] = array(
"name" => $name,
"month" => $month,
"amount"=> $amount);
}
echo json_encode($member_arr);
exit();
}
My Answer:
"SELECT member_id, MIN(amount) AS min_amount FROM contributions WHERE member_id IN (" . implode(',', $memberid_arr) . ") GROUP BY member_id";
Rate this post
4 of 5 based on 7725 votesComments