Page 1 of 1

ORDER BY field from one table, AND by the field of another

Posted: Mon Sep 09, 2013 6:21 am
by simonmlewis
I have a query that can sort by any field in that table.
The query runs, and then a secondary query runs within that just to extract certain fields from another table.

Like so...

Code: Select all

$result = mysql_query ("SELECT * FROM stock ORDER BY $orderby ASC") or die(mysql_error());
 while ($row = mysql_fetch_object($result))
        {
        
        $resultp = mysql_query ("SELECT * FROM products WHERE id = '$row->productid'");
      while ($rowp = mysql_fetch_object($resultp))
        {
}
}
I've been asked though to be able to sort by Category, which is in the Product DB Table.

I can see the only way to do that be via a 'JOIN', but then how do you order by the field from one table, and then by the field from another, ensuring it orders by the second field first.

Re: ORDER BY field from one table, AND by the field of anoth

Posted: Mon Sep 09, 2013 6:24 am
by Celauran
ORDER BY can accept multiple arguments.

Code: Select all

ORDER BY table1.some_field, table2.some_other_field

Re: ORDER BY field from one table, AND by the field of anoth

Posted: Mon Sep 09, 2013 6:28 am
by simonmlewis
Does that have to be done with a JOIN, as I always cockup JOINs.
I need to select everything from 'STOCK' and just category, title, price and romancode from PRODUCTS, and then do that multiple order.

How do you select all from one table, and just a selection from another, while INNER JOIN productid ON products.id ?

Re: ORDER BY field from one table, AND by the field of anoth

Posted: Mon Sep 09, 2013 6:32 am
by Celauran

Code: Select all

SELECT s.id, s.name, s.whatever, p.category, p.title, p.price, p.romancode
FROM stock AS s
INNER JOIN products AS p ON s.productid = p.id
WHERE whatever
ORDER BY a.something, p.category

Re: ORDER BY field from one table, AND by the field of anoth

Posted: Mon Sep 09, 2013 7:12 am
by simonmlewis
How do you echo the results from p.romancode for example?

$row->p.romancode ?
This doesn't work.

Re: ORDER BY field from one table, AND by the field of anoth

Posted: Mon Sep 09, 2013 7:22 am
by simonmlewis
I've worked it out:
p.title AS title, p.romancode AS romancode...................... $row->title, $row->romancode

Re: ORDER BY field from one table, AND by the field of anoth

Posted: Mon Sep 09, 2013 7:25 am
by Celauran
Unless there are name collisions between the columns, you shouldn't even need to do that. $row->romancode should work regardless.

Re: ORDER BY field from one table, AND by the field of anoth

Posted: Mon Sep 09, 2013 7:43 am
by simonmlewis
Brilliant. Have put a stock status into a session too, so the whole setup can be run from one page. Sorting by stock and product fields, and only show in stock, out of stock, or all, so they can drill down.

Cheers.

Re: ORDER BY field from one table, AND by the field of anoth

Posted: Mon Sep 09, 2013 9:42 am
by simonmlewis
I'm now making a real mess of something here, still using JOINs.

This is using two database tables again, STOCK and PRODUCTS.
I need to basically get a count of how many products are in each category.

The Category name/id is not in STOCK, it links via the stock.id and products.id. (or s.id and p.id)

Then I want to show it like this:

Category name A: 5 products
Category name B: 2 products
and so on.

Code: Select all

        $result = mysql_query ("SELECT s.uniqueid, s.productid, s.location, p.catname, p.title
FROM stock AS s INNER JOIN products AS p ON s.productid = p.id ORDER BY p.catname ASC");
        while ($row = mysql_fetch_object($result))
        {
        $result2 = mysql_query ("SELECT s.productid, p.catname, p.id FROM stock AS s INNER JOIN products AS p ON s.productid = p.id'");
        
        $num_result = mysql_num_rows($result2);
        while ($row = mysql_fetch_object($result))
          {
          echo "$row->catname: $num_result<br/>";
          }
        }

Re: ORDER BY field from one table, AND by the field of anoth

Posted: Mon Sep 09, 2013 9:50 am
by simonmlewis
Unless you can see a cleaner way (tho not with PDO yet), I've figure this out:

Code: Select all

        
        $result = mysql_query ("SELECT s.uniqueid, s.productid, s.location, p.catname, p.title
FROM stock AS s INNER JOIN products AS p ON s.productid = p.id GROUP BY catname ORDER BY catname ASC");
        while ($row = mysql_fetch_object($result))
        {
        $result2 = mysql_query ("SELECT s.productid, p.catname FROM stock AS s INNER JOIN products AS p ON s.productid = p.id WHERE p.catname = '$row->catname'");
        
        $num_result = mysql_num_rows($result2);
          echo "$row->catname: $num_result<br/>";
        }

Re: ORDER BY field from one table, AND by the field of anoth

Posted: Mon Sep 09, 2013 10:16 am
by Celauran
All you want is count by category?

Code: Select all

SELECT COUNT(s.uniqueid) AS stock_count, p.catname
FROM stock AS s
INNER JOIN products AS p ON s.productid = p.id
GROUP BY catname
ORDER BY catname ASC
Then just loop through it

Code: Select all

echo "{$row->catname}: {$row->stock_count}";

Re: ORDER BY field from one table, AND by the field of anoth

Posted: Mon Sep 09, 2013 10:39 am
by simonmlewis
Blimey that's better!!