开发者

php - How to efficient writing mysql update in this situation?

I want to let my database article reclassified. I have no good idea to Classification articles more better. I build a reference table, put every probably words of each category in it. then I query from the main table articles, explore every article contents into words and put them into reference table to match whether the article is belong which category? then do a update. I want control every category has a max articles are 5. I write some code like this.

<?php
header('Content-type:text/html; charset=utf-8');
$db = mysql_connect("localhost","root","root") or die("can not connect Mysql Server");
mysql_select_db("mydb",$db);
$result = mysql_query("SELECT content,id,directory,date FROM articles WHERE Order By date DESC");//get all the articles from database
while ($row = mysql_fetch_array($result))
{
$id = $row['id'];
$content = $row['content'];
$words = preg_split("/\s+/",$content); 
$uniqueWords = array_keys(array_flip($words)); 
$parts = '';
foreach($uniqueWords as $word){     
$parts[] = " tag1 = '$word' OR tag2 = '$word' OR tag3 = '$word' OR tag4 = '$word' OR tag5 = '$word' ";   
$where = implode(" OR ", $parts);
mysql_select_db("mydb",$db);
mysql_query("SET NAMES utf8");
$query = mysql_query("SELECT * FROM tags1 WHERE ($where) AND category='art' "); // put every word from each article into `tag1` database whether the article is match category='art'
$citn = '';
            while ($row = mysql_fetch_array($query)) {
        $citn = $row['category'];
        } 
    $catnew = $citn;
mysql_query("UPDATE articles SET directory = '".$catnew."' WHERE id = '".$id."' AND directory ='0'  Order By date DESC LIMIT 5"); // update first 5 articles which is match category='art' 
$query = mysql_query("SELECT * FROM tags1 WHERE ($where) AND category='fashion' "); // put every word from each article into `tag1` database whether the article is match category='fashion'
$citn = '';
            while ($row = mysql_fetch_array($query)) {
        $citn .= $row['category']."|" ;
        } 
    $catnew = explode("|",$citn);
mysql_query("UPDATE articles SET directory = '".$catnew."' WHERE id = '".$id."' AND directory ='0'  Order By date DESC LIMIT 5"); // update first 5 articles which is match category='fashion'
$query = mysql_query("SELECT * FROM tags1 WHERE ($where) AND category='travel' ");// put every word from each article into `tag1` database whether the article is match category='travel'
... // there have 20 `category`, each is the same judge method.
}
}
mysql_close($db);
?>

but the speed is very ve开发者_如何学编程ry slow, I test 60 articles in my articles db table, 20 category in tag1 table. But during the update it will echo Fatal error: Maximum execution time of 60 seconds exceeded in...

How to make an efficient update Or is there has better way to do this work? Thanks.

this is my articles table

id | title | link | content | date | directory
1  | ...   | ...  | ...     | ...  |  0 // all directory value are `0` 
2  | ...   | ...  | ...     | ...  |  0 
3  | ...   | ...  | ...     | ...  |  0
... // more 60 articles

this is my tag1 table

category | tag1    | tag2   | tag3       | tag4    |  tag5   
tourism  | tourism | travel | tour       | journey |  trip
fashion  | style   | vogue  | fashion    | mode    |  Popular
art      | paint   |
... // more 20 categories


Forgive me, I feel I must have read your code wrong as, as I read it theres a number of questions about your code that puzzle me.

Firstly you have a number of areas such as:

  while ($row = mysql_fetch_array($query)) {
$citn = $row['category'];
} 

which, will when run of course only show the last entry in $citn, but in your where clause you specify the category must be 'art' for example, as a result, whats the point of the query, you already knew the answer to be 'art' ? Unless this is simplified code.

So, Im left wondering what you think your code does, as far as I can tell, the first one for 'art' will return $citn as 'art' the second will return "fashion|fashion.." for each line in the tags1 table that it finds, the last update will probably fail if theres more than 1 fashion, as exploding it into an array Im not sure how the SQL will handle a PHP array dumped in there. The last query you dont show any code for, so.. Im a little lost.

I get the impression you wanted to explode the text of a given article, and then see if 5 or more tags it has appear in the cateogry. If so, I dont see this code doing that.

As I said, I may have misread it somehow.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜