1

I'm trying to include prepared statements into my code, yet I encountered a problem. Since I don't like the idea to repeat code each time when I need to make simple query I wrote function. Right now with prepared statements it looks like this:

function mysqli_select($conn, $ask, $param, $vars)
{
    $arr = [];
    $stmt = $conn->prepare($ask);
    $stmt->bind_param($param, $vars);
    $stmt->execute();
    $result = $stmt->get_result();
    while($row = $result->fetch_assoc()) {
        $arr[] = $row;
    }
    $stmt->close();
    return $arr;
}

The problem begins, if the query $ask contains multiple ? and there are more than one variable in $vars. Since I'd like to have this function universal, I need to assume, that I don't know how many $vars there will be. It's clear to me that $vars should be a table, but how to pass them properly in bind_param($param, $vars);?

I'm aware that I need to put there another if to simplify output of queries that I'll know that will return just one row - this is not a problem.

By the way, does such solution - I mean entire idea of such function - is good practice? In all examples I see that everybody pastes the common parts over and over again. Am I doing something wrong? Was I looking on the wrong examples? No tutorial that I read suggest to do such thing, so perhaps doing this is somehow wrong on the idea, or execution side?

Dharman
  • 30,962
  • 25
  • 85
  • 135
Zorann
  • 335
  • 2
  • 16
  • what do you have in param and vars? – Hardood Jun 02 '20 at 10:23
  • As You can see in `$stmt->bind_param($param, $vars);`, $param would be a string that defines variable types passed to prepared statement for example `'ssi'`. $vars would be in this case array of 3 variables like `array($login, $password, $user_id)` – Zorann Jun 02 '20 at 10:26
  • 2
    For me this function is useless. I would throw it away. Maybe switch to PDO. However you can bind an array with `$stmt->bind_param($param, ...$vars);` – Paul Spiegel Jun 02 '20 at 10:27
  • For param variable you are right, but $vars must not be array, it must be bind_param('ssi', $str1,$str2,$int); – Hardood Jun 02 '20 at 10:30
  • @PaulSpiegel I don't see why this function is useless? I don't like to multiplay code lines by repeating lines like `$stmt->execute();` or `$result = $stmt->get_result();` each time when I want to make select. If there is something wrong with such function, please be more specific. – Zorann Jun 02 '20 at 10:32
  • @Hardood Yes, but the question was what if I don't know how many variables will be there. I'll look for linked solution since the topic was closed anyway :( – Zorann Jun 02 '20 at 10:33
  • It is easy to do so, but the params in string, and then implode the array of vars to string with comma between each value. – Hardood Jun 02 '20 at 10:41
  • @PaulSpiegel I never saw the `...$array` thing before, and I'm unable to google it. This does work as expected and it does look like this is exactly the answer that I was looking for, but I'd like to read more about how it works and what it does. How is is called? – Zorann Jun 02 '20 at 10:44
  • @Hardood No, if I'd do that, than my array would be a single string, implode is not the solution here. – Zorann Jun 02 '20 at 10:46
  • 1
    Search for "splat operator" or look under "Argument unpacking" here: https://www.php.net/manual/en/migration56.new-features.php – Paul Spiegel Jun 02 '20 at 10:49
  • Then just switch to PDO, with PDO you can easily bind an array. No need for complexity and reinventing the wheel. – Hardood Jun 02 '20 at 10:49
  • 1
    This is a really good idea to write such function even if you should be using PDO instead of mysqli. mysqli is difficult and if you are going to use it then you are going to need such function. Take a look at a similar function I have written recently: https://stackoverflow.com/a/61954741/1839439 – Dharman Jun 02 '20 at 11:15

0 Answers0