|
一、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;
用户输入文章时，通过选择下拉菜单选定文章分类
写出如何实现这个下拉菜单
drop
table
if
exists
Comment;
drop
table
if
exists
category;
drop
table
if
exists
message;
 
/**/
/*==============================================================*/
/**/
/* Table: Comment */
/**/
/*==============================================================*/
create
table
Comment
(
comment_id
int
unsigned
not
null
,
id
int
unsigned
not
null
,
comment_content
text
,
primary
key
(comment_id)
)
type
=
InnoDB;
/**/
/*==============================================================*/
/**/
/* Table: category */
/**/
/*==============================================================*/
create
table
category
(
category_id
int
not
null
AUTO_INCREMENT,
category_name
varchar
(
40
)
not
null
,
primary
key
(category_id),
key
AK_pk_category_id (category_id)
)
type
=
InnoDB;
/**/
/*==============================================================*/
/**/
/* Table: message */
/**/
/*==============================================================*/
create
table
message
(
id
int
not
null
,
title
varchar
(
120
)
not
null
,
content
text
not
null
,
category_id
int
unsigned,
hit
int
unsigned,
primary
key
(id)
)
type
=
InnoDB;
select
A.id,A.title,A.hits,IFNULL(B.num,
0
)
from
message A
left
join
(
select
id,
count
(
*
)
as
num
from
comment B
group
by
id) B
on
A.id
=
B.id
order
by
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
>
</
html
>
| 
转播
