PHP Mysql PDO to MSSQL /json /jquery /datatables

Question

I'm currently working on a project, i started researching and made it in mysql with Xampp but right now I would like to change it to mssql, i got the connection string working but datatables is showing error.

This is the code (works fine with mysql):

    <?php
    include('db.php');
    include('function.php');
    $query = '';
    $output = array();
    $query .= "SELECT * FROM item ";

    $statement = $connection->prepare($query);
    $statement->execute();
    $result = $statement->fetchAll();
    $data = array();
    $filtered_rows = $statement->rowCount();
    foreach($result as $row)
    {

        $sub_array = array();
        $sub_array[] = $row["id"];
        $sub_array[] = $row["item_id"];
        $sub_array[] = $row["item_name"];
        $sub_array[] = $row["brand"];
        $sub_array[] = $row["description"];
        $sub_array[] = $row["category"];
        $sub_array[] = $row["serial_no"];
        $sub_array[] = $row["consumable"];
        $sub_array[] = $row["d_date"];
        $sub_array[] = $row["rs_no"];
        $sub_array[] = $row["rr_no"];
        $sub_array[] = $row["po_no"];
        $sub_array[] = $row["tag_no"];
        $sub_array[] = $row["transcode"];
        $sub_array[] = $row["supplier"];
        $sub_array[] = $row["d_added"];
        $sub_array[] = $row["p_account"];
        $sub_array[] = $row["c_account"];
        $sub_array[] = $row["status"];
        $sub_array[] = $row["con"];
        $sub_array[] = $row["location"];
        $data[] = $sub_array;
    }
    $output = array(
        "draw"              =>  intval($_POST["draw"]),
        "recordsTotal"      =>  $filtered_rows,
        "recordsFiltered"   =>  get_total_all_records(),
        "data"              =>  $data
    );
    echo json_encode($output);
    ?>

this is the code for function.php:

    <?php



    function get_total_all_records()
    {
        include('db.php');
        $statement = $connection->prepare("SELECT * FROM item");
        $statement->execute();
        $result = $statement->fetchAll();
        return $statement->rowCount();
    }

    ?>

and for the db.php:

    $connection = new PDO( "sqlsrv:Server=sqlserver;Database=testdb", "username","password");

when the table loads it shows an invalid json response:

enter image description here

This is the error the debugger captured:

Fatal error: Uncaught PDOException: SQLSTATE[08001]: [Microsoft][ODBC Driver 13 for SQL Server]TCP Provider: No connection could be made because the target machine actively refused it. in C:\xampp\htdocs\material\upload\start1\examples\db.php:3 Stack trace: #0 C:\xampp\htdocs\material\upload\start1\examples\db.php(3): PDO->__construct('sqlsrv:Server=d...', '', '') #1 C:\xampp\htdocs\material\upload\start1\examples\fetch.php(2): include('C:\xampp\htdocs...') #2 {main} thrown in C:\xampp\htdocs\material\upload\start1\examples\db.php on line 3.

I am using the right credentials also.

-----Update 2------

I have managed to run it locally, might be something in the remote server blocking the connection.

now i am trying to make it work with datatables search with this line of codes which works with PDO mysql.

    if(isset($_POST["search"]["value"]))
     {
      $query .= 'WHERE item_id LIKE "%'.$_POST["search"]["value"].'%"';
      $query .= 'OR item_name LIKE "%'.$_POST["search"]["value"].'%"';
      $query .= 'OR status LIKE "%'.$_POST["search"]["value"].'%"';
     }
    if(isset($_POST["order"]))
     {
      $query .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order'] ['0']['dir'].' ';
     }
   else
     {
   $query .= 'ORDER BY id DESC ';
     }
   if($_POST["length"] != 1)
     {
   $query .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
     }

-----Update 3------

Hi! I have manage to make it run, it was just due to qoutes.

here is the working code :

if(isset($_POST["search"]["value"]))
{
    $query .= "  WHERE item_id LIKE '%".$_POST["search"]["value"]."%' ";
    $query .= " OR item_name LIKE '%".$_POST["search"]["value"]."%' ";
    $query .= " OR status LIKE '%".$_POST["search"]["value"]."%' ";
}
if(isset($_POST["order"]))
{
    $query .= "ORDER BY '".$_POST['order']['0']['column']."' '".$_POST['order']['0']['dir']."' ";
}
else
{
    $query .= "ORDER BY id DESC ";
}

My remaining promblem now is for the LIMIT function. its not working even after changing the qoutes to suit mssql syntax. ty in advance.

if($_POST["length"] != -1)
{
    $query .= "LIMIT '" . $_POST['start'] . "', " . $_POST['length'];
}

hi, its this: DataTables warning: table id=user_data - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1 — dexter delleva, Feb 20 '19 at 08:46
by the way i am not connecting to a local server. could this be the source of the problem? — dexter delleva, Feb 20 '19 at 09:01
No, but you need to check if your SQL Server is running and has TCP/IP enabled. Thanks. — Zhorov, Feb 20 '19 at 09:09
You may read this: https://stackoverflow.com/questions/50624986/unable-to-connect-sqlexpress-with-laravel-5-6/50637022#50637022 — Zhorov, Feb 20 '19 at 09:24
Hi, I tried to run it locally and it works. maybe something in the remote server is blocking the connection as you have pointed out, but im not allowed to make changes to the database. ty — dexter delleva, Feb 21 '19 at 02:50

score 0 · Accepted Answer · answered Feb 20 '19 at 09:05

0

I think is missing "user_data" row in your json

But why you put array, inside array, inside array?

$data = array();
foreach($result as $k => $row)
{
   $data[$k] = $row;
}
$output = array(
    "draw"              =>  intval($_POST["draw"]),
    "recordsTotal"      =>  $filtered_rows,
    "recordsFiltered"   =>  get_total_all_records(),
    "data"              =>  $data
);
echo json_encode($output);

if is not work, open inspector under network and check your json respond

answered Feb 20 '19 at 09:05

Gabriele Carbonai

459
4
18

Hi, I tried to run it locally and it works. though right now my problem is that this (see update above) line of queries prevents datatables from displaying records. ty – dexter delleva Feb 21 '19 at 02:51

PHP Mysql PDO to MSSQL /json /jquery /datatables

1 Answers1