Asked  7 Months ago    Answers:  5   Viewed   28 times

I've just changed all my sql queries to prepared statements using mysqli. To speed this process up I created a function (called performQuery) which replaces mysql_query. It takes the query, the bindings (like "sdss") and the variables to pass in, this then does all the perpared statement stuff. This meant changing all my old code was easy. My function returns a mysqli_result object using mysqli get_result().

This meant I could change my old code from:

$query = "SELECT x FROM y WHERE z = $var";
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result)){
    echo $row['x'];
}

to

$query = "SELECT x FROM y WHERE z = ?";
$result = performQuery($query,"s",$var);
while ($row = mysql_fetch_assoc($result)){
    echo $row['x'];
}

This works fine on localhost, but my web hosting server does not have mysqlnd available, therefore get_result() does not work. Installing mysqlnd is not an option.

What is the best way to go from here? Can I create a function which replaces get_result(), and how?

 Answers

92

Here is a neater solution based on the same principle:

function get_result( $Statement ) {
    $RESULT = array();
    $Statement->store_result();
    for ( $i = 0; $i < $Statement->num_rows; $i++ ) {
        $Metadata = $Statement->result_metadata();
        $PARAMS = array();
        while ( $Field = $Metadata->fetch_field() ) {
            $PARAMS[] = &$RESULT[ $i ][ $Field->name ];
        }
        call_user_func_array( array( $Statement, 'bind_result' ), $PARAMS );
        $Statement->fetch();
    }
    return $RESULT;
}

With mysqlnd you would normally do:

$Statement = $Database->prepare( 'SELECT x FROM y WHERE z = ?' );
$Statement->bind_param( 's', $z );
$Statement->execute();
$Result = $Statement->get_result();
while ( $DATA = $Result->fetch_array() ) {
    // Do stuff with the data
}

And without mysqlnd:

$Statement = $Database->prepare( 'SELECT x FROM y WHERE z = ?' );
$Statement->bind_param( 's', $z );
$Statement->execute();
$RESULT = get_result( $Statement );
while ( $DATA = array_shift( $RESULT ) ) {
    // Do stuff with the data
}

So the usage and syntax are almost identical. The main difference is that the replacement function returns a result array, rather than a result object.

Wednesday, March 31, 2021
 
Revent
answered 7 Months ago
47

The mysqli_stmt_get_result function is PHP 5.3 or greater only. It does not exist for your PHP 5.2.x version (which is not supported any longer btw.).

The alternative is to use mysqli_stmt_bind_result with variable bindings.

In your concrete example this has even the benefit that you do not need to assign the array members to variables, because you can bind the variables directly.

The mysqli_stmt_get_result function was introduced because someone thought this would stand in your way and getting an array would have been easier.

Tuesday, July 6, 2021
 
pocketfullofcheese
answered 4 Months ago
39

yo need create the user "pma" in mysql or change this lines(user and password for mysql):

/* User for advanced features */
$cfg['Servers'][$i]['controluser'] = 'pma'; 
$cfg['Servers'][$i]['controlpass'] = '';

Linux: /etc/phpmyadmin/config.inc.php

Tuesday, July 13, 2021
 
ShadowZzz
answered 3 Months ago
81

There are many ways to do it, I guess the most similar would be:

$fieldName = mysqli_fetch_field_direct($result, $i)->name;

http://www.php.net/manual/en/mysqli-result.fetch-field-direct.php

Thursday, August 12, 2021
 
Andras Zoltan
answered 2 Months ago
36

This is how your code should look (with added SQL Injection protection):

<?php
include "dbinfo.php"; //contains mysqli_connect information (the $mysqli variable)
//inputs
$name = mysqli_real_escape_string($_GET['name']);
$text = mysqli_real_escape_string($_GET['text']);

$sqlqr = "INSERT INTO `ncool`.`coolbits_table` (`name`, `text`, `date`) VALUES ('" . $name . "', '" . $text . "', CURRENT_TIMESTAMP);";

mysqli_query($mysqli,$sqlqr); //function where the magic happens.
?>

Take a look at what I've done. Firstly I've escaped the user input you're retrieving into the $name and $text variables (this is pretty much a must for security reasons) and as others have suggested you should preferably be using prepared statements.

The problem is that you weren't surrounding string values with single quotes ('), which is a requirement of the SQL syntax.

I hope this helps to answer your question.

Thursday, September 2, 2021
 
Pachvarsh
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :