如何将任意数量的值绑定到 mysqli 中的准备好的语句? [英] How to bind an arbitrary number of values to a prepared statement in mysqli?
问题描述
我真的希望有人花一点时间查看我的代码.我正在解析一些新闻内容，我可以将初始解析插入到包含新闻 URL 和标题的数据库中.我想进一步扩展它，传递每个文章链接并解析文章的内容并将其包含在我的数据库中.初始解析的工作原理如下:
find('div[class=mainBlock]', 0);$items = array();foreach ($main->find('a') as $m){$items[] = '("'.mysql_real_escape_string($m->纯文本).'","'.mysql_real_escape_string($m->href).'")';}$reverse = array_reverse($items);mysql_query ("INSERT IGNORE INTO bag_news (article, link) VALUES".(implode(',', $reverse))."");?>如您所见，我使用的是 PHP 简单 HTML DOM 解析器. 为了扩展，我是尝试使用 mysqli 语句，我可以在其中绑定参数，以便将所有 html 标记插入到我的数据库中.我以前用 XML 解析做过这个.问题是我不知道如何绑定数组，看看我的代码是否正确，是否可以这样工作......这是完整的代码:
query("SET NAMES 'utf8'");include_once ('simple_html_dom.php');$html = file_get_html('http://basket-planet.com/ru/');//查找主要新闻$main = $html->find('div[class=mainBlock]', 0);$items = array();foreach ($main->find('a') as $m){$h = file_get_html('http://www.basket-planet.com'.$m->href.'');$article = $h->find('div[class=newsItem]');//转成字符串可以修改内容$a = str_get_html(implode("
", (array)$article));if(isset($a->find('img'))){foreach ($a->find('img') as $img){$img->outertext = '';}}//去掉图片if(isset($a->find('a'))){foreach ($a->find('a') as $link){$link->href = 'javascript:;';$link->target = '';}}//去掉任何javascriptif(isset($a->find('iframe'))){foreach ($a->find ('iframe') as $frame){$frame->outertext = '';}}//去掉 iframe@$a->find('object', 0)->outertext = '';@$a->find('object', 1)->outertext = '';//修改更多内容以仅检索文本内容//将整个内容放入一个 div 中(如果语句在这里有效???)$text_content = ''.$a.'
'.($a->find('object', 0)->data > 0 ?'find('object', 1)->data > 0 ?'plaintext.'","'.$m->href.'","'.$text_content.'")';}//反转数组，使最新的项目具有最后一个ID$reverse = array_reverse($items);$stmt = $mysqli->prepare ("INSERT IGNORE INTO test_news (article, link, text_cont) VALUES (?,?,?)");$stmt->bind_param ???;//(内爆(',', $reverse));$stmt->execute();$stmt->close();?>所以逻辑是针对找到的文章的每个 href，我传递它来解析内容，并尝试将其添加到数组中.我可能有很多错误，但我还不能测试它，因为我不知道如何绑定它以查看它是否有效.而且我也不确定是否可以在 $text_content div 中执行 if 语句...意思是显示播放视频"(如果存在).所以，如果有人能花时间和我一起解决这个问题，我将不胜感激.
更新:将 $text_content div 中的 if 语句更改为比较运算符.
解决方案 这正是 mysqli 真正尴尬的场景.要绑定多个参数，您必须将它们全部作为可变长度参数列表传递给 mysql->bind_param()，但棘手的部分是您必须通过引用绑定它们.PHP 中的引用可能非常混乱.
这是一个粗略的例子(虽然我没有测试过这个确切的代码):
$stmt = $mysqli->prepare("INSERT IGNORE INTO test_news(文章, 链接, text_cont) VALUES (?,?,?)");foreach ($reverse as &$value) {$params[] = &$value;}array_unshift(str_repeat('s', count($params)));call_user_func_array(array($stmt, 'bind_param'), $params);
当我想编写一个通用函数来将参数绑定到 SQL 时，我发现使用 PDO 要容易得多.不需要绑定，只需将一组值传递给 PDOStatement::execute() 方法即可.
$stmt = $pdo->prepare("INSERT IGNORE INTO test_news(文章, 链接, text_cont) VALUES (?,?,?)");$stmt->execute($reverse);
<小时>
更新:如果您需要 $items 来包含多行数据，我会这样做:
首先，在构建 $items 时，将其设为数组数组，而不是将值连接在一起:
foreach ($main->find('a') as $m){$items[] = array($m->plaintext, $m->href, $text_content);}
然后准备一个插入一行的 INSERT 语句，并在 $items 上为每个元组循环执行一次准备好的语句:
$stmt = $pdo->prepare("INSERT INTO test_news(文章, 链接, text_cont) VALUES (?,?,?)");foreach ($items as $tuple) {$stmt->execute($tuple);}
我完全不知道你为什么使用 array_reverse()，我也不知道你为什么使用 INSERT IGNORE，所以我忽略了这些.
I would really like for someone to take a little time and look over my code. I'm parsing some news content and I can insert the initial parse into my database which contains the news URL and the title. I'd like to expand it farther, to pass along each article link and parse the content of the article and include it in my database. The initial parsing works perfectly like this:
<?php
include_once ('connect_to_mysql.php');
include_once ('simple_html_dom.php');
$html = file_get_html('http://basket-planet.com/ru/');
$main = $html->find('div[class=mainBlock]', 0);
$items = array();
foreach ($main->find('a') as $m){
$items[] = '("'.mysql_real_escape_string($m->plaintext).'",
"'.mysql_real_escape_string($m->href).'")';
}
$reverse = array_reverse($items);
mysql_query ("INSERT IGNORE INTO basket_news (article, link) VALUES
".(implode(',', $reverse))."");
?>
As you can see, I'm using PHP Simple HTML DOM Parser. To expand, I'm trying to use mysqli statement where I can bind the parameters so all the html tags get inserted into my database. I've done this before with XML parsing. Problem is I don't know how to bind the array, and see whether my code is correct, if it will work this way... Here's the entire code:
<?php
$mysqli = new mysqli("localhost", "root", "", "test");
$mysqli->query("SET NAMES 'utf8'");
include_once ('simple_html_dom.php');
$html = file_get_html('http://basket-planet.com/ru/');
//find main news
$main = $html->find('div[class=mainBlock]', 0);
$items = array();
foreach ($main->find('a') as $m){
$h = file_get_html('http://www.basket-planet.com'.$m->href.'');
$article = $h->find('div[class=newsItem]');
//convert to string to be able to modify content
$a = str_get_html(implode("
", (array)$article));
if(isset($a->find('img'))){
foreach ($a->find('img') as $img){
$img->outertext = '';}} //get rid of images
if(isset($a->find('a'))){
foreach ($a->find('a') as $link){
$link->href = 'javascript:;';
$link->target = '';}} //get rid of any javascript
if(isset($a->find('iframe'))){
foreach ($a->find ('iframe') as $frame){
$frame->outertext = '';}} //get rid of iframes
@$a->find('object', 0)->outertext = '';
@$a->find('object', 1)->outertext = '';
//modify some more to retrieve only text content
//put entire content into a div (will if statements work here???)
$text_content = '<div>'.$a.'<br>'.
($a->find('object', 0)->data > 0 ? '<a target="_blank" href="'.$a->find('object', 0)->data.'">Play Video</a> ')
($a->find('object', 1)->data > 0 ? '<a target="_blank" href="'.$a->find('object', 1)->data.'">Play Video</a> ')
($a->find('iframe[src*=youtube]', 0)->src > 0 ? '<a target="_blank" href="'.$a->find('iframe', 0)->src.'">Play Video</a> ')
//couple more checks to see if video links are present
.'</div>';
$items[] = '("'.$m->plaintext.'","'.$m->href.'","'.$text_content.'")';
}
//reverse the array so the latest items have the last id
$reverse = array_reverse($items);
$stmt = $mysqli->prepare ("INSERT IGNORE INTO test_news (article, link, text_cont) VALUES (?,?,?)");
$stmt->bind_param ???; //(implode(',', $reverse));
$stmt->execute();
$stmt->close();
?>
So the logic is for every href of an article found, I'm passing it to parse the content and I'm trying to add it to the array. I probably have a ton of errors but I can't test it yet because I don't know how to bind it to see if it works. And I'm also not sure if I can do the if statements inside $text_content div...meaning to display "Play Video" if they exist. So please, if someone can take time to work on this with me I would really appreciate it.
UPDATE: changed the if statements to comparison operators in $text_content div.
解决方案 This is exactly the scenario where mysqli is really awkward. To bind multiple params, you have to pass them all as a variable-length argument list to mysql->bind_param(), but the tricky part is that you have to bind them by reference. References in PHP can be pretty confusing.
Here's an rough example (though I have not tested this exact code):
$stmt = $mysqli->prepare("INSERT IGNORE INTO test_news
(article, link, text_cont) VALUES (?,?,?)");
foreach ($reverse as &$value) {
$params[] = &$value;
}
array_unshift(str_repeat('s', count($params)));
call_user_func_array(array($stmt, 'bind_param'), $params);
I find it much easier to use PDO when I want to write a general-purpose function to bind parameters to SQL. No binding is necessary, just pass an array of values to the PDOStatement::execute() method.
$stmt = $pdo->prepare("INSERT IGNORE INTO test_news
(article, link, text_cont) VALUES (?,?,?)");
$stmt->execute($reverse);
Update: if you need $items to contain multiple rows of data, I'd do it this way:
First, when building $items, make it an array of arrays, instead of concatenating the values together:
foreach ($main->find('a') as $m){
$items[] = array($m->plaintext, $m->href, $text_content);
}
Then prepare an INSERT statement that inserts one row, and loop over $items executing the prepared statement once for each tuple:
$stmt = $pdo->prepare("INSERT INTO test_news
(article, link, text_cont) VALUES (?,?,?)");
foreach ($items as $tuple) {
$stmt->execute($tuple);
}
I don't know why you were using array_reverse() at all, and I don't know why you were using INSERT IGNORE, so I left those out.
这篇关于如何将任意数量的值绑定到 mysqli 中的准备好的语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
