Selecting data | getWhere | selectMax | SelectMin | selectSum | join
Deepak Tailor - Oct 20 2020
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
My name is Deepak tailor as a fullstack developer. I have been in the IT industry (PHP, Nodejs, flutter) for the last 5 years. For professional and customize web development & app development, you can send inquiry on our email.
----
You can contact him at deepaktailor10@yahoo.in