Mysql select array of columns based on id

Question

I am trying to fetch mysql columns based on user checkbox. This means if user select one checkbox (ex, value2), then retrieve only value2 named colum from table. Is there any way to explicitly specify in mysql select command to retrieve all the user checked values (may be from an array).

<form action="yyy.php" method="post">
<input type="checkbox" name="check_list[]" value="value 1">
<input type="checkbox" name="check_list[]" value="value 2">
<input type="checkbox" name="check_list[]" value="value 3">
<input type="submit" />
</form>

I tried received values in array and used like following, but it doesnt work

<?php
if(!empty($data = $_POST['check_list']))   
//cut some parts and simplified to focus on my problem
 $sql = "SELECT $data  FROM mydata WHERE id='$id'"; 
?>

In some posts, I noticed using array for the $id like here. Other method may be looping select statement, but I am not sure.

I am not an expert in this field and would like to know any solution [better] solution for this.

`check_list` is an array so `implode` it like `$data = implode(',',$_POST['check_list'])` and then use `$data` in the query — ɹɐqʞɐ zoɹǝɟ, Feb 03 '15 at 02:59

Logan Wayne · Answer 1 · 2015-02-03T03:12:31.210

You can try this. I've included some explanations in a form of comments /* */:

<?php

$check_list = $_POST["check_list"]; /* STORE THE SUBMITTED CHECK LIST DATA */
$counter = count($check_list); /* COUNT TOTAL ARRAY */

for($x=0; $x<=$counter; $x++){ /* START A LOOP BASED ON HOW MANY CHECKBOXES THERE IS */

  if(!empty($check_list[$x])){ /* IF $check_list IS SELECTED */
    $sql = "SELECT `".$data."` FROM mydata WHERE id='".$id."'"; /* YOUR QUERY. CHANGE THE NECESSARY VARIABLES AND DATA */
    /* DON'T FORGET TO EXECUTE THE QUERY HERE */
  } /* END OF IF */

} /* END OF FOR LOOP */

?>

Kevin · Accepted Answer · 2015-02-03T03:19:36.290

If your column names are equivalent to your checkbox values, normally you'd have an array returned.

<input type="checkbox" name="check_list[]" value="col1" />Column 1 <br/>
<input type="checkbox" name="check_list[]" value="col2" />Column 2 <br/>
<input type="checkbox" name="check_list[]" value="col3" />Column 3 <br/>

In $_POST:

Array
(
    [0] => col1
    [1] => col2
    [2] => col3
)

One way is to implode/glue it with a comma to complete your query statement and get explicitly selected column names.

SELECT col1, col2, col3 FROM table_name

You could also add whitelisting into the mix. Here's the idea.

Super rough example:

if(!empty($_POST['check_list'])) {
    $check_list = $_POST['check_list'];
    $predefined_columns = array('col1', 'col2', 'col3'); // for whitelisting

    $filtered_columns = implode(', ', array_intersect($predefined_columns, $check_list));
    $sql = "SELECT $filtered_columns FROM table_name WHERE id = :id";

    $db = new PDO('mysql:host=localhost;dbname=database_name', 'username', 'password');

    $select = $db->prepare($sql);
    $select->bindParam(':id', $id);
    $select->execute();

    while($row = $select->fetch(PDO::FETCH_ASSOC)) {
        print_r($row);
    }
}

implode solved my problem @Ghost , Logan, Feroz. Thanks for the explanation. — , Feb 03 '15 at 07:13

Mysql select array of columns based on id

2 Answers2