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条)

报告相同问题?

悬赏问题

  • ¥15 MATLAB怎么通过柱坐标变换画开口是圆形的旋转抛物面?
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥30 用arduino开发esp32控制ps2手柄一直报错
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题
  • ¥15 Visual Studio问题