Condition ‘Group By’ in MySql for Unique Results

When there are so many records with same value (same value of a column in each row) and there is need to have only one record of all rows for that particular column, indeed, accumulated values of rest of columns in the rows. In other words, if we need summation or gathered values of different rows in one result so we would utilize the condition ‘group by’ in MySql query.

select * from table_name group by column name

Suppose we have database of different invoices of sale of inventory items. One row show one item and when there are more than one row in an invoice, so invoice number would be same for all items so in all rows of database, column of invoice number would be same. In this scenario, we would extract results for an invoice taking invoice number  group by. The sale amount of each one item would be totaled at glance as invoice amount.

In MySql, table of sales_invoices;

 

invoice_no invoice_date item_name amount
000123 2018-09-03 Item abc 100
000123 2018-09-03 Item xyx 400
000123 2018-09-03 Item zzz 200

 

 

Now we use PHP to extract value of the above invoice.

$invoice = $db_connection->prepare("select *, sum(amount) from sales_invoices group by invoice_no");
$invoice->execute();
while($inv_result = $invoice->fetch(PDO::FETCH_ASSOC)){
echo $inv_result['invoice_no'].' - - - '.$inv_result['sum(amount0'];
}

The result would be: 000123 – – – 700

Database Connection to Connect with MySql through PHP Programming language

Whenever we insert, update, delete or retrieve data from MySql dataabse, we need database connection. Different programming languages have different syntax to interact with database.

Database Connection with MySql through PHP Programming Language 

There are different drivers of PHP which are used to interact with MySql database.

PDO Driver

 

Basic Queries in MySql with PHP

There are so many queries to interact with MySql database, some are used very commonly. One thing should be noted, there is need of database connection to connect with MySql Database. In PHP database connection has be already defined in other one post click here;

TO INSERT DATA INTO DATABASE

insert into table_name (column_1, column_2, column_3) values ('value_1', 'value_2', 'value_3')

with PHP (PDO driver)

$query = “insert into table_name (column_1, column_2, column_3) values (‘value_1’, ‘value_2’, ‘value_3’)”;

$query_execute = $database_connection_variable->prepare($query);

$query_execute->execute();

TO SELECT FROM DATABASE

select column_1, column_2, column_3 from table_name

with PHP (PDO driver)

$query = "select column_1, column_2, column_3 from table_name";

$query_execute = $database_connection_variable->prepare($query);

$query_execute->execute();

TO UPDATE DATA IN DATABASE

update table_name set column_1 = value_1, column_2 = value_2, column_3 = value_3

with PHP (PDO Driver)

$query = "update table_name set column_1 = value_1, column_2 = value_2, column_3 = value_3";

$query_execute = $database_connection_variable->prepare($query);

$query_execute->execute();