Is there anyway i could read specific row from the database instead of displaying every row?

majik27

First of all, thanks for this wonderful library.

I have one few question about this though. Is there anyway i could read specific row from the database instead of displaying every row? At the moment the datatable displays all the rows retrieved from the database. What i want is to initially display specific rows from the database.

If you need me to show my codes just let me know to fully understand.

Thanks,
Mark

allan

How are you querying the database at the moment? Can't you just add a `WHERE` clause?

Allan

majik27

Hi thanks for the fast response.

I followed the steps I found from this website which is using the serverside option.

I have his in my main php file

[code]
oTable = $('#example').dataTable({
"bJQueryUI" : true,
"bProcessing" : true,
"bServerSide" : true,
"sAjaxSource" : "datatable/phpscript/server_processing.php",
"sServerMethod" : "POST"
//"url" : "patientselected_details.php",
});

[/code]

and then this in my server_processing.php

[code]

<?php
/*
* Script: DataTables server-side script for PHP and MySQL
* Copyright: 2010 - Allan Jardine, 2012 - Chris Wright
* License: GPL v2 or BSD (3-point)
*/

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* 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(*xxxx*, *xxxx*, *xxxx*);

/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = *xxxx*;

/* DB table to use */
$sTable = *xxxx*;

/* Database connection information */
$gaSql['user'] = *xxxx*;
$gaSql['password'] = "";
$gaSql['db'] = *xxxx*;
$gaSql['server'] = *xxxx*;

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* 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($_POST['iDisplayStart']) && $_POST['iDisplayLength'] != '-1') {
$sLimit = "LIMIT " . intval($_POST['iDisplayStart']) . ", " . intval($_POST['iDisplayLength']);
}

/*
* Ordering
*/
if (isset($_POST['iSortCol_0'])) {
$sOrder = "ORDER BY ";
for ($i = 0; $i < intval($_POST['iSortingCols']); $i++) {
if ($_POST['bSortable_' . intval($_POST['iSortCol_' . $i])] == "true") {
$sOrder .= "`" . $aColumns[intval($_POST['iSortCol_' . $i])] . "` " . ($_POST['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 ($_POST['sSearch'] != "") {
$sWhere = "WHERE (";
for ($i = 0; $i < count($aColumns); $i++) {
if (isset($_POST['bSearchable_' . $i]) && $_POST['bSearchable_' . $i] == "true") {
$sWhere .= $aColumns[$i] . " LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%' OR ";
}
}
$sWhere = substr_replace($sWhere, "", -3);
$sWhere .= ')';
}

/* Individual column filtering */
for ($i = 0; $i < count($aColumns); $i++) {
if ($_POST['bSearchable_' . $i] == "true" && $_POST['sSearch_' . $i] != '') {
if ($sWhere == "") {
$sWhere = "WHERE ";
} else {
$sWhere .= " AND ";
}
$sWhere .= $aColumns[$i] . " LIKE '%" . mysql_real_escape_string($_POST['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
*/
$sOutput = '{';
$sOutput .= '"sEcho": ' . intval($_POST['sEcho']) . ', ';
$sOutput .= '"iTotalRecords": ' . $iTotal . ', ';
$sOutput .= '"iTotalDisplayRecords": ' . $iFilteredTotal . ', ';
$sOutput .= '"aaData": [ ';
while ($aRow = mysql_fetch_array($rResult)) {
$sOutput .= "[";
for ($i = 0; $i < count($aColumns); $i++) {
if ($aColumns[$i] == "version") {
/* Special output formatting for 'version' */
$sOutput .= ($aRow[$aColumns[$i]] == "0") ? '"-",' : '"' . str_replace('"', '\"', $aRow[$aColumns[$i]]) . '",';
} else if ($aColumns[$i] != ' ') {
/* General output */
$sOutput .= '"' . str_replace('"', '\"', $aRow[$aColumns[$i]]) . '",';
}
}

/*
* Optional Configuration:
* If you need to add any extra columns (add/edit/delete etc) to the table, that aren't in the
* database - you can do it here
*/

$sOutput = substr_replace($sOutput, "", -1);
$sOutput .= "],";
}
$sOutput = substr_replace($sOutput, "", -1);
$sOutput .= '] }';

echo $sOutput;
?>



[/code]

allan

I'm not sure if there is a question in there? If you need to use server-side processing, then you need to modify the WHERE condition as I said.

Allan

majik27

thanks for the fast response.

I don't know which 'WHERE' part of the code I will modify as I don't understand how server-side processing code works really well.

So for example, I only want to retrieve rows from table 'Person' in the database where the boolean column 'isTest' is set to 1.

Can you show me how to do this with the code above.

Many thanks,
Mark

allan

I would suggest echoing out the queries that are built and understanding what they are doing - particularly in relation to server-side processing: http://datatables.net/usage/server-side

At line 107 is where I would suggest you modify the WHERE statement.

Allan