1、数据表字段属性

2、栏目表数据

3、栏目中文章数量表数据

4、源代码

/******db_config.php******/

<?php

$mysql_server_name='localhost'; //改成自己的mysql数据库服务器

$mysql_username='root'; //改成自己的mysql数据库用户名

$mysql_password='root'; //改成自己的mysql数据库密码,初始默认密码为空

$mysql_database='echats'; //改成自己的mysql数据库名

?>

/**********222.php**********/

<?php

require("db_config.php");

$conn=mysql_connect($mysql_server_name,$mysql_username,$mysql_password) or die("连接数据库的过程失败！") ;

mysql_query("set names 'utf8'"); //数据库输出编码

mysql_select_db($mysql_database); //打开数据库

/**************************************************************************************/

echo "id，channel为栏目ID在不同表中的字段名称<br>typename，name为栏目名称在不同表中的字段名称<br>COUNT(channel)，num为栏目下内容数量在不同表中的字段名称<br><br>";

echo "栏目表<br>";

$result1 = mysql_query("select id,typename from dede_channeltype order by id asc");

echo "<table border='1'>

<tr>

<th>id</th>

<th>typename</th>

</tr>";

while($row1 = mysql_fetch_array($result1,MYSQLI_ASSOC)){

echo "<tr>";

echo "<td>" . $row1['id'] . "</td>";

echo "<td>" . $row1['typename'] . "</td>";

echo "</tr>";

}

echo "</table>";

echo "栏目中文章数量表<br>";

$result2 = mysql_query("select channel,COUNT(channel) AS dd from dede_arctiny GROUP BY channel order by channel asc");

echo "<table border='1'>

<tr>

<th>channel</th>

<th>COUNT(channel)</th>

</tr>";

while($row2 = mysql_fetch_array($result2,MYSQLI_ASSOC)){

echo "<tr>";

echo "<td>" . $row2['channel'] . "</td>";

echo "<td>" . $row2['dd'] . "</td>";

echo "</tr>";

}

echo "</table>";

/******************************************************************/

echo "组合两张表数据，写入到新表的数据<br>";

$result = mysql_query("select if(b.nums,b.nums,0) as num,a.typename as name from dede_channeltype as a left join (select channel,count(*) as nums from dede_arctiny group by channel) as b on a.id=b.channel");

echo "<table border='1'>

<tr>

<th>name</th>

<th>num</th>

</tr>";

while($row = mysql_fetch_array($result,MYSQLI_ASSOC)){

echo "<tr>";

echo "<td>" . $row['name'] . "</td>";

echo "<td>" . $row['num'] . "</td>";

echo "</tr>";

//写入新表数据

$sql=mysql_query("replace into test (typename,num) values('".$row['name']."','".$row['num']."')");

}

echo "</table>";

/*********************************************************************/

echo "显示新表的数据<br>";

$result3 = mysql_query("select * from test");

echo "<table border='1'>

<tr>

<th>Id</th>

<th>name</th>

<th>num</th>

</tr>";

while($row3 = mysql_fetch_array($result3,MYSQLI_ASSOC)){

echo "<tr>";

echo "<td>" . $row3['Id'] . "</td>";

echo "<td>" . $row3['typename'] . "</td>";

echo "<td>" . $row3['num'] . "</td>";

echo "</tr>";

}

echo "</table>";

?>

5、运行结果

5、说明

replace：如果不存在就插入，存在就更新

insert ignore：如果不存在就插入，存在就忽略

只对UNIQUE约束的字段起作用。