SQL query syntax

JasonWells

I have been researching the forums and have been struggling with how to properly write this SQL query. I am trying to figure out how to pull a cell's data and use it in the WHERE portion of my query.

Currently I have developed: (it works fine for this one user)
SELECT * FROM tbl_assets WHERE user_id = 3151 AND date_returned IS NULL ORDER BY type_id

I basically need to turn the user_id into a variable. So my query can reference the proper data based off the user_id in the cell.
I know the answer has something to do with rowID, cell.data, mData, and mRender. I just can't figure out the proper syntax. PLEASE HELP

glenderson

Are you trying to populate your table? Or, do some kind of action based upon a user selecting a cell?

JasonWells

I am putting a hyperlink into each row of my working Datatable. I want to be able to take the user_id from that row and on my new page build an SQL query using that user_id too pull the data so I can work with it. I just can't figure out how to reference the data in the cell.

glenderson

I tend to put ID's into the tr element with a data-field. data-id = "{my database row id}" using DT_RowAttr. I have to admit, however, I finding it hard to find this subject currently. But, you send DT_RowAttr in your JSON string. Each TR will have the attributes appended at the tr level. "DT_RowAttr":{"data:id":myrowid, "data-anotherone":anotherfield}

For the on click event of your anchor (I would use a bind at the table level), You need to find the closet tr, $jq(this).closet("tr"),attr("data-id"). I'm sure there's a dataTables equivalent like row().attr("data-id").

So the anchor onclick would be onclick='this.href="mynewpage?id='+$jq(this).closet("tr"),attr("data-id")+';' (I know I have the double and single quotes wrong in this example)

Just look in this example on how the TR is picked off with the on click event and it's attr("id") is selected

https://datatables.net/examples/server_side/row_details.html

crush123

Have you looked at render ?

https://datatables.net/reference/option/columns.render

try something like this for the hyperlink row...

{ data: "user_id",
        "render": function ( data, type, row ) {
            return '<a href="/link?UserID='+ data +'">Link Name</a>';
            }
        }

JasonWells

Thank you all so far for the help but I'm not understanding how this is going to help me with the SQL query.

glenderson

As I mentioned in my response, you pass the ID of the row in the onClick event. Your receiving page then looks at the request variables and applies it to your SQL.