only displaying the one username

hi,

i have an insert page where the user adds a client. it should display in the details page the username of who added it but instead it just display the one username.

in the database it shows in the clients table clientuserid = 2 which is correct but it shows the assigned to username of the userid 1 which is wrong.

can someone guide me to sorting this?

i created a recordset on page called rsusers and changed the assigend to: {rsusers.Username}

many thanks

I'm not understanding the relationship of the info and weather the problem is the information or the rs that you have on the page. Please post back and include an example record and the corresponding client record for it.

Normally you would have the clients related to the user either through a joining table, or if a user can have multiple clients you could keep the userID in the client table.

If the information is stored like this then you would need to select the records from the client table where the userID matches the current user.

Please post back with some more info on the setup that you have and how the data is stored.

hi eric,

client table is:

clientid
firstname
lastname
dateofbirth
client_userid fk to userid in users table which has in that table:

userid
username
firstname
lastname
email

it is inserting it into the database correctly. as in i have a user who has a userid = 2

when he logs in and adds a case it adds it into the mysql database as:

clientid=1
firstname=joe
lastname=bloggs
dateofbirth=01/01/1983
client_userid=2

that is working fine but in the details page i created with data assist it shows the client_userid as 2 but i want to change this to a more user friendly name so the field is "assigned to" and i want the 2 to be the username.

how can i change this?

thanks

Ok, got it. You will need to alter the query on your details page so that it is joined to your users table, it might look like this:

SELECT * FROM client
JOIN user ON client.client_userid = user.userid

If you updated the query to be like this it should join the data with this other table and give you a binding for the username column. You should use this username column instead of the client_userid to see the name.

hi eric,

this might be a stupid question but how exactly do you bind it?

do i edit the recordset generate by dataassist or create a new recordset?

many thanks

You would need to ensure that your main recordset is edited to join the tables. This is the recordset you are using in the search. The recordset query must join the two tables otherwise you will only ever have an id instead of a username.

When the query is updated you can use the username column binding in place of the current user id. So if your current code for that is displaying the userID looks like this:

<?php echo $row_YourRS['userID']; ?>

You would update it to look like this:

<?php echo $row_YourRS['username']; ?>

thanks eric, i tried editing the recordset created by dataassist but it would not allow me to apply the changes so i created a new recordset based on the code you gave me and it seemed to work. will that cause problems or is it ok that i have done this?

also when you say binding do you have to do this with hand code?

could you show me or anyone tell me how you access a binding in dreamweaver?

many thanks

If you have crafted your new recordset in DW then you should be able to get to the bindings from the application panel under bindings. In here you should see your RS and be able to expand it. You can just drag and drop the column where you would like to see that value on your page. If yo select a form element you can bind the value to that element by selecting and clicking on the bind button below the bindings.

You should be fine to use your new recordset, however it is not the recordset that the search will be applied to. You would need to manually apply the DataAssist Search server behavior to this recordset.

hi eric,

so this is the wada recordset. could you tell me where i would add in the code you gave me to produce the username from userid that you gave me earlier.

i managed to do it with new recordset but if it is better to do the way you said could you guide me on the existing recordset?

SELECT ClientID, ClientUserID, Title, FirstName, LastName, DateOfBirth, Email, Address, Address2, City, State, Zip, Country
FROM clients
WHERE ClientID = ParamClientID OR ( -1= ParamClientID2 AND ClientID= ParamSessionClientID)

many thanks

You can update your query to look like this:

SELECT clients.ClientID, clients.ClientUserID, clients.Title, clients.FirstName, clients.LastName, clients.DateOfBirth, clients.Email, clients.Address, clients.Address2, clients.City, clients.State, clients.Zip, clients.Country, user.*
FROM clients
JOIN user ON clients.ClientUserID = user.userid
WHERE ClientID = ParamClientID OR ( -1= ParamClientID2 AND ClientID= ParamSessionClientID)

It is just the addition of the join statement that joins the table, you will need to update for the name of the user table, it is just user in the query, also you need to update the userID column to match the name of the column in your user table.

Using joins can be very helpful when you are trying to get more related information from a recordset. One site that I use as a good reference for the SQL is w3schools.com, here is a link to their join section:

sql_join.asp

This section and the others for sql have some very good information in them and can help you learn how to craft more advanced queries.