Printing an entire SQL result from a prepared statement in PHP

Question

I'm trying to build a function that allows me to abstract the use of the mysqli functions in my code. Currently I have a working version for using standard SQL (no prepared statements).

function mySqlQueryToArray($con, $sql){
    // Process SQL query
    $result = mysqli_query($con, $sql);

    if(mysqli_error($con)){
        out($sql . "\n");
        exit("ERROR: MySQL Error: " . mysqli_error($con));
    }

    // Put result  into 2D array
    $output = array();
    while($row = mysqli_fetch_assoc($result)) {
        array_push($output, $row);
    }

    return $output;
}

Now I'm trying to translate that code into something that can use prepared statements but my research has revealed that when using prepared statements you need to bind each column to a different variable using mysqli_stmt::bind_result which causes a problem because the point of the function is to work for an arbitrary SQL Query.

My main question is this: Is there a way to print out the entire output from a SQL query in a 2D array same as the function above does using prepared statements?

Here's my current code for using prepared statements, it has everything but the bind_result in there.

//Creates a MySQL Query, gets the result and then returns a 2D array with the results of the query
function mySqlQueryToArray($con, $sql, $params){
    // Prepare SQL query
    $stmt = $con->prepare($sql);

    if(mysqli_error($con)){
        echo "$sql=>\n" . print_r($params, true) . "\n";
        exit("$sql=>\n" . print_r($params, true) . "\nERROR: MySQL Error: " . mysqli_error($con));
    }

    // Bind parameters
    foreach($params as $param){
        $type = "s";
        if(gettype($param) == 'integer'){
            $type = "i";
        }
        $stmt->bind_param($type, $param);
    }

    //execute query
    $stmt->execute();

    // Put result  into 2D array
    $output = array();
    // ??? need to bind results and get them into an array somehow

    //close prepared statement
    $stmt->close();

    return $output;
}

I think you would have a much easier time using PDO rather than mysqli for this type of thing. You will have more flexibility when dealing with prepared statements. — Don't Panic, Aug 18 '14 at 15:36

score 0 · Answer 1 · answered Aug 18 '14 at 16:52

PDO turns out to be the answer. I feel like I should post the code I created to solve my problem. Thanks to @Don'tPanic for the tip.

function pdoQuery($sql, $params){
    $db = new PDO('mysql:host=localhost;dbname=My_Database;charset=utf8', 'username', 'password', array(PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
    try {
        $stmt = $db->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return $rows;
    }catch(PDOException $ex){
        echo "ERROR: SQL Error: $sql";
        // logError("ERROR: SQL Error: $sql");
    }
}

Printing an entire SQL result from a prepared statement in PHP

1 Answers1