如何将任意数量的值绑定到mysqli中的预准备语句？ [英] How to bind an arbitrary number of values to a prepared statement in mysqli?

问题描述

我真的希望有人花一点时间看看我的代码。我解析一些新闻内容，我可以插入初始解析到我的数据库，其中包含新闻URL和标题。我想要进一步扩展它，以传递每篇文章链接，并解析文章的内容，并将其包括在我的数据库。初始解析完全像这样：

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))."");
?>

如您所见，我使用 PHP简单的HTML DOM解析器。为了扩展，我试图使用mysqli语句，其中我可以绑定参数，所有的html标签插入到我的数据库。我之前做过这样做与XML解析。问题是我不知道如何绑定数组，看看我的代码是否正确，如果它将工作这种方式...这里是整个代码：

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("\n", (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>&nbsp;&nbsp;')
       ($a->find('object', 1)->data > 0 ? '<a target="_blank" href="'.$a->find('object', 1)->data.'">Play Video</a>&nbsp;&nbsp;')
       ($a->find('iframe[src*=youtube]', 0)->src > 0 ? '<a target="_blank" href="'.$a->find('iframe', 0)->src.'">Play Video</a>&nbsp;&nbsp;')
       //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();
?>

所以逻辑是为每一个href的文章找到，我传递它来解析内容我试图将它添加到数组。我可能有一吨错误，但我不能测试，因为我不知道如何绑定，看看它是否工作。我也不知道如果我能做if语句里面$ text_content div ...意思是显示播放视频，如果他们存在。

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.

更新：将if语句更改为$ text_content div中的比较运算符。

UPDATE: changed the if statements to comparison operators in $text_content div.

推荐答案

这正是mysqli真的很尴尬的情况。要绑定多个参数，你必须将它们作为一个可变长度的参数列表传递给mysql-> bind_param（），但棘手的部分是你必须通过引用来绑定它们 。 PHP中的引用可能很混乱。

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

当我想编写通用函数绑定参数时，我发现使用PDO更容易到SQL。不需要绑定，只需将值的数组传递给PDOStatement :: execute（）方法。

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

---

更新：如果您需要$ items包含多个数据行，我这样做：

---

Update: if you need $items to contain multiple rows of data, I'd do it this way:

首先，当构建$ items时，使它成为数组数组，而不是将值连接在一起： p>

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);
}

然后准备插入一行并循环执行$ items的INSERT语句为每个元组准备的语句一次：

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);
}

我不知道你为什么使用array_reverse我不知道你为什么使用INSERT IGNORE，所以我把它留了。

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屋！