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?