server_side/scripts/custom_data_property.php

<?php
        /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
         * Easy set variables
         */
        
        /* 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( 'engine', 'browser', 'platform', 'version', 'grade' );
        
        /* Indexed column (used for fast and accurate table cardinality) */
        $sIndexColumn = "id";
        
        /* DB table to use */
        $sTable = "ajax";
        
        /* Database connection information */
        $gaSql['user']       = "";
        $gaSql['password']   = "";
        $gaSql['db']         = "";
        $gaSql['server']     = "localhost";
        
        /* REMOVE THIS LINE (it just includes my SQL connection user/pass) */
        include( $_SERVER['DOCUMENT_ROOT']."/datatables/mysql.php" );
        
        
        /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
         * If you just want to use the basic configuration for DataTables with PHP server-side, there is
         * no need to edit below this line
         */
        
        /* 
         * Local functions
         */
        function fatal_error ( $sErrorMessage = '' )
        {
                header( $_SERVER['SERVER_PROTOCOL'] .' 500 Internal Server Error' );
                die( $sErrorMessage );
        }

        
        /* 
         * MySQL connection
         */
        if ( ! $gaSql['link'] = mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password']  ) )
        {
                fatal_error( 'Could not open connection to server' );
        }

        if ( ! mysql_select_db( $gaSql['db'], $gaSql['link'] ) )
        {
                fatal_error( 'Could not select database ' );
        }
        
        
        /* 
         * Paging
         */
        $sLimit = "";
        if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
        {
                $sLimit = "LIMIT ".intval( $_GET['iDisplayStart'] ).", ".
                        intval( $_GET['iDisplayLength'] );
        }
        
        
        /*
         * Ordering
         */
        $sOrder = "";
        if ( isset( $_GET['iSortCol_0'] ) )
        {
                $sOrder = "ORDER BY  ";
                for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ )
                {
                        if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
                        {
                                $sOrder .= "`".$aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."` ".
                                        ($_GET['sSortDir_'.$i]==='asc' ? 'asc' : 'desc') .", ";
                        }
                }
                
                $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 ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" )
        {
                $sWhere = "WHERE (";
                for ( $i=0 ; $i<count($aColumns) ; $i++ )
                {
                        if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" )
                        {
                                $sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ";
                        }
                }
                $sWhere = substr_replace( $sWhere, "", -3 );
                $sWhere .= ')';
        }
        
        /* Individual column filtering */
        for ( $i=0 ; $i<count($aColumns) ; $i++ )
        {
                if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_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 fatal_error( 'MySQL Error: ' . mysql_errno() );
        
        /* Data set length after filtering */
        $sQuery = "
                SELECT FOUND_ROWS()
        ";
        $rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
        $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 fatal_error( 'MySQL Error: ' . mysql_errno() );
        $aResultTotal = mysql_fetch_array($rResultTotal);
        $iTotal = $aResultTotal[0];
        
        
        /*
         * Output
         */
        $output = array(
                "sEcho" => intval($_GET['sEcho']),
                "iTotalRecords" => $iTotal,
                "iTotalDisplayRecords" => $iFilteredTotal,
                "test" => array()
        );
        
        while ( $aRow = mysql_fetch_array( $rResult ) )
        {
                $row = array();
                for ( $i=0 ; $i<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['test'][] = $row;
        }
        
        echo json_encode( $output );
?>
1	<?php
2	/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
3	* Easy set variables
4	*/
5
6	/* Array of database columns which should be read and sent back to DataTables. Use a space where
7	* you want to insert a non-database field (for example a counter or static image)
8	*/
9	$aColumns = array( 'engine', 'browser', 'platform', 'version', 'grade' );
10
11	/* Indexed column (used for fast and accurate table cardinality) */
12	$sIndexColumn = "id";
13
14	/* DB table to use */
15	$sTable = "ajax";
16
17	/* Database connection information */
18	$gaSql['user'] = "";
19	$gaSql['password'] = "";
20	$gaSql['db'] = "";
21	$gaSql['server'] = "localhost";
22
23	/* REMOVE THIS LINE (it just includes my SQL connection user/pass) */
24	include( $_SERVER['DOCUMENT_ROOT']."/datatables/mysql.php" );
25
26
27	/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
28	* If you just want to use the basic configuration for DataTables with PHP server-side, there is
29	* no need to edit below this line
30	*/
31
32	/*
33	* Local functions
34	*/
35	function fatal_error ( $sErrorMessage = '' )
36	{
37	header( $_SERVER['SERVER_PROTOCOL'] .' 500 Internal Server Error' );
38	die( $sErrorMessage );
39	}
40
41
42	/*
43	* MySQL connection
44	*/
45	if ( ! $gaSql['link'] = mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password'] ) )
46	{
47	fatal_error( 'Could not open connection to server' );
48	}
49
50	if ( ! mysql_select_db( $gaSql['db'], $gaSql['link'] ) )
51	{
52	fatal_error( 'Could not select database ' );
53	}
54
55
56	/*
57	* Paging
58	*/
59	$sLimit = "";
60	if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
61	{
62	$sLimit = "LIMIT ".intval( $_GET['iDisplayStart'] ).", ".
63	intval( $_GET['iDisplayLength'] );
64	}
65
66
67	/*
68	* Ordering
69	*/
70	$sOrder = "";
71	if ( isset( $_GET['iSortCol_0'] ) )
72	{
73	$sOrder = "ORDER BY ";
74	for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ )
75	{
76	if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
77	{
78	$sOrder .= "`".$aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."` ".
79	($_GET['sSortDir_'.$i]==='asc' ? 'asc' : 'desc') .", ";
80	}
81	}
82
83	$sOrder = substr_replace( $sOrder, "", -2 );
84	if ( $sOrder == "ORDER BY" )
85	{
86	$sOrder = "";
87	}
88	}
89
90
91	/*
92	* Filtering
93	* NOTE this does not match the built-in DataTables filtering which does it
94	* word by word on any field. It's possible to do here, but concerned about efficiency
95	* on very large tables, and MySQL's regex functionality is very limited
96	*/
97	$sWhere = "";
98	if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" )
99	{
100	$sWhere = "WHERE (";
101	for ( $i=0 ; $i<count($aColumns) ; $i++ )
102	{
103	if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" )
104	{
105	$sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ";
106	}
107	}
108	$sWhere = substr_replace( $sWhere, "", -3 );
109	$sWhere .= ')';
110	}
111
112	/* Individual column filtering */
113	for ( $i=0 ; $i<count($aColumns) ; $i++ )
114	{
115	if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
116	{
117	if ( $sWhere == "" )
118	{
119	$sWhere = "WHERE ";
120	}
121	else
122	{
123	$sWhere .= " AND ";
124	}
125	$sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string($_GET['sSearch_'.$i])."%' ";
126	}
127	}
128
129
130	/*
131	* SQL queries
132	* Get data to display
133	*/
134	$sQuery = "
135	SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
136	FROM $sTable
137	$sWhere
138	$sOrder
139	$sLimit
140	";
141	$rResult = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
142
143	/* Data set length after filtering */
144	$sQuery = "
145	SELECT FOUND_ROWS()
146	";
147	$rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
148	$aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
149	$iFilteredTotal = $aResultFilterTotal[0];
150
151	/* Total data set length */
152	$sQuery = "
153	SELECT COUNT(".$sIndexColumn.")
154	FROM $sTable
155	";
156	$rResultTotal = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
157	$aResultTotal = mysql_fetch_array($rResultTotal);
158	$iTotal = $aResultTotal[0];
159
160
161	/*
162	* Output
163	*/
164	$output = array(
165	"sEcho" => intval($_GET['sEcho']),
166	"iTotalRecords" => $iTotal,
167	"iTotalDisplayRecords" => $iFilteredTotal,
168	"test" => array()
169	);
170
171	while ( $aRow = mysql_fetch_array( $rResult ) )
172	{
173	$row = array();
174	for ( $i=0 ; $i<count($aColumns) ; $i++ )
175	{
176	if ( $aColumns[$i] == "version" )
177	{
178	/* Special output formatting for 'version' column */
179	$row[] = ($aRow[ $aColumns[$i] ]=="0") ? '-' : $aRow[ $aColumns[$i] ];
180	}
181	else if ( $aColumns[$i] != ' ' )
182	{
183	/* General output */
184	$row[] = $aRow[ $aColumns[$i] ];
185	}
186	}
187	$output['test'][] = $row;
188	}
189
190	echo json_encode( $output );
191	?>