How to add a "WHERE" clausole on data processing from MySql?

sineverba

Hi to all!

I'm using this code http://datatables.net/examples/server_side/server_side.html to extract data from MySQL with PHP.

I need to add a fixed condition...

I.e. my FIXED sql must to be :

[code]
$sQuery = "
SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
FROM $sTable

/******* LOOK HERE **************/

/*** --->> ******/ WHERE user_state = 1 AND

/******* END LOOK **************/
$sWhere
$sOrder
$sLimit
";
[/code]

i NEED to add "where user_state = 1" and.-... eventually next dinamic query... Can you help me? Thank ypu!

klex

Hi!

Try to append the string "AND user_state = 1" to $sWhere:
(part of server side example: )
[code]
$sWhere = "";
if ( $_GET['sSearch'] != "" )
{
$sWhere = "WHERE engine LIKE '%".mysql_real_escape_string( $_GET['sS.......";
}
//Afterwars:
$sWhere .= " AND user_state = 1";
[/code]

or put it directly after the var input

[code]
$sQuery = "
SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
FROM $sTable

$sWhere AND user_state = 1
$sOrder
$sLimit
";
[/code]

I would use the first one, source looks better ;)

klex

sineverba

Thx you for the answer...
But it's no correct.
With 1st solution, i obtain all data (not filtered) and when i type something in the search input... the table crashes with a continuous "Processing".

With 2nd solution, the table does'nt load any data... a , sigh!, continous "loading"....

But, i'm thinking, maybe i need to use this example?

http://www.datatables.net/development/filtering at the Custom row filters section?

Only, how i can integrate it with my declaration?

My own table is

[code]

$(document).ready(function() {
$('#example').dataTable( {

"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "ext_user.php",
"sPaginationType": "full_numbers",
"oLanguage": {
"sLengthMenu": "Visualizza _MENU_ risultati per pagina",
"sZeroRecords": "Nessun risultato",
"sInfo": "Visulizzati da _START_ a _END_ di _TOTAL_ risultati",
"sInfoEmpty": "Visualizzati 0 a 0 di 0 risultati",
"sInfoFiltered": "(filtrati da _MAX_ risultati)",
"sSearch": "Cerca in qualsiasi risultato:",
"oPaginate": {
"sFirst": "Primo",
"sLast": "Ultimo",
"sNext": "Successivo",
"sPrevious": "Precedente"

}
}

}



);
} );

[/code]

Thank you and sorry for my English...

sineverba

with this code

[code]
/*** --> **/ $attivo = 0;
$sQuery = "
SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
FROM $sTable
/*** --> **/ WHERE attivo = $attivo
$sWhere
$sOrder
$sLimit
";
[/code]

I Can obtain my result but, when i type something in the search form.... the datatables doesn't filter anymore...

BTW order and paginating all correct!! :)

Adding instead "AND" in SQL Query i obtain only a syntax error...

Any idea pls? Thank you!!!

sineverba

I'm going to crazy.........

I've added this
[code]

$(document).ready(function() {
oTable = $('#example').dataTable( {

"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "ext_user.php",
"sPaginationType": "full_numbers",
"oLanguage": {
"sLengthMenu": "Visualizza _MENU_ risultati per pagina",
"sZeroRecords": "Nessun risultato",
"sInfo": "Visulizzati da _START_ a _END_ di _TOTAL_ risultati",
"sInfoEmpty": "Visualizzati 0 a 0 di 0 risultati",
"sInfoFiltered": "(filtrati da _MAX_ risultati)",
"sSearch": "Cerca in qualsiasi risultato:",
"oPaginate": {
"sFirst": "Primo",
"sLast": "Ultimo",
"sNext": "Successivo",
"sPrevious": "Precedente"

}
}

}



);

/** ------> **/ oTable.fnFilter( 'a', 2 );
} );

[/code]

in 3rd column (email, go to http://www.tresrl.com/infoeco/area_riservata/ext_user.php ) i've at least one address (mine) witch countains "a".

But the table stop on "Loading"..... and it doesn't load nothing :(

If I remove ,2 it goes but filter on ALL columns...........

I need to filter only the 5th column (so the 4 in fnFilter) with "0" or "1"....

Thanx for the help....

badea

Hi sineverba !

I have the same problem i'am trying to add where statement.
Can you give me little advice in this ?

thanks.

UPEngineer

Here is what I did and it works perfectly for what you are needing I believe.

[code]
$sWhere = "";
if ( $_GET['sSearch'] != "" )
{
$sWhere = "WHERE (";
for ( $i=0 ; $i

badea

Hi UPEngineer!

Thanks for your reply.
I tried your code, it works, but when i type something in "search box" it search in all mysql table, and give all results.I need that the search show results only in "when select condition".
Have any idea ?

Thanks.

UPEngineer

hmmm, badea can you post your code for this?

The above code works perfectly for me. Maybe you have a slight change causing it to break?

I just tried my code again echoing the $sQuery and it works.

Here is the WHERE statement using the above code after a real-life example (the echoed $sWhere) typing in the search box and without typing in the search box.

Without Search Box

[code]
WHERE members.division = 336
[/code]

With Search Box Input

[code]
WHERE (lastname LIKE '%apple%' OR gca_number LIKE '%apple%' OR memberstatus LIKE '%apple%' AND members.division = 336)
[/code]

Post your code so we can take a look at it

tope

I have the same issue and believe I know what the culprit is, I'm just trying to figure out how to fix it.

These are my columns I'm pulling.
[code]$aColumns = array( 'RECNO', 'Requester_1', 'General_2', 'Requester_4' );[/code]

This is my Filter section.
[code] $sWhere = "WHERE General_2 != ' '";
if ( $_GET['sSearch'] != "" )
{
$sWhere = "WHERE (";
for ( $i=0 ; $i