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

Question

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);

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 :)

i think you want max sum_total from the unique invoice right ? do you want all invoice with that or only sort by desc sum_total — rahul
– rahul, Commented Dec 18, 2015 at 6:19

user3848444 · Accepted Answer · 2015-12-18 11:37:16Z

2

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

answered Dec 18, 2015 at 11:37

user3848444

Sign up to request clarification or add additional context in comments.

Comments

rahul · Accepted Answer · 2015-12-18 06:24:03Z

0

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

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

answered Dec 18, 2015 at 6:24

rahul

7747 silver badges30 bronze badges

9 Comments

rahul Over a year ago

else you can modify it on your requirement or tell me what exactly you want

user3848444 Over a year ago

Rahul but also want all the values from that row.Can't I just get the last row for that particular invoice.Thanks :)

rahul Over a year ago

yeah sure just add the selected values by comma after max('sum_total'), example_field from .....

user3848444 Over a year ago

ya i tried that but it still gets the first row I have no idea why?

rahul Over a year ago

in your db there is no unique field to identify the last inserted record its same values for all the fields even the date except sum_total, can you make your date differ so we can try by fetching record by last updated date time

|

Ajay Makwana · Accepted Answer · 2015-12-18 08:20:24Z

0

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.

edited Dec 18, 2015 at 8:20

answered Dec 18, 2015 at 6:10

Ajay Makwana

2,3821 gold badge19 silver badges32 bronze badges

3 Comments

user3848444 Over a year ago

This gives the same result I still get the first row

rahul Over a year ago

@AjayMakwana your second query will give only one record

Ajay Makwana Over a year ago

you want all the record and the first record should with maximum total? Then try removing group by.

Arun · Accepted Answer · 2015-12-18 11:04:07Z

0

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;

edited Dec 18, 2015 at 11:04

answered Dec 18, 2015 at 9:00

Arun

1469 bronze badges

6 Comments

user3848444 Over a year ago

Thanks Arun I tried both your solutions the first one still gives me the first row.And the 2nd one gives an error saying 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DESC LIMIT 0, 30' at line 1

Arun Over a year ago

I missed to add sum_total for order by in 2nd query. Now edited it. Ok. I need to know one thing clearly, Do u want all records with unique invoiceNo or Final record only?

user3848444 Over a year ago

The second one gives an error #1242 - Subquery returns more than 1 row

Arun Over a year ago

instead of ORDER BY sum_total DESC use ORDER BY GREATEST(sum_total) in 1st query and let me know.

user3848444 Over a year ago

I tried this SELECT * FROM selected_items GROUP BY invoiceNo ORDER BY GREATEST (sum_total) DESC limit 1; it gave an error #1582 - Incorrect parameter count in the call to native function 'GREATEST'

|

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

9 Comments

3 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

9 Comments

3 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related