shibbir ahmed shibbir ahmed - 3 months ago 8
MySQL Question

How to get the latest 5 company data from two tables

I have 2 tables in mysql database.

a) company

cid company_name
===================
1 AstraZeneca
2 Emirates
3 Development Bank of Singapore
4 Royal Copenhagen
5 xxx
6 xxx


2) history

hid user_id view_id is_save mark_as view date
==============================================================
1 2 2 0 3 2016-08-25 22:06:12
2 3 3 1 3 2016-08-25 22:07:12
3 3 3 0 1 2016-08-25 22:08:12
4 3 2 0 1 2016-08-25 22:09:12
5 2 4 0 1 2016-08-25 22:10:12
6 4 5 0 1 2016-08-25 22:11:12
7 4 6 0 1 2016-08-25 22:12:12


This
view_id
is containing
cid
value.

Now, always I want to show latest 5
company_name
from
company
table as ascending order based on
history
table
view_id
.

For that purpose I am doing following query. But
company_name
is not showing either
ASC
or
DESC
order

Here is the query :

$getViewID3 = mysqli_query($link, "SELECT view_id, hid, is_save FROM history WHERE user_id = '$user_id' AND mark_as = 3 GROUP BY view_id ORDER BY view_date DESC LIMIT 5 ");

if(mysqli_num_rows($getViewID3) > 0 ) {

while( $fetchViewId3 = mysqli_fetch_array($getViewID3) ) {
$viewid3 = (int) $fetchViewId3['view_id'];
$hid3 = (int) $fetchViewId3['hid'];
$is_save3 = (int) $fetchViewId3['is_save'];

$getCompany = mysqli_query($link, "SELECT company_name FROM company WHERE cid = '$viewid3' ORDER BY company_name DESC");

if(mysqli_num_rows($getCompany) > 0 ) {

while ($fetchCompany2 = mysqli_fetch_array($getCompany)) {
$cName = htmlspecialchars($fetchCompany2['company_name']);
$url_link = "{$url}company.php?cid=$viewid";

if($is_save3 == 1) {
$checked = 'checked = "checked"';
} else {
$checked = '';
}

echo "<li><a onClick='window.document.location=\"$url_link\"'>&nbsp;&nbsp;$cName </a> <input type='checkbox' class='data_save' $checked data-hid='$hid' data-saveid='$viewid3' name='save_history'></li>";
}
}
}


For example : Result is showing : A, E, D, R, L letter order.
It's should be show : A, D, E, L, R letter Order from
company_name
column.

Answer

If I didn't misunderstand:

SELECT 
C.company_name
FROM company C 
INNER JOIN 
(
    SELECT 
    view_id,
    MAX(view_date) max_view_date
    FROM history 
    WHERE is_save IN (0,1) AND mark_as = 3
    GROUP BY view_id
    ORDER BY max_view_date DESC 
    LIMIT 5
) AS t
ON C.cid = t.view_id
ORDER BY C.company_name ASC;

Note:

Since you want latest 5 companies the following query will put the last view_date beside the view_id.

Now if you sort these rows based on descending order of max_view_date and later limit the result to 5 then you will get at most five view_ids from the inner query.

Later a simple INNER JOIN between this result set and your company table will finish the job.

Sorry, sorting the final result in ascending order of company name will finish the job.

EDIT:

In order to get all the columns from history table and company_name column from company table:

SELECT 
C.company_name,
t.*
FROM company C 
INNER JOIN
(
    SELECT 
    history.*
    FROM history
    INNER JOIN 
    (
        SELECT 
        view_id,
        MAX(view_date) max_view_date
        FROM history 
        WHERE is_save IN (0,1) AND mark_as = 3
        GROUP BY view_id
        ORDER BY max_view_date DESC 
        LIMIT 5
    ) AS latestHistory
    ON history.view_id = latestHistory.view_id AND history.view_date = latestHistory.max_view_date
) AS t
ON C.cid = t.view_id
ORDER BY C.company_name ASC;