MySQL PDO fetchAll as array with integer index

Question

I have a table with two columns. Table: ctgtable and columns: id and ctg. Since I am entirely moving from previous mysql_* functions to PDO, I am facing some ~~foolish~~ mistakes(or maybe lack of knowledge).

Question

I want to select the entire table's ctg column(a total of at most 20 rows) into an array with integer indexes.

My Method

The nearest possible solution in my opinion was this:

<?php
    $sth = $dbh->prepare("SELECT id, ctg FROM ctgtable");
    $sth->execute();
    /* Fetch all of the values of the second column */
    $result = $sth->fetchAll(PDO::FETCH_COLUMN, 1);
    var_dump($result);
?>

Is there any other shorter/better alternative for the same result? Or this is the best/only possible method to fetch results.

Sample Table

id      ctg
01     movie
27       tv
64     sports

etc.

Sample Result

Array( 1 => "tv",
    2 => "movie",
    3 => "anime",
    4 => "game",
    5 => "telugu"
);

The indexing may or may not start from 0. It doesn't matter to me. I tried searching for such a possible question, but none of them seemed relevant to my question.

Do you plan on using the "id" field from your results?

Percutio
– Percutio

2012-09-15 16:08:32 +00:00
Commented Sep 15, 2012 at 16:08 — Percutio
– Percutio, Commented Sep 15, 2012 at 16:08

Madara's Ghost · Accepted Answer · 2012-09-16 07:39:13Z

6

The method you have is fine. Though if you don't need the ID, why would you need to query it?

<?php
    $sth = $dbh->prepare("SELECT ctg FROM ctgtable");
    $sth->execute();
    /* Fetch all of the values in form of a numeric array */
    $result = $sth->fetchAll(PDO::FETCH_ARRAY);
    var_dump($result);
?>

Less constraints on the MySQL leads to less processing time, which eventually leads to better results.

edited Sep 16, 2012 at 7:39

answered Sep 16, 2012 at 7:26

Madara's Ghost

176k52 gold badges274 silver badges314 bronze badges

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

3 Comments

hjpotter92 Over a year ago

fetchAll(PDO::FETCH_ARRAY, 0); The first column. ^_^

Madara's Ghost Over a year ago

@GiantofaLannister: Edited. There's no column number in FETCH_ARRAY. But basically, query only for what you need, if you don't need the ID, don't query for it. It's as simple as that.

aaronbauman Over a year ago

PDO::FETCH_ARRAY is a non-standard PDO constant. see php.net/manual/en/pdostatement.fetchall.php and php.net/manual/en/pdostatement.fetch.php PDO::FETCH_ASSOC is supported on some systems. PDO::FETCH_COLUMN is preferred.

user1400718 · Accepted Answer · 2012-09-15 16:25:23Z

2

You can simply do the following

   <?php
    //connect to db    

    $array = array();//define array

    $query = "SELECT * FROM ctgtable";    
    $result = $pdo->prepare($query);
    $result->execute();

   while ($row = $result->fetch()) {
      $id =  $row['id'];
      $ctg = $row['ctg'];        
      $array[$id] = $ctg;        
  }

 print_r($array);
 //close connection
?>

answered Sep 15, 2012 at 16:25

user1400718

1 Comment

hjpotter92 Over a year ago

Wouldn't this method be more-time consuming?

Percutio · Accepted Answer · 2012-09-15 16:39:56Z

0

The solution you have is alright, you could also call fetchColumn() instead of fetchAll(). If you chained calls it would look like this:

$entries = $dbh->query("SELECT ctg FROM fruit")->fetchColumn();

answered Sep 15, 2012 at 16:39

Percutio

9547 silver badges8 bronze badges

2 Comments

hjpotter92 Over a year ago

This only fetches the first entry of the column. I want all the entries at once.

Madara's Ghost Over a year ago

Chained calls are not recommended, as it hinders readability and may lead to unexpected results. Also, the solution is wrong.

Collectives™ on Stack Overflow

MySQL PDO fetchAll as array with integer index

Question

My Method

Sample Table

Sample Result

3 Answers 3

3 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Question

My Method

Sample Table

Sample Result

3 Answers 3

3 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related