Trouble With MySQLi Prepared Statements - Why are we using this?

[Allenph]

First off, I would like to say that I absolutely hate MySQLi. I have no idea why it is even used. Every single time I go to make a query the process is twice as long as it used to be, and there is a lack of native functions. 

I'm having some trouble with a prepared statement. This is part of a framework I'm writing, so I have to rely on native PHP and libraries that come pre-packaged with PHP. 
 

$code_query = $node_connection -> prepare("SELECT `operation`, `condition`, `variable`, `condition-variable`, `has-expiration`, `expiration-date` FROM `promo_codes` WHERE `" . $params["reference"] . "` = ?");
    $code_query -> bind_param("s", $individual_code);
    $code_query -> execute();
    $code_query -> bind_result($fetch_data["operation"], $fetch_data["condition"], $fetch_data["variable"], $fetch_data["condition-variable"], $fetch_data["has-expiration"], $fetch_data["expiration-date"]);
    while ($code_query->fetch()) {
      foreach($fetch_data as $fetch_key -> $fetch_value) {
        $code_data[$fetch_key] = $fetch_value;
      }
    }
    $code_query -> store_result();
    if ($stmt->num_rows > 0) {}

This isn't working, and the fact that it isn't is mind-numbing. I'm trying to do something quite simple. This is the beginning of a function which validates promo-codes for my framework. The function of this snippet is to retrieve the data associated with the code being validated in a safe manner. (I.E. I want to avoid injection here particularly, because this function might be linked to a user input on the front-end.) 

I have no idea why this is set up the way it is. There's no native function to create an associative array, and there is no native function to bind the result to an object rather than binding each individual value to a variable. This translates to the fact that I can't get an associative array, and I cannot simply SELECT *. Rather I have to know every column, and assign each column value to an individual variable. Can anyone explain to me why this is a good way to go about things? 

Anyways, what I'm attempting to do here is create a prepared statement, bind a parameter, execute the query, and bind the results for each column to a key in an array. Then I'm trying to loop through the results array and assign the key and value of the result to an array. In the end, I should have an associative array of column name and value pairs. 

Unfortunately, this isn't happening, and I'm not sure why. 

What's the issue here? Is there a better way to do this?

[Jacques1]

If you hate MySQLi, then why did you choose it in the first place? PDO has a much nicer API, and it can be used with all mainstream database systems, not just MySQL. So if there's a chance to switch to PDO, consider doing that.

 

Otherwise you'll have to be a lot more specific than “doesn't work”. Both PHP and MySQL provide detailed error messages. Is your PHP error reporting enabled? Where's the code for handling SQL errors?

[lush_rainforest]

First off, I would like to say that I absolutely hate MySQLi. I have no idea why it is even used. Every single time I go to make a query the process is twice as long as it used to be, and there is a lack of native functions. 

 

I'm having some trouble with a prepared statement. This is part of a framework I'm writing, so I have to rely on native PHP and libraries that come pre-packaged with PHP. 

 

$code_query = $node_connection -> prepare("SELECT `operation`, `condition`, `variable`, `condition-variable`, `has-expiration`, `expiration-date` FROM `promo_codes` WHERE `" . $params["reference"] . "` = ?");
    $code_query -> bind_param("s", $individual_code);
    $code_query -> execute();
    $code_query -> bind_result($fetch_data["operation"], $fetch_data["condition"], $fetch_data["variable"], $fetch_data["condition-variable"], $fetch_data["has-expiration"], $fetch_data["expiration-date"]);
    while ($code_query->fetch()) {
      foreach($fetch_data as $fetch_key -> $fetch_value) {
        $code_data[$fetch_key] = $fetch_value;
      }
    }
    $code_query -> store_result();
    if ($stmt->num_rows > 0) {}

This isn't working, and the fact that it isn't is mind-numbing. I'm trying to do something quite simple. This is the beginning of a function which validates promo-codes for my framework. The function of this snippet is to retrieve the data associated with the code being validated in a safe manner. (I.E. I want to avoid injection here particularly, because this function might be linked to a user input on the front-end.) 

 

I have no idea why this is set up the way it is. There's no native function to create an associative array, and there is no native function to bind the result to an object rather than binding each individual value to a variable. This translates to the fact that I can't get an associative array, and I cannot simply SELECT *. Rather I have to know every column, and assign each column value to an individual variable. Can anyone explain to me why this is a good way to go about things? 

 

Anyways, what I'm attempting to do here is create a prepared statement, bind a parameter, execute the query, and bind the results for each column to a key in an array. Then I'm trying to loop through the results array and assign the key and value of the result to an array. In the end, I should have an associative array of column name and value pairs. 

 

Unfortunately, this isn't happening, and I'm not sure why. 

 

What's the issue here? Is there a better way to do this?

I don't even think you are using MySQLi_* properly. That's why you are having problems. Even if you are using PDO rather than MySQLi_*, your logic will still fail in PDO.

 

First off. You are stuffing the actual variable $params["reference"] in the query. You shouldn't need to do this because you should already know which column you want to use in your WHERE clause. Not to mention we don't ever see $individual_code being used ever in that whole code. Then, I'm not even sure if you can use $fetch_data[] for assigning variables in your bind_result (correct me if I'm wrong). I'm pretty sure you'll receive an undefined index error from those variables. Next, you throw the whole store_result and num_rows at the very end of your code which is bad pratcice. You should be putting store_result and num_rows right after your execute function.

 

Next, where does $stmt come from? There is no where in your code where you call the $stmt variable ever (until your num_rows). Lastly, don't use num_rows > 0. num_rows itself should already be false or true when your query is corrected and written properly.

 

Basically, MySQLi_* is the least of your worries. The whole logic and improper usage of functions and variables are what you need to focus on before you start to diss on something you have no clue of using.

Edited October 8, 2015 by lush_rainforest

[Allenph]

If you hate MySQLi, then why did you choose it in the first place? PDO has a much nicer API, and it can be used with all mainstream database systems, not just MySQL. So if there's a chance to switch to PDO, consider doing that.

 

Otherwise you'll have to be a lot more specific than “doesn't work”. Both PHP and MySQL provide detailed error messages. Is your PHP error reporting enabled? Where's the code for handling SQL errors?

I wish I had gone with PDO. In my last job I worked with predefined functions. Most of the DB stuff was predefined. I've just got back, and started with MySQLi as was advised by a Stack Exchange question.

 

I should have defined what "not working" means. $code_data is simply an array, while $fetch_data is indexed correctly with no values.

 

I could be using MySQLi completely wrong, I admit. But I found no functions in the docs to return an associative array, and I'm a little lost.Should have been a bit more humble before I denounced the extension I suppose.

 

The function has input parameters so that the person building the application. I can get away with being suceptable to SQL injection there because end-users will never be touching that parameter. I can't bund column names with MySQLi as far as I understand, so I digress. I'm receiving results, so I don't think that's the issue.

 

Including $stmt was a mistake. In my code it is indeed $code_query.

 

How can I achieve the effect of an associative array of values where the keys correlate to column names? Where exactly is the error in my logic, and how can I fix it. Could anyone provide an example of a working version of this? Furthermore, is there an easier way to go about getting the associative array with a prepared statement without converting the entire framework to PDO?

[benanamen]

I think your missing the most basic of basic coding.

 

mysqli_fetch_all() Fetches all result rows as an associative array, a numeric array, or both

mysqli_fetch_array() Fetches a result row as an associative, a numeric array, or both

mysqli_fetch_assoc() Fetches a result row as an associative array

 

Your still better off using PDO.

Edited October 8, 2015 by benanamen

[Allenph]

I think your missing the most basic of basic coding.

 

mysqli_fetch_all() Fetches all result rows as an associative array, a numeric array, or both

mysqli_fetch_array() Fetches a result row as an associative, a numeric array, or both

mysqli_fetch_assoc() Fetches a result row as an associative array

 

Thanks for the reply. 

 

I'm familiar with all of these, and have been using them in my framework. However, it is my understanding that I cannot use these functions of prepared statements. I may be missing something, but could you provide an example of using any of these function on such a statement?

[Jacques1]

mysqli_stmt::get_result() yields a MySQLi result set from a prepared statement.

[Allenph]

mysqli_stmt::get_result() yields a MySQLi result set from a prepared statement.

I also should have mentioned that. 

 

I'm aware of this function, but it is only available if you have installed <a href="http://php.net/manual/en/book.mysqlnd.php">Mysqlnd</a>. As this is part of a framework, I would like a solution that doesn't entail installing anything other than the framework. 

 

Is this possible, or am I going to have to find some kind of work around? 

 

Thanks for all the replies thus far.

[Ch0cu3r]

I maybe mistaken but isn't mysqlnd the default mysql driver as of PHP5.3?

[Jacques1]

It's the default since 5.4, and everything below PHP 5.5 has already reached end-of-life. So there's no good reason for avoiding this.

[Allenph]

Hmm. I have confirmed that my version is 5.5.30 with phpversion(). 

However, when I use this code...

 

      $code_query = $node_connection -> prepare("SELECT * FROM `promo_codes` WHERE `" . $params["reference"] . "` = ?");
      $code_query -> bind_param("s", $individual_code);
      $code_query -> execute();
      $code_result = mysqli_fetch_assoc($code_query -> get_result());

 

I get this error...

Fatal error: Call to undefined method mysqli_stmt::get_result()

Edited October 8, 2015 by Allenph

[Jacques1]

What does

php -i | grep mysqlnd

say?

 

Is this a PHP installation from a package?

[Allenph]

What does

php -i | grep mysqlnd

say?

 

Is this a PHP installation from a package?

This is a shared host, I don't have access to the terminal. 

 

This is the default PHP installation from HostGator, the hosting service my client decided to use.

[Jacques1]

Well, then appearently HostGator has decided to manually disable the standard mysqlnd driver (for whatever reason).

 

So now we're talking about a nonstandard PHP setup. Try this:

 

Enable SQL error reporting before you establish the database connection

$mysqliDriver = new mysqli_driver();
$mysqliDriver->report_mode = MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT;

A cleaned-up version of your original code

$code_data = [];

$code_query = $node_connection->prepare('
    SELECT
        operation,
        `condition`,
        variable,
        `condition-variable`,
        `has-expiration`,
        `expiration-date`
    FROM
      promo_codes
    WHERE
      `'.$params['reference'].'` = ?
');
$code_query->bind_param('s', $individual_code);
$code_query->execute();
$code_query->bind_result($code_data['operation'], $code_data['condition'], $code_data['variable'], $code_data['condition-variable'], $code_data['has-expiration'], $code_data['expiration-date']);
$code_query->fetch();

var_dump($code_data);

You really need to get rid of those awful nonstandard identifiers. “condition” is a reserved word, and hyphens don't belong into SQL identifiers. Or is this a requirement as well?

[Allenph]

Thank you very much. No, the column names are not necessarily required to stay the same, it was just a logical name for everything I was storing for the promo codes.

 

 

I was unaware it was bad practice to use hyphens to escape reserved keys. I thought it was best practice to use the most descriptive name you could think of, then worry abut escaping later.