Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 348
  • Last Modified:

SQL group by and order by statment

Hi,

I am creating a message board for a website i have created i am using mysql and php.

i have the following database(excel file)

Here is my thought process
1. id - auto increment to order by newest created
2. original_id - copies the id value of the first message created and continues to be redundant until the message is deleted.
3. status -
      a. active - normal message
      b. deleted - message has been moved to trash
      c. inactive - message has been completely deleted
4. viewed
      a. read - message has not been read
      b. unread - message has been read
5. saved - either empty or 'saved'
6. individuals
      a. multiple - the message is address to a group (3 or more people)
      b. private - 2 people
7. sender / recipient - the sender message is copied for each person in the message including themselves to make separate copies so each user can apply their own settings to the mysql row.
8. composted_ date - date created
9. subject /message - contents of message

the sql statements is as follows
$database='messages' ->name of my database
$sp_query3 ="SELECT * FROM ".$database." WHERE status='active' AND ";
$sp_query3.="saved!='saved' AND recipient='".$user."' GROUP BY original_id ORDER BY id DESC";
$sp_res3 = mysqli_query($link, $sp_query3) or die( mysqli_error($link));
while($sp_result3= mysqli_fetch_assoc($sp_res3)){


}

when i run this query it groups the messages fine however it does not order by id whats wrong?

thanks in advance
messages.xls.xlsx
0
J N
Asked:
J N
2 Solutions
 
plusone3055Commented:
can you show in a screenshot how the query is being returned ?
0
 
J NUnicorn wranglerAuthor Commented:
Array
(
    [id] => 7
    [original_id] => 7
    [status] => active
    [viewed] => read
    [saved] => 
    [individuals] => private
    [sender] => jaymenagy
    [recipient] => jaymenagy
    [composed_date] => 2014-02-10 21:32:06
    [subject] => 
    [message] => 
whats the plans for this weekend/friday

)
Array
(
    [id] => 6
    [original_id] => 5
    [status] => active
    [viewed] => read
    [saved] => 
    [individuals] => private
    [sender] => jaymenagy
    [recipient] => jaymenagy
    [composed_date] => 2014-02-10 21:31:20
    [subject] => Message board
    [message] => 
Hey!

 

So after i spoke with you i thought i would give this a try and it was a tricky one for sure. what do you think?

 

 

)
Array
(
    [id] => 3
    [original_id] => 3
    [status] => active
    [viewed] => read
    [saved] => 
    [individuals] => private
    [sender] => jaymenagy
    [recipient] => jaymenagy
    [composed_date] => 2014-02-10 21:29:02
    [subject] => 
    [message] => 
what are you doing this weekend?

)
Array
(
    [id] => 1
    [original_id] => 1
    [status] => active
    [viewed] => read
    [saved] => 
    [individuals] => private
    [sender] => jaymenagy
    [recipient] => jaymenagy
    [composed_date] => 2014-02-10 21:27:41
    [subject] => 
    [message] => 
Whats up bud!

)

Open in new window

0
 
magarityCommented:
You have to group by averything that isn't an aggregate function.  So you've said 'select *' but then only group by original id.  That's won't work; you'd have to list all the columns in * in the group by.  What exactly are you hoping to do with the group by clause?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
J NUnicorn wranglerAuthor Commented:
So the group by original_id is fine but it is pulling in the wrong id

for example in my excel sheet

for original_id =  the highest value of id=21 and i want the 21 record
0
 
J NUnicorn wranglerAuthor Commented:
please see above i explained it
0
 
J NUnicorn wranglerAuthor Commented:
i added this and it seemed to work out
$columns= "MAX(id) AS id, original_id, status, viewed, saved, individuals, sender, recipient, composed_date, subject, message";

$query ="SELECT ".$message_columns." .....

and it seems to work
0
 
Ray PaseurCommented:
If you're really creating a PHP / SQL application for a message board, that's a little like trying to build a 2001 Chevrolet.  The science has all been done and you might find something that is free and open-source by making a Google search.  Consider phpBB or any of the systems listed her:
http://en.wikipedia.org/wiki/Comparison_of_Internet_forum_software
0
 
J NUnicorn wranglerAuthor Commented:
i figured out the solution based partly on the experts answer
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Tackle projects and never again get stuck behind a technical roadblock.
Join Now