Assign value PHP variable as column name during fetching

Question

I am trying to fetch the values from columns in a MySQL table using PHP. The variable $batch_id is dynamic, i.e. it changes column name depending on the request. Say, it can adopt value like 'Batch1' or 'Batch10'. I was able to extract the column name into the $batch_id variable, but when I am passing the column name, say 'Batch10' the array $batch_Value is populated with 'Batch10' rather than the values of that column. I am sure there must be a simple solution, but I am stuck. Any help will be appreciated. I am using the following code.

$batch_Value = array();
if($num>0){
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
        extract($row);
        $batch_Value[] = $batch_id;
    }
}

On the left what I am getting and on the right is the expected outcome. But right hand one I am getting when I am using the Column name directly

Show 2 examples of what you get in $row and what you want the result to be. — AbraCadaver
– AbraCadaver, Commented Aug 5, 2021 at 21:39

Fenistil · Accepted Answer · 2021-08-05 21:37:04Z

2

Fetch the data as number-indexed array instead of associative one. Also, don't use extract, it's unsafe because it can overwrite existing variables.

$batch_Value = array();
if($num>0){
    while ($row = $stmt->fetch(PDO::FETCH_NUM)){
      $batch_Value[] = $row[0];
    }
}

Or if the desired column is not the first one, then you can find the value in the $row array (which is an associative one, see print_r($row); for details about it), with the following:

$batch_Value = array();
if($num>0){
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
      $batch_Value[] = $row[$batch_id];
    }
}

answered Aug 5, 2021 at 21:37

Fenistil

3,8111 gold badge29 silver badges33 bronze badges

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

Comments

Dharman · Accepted Answer · 2021-08-05 22:00:32Z

1

Never use extract in your code. This will lead to a number of problems.

PDO has a lot of useful fetch modes. I don't see any reason to use while loop.

You can fetch values from a single column like this:

$batch_Value = $stmt->fetchAll(PDO::FETCH_COLUMN);

This will give you a 1D array with a list of the values from the first column.

If you want to fetch full row, indexed by the first column, then you can use PDO::FETCH_UNIQUE:

$batch_Value = $stmt->fetchAll(PDO::FETCH_UNIQUE);

For the description of all fetch modes see https://phpdelusions.net/pdo/fetch_modes

answered Aug 5, 2021 at 22:00

Dharman♦

33.9k27 gold badges106 silver badges157 bronze badges

1 Comment

Fenistil Over a year ago

You can also fetch an other column using $batch_Value = $stmt->fetchAll(PDO::FETCH_UNIQUE, index); where the index is a 0 based column index.

Collectives™ on Stack Overflow

Assign value PHP variable as column name during fetching

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related