CODEWITHSUNDEEP
phppdo
hjpotter92
hjpotter92

Reputation: 80649

MySQL PDO fetchAll as array with integer index

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.

Upvotes: 6

Views: 16658

Answers (3)

Madara&#39;s Ghost
Madara&#39;s Ghost

Reputation: 175007

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.

Upvotes: 6

Percutio
Percutio

Reputation: 954

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();

Upvotes: 0

user1400718
user1400718

Reputation:

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
?>

Upvotes: 2

Related Questions