Help! Mysql Improved
Help! Mysql Improved
Arg! can't get the connection function working properly with MySQLi
anybody modify the script to work with MySQL improved i.e. mysqli_connect()
I have the DataTable working on localHost but can't get it to work on remote server
remote server uses MySQL improved while localHost uses good ol' MySQL
anyIdeas
anybody modify the script to work with MySQL improved i.e. mysqli_connect()
I have the DataTable working on localHost but can't get it to work on remote server
remote server uses MySQL improved while localHost uses good ol' MySQL
anyIdeas
This discussion has been closed.
Replies
I'm not aware of anyone having ported my example backend script to MySQLi, but I can't imagine it would be that much different from regular MySQL. Are you able to log into the database with your user/pass (i.e. might it be a permissions problem)? If it's a general connection issue, rather than something specific to DataTables, perhaps it would be best to ask in a MySQLi forum.
Allan
I'm pretty sure the it's the php
I'm gonna try the php forums
but maybe you can find something I cannot
anyways here's the code
[code]
<?php
/* MySQL connection */
$gaSql['user'] = "USER";
$gaSql['password'] = "PASSWORD";
$gaSql['db'] = "DATABASE";
$gaSql['server'] = "SERVERNAME i.e. localhost";
$gaSql['type'] = "mysql";
$gaSql['link'] = mysqli_connect( $gaSql['server'], $gaSql['user'], $gaSql['password'], $gaSql['db']) or
die( 'Could not open connection to server' );
mysqli_select_db($gaSql['link'], $gaSql['db']) or
die( 'Could not select database '. $gaSql['db'] );
//error
if (!$gaSql['link']) {
echo "Cannot connect to database";
exit;
}
/* Paging */
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) )
{
$sLimit = "LIMIT ".mysqli_real_escape_string( $_GET['iDisplayStart'] ).", ".
mysqli_real_escape_string( $_GET['iDisplayLength'] );
}
/* Ordering */
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i
[/code]
Allan
So i'm not totally sure what is going on
dataTeble is set up so it will return "Cannot connect to database" if that is the output right??
I'm assuming that the XHRequestObject return function will print anything returned from the server???
I'm working on this and I appreciate the input form you.
Any other ideas?
I will post back when i figure out the bug and post the code. I have a feeling I'm not the only one that has had this problem.
thanx
mysqli_real_escape_string ( mysqli $link , string $escapestr )
you give only $escapestr and not $link... About ten tiems...
i.e.
if ( mysqli_real_escape_string( $_GET['sSearch'] ) != "" )
Advice:
add:
error_reporting(E_ALL);
on top on your php script...
mysqli_error($gaSql['link'])
mysqli_real_escape_string( $_GET['sSearch'] )
mysqli_real_escape_string($gaSql['link'], $_GET['sSearch'] )
Regarding the return - actually no DataTables will not parse this information and display it because it is not valid json. I'd recommend using Firebug or similar to see what the return from the server is.
@flies - the $link is optional is it not? http://uk.php.net/manual/en/function.mysql-real-escape-string.php
Regards,
Allan
http://us.php.net/manual/en/mysqli.real-escape-string.php
And he said he is not getting response because he has error reporting disable (probably). Therefore we can't see this error:
Warning: mysqli_real_escape_string() expects exactly 2 parameters, 1 given in /var/www/sql.php on line 43
I want to cry I really do partly cuz i'm sad I lost a whole night with my son and wife trying to find the bug and partly because I can move on with my app
Thank you so very much and as promised here is the code for whomever comes after me
[code]
<?php
/* MySQL connection */
$gaSql['user'] = "USER";
$gaSql['password'] = "PASSWORD";
$gaSql['db'] = "DATABASE";
$gaSql['server'] = "SERVERNAME i.e. localhost";
$gaSql['type'] = "mysql";
$gaSql['link'] = mysqli_connect( $gaSql['server'], $gaSql['user'], $gaSql['password'], $gaSql['db']) or
die( 'Could not open connection to server' );
mysqli_select_db($gaSql['link'], $gaSql['db']) or
die( 'Could not select database '. $gaSql['db'] );
//error
if (!$gaSql['link']) {
echo "Cannot connect to database";
exit;
}
/* Paging */
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) )
{
$sLimit = "LIMIT ".mysqli_real_escape_string($gaSql['link'], $_GET['iDisplayStart'] ).", ".
mysqli_real_escape_string($gaSql['link'], $_GET['iDisplayLength'] );
}
/* Ordering */
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i
[/code]
@flies: Nice catch!
Allan
The other thing is that it was 5.2 which introduced json_encode, and that's not quite as widely adopted as perhaps we would like!
Regards,
Allan
But (to the second argument) you can make alterations in the sql statement (not all but most) and just get the selected columns in the desired order and formatting. But if you checked that it's faster I'm not gonna use it, cause processing power is most important variable in my case :)
Allan
[code]
<?php
/*
* Script: DataTables server-side script for PHP and MySQL
* Copyright: 2010 - Allan Jardine
* 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( '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";
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP server-side, there is
* no need to edit below this line
*/
/*
* MySQL connection
*/
$gaSql['link'] = mysqli_connect( $gaSql['server'], $gaSql['user'], $gaSql['password'] ) or
die( 'Could not open connection to server' );
mysqli_select_db( $gaSql['link'], $gaSql['db'] ) or
die( 'Could not select database '. $gaSql['db'] );
/*
* Paging
*/
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT ".mysqli_real_escape_string($gaSql['link'], $_GET['iDisplayStart'] ).", ".
mysqli_real_escape_string($gaSql['link'], $_GET['iDisplayLength'] );
}
/*
* Ordering
*/
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i $iFilteredTotal,
"aaData" => array()
);
while ( $aRow = mysqli_fetch_array( $rResult ) )
{
$row = array();
for ( $i=0 ; $i
[/code]
Regards,
Allan