close ad
WARNING: Do Not Install the DREAMWEAVER CC 2017 Update »
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

display latest added notes

Thread began 3/17/2010 2:38 pm by mr hankey | Last modified 3/26/2010 3:25 pm by Eric Mittman | 1065 views | 13 replies |

mr hankey

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

Sign in to reply to this post

Eric Mittman

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.

Sign in to reply to this post

mr hankey

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

Sign in to reply to this post

Eric Mittman

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

Sign in to reply to this post

mr hankey

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

Sign in to reply to this post

Eric Mittman

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:

php:
<a href="client_details.php?clientID=<?php echo $row_YourRS['clientID']; ?>">link to client details</a>
Sign in to reply to this post

mr hankey

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

Sign in to reply to this post

Eric Mittman

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.

Sign in to reply to this post

mr hankey

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

LONGTEXT or BLOB???

many thanks

Sign in to reply to this post

Eric Mittman

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.

Sign in to reply to this post
loading

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...