How do I send a parameter on click to be used in sql query?

Question

My UI is a jquery grid. How can I send a parameter to the function (that creates my query) on click from my UI jquery grid?

For example, if this was my grid: http://www.ok-soft-gmbh.com/jqGrid/OK/formEditOnDoubleClick-jqueryui-showlink.htm#

I would want the client name sent to the query to use as a filter. The next page (not google link, as shown in the example when a client is clicked on) would have a grid that only listed companies with that client.

How do I create a query with this functionality?

I want to know how to send a parameter from my UI to the back-end query function.

EDIT ****

Here's what I came up with -

Inside of the UI code (that creates the jqgrid):

function returnHyperLink(cellValue, options, rowdata, action)
{
    return '<a id="myLink" href="#" onclick="callCompanies(\''+cellValue+'\');">'+cellValue+'</a>'
}

function callCompanies(cellValue) {
        $("#contents").load("jsp/companies.jsp");

}

and then in the back-end code:

I add

@GET
@Path("/{cellValue}")

to the top of my function and eventually can use cellValue to filter the query, like:

+ " where a.evt_type_id = cellValue " + "and "

just append the id/name of client to a back end url/ endpoint like this : http://domain?name=client-name — binariedMe, Jul 05 '16 at 17:04
If the data isn't private them I would just add it to the url like the comment above. However, if it is private or you want a cleaner operation then I would make an [ajax](http://www.w3schools.com/jquery/ajax_ajax.asp) call and send the data with it. — Gary Holiday, Jul 05 '16 at 17:08
@binariedMe okay I made an edit to the post. Is that what you are suggesting to do? — Jules Mena, Jul 05 '16 at 17:36
@GaryJohnson This is my first time doing sql. but does the edit to my post look like we are on the same track? — Jules Mena, Jul 05 '16 at 17:37

score 0 · Answer 1 · edited May 23 '17 at 12:16

0

I posted the demo for the answer on your previous question and described the properties of options parameter. The rowid is the rowid. Thus one can use getLocalRow (getRowData or getCell) to access to the data from the row. Then one can just append the

onClick: function (options) {
    var item = $(this).jqGrid("getLocalRow", options.rowid);
    window.location = "https://www.google.com/#q=" + encodeURIComponent(item.name);
}

You can see the results on the modified demo. I remind that onClick can be defined in formatoptions of formatter: 'showlink' for free jqGrid fork, which I develop.

If you prefer to use custom formatter then you can define the value of onclick using this and event. You can modify the code fragment onclick="callCompanies(\''+cellValue+'\');" used in your code to for example following

onclick="callCompanies.call(this, \''+cellValue+'\', event);"

Then you can use $(this).closest("tr.jqgrow").attr("id") inside of callCompanies function to get the rowid. It allows to use getLocalRow, getRowData or getCell to get the data from the row.

edited May 23 '17 at 12:16

Community

1
1

answered Jul 05 '16 at 18:56

Oleg

220,925
34
403
798

Thank you! I already have the link working just fine; what I'm really wondering about is sql. Do you know how I can transfer that variable, item.name in this example, to my sql code? Would I use a path to carry the parameter over? (as shown above) and can I use the parameter to filter the way I have attempted? – Jules Mena Jul 05 '16 at 19:31
@JulesMena: JavaScript can't execute an SQL statement directly, but one can send the *parameter* to the server. In the example `q` parameter be used by `google.com` for searching. If you click on the link from [the modified demo](http://www.ok-soft-gmbh.com/jqGrid/OK/formEditOnDoubleClick-jqueryui-showlink1.htm) then the Google displays the results based on the content of `name` column of the grid. Google makes request to the database internally (it can be compared with SELECT using WHERE) and displays the filtered results. – Oleg Jul 05 '16 at 19:46
In the code that is under my edit, I am trying to send the parameter to the server. By using @Path("/{cellValue}") am I able to use cellValue as a variable in my back-end code? Is this one way I can successfully transfer the parameter and use it in the query statement (using SELECT and WHERE). Like this: Query query = entityManager.createQuery(" Select esd FROM EventSubscriptionDetailEntity esd, " + " ClientSystemEventConsumerEntity cs, " + **" WHERE a.evt_type_name = cellValue "** + ......etc – Jules Mena Jul 05 '16 at 19:56
@JulesMena: Sorry, but the question has no relation to jqGrid. It's only the problem of the backend (server), which you use. I personally use ASP.NET on the server side and getting of any URL parameter is really simple. `@GET` and `@Path` shows that you use some *another* framework on the server side. You should just examine how to get parameters on the server side. It should be very simple (like `@PathParam("paramame")`), but I can't help you here because I use another framework on the server side. – Oleg Jul 05 '16 at 20:10
okay thanks! As far as the grid goes, I just included that as additional background info for a better understanding of what I was doing. I was hoping that by tagging jqgrid, someone would find the question who has done something similar in all aspects of the question. – Jules Mena Jul 05 '16 at 20:14

How do I send a parameter on click to be used in sql query?

1 Answers1