implementing mysqli_connect or mysqli_

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
orchid1
Forum Newbie
Posts: 11
Joined: Thu Aug 06, 2009 12:35 pm

implementing mysqli_connect or mysqli_

Post by orchid1 »

hey guys and gals hope someone can help me been trying to figure this one out for like three days and finally broke down and here's my cry for help :banghead:

trying to check to see if perhaps I missed something along the way implementing mysqi_connect and mysqli_select_db

can't find the prob or bug I'm not geting any returned result from the server at all when I call this script I'm not even getting a "cannot connect to server" message.
Don't want to put to lengthy of an explanation but the jist of it is. I'm using a jquery plugin to sort thorugh my database and return results to my a dynamic table the plugin is http://www.datatables.net/usage. I have itworking on my localhost just fine and I love it however on my locahost i run it in the good ol' fashion regular mysql_connect as apposed to mysql"i"_connect because my remote server either doesn't take the old commands. Anyways under normal circumstances with my own scripts I can connect to mysql with mysl"i"_connect aka mysqlImproved
but anyways maybe someone out there with fresher eyes can point me in the right direction

heres the code

Code: Select all

 
<?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<mysqli_real_escape_string( $_GET['iSortingCols'] ) ; $i++ )
        {
            $sOrder .= fnColumnToField(mysqli_real_escape_string( $_GET['iSortCol_'.$i] ))."
                ".mysqli_real_escape_string( $_GET['iSortDir_'.$i] ) .", ";
        }
        $sOrder = substr_replace( $sOrder, "", -2 );
    }
        
    /* Filtering */
    $sWhere = "";
    if ( mysqli_real_escape_string( $_GET['sSearch'] ) != "" )
    {
        $sWhere = "WHERE selector LIKE '%".mysqli_real_escape_string( $_GET['sSearch'] )."%' OR ".
                        "Name LIKE '%".mysqli_real_escape_string( $_GET['sSearch'] )."%' OR ".
                        "City LIKE '%".mysqli_real_escape_string( $_GET['sSearch'] )."%' OR ".
                        "strVar LIKE '%".mysqli_real_escape_string( $_GET['sSearch'] )."%' OR ".
                        "buzzer LIKE '%".mysqli_real_escape_string( $_GET['sSearch'] )."%'";
    }
    
    $sQuery = "
        SELECT Id, selector, Name, City, strVar, buzzer
        FROM   main
        $sWhere
        $sOrder
        $sLimit
    ";
               
    $rResult = mysqli_query($gaSql['link'], $sQuery) or die(mysqli_error());
    
    $sQuery = "
        SELECT Id
        FROM   main
    ";
    $rResultTotal = mysqli_query($gaSql['link'], $sQuery) or die(mysqli_error());
    $iTotal = mysqli_num_rows($rResultTotal);
    
    if ( $sWhere != "" )
    {
        $sQuery = "
            SELECT Id
            FROM   main
            $sWhere
        ";                    
        $rResultFilterTotal = mysqli_query($gaSql['link'], $sQuery) or die(mysqli_error());
        $iFilteredTotal = mysqli_num_rows($rResultFilterTotal);
    }
    else
    {
        $iFilteredTotal = $iTotal;
    }
    
    $sOutput = '{';
    $sOutput .= '"iTotalRecords": '.$iTotal.', ';
    $sOutput .= '"iTotalDisplayRecords": '.$iFilteredTotal.', ';
    $sOutput .= '"aaData": [ ';
    while ( $aRow = mysqli_fetch_array( $rResult ) )
    {
        $sOutput .= "[";
        $sOutput .= "'".$aRow['strVar']."',";
        $sOutput .= "'".$aRow['selector']."',";
        $sOutput .= "'".$aRow['Name']."',";
        $sOutput .= "'".$aRow['City']."',";
        $sOutput .= "'".$aRow['buzzer']."'";
        $sOutput .= "],";
    }
    $sOutput = substr_replace( $sOutput, "", -1 );
    $sOutput .= '] }';
    
    echo $sOutput;
        
    function fnColumnToField( $i )
    {
        if ( $i == 0 )
            return "Id";
        else if ( $i == 1 )
            return "selector";
        else if ( $i == 2 )
            return "Name";
        else if ( $i == 3 )
            return "City";
        else if ( $i == 4 )
            return "buzzer";
    }
?>
 
 
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: implementing mysqli_connect or mysqli_

Post by jackpf »

Have you got error reporting turned on?
straightman
Forum Commoner
Posts: 48
Joined: Sun Apr 19, 2009 5:20 am

Re: implementing mysqli_connect or mysqli_

Post by straightman »

As per your script:

if you say that nothing is happening is because the script has commited suicide at the

time you gave him a choice, either connect or die. But you did not let him even leave a

testament or last will, therefore, include the or die(mysqli_error) to see why it decided

to die instead.


1) I dont know why you keep your db elements in an array.

2) You should put them as constants

3) Get them into a separate file

4) put that file out of the web path

5) reach through an include require once

6) not merely say "or die" because if something goes wrong, you are not seeing the error. Code
it so as to display the error or die(mysqli_error)


7) where is your value of idisplaystart that gives it to your GET? same thing with isortcol


more to comment later on

=======================================================================================













[quote="orchid1"]hey guys and gals hope someone can help me been trying to figure this one out for like three days and finally broke down and here's my cry for help :banghead:

trying to check to see if perhaps I missed something along the way implementing mysqi_connect and mysqli_select_db

can't find the prob or bug I'm not geting any returned result from the server at all when I call this script I'm not even getting a "cannot connect to server" message.
Don't want to put to lengthy of an explanation but the jist of it is. I'm using a jquery plugin to sort thorugh my database and return results to my a dynamic table the plugin is http://www.datatables.net/usage. I have itworking on my localhost just fine and I love it however on my locahost i run it in the good ol' fashion regular mysql_connect as apposed to mysql"i"_connect because my remote server either doesn't take the old commands. Anyways under normal circumstances with my own scripts I can connect to mysql with mysl"i"_connect aka mysqlImproved
but anyways maybe someone out there with fresher eyes can point me in the right direction

heres the code
[syntax=php] 
<?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<mysqli_real_escape_string( $_GET['iSortingCols'] ) ; $i++ )
        {
            $sOrder .= fnColumnToField(mysqli_real_escape_string( $_GET['iSortCol_'.$i] ))."
                ".mysqli_real_escape_string( $_GET['iSortDir_'.$i] ) .", ";
        }
        $sOrder = substr_replace( $sOrder, "", -2 );
    }
       
    /* Filtering */
    $sWhere = "";
    if ( mysqli_real_escape_string( $_GET['sSearch'] ) != "" )
    {
        $sWhere = "WHERE selector LIKE '%".mysqli_real_escape_string( $_GET['sSearch'] )."%' OR ".
                        "Name LIKE '%".mysqli_real_escape_string( $_GET['sSearch'] )."%' OR ".
                        "City LIKE '%".mysqli_real_escape_string( $_GET['sSearch'] )."%' OR ".
                        "strVar LIKE '%".mysqli_real_escape_string( $_GET['sSearch'] )."%' OR ".
                        "buzzer LIKE '%".mysqli_real_escape_string( $_GET['sSearch'] )."%'";
    }
   
    $sQuery = "
        SELECT Id, selector, Name, City, strVar, buzzer
        FROM   main
        $sWhere
        $sOrder
        $sLimit
    ";
               
    $rResult = mysqli_query($gaSql['link'], $sQuery) or die(mysqli_error());
   
    $sQuery = "
        SELECT Id
        FROM   main
    ";
    $rResultTotal = mysqli_query($gaSql['link'], $sQuery) or die(mysqli_error());
    $iTotal = mysqli_num_rows($rResultTotal);
   
    if ( $sWhere != "" )
    {
        $sQuery = "
            SELECT Id
            FROM   main
            $sWhere
        ";                    
        $rResultFilterTotal = mysqli_query($gaSql['link'], $sQuery) or die(mysqli_error());
        $iFilteredTotal = mysqli_num_rows($rResultFilterTotal);
    }
    else
    {
        $iFilteredTotal = $iTotal;
    }
   
    $sOutput = '{';
    $sOutput .= '"iTotalRecords": '.$iTotal.', ';
    $sOutput .= '"iTotalDisplayRecords": '.$iFilteredTotal.', ';
    $sOutput .= '"aaData": [ ';
    while ( $aRow = mysqli_fetch_array( $rResult ) )
    {
        $sOutput .= "[";
        $sOutput .= "'".$aRow['strVar']."',";
        $sOutput .= "'".$aRow['selector']."',";
        $sOutput .= "'".$aRow['Name']."',";
        $sOutput .= "'".$aRow['City']."',";
        $sOutput .= "'".$aRow['buzzer']."'";
        $sOutput .= "],";
    }
    $sOutput = substr_replace( $sOutput, "", -1 );
    $sOutput .= '] }';
   
    echo $sOutput;
       
    function fnColumnToField( $i )
    {
        if ( $i == 0 )
            return "Id";
        else if ( $i == 1 )
            return "selector";
        else if ( $i == 2 )
            return "Name";
        else if ( $i == 3 )
            return "City";
        else if ( $i == 4 )
            return "buzzer";
    }
?>
 
 [/syntax][/quote]
straightman
Forum Commoner
Posts: 48
Joined: Sun Apr 19, 2009 5:20 am

Re: implementing mysqli_connect or mysqli_

Post by straightman »

straightman wrote:As per your script:

if you say that nothing is happening is because the script has commited suicide at the

time you gave him a choice, either connect or die. But you did not let him even leave a

testament or last will, therefore, include the or die(mysqli_error) to see why it decided

to die instead.


1) I dont know why you keep your db elements in an array.

2) You should put them as constants

3) Get them into a separate file

4) put that file out of the web path

5) reach through an include require once

6) not merely say "or die" because if something goes wrong, you are not seeing the error. Code
it so as to display the error or die(mysqli_error)


7) where is your value of idisplaystart that gives it to your GET? same thing with isortcol

more to commwent later on
 
 
[/quote]
orchid1
Forum Newbie
Posts: 11
Joined: Thu Aug 06, 2009 12:35 pm

Re: implementing mysqli_connect or mysqli_

Post by orchid1 »

figured out the problem

mysqli requires two variables for the ".mysqli_real_escape_string()" function

Code: Select all

 
//i.e.
 
.mysqli_real_escape_string($XXXX, $YYYYY ) 
 
//or in my case 
 
 .mysqli_real_escape_string($gaSql['link'], $_GET['sSearch'] )
 
i missed this about 10 tens
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: implementing mysqli_connect or mysqli_

Post by jackpf »

Oh right, I didn't notice that. Nice one.
Post Reply