PHP面试题及答案(七)
一、PHP/MySQL编程
1) 某内容管理系统中，表message有如下字段
id 文章id
title 文章标题
content 文章内容
category_id 文章分类id
hits 点击量
创建上表，写出MySQL语句
2)同样上述内容管理系统：表comment记录用户回复内容，字段如下
comment_id 回复id
id 文章id，关联message表中的id
comment_content 回复内容
现通过查询数据库需要得到以下格式的文章标题列表,并按照回复数量排序，回复最高的排在最前面
文章id 文章标题 点击量 回复数量
用一个SQL语句完成上述查询，如果文章没有回复则回复数量显示为0
3) 上述内容管理系统，表category保存分类信息，字段如下
category_id int(4) not null auto_increment;
categroy_name varchar(40) not null;
用户输入文章时，通过选择下拉菜单选定文章分类
写出如何实现这个下拉菜单
droptableifexists Comment;
droptableifexists category;
droptableifexists message;
/*==============================================================*/
/* Table: Comment */
/*==============================================================*/
createtable Comment
(
comment_id int unsigned notnull,
id int unsigned notnull,
comment_content text,
primarykey (comment_id)
)
type = InnoDB;
/*==============================================================*/
/* Table: category */
/*==============================================================*/
createtable category
(
category_id int notnull AUTO_INCREMENT,
category_name varchar(40) notnull,
primarykey (category_id),
key AK_pk_category_id (category_id)
)
type = InnoDB;
/*==============================================================*/
/* Table: message */
/*==============================================================*/
createtable message
(
id int notnull,
title varchar(120) notnull,
content text notnull,
category_id int unsigned,
hit int unsigned,
primarykey (id)
)
type = InnoDB;
select A.id,A.title,A.hits,IFNULL(B.num,0)
from message A leftjoin (select id,count(*) as num from comment B groupby id) B
on A.id=B.id
orderby B.num desc;
droptableifexists category;
droptableifexists message;
/*==============================================================*/
/* Table: Comment */
/*==============================================================*/
createtable Comment
(
comment_id int unsigned notnull,
id int unsigned notnull,
comment_content text,
primarykey (comment_id)
)
type = InnoDB;
/*==============================================================*/
/* Table: category */
/*==============================================================*/
createtable category
(
category_id int notnull AUTO_INCREMENT,
category_name varchar(40) notnull,
primarykey (category_id),
key AK_pk_category_id (category_id)
)
type = InnoDB;
/*==============================================================*/
/* Table: message */
/*==============================================================*/
createtable message
(
id int notnull,
title varchar(120) notnull,
content text notnull,
category_id int unsigned,
hit int unsigned,
primarykey (id)
)
type = InnoDB;
select A.id,A.title,A.hits,IFNULL(B.num,0)
from message A leftjoin (select id,count(*) as num from comment B groupby id) B
on A.id=B.id
orderby B.num desc;
<html>
<head><title>JS打印</title></head>
<body>
<form>
<select id="category" name="category">
<?php
mysql_connect("localhost","root","") or die("db conn error:".mysql_error());
mysql_select_db("phpinterview") or die("db error".mysql_error());
$result=mysql_query("select category_id,category_name from category");
while($row=mysql_fetch_array($result))
{
echo"<option value='".$row["cateogry_id"]."'>".$row["category_name"]."</option>";
}
?>
</select>
</form>
</body>
<head><title>JS打印</title></head>
<body>
<form>
<select id="category" name="category">
<?php
mysql_connect("localhost","root","") or die("db conn error:".mysql_error());
mysql_select_db("phpinterview") or die("db error".mysql_error());
$result=mysql_query("select category_id,category_name from category");
while($row=mysql_fetch_array($result))
{
echo"<option value='".$row["cateogry_id"]."'>".$row["category_name"]."</option>";
}
?>
</select>
</form>
</body>
