4 folders in 1 subselect query to multidimensional arrays?

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
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

4 folders in 1 subselect query to multidimensional arrays?

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

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

Post 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
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

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

Post 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?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post 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]
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

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

Post 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
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

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

Post 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.
Post Reply