从一个表(mysql)中获取每个标题，从另一个表中获取url，并使用title提取每个url上的数据 - fetch each title from one table (mysql) and fetch url from another table and extract data on each url using title

我有两个表"product_title"有产品标题和"product_list_url"有产品列表页面的url。我想从产品标题表取标题，取和去每个url一个接一个搜索相同的标题在页面上，如果发现，提取一些数据并保存到数据库。

我想检查每个url上的每个产品。但是没有得到要求的结果

这是我的代码。

<?php
set_time_limit(0);
$dbhost = "localhost";
$dbuser = "root";
$dbpass = "";
$dbname = "crawl";
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ("Error connecting to database");
mysql_select_db($dbname, $conn);
$q1 = mysql_query("SELECT * FROM `product-title`");
$q2 = mysql_query("SELECT * FROM `product-list-url`");
while($res1 = mysql_fetch_assoc($q1)){
    $product_title = $res1['title'];
    while($res2 = mysql_fetch_assoc($q2)){
        $url = $res2['url'];
        $html = file_get_contents($url);        
        $doc = new DOMDocument(); 
        @$doc->loadHTML($html);     
        $xpath = new DOMXPath($doc);
        $found = $xpath->evaluate("boolean(//span[contains(text(), '$product_title')])");
        if($found == false){
            echo "Not Found";       
        }
        else{ 
            $elements = $xpath->evaluate("//span[contains(text(), '$product_title' )]/following-sibling::div/span[@class='list_sale_price']");
            if (!is_null($elements)) {
                foreach ($elements as $element) {
                    $nodes = $element->childNodes;
                    foreach ($nodes as $node) {
                        $price = $node->nodeValue;
                        $price1 = preg_replace('/[^0-9-.]/','',$price);
                        $date = date('y-m-d');
                        mysql_query("INSERT INTO `prices` (`ptitle`, `price`, `date`) VALUES ('$product_title', '$price1', '$date')") or die(mysql_error());                    
                    }
                }
            }   
        }
    }
} 
?>

首先，使用mysql_是不好的，因为它将被弃用。关于这个话题，我认为你正在寻找的答案是JOIN。表product-title必须对每个产品有某种唯一ID，对吧?我假设您的product-list-url表也有与第一个表中的产品匹配的ID。由于我没有任何表格结构，我将给你们举一些例子。

SELECT t1.title
     , t2.url
FROM `product-title` t1
JOIN `product-list-url` t2 ON t1.id = t2.id

只是重置指针可能允许您处理第一个product-title之后的记录。没有测试，也没有检查你的文档处理。

<?php
set_time_limit(0);
$dbhost = "localhost";
$dbuser = "root";
$dbpass = "";
$dbname = "crawl";
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ("Error connecting to database");
mysql_select_db($dbname, $conn);
$q1 = mysql_query("SELECT * FROM `product-title`");
$q2 = mysql_query("SELECT * FROM `product-list-url`");
while($res1 = mysql_fetch_assoc($q1))
{
    $product_title = $res1['title'];
    while($res2 = mysql_fetch_assoc($q2))
    {
        $url = $res2['url'];
        $html = file_get_contents($url);        
        $doc = new DOMDocument(); 
        @$doc->loadHTML($html);     
        $xpath = new DOMXPath($doc);
        $found = $xpath->evaluate("boolean(//span[contains(text(), '$product_title')])");
        if($found == false)
        {
            echo "Not Found";       
        }
        else
        { 
            $elements = $xpath->evaluate("//span[contains(text(), '$product_title' )]/following-sibling::div/span[@class='list_sale_price']");
            if (!is_null($elements)) {
                foreach ($elements as $element) {
                    $nodes = $element->childNodes;
                    foreach ($nodes as $node) {
                        $price = $node->nodeValue;
                        $price1 = preg_replace('/[^0-9-.]/','',$price);
                        $date = date('y-m-d');
                        mysql_query("INSERT INTO `prices` (`ptitle`, `price`, `date`) VALUES ('$product_title', '$price1', '$date')") or die(mysql_error());                    
                    }
                }
            }   
        }
    }
    mysql_data_seek($q2, 0);
} 
?>

然而，将标题列表读入内存，然后处理每个文件一次，多次检查内容可能更有效。每个标题一次。

像这样:-

<?php
set_time_limit(0);
$dbhost = "localhost";
$dbuser = "root";
$dbpass = "";
$dbname = "crawl";
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ("Error connecting to database");
mysql_select_db($dbname, $conn);
$product_titles = array();
$q1 = mysql_query("SELECT * FROM `product-title`");
while($res1 = mysql_fetch_assoc($q1))
{
    $product_titles[] = $res1['title'];
}
$q2 = mysql_query("SELECT * FROM `product-list-url`");
while($res2 = mysql_fetch_assoc($q2))
{
    $url = $res2['url'];
    $html = file_get_contents($url);        
    $doc = new DOMDocument(); 
    @$doc->loadHTML($html);     
    $xpath = new DOMXPath($doc);
    foreach($product_titles as $product_title)
    {
        $found = $xpath->evaluate("boolean(//span[contains(text(), '$product_title')])");
        if($found == false)
        {
            echo "Not Found";       
        }
        else
        { 
            $elements = $xpath->evaluate("//span[contains(text(), '$product_title' )]/following-sibling::div/span[@class='list_sale_price']");
            if (!is_null($elements)) 
            {
                foreach ($elements as $element) 
                {
                    $nodes = $element->childNodes;
                    foreach ($nodes as $node) 
                    {
                        $price = $node->nodeValue;
                        $price1 = preg_replace('/[^0-9-.]/','',$price);
                        $date = date('y-m-d');
                        mysql_query("INSERT INTO `prices` (`ptitle`, `price`, `date`) VALUES ('$product_title', '$price1', '$date')") or die(mysql_error());                    
                    }
                }
            }   
        }
    }
}
?>