fenway

SHOW WARNINGS.

All of these are error-prone -- write a simple DB wrapper that takes a hash and you're done.

Um, no. The reason not to use * is that you're using GROUP BY. The only valid column to return is p2.product_uid, along with the COUNT(). And there's nothing wrong with short aliases -- that's the whole point. Though, as suggested earlier, p and p2 really aren't descriptive enough. And there's no harm is getting to count for the original product uid either. So: SELECT `pr`.`product_id` , COUNT(*) as num FROM `orders` AS `o` INNER JOIN `orders_products` AS `po` ON ( o.order_id = po.order_id ) INNER JOIN `orders_products` AS `pr` ON ( o.order_id = pr.order_id ) WHERE po.product_id = '020577' GROUP BY `pr`.`product_id` ORDER BY num DESC LIMIT 3

IGNORE requires some UNIQUE key, not necessarily a primary key.

You marked it as "solved", and yet I don't see a solution.

And next time, don't start another thread.

As though that's the only problem...

If you call "p" the "originalProduct" and "p2" the "relatedProducts", it won't be confusing. I don't know what you mean by "where to count".

If you mix INNER and LEFT JOINs, you might get back nothing at all. However, if you're getting back any rows, than the columns from the `users` table shouldn't be NULL. Post your output.

OK -- so at this point you have all of the orders for any given product. Now, you need to use this as a derived table, and join back the orders_products table, using the order_id as the join condition, and then group by product_id, and COUNT(*) all of the products ever ordered together.

You need to burn whatever book told you to do so.

GRANT .... ON db1.yourTable TO 'prashant'@'localhost';

INT UNSIGNED is 4 billion -- that's enough for most.

Or, if you're an ANSI SQL freak, you can use || (if ansi mode is on).

This topic has been moved to Installation & Configuration Issues. http://www.phpfreaks.com/forums/index.php?topic=350836.0

Sorry, I was hacking at your two statements -- left in the alias by mistake. UPDATE `links` LEFT JOIN `accounts` as a ON a.username=links.author SET `links`.`author` = `a`.`id`

Safer way is to add another column until you're certain you've done it correctly. And it's not always "very bad" to store them as text -- depends what they're protecting.

TLDR -- but in general, if you want to example the values of columns in a table being LEFT JOIN-ed, you need to move it to the ON clause, or the WHERE will always fail.

You're looking to produce a "pivot table".

I have different complaints about auto-increment -- particularly as they relate to InnoDB and server restarts -- but the fact that you can assign an arbitrary one make it all meaningless, and gaps can filled as well if rows are deleted under certain circumstances. You have to treat this as meta-data, that's true -- consider it a "row pointer", and nothing else -- it's an integer for monotonic convenience, nothing more. @ManiacDan -- not that old, but those problems are still fresh.

Yes, you can.

I suppose you could do some magic with triggers and user variables, but why?

UPDATE `links` LEFT JOIN `accounts` as a ON a.username=l.author SET `links`.`author` = `a`.`id`

You'll have to batch your updates -- based on UIDs, not random DB order and LIMIT. But you really should check out those warnings.

Don't rely on the absolute value of an ID for anything.