Multiple Table Query

[SanShou]

I was wondering if anyone could help me with this query as I am trying to learn MySQL and PHP by designing a website.

Basically there are three tables I am concerned with (note the fields are shortened to the relevant ones):

tblPlatform
* platId
* platName

tblProducts
* prodId
* platId
* prodName

tblReviews
* revId
* prodId
* revScore
* revTitle

What I want to do is get the latest ten reviews for ten different products on a platform out of the reviews table.

I tried this (which makes it obvious I am just pulling at straws and don't really know what to do):

SELECT DISTINCT revTitle, revScore, revId, prodId FROM tblReviews WHERE prodId = ANY (SELECT prodId FROM tblProducts WHERE platId='$plat_id') GROUP BY prodId ORDER BY date LIMIT 0,10

This just produces large error messages about my sql query.

Thanks,
SanShou

[coopster]

Welcome to WebmasterWorld, SanShou!

You are attempting to use subqueries [mysql.com], so the first thing you need to find out is whether or not the version of MySQL you are running supports your syntax. You can do this from a mysql commnand line:

SELECT VERSION(); 

or with a simple little PHP script:

<?php 
phpinfo(); 
?> 

The version of MySQL must be >= 4.1.

You can accomplish your goal without subqueries by a table JOIN [mysql.com] as well. These discussions may give you some ideas:

MySQL queries spanning multiple tables with different keys [webmasterworld.com]
Multiple Inner Join Problem [webmasterworld.com]
SELECT within SELECT [webmasterworld.com]

[webadept]

Hi, welcome, I'm new here too, but not new to this question.

You can use a sub query, and you can use joins, this is ... legal? I guess. If you are programming for the web, you do these two things as little as possible. If there is a way out of it, you take it. If you really have to use a join, and you just can't figure out how to do it with out one, you make real sure the query is very lean and takes advantage of the indexing schema on the database.

Ignoring this is the root cause of slow dynamic sites, especially MS SQL sites. I've seen very few of these "slow" or "problem" sites that have a) a properly setup indexing system on the database and b) clean select statements.

I'm not really a Micro$oft fan, but it is shame really that their server is much faster than most of their customers will allow it to run.

Another tip here, since I seem to be in the mood to rant today ... (you can leave, it's okay, when I get like this I don't really need an audience.. I just rant and code, rant and code).. :-)

Anyway, the other tip I was going to mention is the number of queries you use to create a single page. If you are above 5 simple selects, this normally means you are trying to do too much on a single page, or you maybe doing something which could be done another way.

If you are dong INTRANET programming (creating a project management piece which you know will be run on the local network, always, or point to point on high spleen lines) then this number can go up to about 15, if you are very clean on the sql and use the indexing to the best advantage.

I'm not suggesting that's the law, or you will be able to fulfill a clients need under these guidelines, all I'm saying, is that if you are past these numbers, it's time to start looking over your shoulder a bit, and sniffing the air for smoke.

There are a number of things to learn about code and databases, and they help a great deal when working on the web, when to sort with the query and when to do it with code, for instance.

Another tip is that SELECT * is never the answer, for any reason.

</rant>

.. hey.. where is every body?

[Timotheos]

Seems like joins would be a necessity if someone has properly normalized [webmasterworld.com] their database.

[edited by: jatar_k at 2:28 am (utc) on Feb. 21, 2004]