PHP/MYSQLi Bind Result

Question

I'm writing for a web, that does not have mysqlnd. So I need to quickly change all my code to not use it. I thought I was there with this function, but it repeats results.

I would really appreciate any help as to the fix.

function getDBResults($sql, $params=array()) { // param 1 sql, param2 array of bind parameters
    $con=mysqli_connect(DB_HOST,DB_USER,DB_PASS,DB_DATABASE);
    if (!is_array($params)) {
        $params = array($params);
    }

    $query = $con->stmt_init(); // $this->connection is the mysqli connection instance

    // Start stmt
    if($query->prepare($sql)) {

        if (count($params)>0) { // Skip of there are no paremeters
            // This will loop through params, and generate types. e.g. 'ss'
            $types = '';                        
            foreach($params as $param) {        
                if(is_int($param)) {
                    $types .= 'i';              //integer
                } elseif (is_float($param)) {
                    $types .= 'd';              //double
                } elseif (is_string($param)) {
                    $types .= 's';              //string
                } else {
                    $types .= 'b';              //blob and unknown
                }
            }
            array_unshift($params, $types);
            // Bind Params
            $bpArray = array($query,'bind_param');
            $bpArray = 'mysqli_stmt_bind_param';

            array_unshift($params, $query);

            $tmp = array();
            foreach($params as $key => $value) {
                $tmp[$key] = &$params[$key];
            };

            call_user_func_array($bpArray, $tmp); 
        }

        $query->execute(); 

        // Get metadata for field names
        $meta = $query->result_metadata();

        // initialise some empty arrays
        $fields = $results = array();

        // This is the tricky bit dynamically creating an array of variables to use
        // to bind the results
        while ($field = $meta->fetch_field()) { 
            $var = $field->name; 
            $$var = null; 
            $fields[$var] = &$$var; 
        }

        // Bind Results
        call_user_func_array(array($query,'bind_result'),$fields);

        // Fetch Results
        while (mysqli_stmt_fetch($query)) { 
            call_user_func_array(array($query,'bind_result'),$fields);
            $results[] = $fields; 
        }
        $query->close();
        // And now we have a beautiful
        // array of results, just like
        //fetch_assoc
        return $results;
    }
}

what do you mean? Im new to php/mysql - this is the php web ive had to write so forgive my ignorance. — Denzil Newman, Mar 15 '14 at 19:43
I cannot see why it doesnt work, thats why I am asking for help. I dont ask before looking. Im sorry but I really dont find your answer helpful. (I asked what you mean incase you were refering to some specific debugging feature I may have been unaware of) — Denzil Newman, Mar 15 '14 at 19:46
I would rather say that it is *occupation* you choose it trolling you as it turned to be not a rose garden you were thinking of. — Your Common Sense, Mar 15 '14 at 19:47
First, this is not an answer, but a *comment*. Second, debugging *is* a technique used by programmers to find out what is going wrong. — Your Common Sense, Mar 15 '14 at 19:49
BTW, if you are new to php/mysql, then let me suggest you to use PDO instead of mysqli, as it doesn't have none of the latter's problems - neither with binding variable number of params, nor with binding results. — Your Common Sense, Mar 15 '14 at 19:50
thanks for the PDO suggestion, I have seen that. Im concerned about its availability (on the host being used). Before this week I didnt even know there were so many connectors/drivers for mysql/php. I really am a complete newbie to it — Denzil Newman, Mar 15 '14 at 19:52
I have to say that your attempt is quite awesome for someone who is new to the business - most of codes posted here are way silliest. But honestly, doing debugging all day round on my job, I don't feel like doing the same on a weekend, and doing it "mentally", by means of reading code (as real debugging require *running* it). But what I wanted to say is that "being stuck" is a *regular* state for the programmer - there is nothing special in it. — Your Common Sense, Mar 15 '14 at 20:07
Thank you, I know too well. The problem is I am just unfamiliar with bind results - I code with different languages but usually stuck with old Classic ASP and VBA. I wouldnt be asking if it wasnt the combination of a Monday deadline and my sons birthday tomorrow. Not that its your problem. Also I cant claim full credit, its a frankenstein like creation of three another dynamic parameter scripts :) . — Denzil Newman, Mar 15 '14 at 20:14
Just give PDO a try. as I said above, it doesn't have none of the problems this frankenstein is intended to solve. I'll show you the code — Your Common Sense, Mar 15 '14 at 20:17
Ok will give it a try, looking at other answers its a bit of a panacea it seems :) — Denzil Newman, Mar 15 '14 at 20:19

score 1 · Accepted Answer · edited May 23 '17 at 12:21

1

function getDBResults($sql, $params=array()) {
    global $pdo; // you should NEVER connect in a function! But use already opened.
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll();
}

see - three simple lines.

here is all the info you need. Just remember you have to connect only once per application and use this connection all the way.

edited May 23 '17 at 12:21

Community

1
1

answered Mar 15 '14 at 20:19

Your Common Sense

156,878
40
214
345

it's just because mysqli is actually a raw API but PDO is a higher-level library that already have methods for all the general purpose tasks. Once *can* build one upon mysqli, but it will take time – Your Common Sense Mar 15 '14 at 20:26
Thanks, tested on host seems to support PDO, what a relief. – Denzil Newman Mar 15 '14 at 20:38
Just to add, I know we "got off on the wrong foot" - I really appreciate your answer. So simple I wish I knew about PDO days ago... as I'm rewriting my libs for it, the code is a fraction of its original size - its almost 'elegant' now in places :) – Denzil Newman Mar 16 '14 at 01:30

PHP/MYSQLi Bind Result

1 Answers1