Solved

SQL group by and order by statment

Posted on 2014-02-11
8
340 Views
Last Modified: 2014-02-16
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
Comment
Question by:M. Jayme Nagy
8 Comments
 
LVL 22

Expert Comment

by:plusone3055
ID: 39850913
can you show in a screenshot how the query is being returned ?
0
 
LVL 6

Author Comment

by:M. Jayme Nagy
ID: 39850951
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
 
LVL 13

Assisted Solution

by:magarity
magarity earned 150 total points
ID: 39850970
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
 
LVL 6

Author Comment

by:M. Jayme Nagy
ID: 39850976
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 6

Author Comment

by:M. Jayme Nagy
ID: 39850980
please see above i explained it
0
 
LVL 6

Accepted Solution

by:
M. Jayme Nagy earned 0 total points
ID: 39851089
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39851272
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
 
LVL 6

Author Closing Comment

by:M. Jayme Nagy
ID: 39862475
i figured out the solution based partly on the experts answer
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
echo button 13 45
Not needed 13 53
Creating Image Thumbnails Using PHP 3 15
php connect() failed error 25 13
Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now