23 replies to this topic

[Kristoff1875]

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

http://forums.phpfre...fferent-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 by Kristoff1875, 17 January 2013 - 10:43 PM.

[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 by Kristoff1875, 19 January 2013 - 10:30 PM.

[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 by Kristoff1875, 20 January 2013 - 12:09 AM.