title: Creating a JSON-Array from data got by Select query from MySQL Database
body: <p>I am trying to transform the result of an SQL Select-Query, to a JSON-File in a format readable by DataTables. </p>
  
  <p>Now I am getting the results transformed to this form: </p>
  
  <pre><code>{  
     "data":[  
        [  
           "pIdWrittenHere1232131231",
           "sNameWrittenHere",
           "lNameWrittenHere",
           "http://Some.pLogUrl.writtenHere.com/",
           "incentivWrittenHere",
           "https://someDetailsUrl.WrittenHere.com/
        ],
         [  
           "pId2WrittenHere1232131231",
           "sName2WrittenHere",
           "lName2WrittenHere",
           "http://Some.pLogUrl.writtenHere.com/",
           "incentiv2WrittenHere",
           "https://someDetailsUrl.WrittenHere.com/
        ]
      ]
  }
  </code></pre>
  
  <p>Here is the php I am using to transform them actually: </p>
  
  <pre><code>&lt;?php
  
  
                                          /* Array of database columns which should be read and sent back to DataTables. Use a space where
                                           * you want to insert a non-database field (for example a counter or static image)
                                           */
                                          $aColumns = array( 'pId', 'sName', 'lName', 'pLogUrl', 'incent', 'det_sUrl');
  
                                          /* Indexed column (used for fast and accurate table cardinality) */
                                          $sIndexColumn = "id";
  
                                          /* DB table to use */
                                          $sTable = "tableName_here";
  
                                          /* Database connection information */
                                          $gaSql['user']       = "userHere";
                                          $gaSql['password']   = "passHere";
                                          $gaSql['db']         = "dataBaseHEre";
                                          $gaSql['server']     = "serverHere";
  
  
                                          /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
                                           * If you just want to use the basic configuration for DataTables with PHP server-side, there is
                                           * no need to edit below this line
                                           */
  
                                          /* 
                                           * MySQL connection
                                           */
                                          $gaSql['link'] =  mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password']  ) or
                                              die( 'Could not open connection to server' );
  
                                          mysql_select_db( $gaSql['db'], $gaSql['link'] ) or 
                                              die( 'Could not select database '. $gaSql['db'] );
  
  
                                          /* 
                                           * Paging
                                           */
                                          $sLimit = "";
                                          if ( isset( $_GET['iDisplayStart'] ) &amp;&amp; $_GET['iDisplayLength'] != '-1' )
                                          {
                                              $sLimit = "LIMIT ".mysql_real_escape_string( $_GET['iDisplayStart'] ).", ".
                                                  mysql_real_escape_string( $_GET['iDisplayLength'] );
                                          }
  
  
                                          /*
                                           * Ordering
                                           */
                                          if ( isset( $_GET['iSortCol_0'] ) )
                                          {
                                              $sOrder = "ORDER BY  ";
                                              for ( $i=0 ; $i&lt;intval( $_GET['iSortingCols'] ) ; $i++ )
                                              {
                                                  if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
                                                  {
                                                      $sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."
                                                          ".mysql_real_escape_string( $_GET['sSortDir_'.$i] ) .", ";
                                                  }
                                              }
  
                                              $sOrder = substr_replace( $sOrder, "", -2 );
                                              if ( $sOrder == "ORDER BY" )
                                              {
                                                  $sOrder = "";
                                              }
                                          }
  
  
                                          /* 
                                           * Filtering
                                           * NOTE this does not match the built-in DataTables filtering which does it
                                           * word by word on any field. It's possible to do here, but concerned about efficiency
                                           * on very large tables, and MySQL's regex functionality is very limited
                                           */
                                          $sWhere = "";
                                          if ( $_GET['sSearch'] != "" )
                                          {
                                              $sWhere = "WHERE (";
                                              for ( $i=0 ; $i&lt;count($aColumns) ; $i++ )
                                              {
                                                  $sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ";
                                              }
                                              $sWhere = substr_replace( $sWhere, "", -3 );
                                              $sWhere .= ')';
                                          }
  
                                          /* Individual column filtering */
                                          for ( $i=0 ; $i&lt;count($aColumns) ; $i++ )
                                          {
                                              if ( $_GET['bSearchable_'.$i] == "true" &amp;&amp; $_GET['sSearch_'.$i] != '' )
                                              {
                                                  if ( $sWhere == "" )
                                                  {
                                                      $sWhere = "WHERE ";
                                                  }
                                                  else
                                                  {
                                                      $sWhere .= " AND ";
                                                  }
                                                  $sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string($_GET['sSearch_'.$i])."%' ";
                                              }
                                          }
  
  
                                          /*
                                           * SQL queries
                                           * Get data to display
                                           */
                                          $sQuery = "
                                              SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
                                              FROM   $sTable
                                              $sWhere
                                              $sOrder
                                              $sLimit
                                          ";
                                          $rResult = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
  
                                          /* Data set length after filtering */
                                          $sQuery = "
                                              SELECT FOUND_ROWS()
                                          ";
                                          $rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
                                          $aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
                                          $iFilteredTotal = $aResultFilterTotal[0];
  
                                          /* Total data set length */
                                          $sQuery = "
                                              SELECT COUNT(".$sIndexColumn.")
                                              FROM   $sTable
                                          ";
                                          $rResultTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
                                          $aResultTotal = mysql_fetch_array($rResultTotal);
                                          $iTotal = $aResultTotal[0];
  
  
                                          /*
                                           * Output
                                           */
                                          $output = array(
  
                                              "aaData" =&gt; array()
                                          );
  
                                          while ( $aRow = mysql_fetch_array( $rResult ) )
                                          {
                                              $row = array();
                                              for ( $i=0 ; $i&lt;count($aColumns) ; $i++ )
                                              {
                                                  if ( $aColumns[$i] == "version" )
                                                  {
                                                      /* Special output formatting for 'version' column */
                                                      $row[] = ($aRow[ $aColumns[$i] ]=="0") ? '-' : $aRow[ $aColumns[$i] ];
                                                  }
                                                  else if ( $aColumns[$i] != ' ' )
                                                  {
                                                      /* General output */
                                                      $row[] = $aRow[ $aColumns[$i] ];
                                                  }
                                              }
                                              $output['aaData'][] = $row;
                                          }
  
                                          $output = str_replace("\\/", "/", $output);
  
                                          echo json_encode($output, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE);
  
  
                                      ?&gt;
  </code></pre>
  
  <p>I thnik something must be changed in the php, so that I get the Data in the Datatable...</p>
  
  <p>Here is my JavaScript: </p>
  
  <pre><code>$(document).ready(function() {
      $('#oShopTable').DataTable( {
          "bProcessing": true,
          "bServerSide": true,
          "ajax": "AdminPage2.php"
  
      } );
  } );
  </code></pre>
  
  <p>Here is the returned, when I replace this <code>echo json_encode($output, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE);</code> with this <code>var_dump($output['data'][0]);</code></p>
  
  <pre><code>array(6) { 
      [0]=&gt; string(8) "pIdHere12312312" 
      [1]=&gt; string(10) "sNameHere" 
      [2]=&gt; string(17) "lNameHere" 
      [3]=&gt; string(85) "https://somePLogUrlHere.com" 
      [4]=&gt; string(22) "incentivHere" 
      [5]=&gt; string(161) "someUrlHere" 
  }
  </code></pre>