MySQL PDO fetchAll as an integer index array

Question

I have a table with two columns. Table: ctgtable and columns: id and ctg . Since I am completely moving from previous mysql_* to PDO , I encounter some doolish errors (or possibly a lack of knowledge).

Question

I want to select the entire ctg column (no more than 20 rows in total) into an array with integer indices.

My method

The closest possible solution, in my opinion, was the following:

 <?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 is this the best / only possible method to get the results.

Sample table

 id ctg 01 movie 27 tv 64 sports 

and etc.

Result Example

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

Indexing may or may not start at 0. This does not matter to me. I tried to find such a possible question, but none of them was relevant to my question.

Answer 1

The method that you have is fine. Although, if you do not need an identifier, why do you need to request 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); ?> 

Fewer MySQL restrictions result in less processing time, which ultimately leads to better results.

Answer 2

You can just 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 ?> 

Answer 3

Your solution is fine, you can also call fetchColumn () instead of fetchAll() . If you chained your calls, it will look like this:

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