Search unica mysql

Asked

Viewed 60 times

1

I’m doing a db search that needs to resume the sum of a field and the last corresponding value, and grouping them to an index

Query

  $stmt = getConn()->query("SELECT senha,movi,SUM(entrada) as entrada,custo FROM estoquemovimento ORDER BY id DESC");
  $items = $stmt->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_ASSOC | PDO::FETCH_UNIQUE);

Answer

Array
(
    [NbpsCQAMyc] => Array
        (
            [movi] => Adicionado
            [entrada] => 2.000
            [custo] => 3.00
        )

)

is all correct, in the array formatting, only the cost is searching for the first record, and not the last record

I need the search to return the cost related to the last record

php mysql

  • uses LIMIT 1, so it only takes one record

  • even with LIMIT 1, returns the cost for the first record

  • yes, pq cost and the current value of the last purchase, is input and all the weight that entered

1 answer

2


EDIT #1

As noted by the questioner, MariaDB ignores the ORDER BY in subquery: Why is ORDER BY in a FROM Subquery Ignored?

The solution is to use a LIMIT to search all records:

SELECT SUM(entrada) as entrada,custo 
FROM (SELECT * FROM estoquemovimento ORDER BY id DESC LIMIT 999999999) alias_tab

Or a JOIN: LINK

The ORDER BY is applied in the return of your query, i.e., in the result. So he’s ordering by id your return, which in this case is only 1 line.

One option is to subquery, ordering before and selecting the result:

SELECT SUM(entrada) as entrada,custo
FROM (SELECT * FROM estoquemovimento ORDER BY id DESC) alias_tab

Example in Sqlfiddle

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.