Fetching data from database using MySQL select in PDO

MySQL select statement or clause is used to select data from MySQL database. Under this walkthrough, we are going to apply MySQL select statement using PDO. SELECT clause of MySQL is one of the most commonly used clauses, especially when fetching data from MySQL database.

Fetching data from table using MySQL select in PDO

First thing would be to connect to the database, in our First PDO tutorial we created our config.inc.php.

To fetch data from database using PDO following methods are used:

  1. Using PDO::FETCH_ASSOC
  2. Using PDO::FETCH_NUM


Using PDO::FETCH_ASSOC
PDO::FETCH_ASSOC is commonly used when fetching data from table inform of associative arrays. Association array is an array that contains strings or characters as keys. The following example shows how to use PDO::FETCH_ASSOC to fetch data from a database table using PDO.

<?php 

require "config.inc.php";

try{
	$query = $con->prepare("SELECT * FROM pdo_table");
	$query->execute();
	
	if($query->rowCount()){
		while($data = $query->fetch(PDO::FETCH_ASSOC)){
			$firstName = $data['firstName']; 
			$secondName = $data['secondName'];
			$email = $data['email']; 
			
			echo "First Name: $firstName<br />";
			echo "Second Name: $secondName<br />";
			echo "Email: $email"; 
			
		}
	} else {
		echo "No data was found in our records";
	}
	
} catch(Exception $e){
	$die($e->getMessage());
}

2. Using PDO::FETCH_NUM
MySQL SELECT clause can also be used hand in hand with PDO::FETCH_NUM to fetch data from database table in form of Indexed arrays. Indexed array is an array that contains numbers as keys and it normly starts it’s index from zero. PDO::FETCH_NUM is commonly used when numbers of columns to be selected are specified and required results is specifically from same same column(s) e.g. SELECT firstName, secondName from table_name. Even without having to specify WHERE clause, our example tells us that we have specified the columns we want and result we need is first name and second name only. That means firstName will be index zero while secondName will be index one. The following example illustrates how to usemysql select along with PDO::FETCH_NUM



<?php 

require "config.inc.php";

try{
	$query = $con->prepare("SELECT firstName, email FROM pdo_table WHERE secondName = ? ");
	$query->bindValue(1, "smile");
	$query->execute();
	
	if($query->rowCount()){
		while($data = $query->fetch(PDO::FETCH_NUM)){
			$firstName = $data[0];
			$email = $data[1];
			
			echo "$firstName and $email";
		}
	}
	
	
	
} catch(Exception $e){
	$die($e->getMessage());
}

Please Read: Difference between bindValue and bindParam in PDO

Difference between PDO::FETCH_ASSOC and PDO::FETCH_NUM

The main difference between PDO::ASSOC and PDO::FETCH_NUM is their array presentation, as we have seen above, PDO::FETCH_ASSOC is an associative array while PDO::FETCH_NUM is an indexed array.