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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

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

Post by Celauran »

ORDER BY can accept multiple arguments.

Code: Select all

ORDER BY table1.some_field, table2.some_other_field
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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 ?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

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

Post 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
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post by simonmlewis »

How do you echo the results from p.romancode for example?

$row->p.romancode ?
This doesn't work.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post by simonmlewis »

I've worked it out:
p.title AS title, p.romancode AS romancode...................... $row->title, $row->romancode
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

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

Post by Celauran »

Unless there are name collisions between the columns, you shouldn't even need to do that. $row->romancode should work regardless.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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/>";
          }
        }
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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/>";
        }
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

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

Post 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}";
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post by simonmlewis »

Blimey that's better!!
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply