Accesing the result of SELECT query on multipile tables

[HellgY]

Here is as an example of a query that my search form generates:

SELECT uploads.title, uploads.date, COUNT(downloads.ID) AS downloads, COUNT(reviews.ID) AS reviews, SUM(reviews.rank) AS rank, uploads.size, users.username, users.stars FROM uploads WHERE users.username LIKE '%oleg%' AND (uploads.title LIKE '%file%' OR uploads.description LIKE  '%file%') AND DATE_SUB(CURDATE(),INTERVAL 31 DAY) <= uploads.date AND uploads.size > '100' and uploads.size <= '300' ORDER BY uploads.title ASC, users
WHERE users.ID = uploads.user_ID, downloads WHERE downloads.file_ID = uploads.ID, rank WHERE rank.file_ID = uploads.ID 

Let's i assume i ran this query and got some results, and the result idenifier is now '$result'.

what would be the key of each field after i'll fetch an associated array out of those results?.

I was thinking that it might be just the name of the field, just like in a simple query, but then, there are some fields with the same name in different tables,
so far i didn't had to face this situation in praticular, but i'm sure that mysql
is designed to avoid that situation at all costs.

I also thought the results might be placed in a 2-dimmensional array, where the first key is the table name, and the second is the field name.
for example:

$arr['table']['field'] 

Or perhaps the key is just like the filed name, as it appears in the query?
for example:

$arr['table.field'] 

I searched the web for the answer, and sadly, i was unable to find anytihng to answer my question.

I was hoping you can ******** that for me, guys.

I can always use AS after every field i select, but that would be nusty.

Also i'd like to order the results by the results of the query by the result of one of the SUM() or COUNT() functions. and i use the key i gave them with AS.

example:

SELECT uploads.name, COUNT(reviews.ID) AS reviews FROM reviews WHERE
reviews.file_ID = uploads.ID, uploads WHERE uploads.size < 50 ORDER BY reviews

Can i do this?

[so_is_this]

I have never see a single SQL statement with multiple WHERE clauses.
Are you sure that is valid? Are you trying to create a JOIN or a UNION?

[HellgY]

Yes I'm trying to create a JOIN, as for UNION?, i don't know what it is.

I read that "," is semantically similar to "JOIN" in MySQL, and since i never got to understanding INNER JOIN, I have no choice but using this method.

I'd really appreciate a link to a decent online tutorial that explains the INNER JOIN function.

[so_is_this]

OK, this:

SELECT uploads.title, uploads.date, COUNT(downloads.ID) AS downloads, COUNT(reviews.ID) AS reviews, SUM(reviews.rank) AS rank, uploads.size, users.username, users.stars
  FROM uploads
 WHERE users.username LIKE '%oleg%'
   AND (uploads.title LIKE '%file%' OR uploads.description LIKE  '%file%')
   AND DATE_SUB(CURDATE(),INTERVAL 31 DAY) <= uploads.date
   AND uploads.size > '100'
   AND uploads.size <= '300'
 ORDER BY uploads.title ASC,
 users WHERE users.ID = uploads.user_ID,
 downloads WHERE downloads.file_ID = uploads.ID,
 rank WHERE rank.file_ID = uploads.ID 

would become something more like this for an implicit JOIN:

SELECT uploads.title, uploads.date, COUNT(downloads.ID) AS downloads, COUNT(reviews.ID) AS reviews, SUM(reviews.rank) AS rank, uploads.size, users.username, users.stars
  FROM uploads, users, downloads, rank
 WHERE uploads.user_ID = users.ID
   AND uploads.ID = downloads.file_ID
   AND uploads.ID = rank.file_ID
   AND users.username LIKE '%oleg%'
   AND (uploads.title LIKE '%file%' OR uploads.description LIKE  '%file%')
   AND DATE_SUB(CURDATE(),INTERVAL 31 DAY) <= uploads.date
   AND uploads.size > '100'
   AND uploads.size <= '300'
 ORDER BY uploads.title ASC; 

and would become something more like this for an explicit JOIN:

SELECT uploads.title, uploads.date, COUNT(downloads.ID) AS downloads, COUNT(reviews.ID) AS reviews, SUM(reviews.rank) AS rank, uploads.size, users.username, users.stars
  FROM uploads
  JOIN users ON uploads.user_ID = users.ID
  JOIN downloads ON uploads.ID = downloads.file_ID
  JOIN rank ON uploads.ID = rank.file_ID
 WHERE users.username LIKE '%oleg%'
   AND (uploads.title LIKE '%file%' OR uploads.description LIKE  '%file%')
   AND DATE_SUB(CURDATE(),INTERVAL 31 DAY) <= uploads.date
   AND uploads.size > '100'
   AND uploads.size <= '300'
 ORDER BY uploads.title ASC; 

[HellgY]

I figured i'll be better learn to use the JOIN statement apporopreatly, so i did, and now i am using it, and it works

nfgaming.net/public/site/upload/search.php