CODEWITHSUNDEEP
phpmysql
Alex _TNT
Alex _TNT

Reputation: 319

Combine 2 tables and sort them by date field and output the result as a list

How do I combine 2 tables sort them by date and output them into a list? Here's what I currently have.

The date in the list should be this format 2015-06-27 23:00

SELECT date, name, quantity, FROM downloads ORDER BY(`Date`) DESC
SELECT date, name, quantity, price, FROM sales ORDER BY(`Date`) DESC


$result_sales = $mysqli->query($sql_sales);

while($row = $result_sales->fetch_assoc()){
       $sale = rown["quantity"];
       $name = row["name"]; 
       $date_str = strtotime($row["date"]);
       $year = date('Y', $date_str);
       $month = date('n', $date_str) - 1;
       $day = date('j', $date_str);
       $hour = date('H', $date_str);
       $minute = date('i', $date_str);
       $ans_sales = " " . sprintf('%s, %s, %s, %s, %s',$year,$month,$day,$hour,minute) . " - ".  $name ." - " .  $sale . " - Sale Added";
       echo $ans_sales . ",\r\n";
}

Upvotes: 0

Views: 53

Answers (3)

Saty
Saty

Reputation: 22532

Remove , near FROM . 

SELECT date, name, quantity, price, FROM sales ORDER BY(`Date`) DESC
                                  ^^

And for combining two table use UNION ALL

(SELECT date, name, quantity FROM downloads ORDER BY `Date` DESC)

UNION ALL

(SELECT date, name, quantity, price FROM sales ORDER BY `Date` DESC)


ORDER BY `Date` DESC

Upvotes: 1

dhi_m
dhi_m

Reputation: 1265

Create a mysql VIEW 'view_all' with the query

SELECT 
        date, 
        name, 
        quantity,
        null as price
    FROM downloads 

    UNION ALL

    SELECT 
        date, 
        name, 
        quantity, 
        price
    FROM sales

then execute

SELECT * FROM 'view_all' ORDER BY(date) DESC;

Upvotes: 1

Trey Mack
Trey Mack

Reputation: 4705

SELECT date, name, quantity, 0 as price FROM downloads
union all
SELECT date, name, quantity, price FROM sales
ORDER BY(`Date`) DESC

Upvotes: 1

Related Questions