display latest added notes

hi,

i have a users table clients table and notes table.

users link to clients table via fk and fk from clients to notes.

i want to be able to display the latest say 5 notes added to any client for that user.

could anyone help provide a sql query that would do this for my recordset?

many thanks

Are you currently recording the time when notes are added? You will need to do this first.

Next you will need to select from your clients table where the userID matches the currently logged in user, you will then need to join the clients table with the notes table where the client id from the clients table matches the client id in your notes table. Once you have done this you can order by the date date/time column in descending order for the notes and limit it to 5 results. This will give you the last five notes for a given user.

thanks eric, yes recording date added column with timestamp.

i know in the simple query you can choose how to order by etc but it needs to be in advanced mode as i need to inner join the tables so how would i add the notes by latest 5 added by timestamp in sql?

many thanks

It is going to be up to you to get the queried worked out properly but based on what you have posted it might look something like this:

SELECT * FROM users
JOIN clients ON users.userID = clients.userID
JOIN notes ON clients.clientID = notes.clientID
WHERE users.userID = colname
ORDER BY date_column DESC
LIMIT 5

colname would be defined as:
name: colname
type: number
default value: -1
runtime value: <your current user's id session variable>

This is just an example of course and you would need to alter this to fit your needs but it should help you to get the proper query in place. Here is a link to the w3schools on how to create JOIN statements:

sql_join.asp

that is great Eric, how would i create a link from this to that client?

so on this page it shows the notes, it will have a button "view" when they click it takes them to the clients detail page generated by Data Assist?

many thanks

If you have the recordset working then you should be able to just add in a URL parameter that the client detail page is looking for. When you do a search for clients what does the URL look like when you go the details of a particular client?

You will need to replicate the same URL parameter and value when linking to the client details page. It should have the client id in the URL parameter, you could use the client id value from your recordset for the value of this URL parameter.

The link will look something like this:

<a href="client_details.php?clientID=<?php echo $row_YourRS['clientID']; ?>">link to client details</a>

hi eric,

thank for that, i will look at the results page url parameter.

one final thing, i looked at the link you provided re joins in sql.

i gather that INNER JOIN is best to use rather than the LEFT JOIN etc?

many thanks

Unless you know that you need a specific type of join you should probably just use a JOIN by itself. I only use inner and left type of joins when I have a specific need for them.

thanks eric, what data type in mysql should i use to hold notes?

LONGTEXT or BLOB???

many thanks

Unless you have an explicit need for one of these two datatypes I would suggest going with a varchar type of column, just make sure to set the size to an appropriate amount to hold the data. I seem to recall experiencing errors when trying to use a blob type myself, but it might work just fine for you.