先把需求说一下吧。从 MSSQL 中导出一个文章表,需要插入到 PHPCMS 中的内容表 phpcms_content 去,需要做到文章可以发布到不同的栏目中去。也就是说,需要复制一条记录,并修改其 catid,再插入到表尾的位置上。
MySQL 复制一条数据并插入的语句:
1 |
INSERT INTO phpcms_content (SELECT ".$r[$i]['aid']." + 520, ".$r[$i]['cateid']." , news_catid, catid, typeid, areaid, title, style, thumb, keywords, keywords, posids, url, listorder, status, userid, username, inputtime, updatetime, searchid, islink, prefix FROM phpcms_content WHERE contentid = '".$r[$i-1][' aid ']."' ) |
大致为:insert into a SELECT id+1, ...(其它字段) FROM a ;
下面PHP具体程序:
01 |
$query = "SELECT * FROM articleincategory ORDER BY ArticleID " ; |
02 |
$result = $connector -> query( $query ); |
05 |
while ( $myrow = $connector -> fetch_array( $result )) |
07 |
$r [ $i ][ 'aid' ] = $myrow [ "ArticleID" ]; |
08 |
$r [ $i ][ 'cateid' ] = $myrow [ "CategoryID" ]; |
12 |
for ( $i = 0; $i < count ( $r ); $i ++) |
16 |
if ( $r [ $i ][ 'aid' ] == $r [ $i -1][ 'aid' ] ) |
18 |
echo '第 ' . $i . ' 条数据 ' . $r [ $i ][ 'aid' ] . ' 与前一条数据 ' . $r [ $i -1][ 'aid' ] . ' 重复' . '<br/>' ; |
19 |
$sql = " INSERT INTO phpcms_content (SELECT " . $r [ $i ][ 'aid' ]. " + 520, " . $r [ $i ][ 'cateid' ]. ", news_catid, catid, typeid, areaid, title, style, thumb, keywords, keywords, posids, url, listorder, status, userid, username, inputtime, updatetime, searchid, islink, prefix FROM phpcms_content WHERE contentid = '" . $r [ $i -1]['aid ']."' ) "; |
26 |
else if ( $r [ $i ][ 'aid' ] != $r [ $i -1][ 'aid' ] ) |
28 |
$sql = " UPDATE phpcms_content SET origin_cateid = '" . $r [ $i ]['cateid ']."' WHERE contentid = '".$r[$i][' aid ']."' "; |
如果不需要插入,则更简单:insert into mytable (select * from mytable where id=1) ON DUPLICATE KEY UPDATE id=2;
或者: update mytable set id=2 where id=1;
(责任编辑:IT) |