Selecting data | getWhere | selectMax | SelectMin | selectSum | join

Deepak Tailor Image
Deepak Tailor - Oct 20 2020
Selecting data | getWhere | selectMax | SelectMin | selectSum | join

The codeigniter provides us with the query builder class. Which makes it easy to communicate with the database. In this, we can insert, read, update, delete data etc.

The following functions allow you to build SQL SELECT statements.

Load database in constructor :

$this->seller // database connection

get() function

From the database, we have to retrieve the data, so we use the get function.

$this->seller // database connection set
public function select_data()
{
	$query = $this->seller->table('users')
			 ->get();
	echo "<pre>";
	print_r($query->getResult());
}
get() function with limit

If we have to limit it in the query, then we will pass the number in the first parameter.

public function select_data()
{
	$query = $this->seller->table('users')
				 ->get(1);
	echo "<pre>";
	print_r($query->getResult());
}
get() function with limit or offset

can also use the offset parameter along with the limit.

public function select_data()
{
	$query = $this->seller->table('users')
	 ->get(1,2);
	 // first parameter for limit
	 // second patrameter for offset
	echo "<pre>";
	print_r($query->getResult());
}
Select data with getWhere() function

In the getWhere function, we can fetch data with multiple conditions. We do not need to use the gate method separately.

public function select_data()
{
	$query = $this->seller->table('users')
		->getWhere(['id'=>29]);
			
	echo "<pre>";
	print_r($query->getResult());
}
// With multiple conditions
public function select_data()
{
	$query = $this->seller->table('users')
			->getWhere([
				'id'	=>	29,
				'age'	=>	'20',
				'name'	=>	'deepak Tailor'
			]);
	echo "<pre>";
	print_r($query->getResult());
}
Fetch data with selected columns

If we want to use only a few selected columns from the table, then we use the select function. Custom columns can also be selected.

public function select_data()
{
	$query = $this->seller->table('users')
		->select('name,age')
		->getWhere([
			'id'	=>	29
		]);
	echo "<pre>";
	print_r($query->getResult());
}
Fetch data with select max() function

If you want to fetch the result by filtering the maximum or minimum from the table, we can use selectMax() or selectMin() function.

// selectMax 
public function select_data()
{
	$query = $this->seller->table('users')
				->selectMax('age')
				->get();
	echo "<pre>";
	print_r($query->getResult());
}

// selectMin
public function select_data()
{
	$query = $this->seller->table('users')
				->selectMin('age')
				->get();
	echo "<pre>";
	print_r($query->getResult());
}

// selectAvg
public function select_data()
{
	$query = $this->seller->table('users')
				->selectAvg('age','avg_age')
				->get();
	echo "<pre>";
	print_r($query->getResult());
}
Fetch data with sum column value

Let us use a selectSum() function to sum a column.

public function select_data()
{
	$query = $this->seller->table('users')
				->selectSum('age','total_age')
				// select parameter for rename column value
				->selectSum('salary','total_salary')
				// select parameter for rename column value
				->get();
				
	echo "<pre>";
	print_r($query->getResult());
}
public function select_data()
{
	$query = $this->seller->table('users')
			->selectCount('age')
			->selectCount('age','count_total_age') 
			// rename column
			->get();
	echo "<pre>";
	print_r($query->getResult());
}
Fetch data with join function

Join function We use two or more tables to connect. So that the data of both tables can be used.

public function select_data()
{
	$query = $this->seller->table('users')
			->join('order','order.user_id = users.id','left')
			->get();
	echo "<pre>";
	print_r($query->getResult());
}
public function select_data()
{
	$query = $this->seller->table('users')
			->select('order.name as product_name, users.*')
			->join('order','order.user_id = users.id','left')
			->get();
	echo "<pre>";
	print_r($query->getResult());
}
Deepak Tailor Image
Deepak Tailor

My name is Deepak tailor as a fullstack developer. I have been in the IT industry for the last 5 years. For professional and customize web development, you can send inquiry on our email.
----
You can contact him at support@fulstackdev.com