jQuery_dsDataTable/ajax.php at master · developscript/jQuery_dsDataTable · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
<?php

/* !
 *
 * DevelopScript - DataTable v0.6.5 (http://developscript.com)
 *
 * Licensed under the MIT license.
 *
 * @file            ajax.php
 * @author          Rafael Pegorari
 * @date            13/07/2015
 *
 *
 * ====== Get ======
 * ['dsRecordPages']    => Amounts of records per page.
 * ['dsSearch']         => String to input search.
 * ['dsPageNow']        => Current page.
 * ['dsOrder']          => Order of the columns = ['dsOrder'][*] = array("name" => "nameColumn", "order" => "ASC||DESC").
 *
 *
 * ====== Returns ======
 * $return['total_rows']  => Total table rows.
 * $return['rows']        => Total table rows - query.
 * $return['pages']       => Full table pages = ceil($total_rows_query / $_POST['dsRecordPages']).
 * $return['fields'][]    => Associated fields ex.['name'] => 'Rafael', ['surname'] => 'Pegorari'.
 * ****** Name and surname fields must be set in. ******
 * .dsDataTable({
 *               columns: [
 *                   {name: "name"},
 *                   {name: "surname"}
 *              ],
 * *****************************************************
 * $return['start']       => Start to limit.
 * $return['end']         => End to limit = $start_from + mysql_num_rows($sql).
 *
 */

$server = 'localhost';
$user = 'root';
$password = '';
$table = 'dsdatatable';

$db = mysql_connect($server, $user, $password) or die('Error!');

mysql_select_db($table, $db) or die('Error!');


//====== ARRAY RETURN ======
$return = array();

//====== SELECT ======
$select = "SELECT * FROM users";

//====== WHERE ======
$where = ' WHERE 1=1 ';
if (isset($_POST['dsSearch']) && $_POST['dsSearch'] != '') {
    $where .= " AND (name LIKE '%" . $_POST['dsSearch'] . "%' OR "
            . "surname LIKE '%" . $_POST['dsSearch'] . "%' OR "
            . "email LIKE '%" . $_POST['dsSearch'] . "%' OR "
            . "department LIKE '%" . $_POST['dsSearch'] . "%' ";
    if (is_numeric($_POST['dsSearch'])) {
        $where .= " OR salary = " . $_POST['dsSearch'];
    }
    $where .= " )";
}

//====== LIMIT ======
$start_from = ($_POST['dsPageNow'] - 1) * $_POST['dsRecordPages'];
$limit = " LIMIT " . $start_from . ", " . $_POST['dsRecordPages'];

//====== ORDER ======
$order = '';
if (isset($_POST['dsOrder']) && count($_POST['dsOrder'] !== 0)) {
    $order = " ORDER BY";
    foreach ($_POST['dsOrder'] as $obj) {
        $order .= ' ' . $obj['name'] . ' ' . $obj['order'] . ',';
    }
    $order = substr($order, 0, -1);
}

//====== TOTAL PG ======
$total_rows = mysql_num_rows(mysql_query($select));
$total_rows_query = mysql_num_rows(mysql_query($select . $where));
$total_pages = ceil($total_rows_query / $_POST['dsRecordPages']);

$return['total_rows'] = $total_rows;
$return['rows'] = $total_rows_query;
$return['pages'] = $total_pages;

//====== FIELDS ======
if ($total_rows_query != 0) {
    $sql = mysql_query($select . $where . $order . $limit);
    while ($fields = mysql_fetch_assoc($sql)) {
        $return['fields'][] = $fields;
    }

    $return['start'] = $start_from;
    $return['end'] = $start_from + mysql_num_rows($sql);
}

echo json_encode($return);