Problème avec le jquery datatables

Question

jytest

12 Jan 2013 à 22:29

Bonsoir,

Puis-je vous demander votre aide ?
Suite à un précédent post sur le site,
j'ai fortement simplifié mon code !

Voici mes 3 questions :
1.
Outre le fait que le jquery rencontre une erreur (que je ne trouve pas),
peut-être "MySQL Error: 1054" dans server_processing...
Bizarre, j'y ai rien modifié sauf le début !
Comment faire pour résoudre cela ?
2.
mon autre souci réside exclusivement avec le scrolling de la table.
Lors du déplacement du scrolling, il faudrait que datatables "recharge"
la table de x références supplémentaires.
Mais comment faire ?
3.
Peut-être, ne faut-il pas "charger" un début de table dans ma page html ?
Mais alors, comment faire ?

J'utilise une bdd de plus de 9000 références...
Et charger en mémoire les 9000 références en une seule fois,
cela bloque la page un certain temps.
C'est pourquoi, je souhaiterais utiliser les server_processing de datatables.

Lorsque le scrolling est en bas,
je souhaiterais un rechargement de références depuis la bdd !
Et c'est là que cela coince.

Voici la page en question est ici
Voici ce que je souhaiterais faire (réf. datatables) ici

Voici le code de la page :


<?php
	session_start();
	ob_start();
	
	require_once("php/connexionMysql.inc.php");

	ob_end_flush();
?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name = "reply-to" content = "creaprint@skynet.be"/>
<meta name = "author" content = "creaprint"/>
<meta name = "description" content = "bdd"/>
<meta name = "keywords" content = ""/>

<title>celinni.com - bdd</title>
<style type="text/css">
<!--
-->
</style>
<link type="text/css" rel="stylesheet" href="css/general.css">
<link type="text/css" rel="stylesheet" href="css/general12.css">
<link type="text/css" rel="stylesheet" href="css/recherche.css">
<link type="text/css" rel="stylesheet" href="js/dt/jquery.dataTables.css">
<link type="text/css" rel="stylesheet" href="js/dt/jquery.dataTables-my.css">
<script type="text/javascript" src="js/jquery.js"></script>
<script type="text/javascript" src="js/dt/jquery.dataTables.js"></script>
<script type="text/javascript">
	$(document).ready(function() {
		$('#example').dataTable( {
			"sScrollY": "615px",
			"sAjaxSource": "js/dt/server_processing.php",
			"bServerSide": true,
			"sDom": "frtiS",
			"oScroller": {
				"loadingIndicator": true
			}
		} );
	} );
</script>
<style type="text/css">
<!--

-->
</style>

</head>
<body>

<div id="ombre">
<div id="site">
    <div id="contenu">
        <div id="bloc30">
        	<div id="bloc31">
				<table cellpadding="0" cellspacing="0" border="0" class="display" id="example">
                    <thead>
                        <tr>
                            <th align='center' style='width:25px;'></th>
                            <th align='left'>&nbsp;idreference</th>
                            <th align='left'>&nbsp;forme</th>
                            <th align='right' style='width:30px;'>&nbsp;carat</th>
                            <th align='left'>&nbsp;taille</th>
                            <th align='center' style='width:45px;'>&nbsp;couleur</th>
                            <th align='center' style='width:45px;'>&nbsp;pureté</th>
                            <th align='center' style='width:45px;'>&nbsp;poli</th>
                            <th align='center' style='width:45px;'>&nbsp;sym</th>
                            <th align='center' style='width:45px;'>&nbsp;prof</th>
                            <th align='center' style='width:45px;'>&nbsp;table</th>
                            <th align='center' style='width:45px;'>&nbsp;fluo</th>
                            <th align='right' style='width:45px;'>&nbsp;prix/ct</th>
                            <th align='center' style='width:45px;'>&nbsp;colette</th>
                            <th align='right' style='width:45px;'>&nbsp;Lg/lg</th>
                            <th align='right'>&nbsp;délai</th>
                            <th align='right'>&nbsp;prix</th>
                            <th>&nbsp;détail</th>
                        </tr>
                    </thead>
                    <tbody>
                    <?php
						$query = mysql_query("SELECT * FROM diamant_data");
						for ($nbresultat=1; $nbresultat<1000; $nbresultat++) {
							$back = mysql_fetch_assoc($query);
							$dimensions = strtolower(trim($back['dimensions']));
							$mesures = explode("x",$dimensions);
							if ($mesures[0] != 0 AND $mesures[1] != 0) {
								if ($mesures[0] > $mesures[1]) {
									$rll = round($mesures[0] / $mesures[1], 2);
								} else {
									$rll = round($mesures[1] / $mesures[0], 2);
								}
							} else {
								$rll = "nc";
							}
							echo "<tr style='cursor:pointer;'>";
							echo 	"<td align='center' style='width:25px;'>";
							   $id= "comparer".sprintf('%02d', $nbresultat);
							   echo "<input type='checkbox' name='comparer[".$nbresultat."]' id='".$id."' class='curseur' ";
							   echo "value='".$nbresultat."'> ";
							   echo "</td>";
							echo 	"<td align='left'>".trim($back['item_id'])."</td>";
							echo 	"<td align='left'>".trim($back['cut'])."</td>";
							echo 	"<td align='right' style='width:30px;'>".number_format(trim($back['carat']), 2, ',', ' ')."</td>";
							echo 	"<td align='left'>".trim($back['cut_grade'])."</td>";
							echo 	"<td align='center' style='width:45px;'>".trim($back['color'])."</td>";
							echo 	"<td align='center' style='width:45px;'>".trim($back['clarity'])."</td>";
							echo 	"<td align='center' style='width:45px;'>".trim($back['polish'])."</td>";
							echo 	"<td align='center' style='width:45px;'>".trim($back['symmetry'])."</td>";
							echo 	"<td align='center' style='width:45px;'>".number_format(trim($back['depth']), 1, ',', ' ')."</td>";
							echo 	"<td align='center' style='width:45px;'>".number_format(trim($back['table']), 1, ',', ' ')."</td>";
							echo 	"<td align='center' style='width:45px;'>".trim($back['fluo_intensity'])."</td>";
							echo 	"<td align='right' style='width:45px;'>".number_format(trim($back['price_per_carat']), 2, ',', ' ')."</td>";
							echo 	"<td align='center' style='width:45px;'>".trim($back['culet_size'])."</td>";
							echo 	"<td align='right' style='width:45px;'>".number_format($rll, 2, ',', ' ')."</td>";
							echo 	"<td align='right'>délai</td>";
							echo 	"<td align='right'>".number_format(trim($back['total_price']), 2, ',', ' ')."</td>";
							echo 	"<td align='center'><a href=''><div class='voir'>Détails</div></a></td>";
							echo "</tr>";
						}
					?>
                    </tbody>
                </table>
            </div>
        	<div id="bloc32">
            </div>
            <div style="clear:left;"></div>
        </div>
    </div>
</div>
</div>



</body>
</html>

Voici server_processing :


<?php
	session_start();

	/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
	 * 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( ' ', 'item_id', 'cut', 'carat', 'cut_grade', 'color', 'clarity', 'polish', 'symmetry',
		'depth', 'table', 'fluo_intensity', 'price_per_carat', 'culet_size', ' ', ' ', 'total_price', ' ');

	/* Indexed column (used for fast and accurate table cardinality) */
	$sIndexColumn = "item_id";
	
	/* DB table to use */
	$sTable = "diamant_data";
	
	/* Database connection information */
	$gaSql['user']       = "xxx";
	$gaSql['password']   = "yyy";
	$gaSql['db']         = "zzz";
	$gaSql['server']     = "xyz";	
	
	/* 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,
		"aaData" => 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['aaData'][] = $row;
	}
	
	echo json_encode( $output );
?>

Voici la structure du site (on ne sait jamais !!!) :

Modifié par jytest (13 Jan 2013 - 00:25)

Sujet clos