php pdo mysql query exec区别_PHP-mysql_query到PDO和准备好的语句

我的CMS中有此功能,该功能可将字段和值的变量列表插入两个不同的表中.一个是静态索引表,一个是动态索引表.这是功能：

function insertFields($fields)

{

$stdfields = array();

$extfields = array();

/* Separate the fields based on if the fields is standard or extra. $this->fields is a csv list of the defined extra fields */

foreach($fields as $field => $value)

{

$fields[mysql_real_escape_string($field)] = mysql_real_escape_string($value);

if(strstr($this->fields, $field))

$extfields[$field] = $value;

else

$stdfields[$field] = $value;

}

//Build the 2 queries -- Maybe there is a better way to do this?

$extfieldcount = count($extfields);

$stdfieldcount = count($stdfields);

$stditers = 0;

$extiters = 0;

foreach($extfields as $field => $value)

{

if($extiters != $extfieldcount)

{

$extfields.= $field.", ";

$extvalues.= "'".$value."', ";

}

else

{

$extfields.= $field." ";

$extvalues.= "'".$value."' ";

}

$extiters++;

}

foreach($stdfields as $field => $value)

{

if($stditers != $stdfieldcount)

{

$newfields.= $field.", ";

$newvalues.= "'".$value."', ";

}

else

{

$newfields.= $field." ";

$newvalues.= "'".$value."' ";

}

$stditers++;

}

//Inset the standard fields

$stdquery = "INSERT INTO masteridx (".$newfields.") VALUES (".$newvalues.")";

$this->dbQuery($stdquery);

/* not perfect. I need a better way to find the id that was inserted, so I can combine three queries into at least two */

$findlastquery = "SELECT `id` FROM `masteridx` WHERE `slug`='".$fields['slug']."' LIMIT 1";

$result = $this->dbQuery($findlastquery);

$result = mysql_fetch_assoc($result);

$tempfield = "id, ";

$tempvalue = "'".$result['id']."', ";

//Insert the extra fields

$extquery = "INSERT INTO ".$this->type." (".$tempfield.$extfields.") VALUES (".$tempvalue.$extvalues.")";

$this->dbQuery($extquery);

}

因此,对于准备好的语句,我无法仅绑定值,对吧？因此,如果我做类似的事情,我仍然不得不逃避这些领域：

for ($i = 0; $i <= $stdfieldcount; $i++)

{

if($i < $stdfieldcount)

$qs.= '?, ';

else

$qs.= '? ';

}

$sth = $dbh->prepare("INSERT INTO masteridx ({$stdfields}) VALUES ({$qs})");

$sth->execute($array_of_stdfield_values);

如果我仍然必须逃离田野,那又有什么意义呢？此函数最终将包含多个文章及其字段的数组.字段本身也每次都不同..我发现当我第一次查看准备好的语句时,我可以将其交给一个字段数组和一个值数组,但是我想并不是这样.

我的问题是真的,你们所有人将如何实现？我想开始与数据库无关,而PDO看起来是实现此目的的好方法.

解决方法:

PHP提供了许多便捷功能,这些功能可以手工完成很多工作.

> PDO在您的SQL语句中支持命名参数,因此您可以在其中的键与命名参数占位符匹配的地方传递键/值数组.

> join()函数对于构建以逗号分隔的列表非常有用.

>存在许多操作arrays的功能.

>一些函数允许您给callback(在PHP 5.3中可以是闭包)动态处理数组.

示例(未经测试)：

function insertFields($fields) {

$columns = join(",", array_map(

function($col) { return "`".preg_replace("/`/gu","``",$col)."`"},

array_keys($fields)));

$params = join(",", array_map(

function($col) { return ":".preg_replace("/[`\s]/gu","",$col)},

array_keys($fields)));

$stdquery = "INSERT INTO masteridx ({$columns}) VALUES ({$params})";

$stmt = $pdo->prepare($stdQuery);

$stmt->execute($fields);

}

标签：prepared-statement,mysql,php,pdo

来源： https://codeday.me/bug/20191102/1990295.html