Server side: Where clause = custom value

County

Hello all,
With my current adaptation to ssp.class.php which allows for SQL join tables, where clauses, and group by clauses, is it possible to include a custom variable in the WHERE clause for the person's username? For example, the goal is that the user will enter the username into a search box and server_processing.php will change u.name to the text entered in the search bar.
server_processing.php

$table = <<<EOT
 (
   SELECT u.name

    FROM USER u
   
    WHERE u.name = :usernameFromTextbox
  
 ) temp
EOT;
 
// Table's primary key
$primaryKey = 'id';

allan

You note that you've modified the SSP class already, so I'm not entirely clear why it couldn't be adapted further to add what you are looking for? Or is the question that you aren't sure how to send the value from the input element for the user name? If that is the case, see this example.

Allan

County

I'm basically trying to use a Post **or **Get value from a text box on the web page and send the textbox value to the server side script's Where value. For example, I could change WHERE u.name = 'County' and it will run just fine. But I'm trying to allow the textbox search to make 'County' be whatever is entered in the textbox with the server side script. While of course, avoiding SQL injection. I am not sure how to do this with SSP class or the server side script.

kthorngren

Use ajax.data as a function to get the input value and send as a parameter. There are a couple examples of this in the docs.

Kevin

County

@kthorngren Which example from that doc you listed would be most relevant to this situation? If it's possible to provide that information and perhaps even an example, that would be much appreciated and could help me get to the right direction.

kthorngren

The 2nd or 3rd example in the ajax.data docs.

Kevin

County

Just to provide some more information, this is also what my current script looks like. I was putting this together based on the docs I've been reading. I'd like to note that "data"" function (d) and the two lines below that currently do not do anything with my current script. I just had them included as my own reference.

    <script>
        $(document).ready(function() {
    $('#example').DataTable( {
        "processing": true,
        "serverSide": true,
        "bSort" : false,
        "paging": false,
        "ajax": {
            "url": "scripts/server_processing.php",
             "type": "POST",
            "data": function ( d ) {
                d.myKey = "myValue";
                 d.custom = $('#myInput').val();
            }
        }
    } );
} );
</script>

colin

Those two values, myKey and custom, in ajax.datawill get sent to the server now. You'll need to then use those values in that server-side script. Have you modified your script to use them?

Colin

County

@colin I have not modified them just yet. I'm still trying to figure out how to send the value of "Search" into ajax.data.
On server_processing.php, I have:

require( 'ssp.class.php' );
$search = $_POST['search'];

echo json_encode(


    SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )

);

On the page with the form, I have:

<form action="scripts/server_processing.php" method="post">
Search <input type="text" name="search"><br>
<input type ="submit">
</form>

kthorngren

I don't use PHP so not sure if the syntax is correct but you need to use the name of the parameter you are sending, such as custom. Try $search = $_POST['custom'];.

Use the browser's network inspector tool to see the parameters sent in the XHR request.

Kevin

County

So on the page that has the table before pressing "Search," all values (without the Where clause) show up in the table correctly. However, whenever I search for the username, it outputs all of the data on the server_processing.php page still without the Where clause..but this time, in json.
{"draw":0,"recordsTotal":229,"recordsFiltered":229,"data":[["...........

kthorngren

"draw":0

That doesn't seem right. When you initialize Datatbles the draw parameter will start at 1 and increment by 1 for each draw. The response is expected to send the same draw number and should never be 0. Sounds like you are customizing the Datatabalbes provided SSP Class script. You need to make sure to follow the server side processing protocol. Maybe you can post the full server_processing.php script so we can take a look.

Kevin