CODEWITHSUNDEEP
phpmysql
user3848444
user3848444

Reputation:

How to access a particular value from mysql database using php?

This is my php script that selects everything from invoiceNo where invoiceNo is distinct.

<?php

require 'init.php';
$query = 'SELECT * FROM  `selected_items` WHERE invoiceNo IN ( SELECT DISTINCT ( invoiceNo) AS invoiceNo FROM selected_items ) GROUP BY invoiceNo;';
$res = mysqli_query($con, $query);
$result = [];
while ($row = mysqli_fetch_array($res)) {
    array_push($result, [
        'custInfo'    => $row[0],
        'invoiceNo'   => $row[1],
        'barcode'     => $row[2],
        'description' => $row[3],
        'weight'      => $row[4],
        'rate'        => $row[5],
        'makingAmt'   => $row[6],
        'net_rate'    => $row[7],
        'itemTotal'   => $row[8],
        'vat'         => $row[9],
        'sum_total'   => $row[10],
        'bill_type'   => $row[11],
        'date'        => $row[12],
        'advance'     => $row[13],
        'balance'     => $row[14],
    ]);
}
echo json_encode(['result' => $result]);
mysqli_close($con);

enter image description here

Right now this script gives me the first value from sum_total i.e it gives me the first row from my database how can I get the last row.I am new to programming any suggestions or help is appreciated.Thanks :)

Upvotes: 5

Views: 121

Answers (4)

user3848444
user3848444

Reputation: 

Select * From (
SELECT t.*, 
       @rownum := @rownum + 1 AS rank
  FROM selected_items t, 
       (SELECT @rownum := 0) r order by rank DESC
) si GROUP BY si.invoiceNo;

This query solved my problem

Upvotes: 2

Arun
Arun

Reputation: 146

If you need to get only last record use limit.

$query ="SELECT * FROM  `selected_items` GROUP BY invoiceNo ORDER BY `sum_total` DESC limit 1;

If you need to get highest to lowest sum_total record try the below code,

$query ="SELECT * FROM `selected_items` where `sum_total` = (SELECT max( `sum_total` ) FROM `selected_items` GROUP BY invoiceNo) GROUP BY invoiceNo ORDER BY `sum_total` DESC;

Upvotes: 0

Ajay Makwana
Ajay Makwana

Reputation: 2372

try like this :

$query ="SELECT * FROM  `selected_items` WHERE invoiceNo IN ( SELECT DISTINCT ( invoiceNo) AS invoiceNo FROM selected_items ) ORDER BY `sum_total` DESC";
$query ="SELECT max( `sum_total` ) FROM selected_items";

Where column_name can be vary.

Upvotes: 0

rahul
rahul

Reputation: 774

Try this, i think this is you want, may it help

$query ="SELECT max( `sum_total` ) FROM `selected_items` GROUP BY invoiceNo;";

Upvotes: 0

Related Questions