php - Laravel sql query is giving error while calling from ajax

Question

I have reg_dental_camp table having columns id, reg_name, reg_email, reg_phone, reg_type, reg_info, created_at, updated_at

I want to fetch the table data in Laravel and show them using ajax.

The function I have written is:

public function showUsers(){
     $table ="SELECT * FROM `reg_dental_camp`";
     $primaryKey = 'id';
     $userColumns = array(
          array( 'db' => 'id',  'dt' => 'id' ),
          array( 'db' => 'reg_name', 'dt' => 'reg_name' ),
          array( 'db' => 'reg_email', 'dt' => 'reg_email' ),
          array( 'db' => 'reg_phone', 'dt' => 'reg_phone' ),
          array( 'db' => 'reg_type', 'dt' => 'reg_type' ),
          array( 'db' => 'reg_info', 'dt' => 'reg_info' ),
          array(
              'db'        => 'created_at',
              'dt'        => 'created_at',
              'formatter' => function( $d, $row ) {
                  return date( 'jS M y', strtotime($d));
              }
          )
      );
      if($_SERVER['HTTP_HOST']=='localhost'){
          $sql_details = array(
              'user' => 'root',
              'pass' => '',
              'db'   => 'ubl',
              'host' => 'localhost'
          );
      }
      

      $userResult =  SSP::simple( $_GET, $sql_details, $table, $primaryKey, $userColumns);

      print_r($userResult);
}

While visiting from the main page it is showing error number 500 on ajax call URL and visiting directly to the ajax call URL is is giving SQL error SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT * FROM `reg_dental_camp`

How can I solve this problem? Thanks in advance

question from:https://stackoverflow.com/questions/65878962/laravel-sql-query-is-giving-error-while-calling-from-ajax

Answer 1

Your are using simple method wrongly

Simple implementation from GitHub

static function simple ( $request, $conn, $table, $primaryKey, $columns )
{
    $bindings = array();
    $db = self::db( $conn );

    // Build the SQL query string from the request
    $limit = self::limit( $request, $columns );
    $order = self::order( $request, $columns );
    $where = self::filter( $request, $columns, $bindings );

    // Main query to actually get the data
    $data = self::sql_exec( $db, $bindings,
        "SELECT `".implode("`, `", self::pluck($columns, 'db'))."`
         FROM `$table`
         $where
         $order
         $limit"
    );

    // Data set length after filtering
    $resFilterLength = self::sql_exec( $db, $bindings,
        "SELECT COUNT(`{$primaryKey}`)
         FROM   `$table`
         $where"
    );
    $recordsFiltered = $resFilterLength[0][0];

    // Total data set length
    $resTotalLength = self::sql_exec( $db,
        "SELECT COUNT(`{$primaryKey}`)
         FROM   `$table`"
    );
    $recordsTotal = $resTotalLength[0][0];

    /*
     * Output
     */
    return array(
        "draw"            => isset ( $request['draw'] ) ?
            intval( $request['draw'] ) :
            0,
        "recordsTotal"    => intval( $recordsTotal ),
        "recordsFiltered" => intval( $recordsFiltered ),
        "data"            => self::data_output( $columns, $data )
    );
}

So you need to provide table name only not a query. Change table variable to

$table = "reg_dental_camp"