MySQL sorting the result using a field of a different table

Question

I have 3 tables of the following structure

    products
    {
        pid
        pname
        plocation...
    }

    services
    {
        s_id
        s_name
        s_location...
    }

    txn
    {
        tid
        pid...
    }

I am using the following query to get the values I require.

    $dbquery = "SELECT DISTINCT products.pid AS id,
                   products.pname AS name,
                   products.p_desc AS description,
                   products.p_loc AS location,
                   products.category AS category,
                   products.p_uid AS userid,
                   products.add_date AS dateadded,
                   products.isaproduct AS whatisit
              FROM products
             WHERE products.pname
              LIKE '%".$keyword."%'
               AND category = '".$refine_value."'
             UNION
             SELECT DISTINCT services.s_id AS id,
                   services.s_name AS name,
                   services.s_desc AS description,
                   services.s_category AS category,
                   services.s_uid AS userid,
                   services.s_location AS location,
                   services.date_added AS dateadded,
                   services.isaservice AS whatisit
              FROM services
             WHERE services.s_name
              LIKE '%".$keyword."%'
               AND s_category = '".$refine_value."'

I need to order the values I select by getting the count of pid in txn table which I get in the query above.

How can I do this?

Answer 1

First of all, use parameterized queries instead of building a query string like that. It bothers me.

But it looks like all you're really needing to do is to group by the pid column in the txn table.

Define a view like

SELECT pid, COUNT(tid) tid_count
FROM txn
GROUP BY pid

And join it into your query, then order by tid_count.