Sorting by newest post and unread icons?

[Kristoff1875]

I've set up a client area for my clients to have a record of communication kept as discussed here:

 

http://forums.phpfreaks.com/topic/272466-relating-new-rows-in-2-different-tables/

 

And all is working well, however i'm wondering if I can make a slight tweak and if anyone could give me a heads up on how to go about it.

 

Currently i've got a jobs list on the main page, that lists everything from the Jobs table with no restrictions. Within all of these jobs are various posts that are stored in the "Posts" table. Currently the jobs are sorted by the newest job added to "Jobs", would it be possible to show the one with the latest post in "Posts" at the top? And whilst doing that, would it be possible to add an unread message icon like the type you get on forums to notify you of an unread message.

 

As I say, not looking for answers, more pointers of where to start!

 

Thanks in advance

 

Chris

[kicken]

For the sorting, join to the posts table and then ORDER BY the post's date.  Use GROUP BY to just get the max date.  Something like this:

SELECT
JobId,
JobTitle, 
MAX(PostedOn) as postedOn
FROM jobs
LEFT JOIN posts ON posts.JobId=jobs.JobId
GROUP BY
JobId,
JobTitle
ORDER BY
postedOn

 

As for the unread indicator, you'll have to add a column to the posts table indicating whether that post is read or not.  Whenever a client views the post then update that column to indicate it has been read.  When you select your list of jobs, check for and unread rows using that column.

 

[Kristoff1875]

Thanks for this buddy, I don't have a JobID and a JobTitle, I only have JobNumber and ClientID. I've used the following changing to my column's names etc:

 

$result = mysql_query("SELECT
    JobNumber,
    MAX(PostDate) as postDate
FROM Jobs
LEFT JOIN Posts ON Posts.JobNumber=Jobs.JobNumber
GROUP BY
    JobNumber
ORDER BY
    postDate");

 

As my date was PostDate not PostedOn, but i'm currently getting:

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/gdesignz/public_html/clients/admin/index.php on line 220

 

Thanks again for your help, any pointers on what i've missed/messed up would be ace.

[kicken]

Use mysql_error to figure out why your query is failing.

[Kristoff1875]

Column 'JobNumber' in field list is ambiguous

 

Assuming this is because JobNumber is in both tables so it doesn't know which I am calling?

[kicken]

Yes.  You need to prefix it with the table name you want to pull the field from.  That would be the Jobs table in this case, so:

SELECT
                Jobs.JobNumber,
                MAX(PostDate) as postDate
FROM Jobs
LEFT JOIN Posts ON Posts.JobNumber=Jobs.JobNumber
GROUP BY
                Jobs.JobNumber
ORDER BY
                postDate

[Kristoff1875]

Thanks for this, I was only getting the job numbers display in the list so used:

 

SELECT
		    Jobs.*,
		    MAX(PostDate) as postDate
FROM Jobs
LEFT JOIN Posts ON Posts.JobNumber=Jobs.JobNumber
GROUP BY
		    Jobs.JobNumber
ORDER BY
		    PostDate

 

Which is displaying results, but not the recently updated ones first, and it's also not displaying all of the results.

[Kristoff1875]

Right, I think i've noticed why it's not displaying all of the jobs...

 

Each Client has a job number relating to the current job, so each Client will have a JobNumber 1 and at the moment if ClientID 12345 JobID 1 is being called, then ClientID 12346 or any client's after this JobID 1 is not being shown...

[kicken]

Either limit the query using a WHERE clause to a single client, or add the client id into the GROUP BY so it groups on the combination of the client id and job number.

 

You'll probably need to modify the left join's ON condition to include a client id match also if the job numbers are re-used for different clients, otherwise client #12345 would see there job number 1 light up as having a new unread post when something gets posted to client #12346's job number 1.

 

 

[Kristoff1875]

This is where it gets a bit (more?!) confusing...

 

I'm currently working on the admin side which is just for me, I have a list of all of the jobs, from all of the clients. The client side will, I assume, be much simpler, seeing as I will only be calling for the jobs relating to their ClientID.

 

At the moment i've got:

 

SELECT
		 Jobs.*,
		 MAX(PostDate) as postDate
FROM Jobs
LEFT JOIN Posts ON Posts.JobNumber=Jobs.JobNumber
GROUP BY
		 Jobs.ClientID
ORDER BY
		 postDate

 

Which is showing all of the jobs, but not in the correct order. My hunch was that by Grouping by the the ClientID rather than by JobNumber, it'd show all of the jobs, but now I only see one of that client's jobs in the list, plus it's not in any order.

 

Update:

 

Think i've sorted the problem with it only showing 1 from each client or 1 of each job:

 

SELECT
		 Jobs.*,
		 MAX(PostDate) as postDate
FROM Jobs
LEFT JOIN Posts ON Posts.JobNumber=Jobs.JobNumber
GROUP BY
		 Jobs.ClientID, Jobs.JobNumber
ORDER BY
		 postDate

 

Just testing the sorting!

 

ANOTHER UPDATE:

 

It's not sorting with the latest item to have a post at the top, to be honest, I can't make out an order to it at all!

 

Scrap that, It's sorting by JobNumber. All the 1's are together, then all the 2's and so on.

Edited January 18, 2013 by Kristoff1875

[Kristoff1875]

I can't seem to get it to sort by the date now, any suggestions much appreciated:

 

SELECT
Jobs.*,
MAX(PostDate) as postDate
FROM Jobs
LEFT JOIN Posts ON Posts.JobNumber=Jobs.JobNumber
GROUP BY
Jobs.ClientID, Jobs.JobNumber
ORDER BY
postDate

[Kristoff1875]

Sorry to bump but tried loads of things and can't seem to get it to work, any ideas much appreciated.

[Christian F.]

Try giving the alias a different name than the field.

[Kristoff1875]

Still get the same results i'm afraid.

[kicken]

http://sqlfiddle.com/#!2/76be3/1/0

 

[Kristoff1875]

You sir, are, as your status suggests, an absolute guru. What a star. Thank you.

 

I found that after changing a few of the key tables etc, it worked but missed a few vital details off the list i've got, however by changing it to:

 

SELECT
c.*
, j.*
, MAX(p.PostDate) as postedOn
FROM Clients c
INNER JOIN Jobs j ON c.ClientId=j.ClientId
LEFT JOIN Posts p ON j.JobNumber=p.JobNumber AND c.ClientId=p.ClientId
GROUP BY
c.ClientId
, j.JobNumber
ORDER BY
postedOn DESC

 

So selecting all from the tables, the results show fine. Could I be really cheeky though, would you be able to explain what that code is actually doing? I love that you've helped out so much, but I want to understand what it's doing as at the moment, it's working, but i've learnt nothing!

 

Many thanks again.

[Kristoff1875]

And thought you may want to know, in the last 20 minutes, i've managed to set the unread icon next to the post! Added 2 columns to the Jobs table "Read" and "AdminRead" both with TinyINT (1). Added:

 

 UPDATE Jobs SET AdminRead = 1 WHERE ClientID='$clientid' AND JobNumber='$jobid' 

 

To the admin job detail page and the client alternative to their page, and when a new post is added it also updates the Jobs table setting the Read value to 0.

Edited January 20, 2013 by Kristoff1875

[Kristoff1875]

I can't edit, but obviously the columns are called AdminUnread and Unread!

[kicken]

One thing: You shouldn't use * in your column list.  Especially with GROUP BY also in the query, however never using it is ideal. If certain options were set on your sql server, your query would start failing with an error about trying to select non-aggregate columns.  List out only the columns you actually need to use in your page so that you select only those columns.  It will reduce the work the server has to do, better document your code, help prevent problems from table structure changes, and more.

 

would you be able to explain what that code is actually doing? 

 

Basically what is going on there is something like this, in a rough step-by-step process.

 

1) The server first selects all the rows from the clients table.

2) For each of the above rows, it checks the Jobs table for any rows where the given join condition (ON c.ClientId=j.ClientId) is true.  It then combines the found rows with the row from the Clients table (duplicating the Clients table row if necessary).  If no rows in the Jobs table are found matching that condition, then the row from the clients table is elimiated from the final results.

3) For each of the above rows, it then checks the Posts table for any rows that match the given join condition.  Any rows that match are merged into the result set (again duplicating the fields from Clients and Jobs as necessary to fill out all the rows).  If no rows are found, and fields from the Posts table are treated as being NULL.

-) What you have at this point as a result is all the rows matching the given conditions.  Next the server applies the group by

4) Groups the rows by the given group by directive.  Any rows that have the same values in the columns referenced by the group by directive get lumped together. Like sorting all the rows into a bunch of little boxes where each box is a unique combination of the GROUP BY columns.

5) Next the server applies any aggregate functions in the query.  In this case the MAX function is applied to the PostDate column.  The server will look at each group and determine what the MAX (most recent) PostDate is and use that value in the result set.

-) At this point you'll have your final result set.  For any columns not listed in the group by and not used in an aggregate function, mysql will pick a value at random from one of rows of the group to fill in that value.

6) Lastly the server applies any sorting operations to the result set just before returning it to your PHP code.  In this instance, it sorts it by the MAX(PostDate) (referenced via it's alias of postDate) column in descending order.  Most-recent to oldest essentially.

 

 

[Kristoff1875]

That's absolutely brilliant thank you so much.

 

So if I understand it properly, running in order down the code, it says

 

Call all from TABLE A > Only call certain things from TABLE B > Check if TABLE B has anything matching TABLE A in a certain column, discard anything that doesn't > Check if TABLE C has anything that matches any of the results that TABLE A and TABLE B sorted PLUS check and see if it matches the other value, if it doesn't then discard that too, then list all the results in the order requested?

 

Does seperating the GROUP BY items with a comma mean that both values have to be there to group them? For example calling GROUP BY for just JobNumber would show only 1 JobNumber 1, and using ClientID would show only one of Client ID 12345's jobs, but by using both values it's showing all of that Client's jobs?

 

You're an absolute star and that has been a massive help, hopefully I understood all that properly!

 

As for your first point about not using * is that just an issue when calling from more than one table? Or an issue all the time? I notice lots of people using it and just assumed that it was an easy way of calling most of the columns from a table and the unused column values would just be discarded?

 

Also, where the tables are called and the names seem shorter for example c.ClientID j.JobNumber where abouts is that set?

Edited January 20, 2013 by Kristoff1875

[Kristoff1875]

Is this cleaner rather than using *?

 

SELECT
c.ClientID
, j.JobNumber , j.Username , j.Description , j.ClientID , j.Status , j.AdminUnread
, MAX(p.PostDate) as postedOn
FROM Clients c
INNER JOIN Jobs j ON c.ClientID=j.ClientID
LEFT JOIN Posts p ON j.JobNumber=p.JobNumber AND c.ClientID=p.ClientID
GROUP BY
c.ClientID
, j.JobNumber
ORDER BY
postedOn DESC, c.ClientID, j.JobNumber DESC

 

Have also added in a few extra orders for the jobs with no posts.

Edited January 20, 2013 by Kristoff1875

[kicken]

Call all from TABLE A > Only call certain things from TABLE B > Check if TABLE B has anything matching TABLE A in a certain column, discard anything that doesn't > Check if TABLE C has anything that matches any of the results that TABLE A and TABLE B sorted PLUS check and see if it matches the other value, if it doesn't then discard that too, then list all the results in the order requested?

 

More or less, except for the case of the LEFT JOIN (Table C) nothing is discarded if there are no matches.  Instead of discarding the row it is "joined" to a single row consisting of all NULL values.  Basically any field from Table C would just have the value NULL when selected.

 

Does seperating the GROUP BY items with a comma mean that both values have to be there to group them? For example calling GROUP BY for just JobNumber would show only 1 JobNumber 1, and using ClientID would show only one of Client ID 12345's jobs, but by using both values it's showing all of that Client's jobs?

 

As I said, you can think of it as creating a bunch of little boxes to hold the different rows.  Each box is labeled by the columns listed in the GROUP BY clause.  See the diagram, might help make sense of it.  Based on my fiddle above.

 

As for your first point about not using * is that just an issue when calling from more than one table? Or an issue all the time? 

 

In applies to both.  You may see it a lot in example queries on the forums and such simply because it's easier to type and we often do not know what a user's column names actually are.  When writing your actual queries though you should not use it.  I use it in only two cases:

1) On the forums sometimes when posting sample queries

2) When building/debugging queries during development in a tool like Mysql Workbench/PhpMyAdmin.

 

Once the query is working propery and ready to be put into your script, list out the columns you need.

 

Take for example a setup like these forums.  On the forum listing page all you really need is the title, ID#, and author name of each topic.  You don't need the post data or any extra author info.  If you were use * you'd get all that extra junk though which is just going to eat up unecessary memory in PHP and bandwidth between PHP and the sql server.  As an added benefit if by chance all the columns you need are indexed mysql can pull the values straight from the index and not have to hit the table's datastore at all which lets the query run entirely in RAM and not have to wait for any disk I/O which is the main cause of slowness.

 

Also, where the tables are called and the names seem shorter for example c.ClientID j.JobNumber where abouts is that set?

 

In the from and join clauses I set the alias for the table.

...
FROM Clients c
INNER JOIN Jobs j
...

 

The c and j are the table aliases.  That bit essentially means "...From the Clients table (which will now be known as 'c') Join with the Jobs table (which will now be known as 'j') ..."

[Kristoff1875]

Ah perfect thanks. So if I needed to reference a table more than just calling from it the first time, I could say 'SELECT * FROM TableName A' and then use 'TableName A' to refer to it in future?

[kicken]

Once you've aliased a table in a query, then you need to refer to it by it's alias name (and only that).  Notice I just used c.ClientId not Clients c.ClientId

 

Aliasing is useful to shorten the table names and reduce the amount of typing you have to do in the query if you need/choose to prefix your fields with a table reference.

 

Aliasing is also necessary if you need to join the same table twice.  For example if you had a message table and a message had two user ID's, one for FromID (Who sent it) and ToID (Who receives it) and you want to display their names, you'd do something like:

SELECT
   f.Name as fromName
   , t.Name as toName
FROM messages m
INNER JOIN users f ON m.FromID=f.UserID
INNER JOIN users t ON m.ToID=t.UserID