Page 1 of 1

4 folders in 1 subselect query to multidimensional arrays?

Posted: Thu May 21, 2009 10:16 pm
by JAB Creations
I'm wondering if it's possible to use a MySQL subselect query to fetch mail in four separate private message folders in MySQL and return each folder in their own arrays so that the query itself is a multi-dimensional array?

I've been trying things like this...

Code: Select all

SELECT *FROM (SELECT * FROM private_messages WHERE id_for='inbox') AS a OR FROM (SELECT * FROM private_messages WHERE id_for='sent') AS b
Is this possible? I'm mainly interested in counting the number of messages in all folders in a single folder if possible.

An added note: the MySQL tables are now relational though I thought I'd use actual folder names for the sake of simplicity.

Re: 4 folders in 1 subselect query to multidimensional arrays?

Posted: Thu May 21, 2009 10:47 pm
by Eran
The OR condition is for a WHERE statement only. There is really no need for a subquery here unless you have special groups since it is from the same table.

Code: Select all

SELECT * FROM private_messages WHERE id_for='inbox' OR id_for='sent'
The OR condition is actually a UNION operation and is equal to

Code: Select all

( SELECT * FROM private_messages WHERE id_for='inbox' ) 
UNION ALL
( SELECT * FROM private_messages WHERE id_for='sent' )
All this information is in the manual:
http://dev.mysql.com/doc/refman/5.0/en/union.html
http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html

Re: 4 folders in 1 subselect query to multidimensional arrays?

Posted: Fri May 22, 2009 12:15 am
by JAB Creations
Thanks a ton for your reply pytrin!

Here are a couple queries I tried...

Code: Select all

SELECT id_for_folder, COUNT(id_for) FROM public_private_messages GROUP BY id_for_folder
This generates a nice array of folders and message count...perfect for the private message menu.

I tried the UNION query and it works great!

Code: Select all

( SELECT * FROM public_private_messages WHERE id_for_folder='archive' )UNION ALL( SELECT * FROM public_private_messages WHERE id_for_folder='trash' )
I tried to combine them to see if I could possibly ax off a query...

Code: Select all

(SELECT *FROM public_private_messagesWHERE id_for_folder = 'trash')UNION ALL ( SELECT id_for_folder, COUNT(id_for) FROM public_private_messages GROUP BY id_for_folder )
...and I got the following error: The used SELECT statements have a different number of columns

Unless MySQL has some other method to allow me to pass this information through altogether I think I had an idea to reduce overall page loads.

I could simply load all the messages in the folders and instead of having each folder link open a new page I could simply have JavaScript toggle the mail table that displays all the messages. It may be a little extra work though I'm not sure how else I could reduce the number of queries if the number of returned columns always needs to be the same?

wait...

I just had an idea and tried the following...

Code: Select all

(SELECT *, COUNT(id_for) FROM public_private_messagesWHERE id_for_folder = 'trash')UNION ALL ( SELECT *, COUNT(id_for) FROM public_private_messages GROUP BY id_for_folder )
I was able to return all the data however it will only return the first entry per folder.

Trying this...

Code: Select all

(SELECT * FROM public_private_messagesWHERE id_for_folder = 'trash')UNION ALL ( SELECT * FROM public_private_messagesWHERE id_for_folder = 'archive' )
...returns all the data save the count...

Can I still fit MySQL's count syntax in the query or do you think it would be better off to use PHP to account the messages in each folder via PHP?

Re: 4 folders in 1 subselect query to multidimensional arrays?

Posted: Fri May 22, 2009 1:30 am
by VladSun
As I've told you before - never ever use * in UNION query. Think of changing table structure in the future (e.g. adding a column).

You should use UNION only for similar (even logically equal) table fields,

Example: Fetch all titles from DB.
[sql]SELECT news.title FROM newsUNIONSELECT article.title FROM article[/sql]

or for logically connected subqueries.

Example: Fetch the maximum prices for goods within price ranges [0, 10$), [10, 100), [100, ...)
[sql]SELECT '0_10' AS range, max(price) AS max_price FROM goods WHERE price < 10UNIONSELECT '10_100', max(price) FROM goods WHERE price >= 10 AND price < 100UNIONSELECT '100_inf', max(price) FROM goods WHERE price >= 100[/sql]

Re: 4 folders in 1 subselect query to multidimensional arrays?

Posted: Fri May 22, 2009 1:33 am
by Eran
You can also fill out missing columns with NULL values if necessary -

Code: Select all

SELECT news.title,COUNT(authors) AS count FROM news
UNION
SELECT article.title,NULL FROM article

Re: 4 folders in 1 subselect query to multidimensional arrays?

Posted: Fri May 22, 2009 3:08 am
by JAB Creations
Thanks for both of your replies!

pytrin, I tried adding null at your suggestion...

Code: Select all

(SELECT pf.folder, pm.id, pm.date_sent, pm.subject, ua.user_username, ua.user_username_base FROM private_messages AS pm LEFT JOIN user_accounts AS ua ON ua.user_id=pm.id_from LEFT JOIN private_messages_folders AS pf ON pm.id_folder=pf.id)UNION ALL(SELECT id_for_folder AS folder_index, COUNT(id_for), NULL, NULL, NULL, NULL FROM public_private_messages GROUP BY id_for_folder)
...and I received the error message about differing collation so after I changed it the query was successful!

I added an alias for the folders so when I go through the arrays if I find "folder" it's a row for the messages though if I find "folder_index" then I know it's for the menu! As far as I can tell this would be the least intensive way to approach everything! :twisted:

VladSun, if you did a text search in my site's code (Version 2.9 of course is what I'm working on) for "SELECT *) you would find err...ah...a new project for me to complete tonight! :oops: Actually only a few instances so I'll be sure to figure out why they are in my code though I don't have any instances of UNION in my code yet until I implement the stuff I've been testing. Is it bad practice to use SELECT * even in simple queries?

I was reading a lot of tutorials earlier.

When I eventually strike it rich I want to hire and pay you guys a whole lot of money to rewrite mysql.com php.net style! :mrgreen:

I was wondering earlier...can the WHILE syntax in MySQL be used in conjunction with JOIN? In example right now I've only got four folders though let's say I wanted in the far future to allow people to add their own folders...would I be able to use the WHILE loop to get each folder between the private_message and private_message_relational tables? That is instead of manually/statically using SELECT for each folder and thus avoiding a second query? It's just a thought I had earlier though most things that came up for MySQL while loop referenced PHP's while loop and mysql_fetch_assoc/mysql_fetch_row instead. It's not something I'm considering at any serious level right now because I have so much on my plate and I wouldn't do anything like that for a few years most likely any way.