Parse mySQL query results and form array for if/else

Question

I need to get all the values in one column in a table so I can create a whitelist for another query. Here's how I'm getting that:

$stmt = $dbh->prepare("select GROUP_CONCAT( cohort_id SEPARATOR ',') as cohort_id from ( select distinct cohort_id from table_cohorts) as m"); 
$stmt->execute(); 
$row = $stmt->fetch();
$avails = json_encode($row);

If I var_dump $avails, I get this:

string(125) "{"cohort_id":"national_percent,database_percent,cohort_1,cohort_2","0":"national_percent,database_percent,cohort_1,cohort_2"}"

The stuff with "cohort_id" is what I want. I need to form an array so I can drop it in something like this:

(in_array($sortvalue, $arrayofpossibleoptions)) //$sortvalue comes from AJAX

How can I get this in the right format? One further wrinkle, I need to add one additional value to the $arrayofpossibleoptions or something like this, but not sure what the proper syntax is:

(in_array($sortvalue, $arrayofpossibleoptions || 'another' ))

If I var_dump $row, I get this:

array(2) {
  ["cohort_id"]=>
  string(51) "national_percent,database_percent,cohort_1,cohort_2"
  [0]=>
  string(51) "national_percent,database_percent,cohort_1,cohort_2"
}

sounds like you need to explode() that value into an array, which begs the question of why you're doing a group_concat() inside the query, since you're just going to be UNDOING that concatting anyways. kill the concat, do a normal query, fetch in a loop, and build your array in php. — Marc B
– Marc B, Commented Apr 23, 2015 at 14:10
is this in conjuction to what you're trying to do earlier in the whitelisting? you don't need to have that in json string form if its an array already in PHP, (or you need this in your client-side as well?) anyway, its a bad idea all the way just like Marc eloquently stated. — Kevin
– Kevin, Commented Apr 23, 2015 at 14:11
@MarcB Got it. Using some inherited code, that obviously doesn't apply. I changed to this, but it only gives me the first value from the query, not all of them. $stmt = $dbh->prepare("select cohort_id as cohort_id from ( select distinct cohort_id from table_cohorts) as m"); $stmt->execute(); $row = $stmt->fetch(); $categories = explode(",",$row["cohort_id"]); — jonmrich
– jonmrich, Commented Apr 23, 2015 at 14:18
->fetch() only fetches one row. do a while($row = ->fetch()) { $data[] = explode(...) } to build a new array of your exploded stuff. — Marc B
– Marc B, Commented Apr 23, 2015 at 14:21

Kevin · Accepted Answer · 2015-04-23 14:21:08Z

1

If you need this data to be loaded into in_array, then it should be in an array form, you can't use a json string.

$stmt = $dbh->prepare('SELECT DISTINCT cohort_id FROM table_cohorts'); 
$stmt->execute();
$cohort_ids = array();
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $cohort_ids[] = $row['cohort_id'];
}

if(in_array($user_input, $cohort_ids)) {
    // do the rest
} else {
    // oops your input is not any of those
}

answered Apr 23, 2015 at 14:21

Kevin

41.9k12 gold badges57 silver badges72 bronze badges

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

1 Comment

jonmrich Over a year ago

Worked like a charm. I actually figured out a way to do it before your post, but it was WAY less elegant, so yours works perfectly. Thanks.

Collectives™ on Stack Overflow

Parse mySQL query results and form array for if/else

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related