drgc9632 2013-02-12 07:35
浏览 48
已采纳

动态mysqli准备语句失败

I am trying to write a very small abstraction layer for my mysqli connection and have run into a problem. Since I am maintaining older code I need to get an associative array from my query as this is the way the code has been set up and therefor less work for me once this works... This function does work with all kinds of queries(not just select)...

my function I wrote is this:

function connectDB($query,$v=array()) {
    $mysqli = new mysqli(HOST,USER,PW,DATABASE);

    if($res=$mysqli->prepare($query)) {
            //dynamically bind all $v
            if($v) {
            $values=array($v[0]);
            for($i=1; $i<count($v); $i++) {
                ${'bind'.$i}=$v[$i];
                $values[]=&${'bind'.$i};
            }
            call_user_func_array(array($res,'bind_param'),$values);
        }
        $res->execute();

        //bind all table rows to result
        if(strtolower(substr($query,0,6))=="select") {
            $fields=array();
            $meta=$res->result_metadata();
            while($field=$meta->fetch_field()) { 
                ${$field->name}=null;
                $fields[$field->name]=&${$field->name};
            }
            call_user_func_array(array($res,"bind_result"),$fields);

            //return associative array
            $results = array();
            $i=0;
            while($res->fetch()) {
                $results[$i]=array();
                foreach($fields as $k => $v) $results[$i][$k] = $v;
                $i++;
            }
        }
        else {
            $results=$mysqli->affected_rows;
            if($mysqli->affected_rows<1) $results=$mysqli->info;
        }

        $res->close();
    }
    $mysqli->close();

    return $results;
}

so if I call:

$MySqlres=connectDB("select * from `modx_events` events  limit 1");
var_dump($MySqlres);

I get a nice associative array with the content of my select.

Now unfortunately the following mysql query will return NULL as a value to all of it's array keys:

$MySqlres=connectDB("select *, events.`id` as `ID`,venues.`name` as `venueName`,
venues.`suburb` as `venueSuburb`,venues.`advertiser` as `venueAdvertiser`
from `modx_events` events left join `modx_venues` venues on events.`venue`=venues.`id`
where events.`id`!='e' order by events.`start_date` asc, venues.`name` limit 1");

(the same query runs as pure SQL and will return the correct values) Any idea what this could be? Does my associative array function fail? Is there something wrong with the way I implemented the query?

ps: PDO is not an option and mysqlnd is not installed... :(

ADDED QUESTION

  • is this too much of overhead just to preserve the associative array return? Should I go with $res->fetch_object() instead?
  • 写回答

2条回答 默认 最新

  • doudeng9425 2013-02-13 08:32
    关注

    I just fixed the function. Perhaps this is interesting for someone else:

    function connectDB($mysqli,$query,$v=array()) {
    
        if($mysqli->connect_errno) {
            return array('error'=>'Connect failed: '.$mysqli->connect_error); //error handling here
            exit();
        }
    
        if(substr_count($query,"?")!=strlen($v[0])) {
            return array('error'=>'placeholders and replacements are not equal! placeholders:'.substr_count($query,"?").' replacements:'.strlen($v[0]).' ('.$v[0].')'); //error handling here...
            exit();
        }
    
        if($res=$mysqli->prepare($query)) {
                //dynamically bind all $v
                if($v) {
                $values=array($v[0]);
                for($i=1; $i<count($v); $i++) {
                    ${'bind'.$i}=$v[$i];
                    $values[]=&${'bind'.$i};
                }
                call_user_func_array(array($res,'bind_param'),$values);
            }
            $res->execute();
    
            //bind all table rows to result
            if(strtolower(substr($query,0,6))=="select") {
                $field=$fields=$tempRow=array();
                $meta=$res->result_metadata();
                while($field=$meta->fetch_field()) {
                    $fieldName=$field->name;
                    $fields[]=&$tempRow[$fieldName];
                }
                $meta->free_result();
                call_user_func_array(array($res,"bind_result"),$fields);
    
                //return associative array
                $results=array();
                $i=0;
                while($res->fetch()) {
                    $results[$i]=array();
                    foreach($tempRow as $k=>$v) $results[$i][$k] = $v;
                    $i++;
                }
                $res->free_result();
    
            }
            else { //return infos about the query
                $results["affectedRows"]=$mysqli->affected_rows;
                $results["info"]=$mysqli->info;
                $results["insertID"]=$mysqli->insert_id;
            }
    
            $res->close();
        }
    
        return $results;
    }
    

    cheers

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥45 工程软件单片机设计课题要求
  • ¥15 在centos7安装conda
  • ¥15 c#调用yolo3 dll文件获取的数据对不上
  • ¥20 WPF 如何实现多语言,label 和cs(live Charts)中是否都能翻译
  • ¥15 STM32F103上电短路问题
  • ¥15 关于#单片机#的问题:以ATMEGA128或相近型号单片机为控制器设计直流电机调速的闭环控制系统(相关搜索:设计报告|软件设计|流程图)
  • ¥15 打开软件提示错误:failed to get wglChoosePixelFormatARB
  • ¥15 (标签-python|关键词-char)
  • ¥15 python+selenium,在新增时弹出了一个输入框
  • ¥15 苹果验机结果的api接口哪里有??单次调用1毛钱及以下。