78400

How to sort and add my database record? [duplicate]

<strong>Possible Duplicate:</strong> How to sort by previous date in database?

I want to do is select from mysql which is previous date records then from my database records I need to add the amount which is sort by date

my database records:

amount | date 2.0 | 2011-10-01 1.4 | 2011-10-02 2.6 | 2011-10-01 2.3 | 2011-10-02 4.4 | 2011-10-01 5.6 | 2011-10-03 6.3 | 2011-10-01

What I want to display in my interface:

amount | date 2.0 | 2011-10-01 2.6 | 2011-10-01 4.4 | 2011-10-01 6.3 | 2011-10-01 --------------------------- 15.3 | 2011-10-01 1.4 | 2011-10-02 2.3 | 2011-10-02 ---------------------------- 3.7 | 2011-10-02 5.6 | 2011-10-03 ---------------------------- 5.6 | 2011-10-03

my code (it not work):

$Current = date("Y-m-d" ,strtotime("now")); $query=mysql_query("SELECT * from item where date < $Current order by date"); while($re=mysql_fetch_array($query)){ echo $total = $total + $re['amount']; echo $re['date']; }

Isn't this can work nicely ?

thanks

Answer1:

Have you looked into MySQL's WITH ROLLUP option.

Something like this

SELECT date,amount,item_id FROM table GROUP BY date,item_id WITH ROLLUP

Answer2:

You want to retrieve and display all those records anyway, don't you? Then: In your php script you can store the last date it "handled". If the next record has another date value the current "group" is finished -> print the current sum, reset it to zero and store the new date. Print the current record, increase $sum by amount. Repeat until there are no more records.

<?php $pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'localonly', 'localonly'); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); setup($pdo); $prevDate = null; $sum = 0; foreach( $pdo->query('SELECT * FROM soFoo WHERE `date`<CURDATE() ORDER BY `date`', PDO::FETCH_ASSOC) as $row ) { if ( $prevDate!=$row['date'] ) { if ( !is_null($prevDate) ) { echo "-----------------\n", $sum, ' | ', $prevDate, "\n\n"; $sum = 0; } $prevDate=$row['date']; } echo $row['amount'], ' | ', $row['date'], "\n"; $sum += $row['amount']; } function setup($pdo) { $pdo->exec('CREATE TEMPORARY TABLE soFoo ( id int auto_increment, amount Decimal(8,1), `date` Date, primary key(id) )'); $pdo->exec("INSERT INTO soFoo (amount,`date`) VALUES (2.0, '2011-10-01'), (1.4, '2011-10-02'), (2.6, '2011-10-01'), (2.3, '2011-10-02'), (4.4, '2011-10-01'), (5.6, '2011-10-03'), (6.3, '2011-10-01') "); }

prints

2.0 | 2011-10-01 2.6 | 2011-10-01 4.4 | 2011-10-01 6.3 | 2011-10-01 ----------------- 15.3 | 2011-10-01 1.4 | 2011-10-02 2.3 | 2011-10-02 ----------------- 3.7 | 2011-10-02 5.6 | 2011-10-03

Answer3:

1) You can query for each date you wanna show

SELECT *, SUM(amount) FROM table WHERE date = @date;

2) You can query for all the dates, and query for the totals

-- table rows:

SELECT * FROM table WHERE date < @date ORDER BY date;

-- table totals:

SELECT SUM(amount) FROM table WHERE date < @date GROUP BY date ;

You could do 2nd option in just one query, but the performance would be quite worse:

SELECT *, (SELECT SUM(amount) FROM table WHERE date < @date GROUP BY DATE) AS total FROM table WHERE date < @date ORDER BY date;

Recommend

  • How to select sums of certain 'id' and devide the result by groups
  • How to conditionally convert inches to cm in MySQL (or similar conversions during SELECT)?
  • Adding a pivot to a rollup
  • How to use aggregate SUM function on result of SUM in INNER JOIN?
  • Converting mysql date from one format to another
  • strtotime() converts a non existing date to another date
  • Show a link in fpdf
  • cURL not working while fetching Google API data
  • output json array in php
  • Can I run Robotium tests automatically in parallel on multiple emulators?
  • Android : Strike out Text with bold or thicker line than default STRIKE_THRU_TEXT_FLAG
  • Swift gives “self used before all stored procedures are initialized” error when building child nodes
  • Storing data from SQL in array
  • end daemon processes with multiprocessing module
  • How to search a CSV file with php by checking if a date falls between 2 ranges
  • SQL: Getting the physical size of a subset of a table
  • pip in virtualenv gets ConnectTimeoutError
  • Add dynamic data to line chart from mysql database with highcharts
  • MYSQ & MVC3 SQL connection error \\ ProviderManifestToken but I am using MySQL
  • Stop Bash Script if Hive Fails
  • How to add git credentials to the build so it would be able to be used within a shell code?
  • Marklogic : Query response time is very high
  • ActiveRecord query for a count of new users by day
  • Can you perform a UNION without a subquery in SQLAlchemy?
  • Moving mysql files across servers
  • PostgreSQL Query without WHERE only ORDER BY and LIMIT doesn't use index
  • Breeze - Deleted Items nav properties bug
  • javaw.exe and eclipse startup problems
  • How to check if every primary key value is being referenced as foreign key in another table
  • Display Images one by one with next and previous functionality
  • Upload files with Ajax and Jquery
  • Transpose CSV data with awk (pivot transformation)
  • Comma separated Values
  • File upload with ng-file-upload throwing error
  • SQL merge duplicate rows and join values that are different
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • need help with bizarre java.net.HttpURLConnection behavior
  • Append folder name and increment by 1 using batch script
  • Android Heatmap on canvas or ImageView
  • Conditional In-Line CSS for IE and Others?