• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

display table with specific format in webpage

 
arushi tomar
Ranch Hand
Posts: 91
3
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

I have a database in which two tables are present, user and customer, which have many to many mapping between those, using Id of both the tables

for example: USER TABLE


CUSTOMER TABLE


Mapping Table


Now, what I want to do is, I want to show a table in my webpage with following format:


Instead of yes or no, I want to show a checkbox, and from here only, if I select or deselect a checkbox, mapping table should be updated accordingly, for example, if for User5 I select the checkbox under Customer2, a new entry should be made in the mapping table, and if I deselect any box, the entry should be deleted. I am using Mysql and Java.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
arushi tomar wrote:Now, what I want to do is, I want to show a table in my webpage with following format:

I think you have to start with writing the query to select all necessary information to represent the table in your webpage. Did you already have written this query? Or maybe you are struggling to write this query? If that's the case, let us know with which part you are having difficulties.

Kind regards,
Roel

PS. I like your post as you have made the effort to add some data for demonstration purposes. That makes it so much easier to know what the query (and the webpage) should be capable of. Have a cow!
 
arushi tomar
Ranch Hand
Posts: 91
3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:
I think you have to start with writing the query

Thank you for the cow

Yes I have been struggling with the query and that is the first step towards this, because of which I am unable to move forward. Help with the mysql query would be very much appreciated.
Thanks
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
arushi tomar wrote:Help with the mysql query would be very much appreciated.

Definitely! Here at CodeRanch we are willing to help and give you advice if you are stuck with an issue, but we are NotACodeMill and you should definitely DoYourOwnHomework and ShowSomeEffort. This is a nice quote which illustrates perfectly what we are trying to do here at CodeRanch: Give a man a fish and you feed him for a day; teach a man to fish and you feed him for a lifetime.

So the first step: which information do you need to retrieve from the database in order to create this table? Once you know which data (columns) you need, you can try writing the query to select this data. So which query do you already write and which issues are you experiencing?
 
arushi tomar
Ranch Hand
Posts: 91
3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:So the first step: which information do you need to retrieve from the database in order to create this table? Once you know which data (columns) you need, you can try writing the query to select this data. So which query do you already write and which issues are you experiencing?


Yes, I have tried to do it, but I have no idea how to show the last table as I did in the question on my webpage, I have very less experience with mysql queries or even hibernate still I have done a lot of work on those in my project, but this something new, I want to show users vertically and customers horizontally, and show a check box for yes or no according to the mapping.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
arushi tomar wrote:I have very less experience with mysql queries or even hibernate still I have done a lot of work on those in my project, but this something new, I want to show users vertically and customers horizontally, and show a check box for yes or no according to the mapping.

That's why we are going back to the basics first. Once we have sorted out the basics, we can go one step further. So currently you don't have to worry about how to represent the table in your webpage. That's one of the last steps in the whole process.

But given the table you want to appear on the webpage, you should be able to list all the information you need to retrieve from the database. So that's your first step.
 
arushi tomar
Ranch Hand
Posts: 91
3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:
That's why we are going back to the basics first.


Yes, I am able to do that
I was doing this:

select us.Id, us.userId, cu.companyName FROM USER us INNER JOIN USER_CUSTOMER uc ON us.id = uc.userId INNER JOIN CUSTOMER cu ON cu.id = uc.accountId ORDER by us.id ASC"

from where I am getting username from USER table and customer name from CUSTOMER table using the mapping table USER_CUSTOMER, but i want to display it the way i am showing in the question, this simply gave a table with two columns
 
Dave Tolls
Ranch Hand
Posts: 2095
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What does that query give you?
Using the example data you have in the first post, as an example.

We can then step through it and either show how to use that result set directly, or modify the query so it will give you something closer to the end result you need.
 
Tim Moores
Bartender
Posts: 3129
50
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You may also want to check out the DisplayTag library (displaytag.org). It helps a lot with displaying tabular data -especially from a DB- in JSP pages.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
arushi tomar wrote:I was doing this:

select us.Id, us.userId, cu.companyName FROM USER us INNER JOIN USER_CUSTOMER uc ON us.id = uc.userId INNER JOIN CUSTOMER cu ON cu.id = uc.accountId ORDER by us.id ASC"

Nice! That's a very good starting point. Now you have to check if this query provides all the information required to build the desired table. This can be easily done by checking the rows returned by this query using the example data from your original post (as Dave already suggested too).

arushi tomar wrote:but i want to display it the way i am showing in the question, this simply gave a table with two columns

It will provide a table with 3 columns And (thanks to your excellent first post with example data) we are perfectly aware of how you want to display this data. But if you don't have all required data, you can't display it So our first concern is to retrieve all required data. Once that's taken care of, you could think about processing this data to represent it the way you want.
 
J. Kevin Robbins
Bartender
Pie
Posts: 1801
28
Chrome Eclipse IDE Firefox Browser jQuery Linux MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I never heard of displaytag. Thanks for that. I found it here.

(I'm still partial to DataTables, but this looks a lot simpler to get started)
 
arushi tomar
Ranch Hand
Posts: 91
3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:What does that query give you?


It gives me a table like this:

UserName CustomerName
user1 customer1
user1 customer2
user2 customer3
 
arushi tomar
Ranch Hand
Posts: 91
3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:
Nice! That's a very good starting point.


Yes, It does give me everything I need. I need the users and customers mapped with each user, which I am getting in my output table, I have added it in the previous reply.
 
arushi tomar
Ranch Hand
Posts: 91
3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:
It will provide a table with 3 columns


Yes it gives me three columns, I forgot to add Id in the reply i posted, the table it gives me is:

Id User Customer
1 User 1 Customer 1
1 User 1 Customer 2
2 User 2 Customer 3
 
arushi tomar
Ranch Hand
Posts: 91
3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Tim Moores wrote:You may also want to check out the DisplayTag library (displaytag.org). It helps a lot with displaying tabular data -especially from a DB- in JSP pages.


Thank you Tim, that looks very helpful
 
arushi tomar
Ranch Hand
Posts: 91
3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
J. Kevin Robbins wrote:I never heard of displaytag. Thanks for that. I found it here.

(I'm still partial to DataTables, but this looks a lot simpler to get started)


Thank you Looking into it.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
arushi tomar wrote:the table it gives me is:

Id User Customer
1 User 1 Customer 1
1 User 1 Customer 2
2 User 2 Customer 3

If I have a look at the mapping table, I'm pretty sure the table would look slightly different: e.g. you would have 4 rows, user1 is linked with customer2 and customer3,...

arushi tomar wrote:Yes, It does give me everything I need. I need the users and customers mapped with each user, which I am getting in my output table, I have added it in the previous reply.

Are you really sure? If you want to add (or remove) mappings you have to be able to identify both users and customers. So you need the customer id as well. But that's an easy fix. More importantly you are also missing the users which are not yet linked to a customer (like user4 and user5). If you want to display these users as well in your table, you need to have them in your query as well. And I assume that if you would have a customer4 which is not yet linked to any customer, you want customer4 to be in the table as well. So just like with the users, you also need to add the customers which are not yet linked to a customer (unless that's not a business requirement).

Currently you are using an INNER JOIN to combine the information from different tables. But there are other (different) SQL JOINs as well. If you look at this article, you'll get an overview of the SQL JOINs with an explanation and some code samples to see the different results. After carefully reading this article, I'm pretty sure you'll be able to change your query accordingly. If you did, don't forget to add the new table returned by the query (so we can see the result of our hard work )

Hope it helps!
Kind regards,
Roel
 
arushi tomar
Ranch Hand
Posts: 91
3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:
If I have a look at the mapping table, I'm pretty sure the table would look slightly different: e.g. you would have 4 rows, user1 is linked with customer2 and customer3,...


In mapping table, I have the same scenario that I have shown in the question.

For mapping, I have used this in USER class (I have used hibernate)

More importantly you are also missing the users which are not yet linked to a customer (like user4 and user5).


yes I want to have my final table with all the users and all the customers, so for that I will look into the queries, and check how to get them to my mapping table for further requirements.

Thank you

 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
arushi tomar wrote:yes I want to have my final table with all the users and all the customers, so for that I will look into the queries, and check how to get them to my mapping table for further requirements.

Awesome! And don't hesitate to let us know if you are experiencing some issues while doing so.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic