如何使用mysql查询在我的数据库中仅查找特定表和特定列名 [英] How to find only specific tables and specific column names in my database using mysql query
问题描述
因为我已经让管理员动态创建表单.当他创建表单时，我所做的是我动态地为表单创建了表格.一切正常.现在我想显示特定的表格和特定的用于生成报告的列.
我的问题是:
我不想显示用于生成报告的 (userlogin,place,venue) 表和三列 (user_id,user_common_id,ayear) 这三列对于我动态创建的所有表都是通用的.
基于每年(2013、2014、2015)我想生成报告.这个我会在生成报告时以表格形式发送到mysql.我想列出我动态创建的表格是什么.
如何为此编写查询.
到目前为止，我还提供了我的代码，我所做的在这里:
query("SHOW TABLES");while ( $row = $result->fetch_row() ){$table = $row[0];echo '<h3>',$table,'</h3>';$result1 = $mysqli->query("SELECT * FROM $table where ayear='2014'");如果($result1){echo '';$column = $mysqli->query("SHOW COLUMNS FROM $table");echo '';while($row3 = $column->fetch_row() ){echo ''.$row3[0].' ';}回声'</tr>';while($row2 = $result1->fetch_row() ){echo ' ';foreach($row2 as $key=>$value) {echo '<td>',$value,'</td>';}回声'</tr>';}echo '</table><br/>';}}$mysqli->close();?>结果如下
(待填写)
这是预期的结果
id 日期 No_of_Days 详情 user_id user_common_id ayear-- -- -- ---------- ---------------- -------- ----------- -----1 20/07/2013 1 电影放映... 16 311 12 29/08/2013 1 戏剧... 16 318 13 08/03/2014 1 视频会议.. 10 621 1
解决方案 首先我介绍这个小块，以后可能对其他人有用.
select table_name,column_name,ordinal_position from information_schema.columns其中 table_schema = '事件'按 table_name,ordinal_position 排序
现在开始.
$mysqli = new mysqli("localhost", "root", "", "event");$result = $mysqli->query("SHOW TABLES");$dontDisplay = array('user_id','user_common_id','ayear');while ( $row = $result->fetch_row() ){ $table = $row[0];echo '<h3>',$table,'</h3>';$iterationOrdIgnore=array();$result1 = $mysqli->query("SELECT * FROM $table where ayear='2014'");如果($result1){ $ff = $result1->fetch_fields();$iLoop=0;foreach ($ff 为 $oneF) {if (in_array($oneF->name, $dontDisplay)) {$iterationOrdIgnore[]=$iLoop;}$iLoop++;}echo '';$column = $mysqli->query("SHOW COLUMNS FROM $table where field not in ('user_id','user_common_id','ayear')");echo '';while($row3 = $column->fetch_row() ){ echo '<th>'.$row3[0].'</th>';}回声'</tr>';while($row2 = $result1->fetch_row() ){回声' ';$iLoop=0;foreach($row2 as $key=>$value) {如果 (!in_array($iLoop, $iterationOrdIgnore)) {echo "<td>".$value."</td>";}$iLoop++;}回声'</tr>';}echo '</table><br/>';}}$mysqli->close();我使用了 $dontDisplay 数组作为不显示的列名.
您处理的每个表都可以预期在不同的顺序位置(当然)具有列.所以数组 $iterationOrdIgnore 保存了你正在处理的表的序数位置......忽略.
fetch_fields() 用于对结果集获取列名以驱动上述 $iterationOrdIgnore.
当列值被回显时，要忽略的将被忽略.
另外，请密切关注彼此远离的两条线.
$dontDisplay = array('user_id','user_common_id','ayear');和$column = $mysqli->query("SHOW COLUMNS FROM $table where field not in ('user_id','user_common_id','ayear')");
以某种方式将它们组合起来，用于不易出错的情况，即您修改一个而不是另一个.但这是一个快速的尝试，以证明它有效.它当然可以清理.
请参阅fetch_fields 和in_array().
架构(供他人测试)
创建表t1( id int auto_increment 主键，colQ int 不为空，colR int 不为空，详细信息 varchar(50) 不为空，user_id int 不为空，user_common_id int 不为空，一年整数不为空);插入 t1(colq,colr,particulars,user_id,user_common_id,ayear) 值 (1,1,'blah blah 1',1,1,2014),(2,2,'blah blah 2',2,2,2013),(3,3,'等等等等 3',3,3,2012);创建表 t2( -- 不同的表列顺序id int auto_increment 主键，详细信息 varchar(50) 不为空，一年 int 不为空，colQ int 不为空，user_id int 不为空，colR int 不为空，user_common_id int 不为空);插入 t2(colq,colr,particulars,user_id,user_common_id,ayear) 值 (4,4,'Show 1',1,1,2014),(9,9,'Show 2',2,2,2013),(13,13,'Show 3',3,3,2012);插入 t2(colq,colr,particulars,user_id,user_common_id,ayear) 值 (99,99,'2014 2014 2014',1,1,2014),(9,9,'Show 2',2,2,2013),(13,13,'显示 3',3,3,2012);
Since I have given to administrator to created the form dynamically.when he created form,What I have done is I have created the table for the form dynamically.Every thing is working fine.Now I want to show specific table and specific column for report generation.
My questions are :
I don't want to show (userlogin,place,venue) table for report generation and three columns (user_id,user_common_id,ayear) these three column are common for all the table which I created dynamically.
Based on every year(2013,2014,2015) I want to generate the report.This i will send in form to mysql while generation report.I want to list what are the forms I have created dynamically.
How to write the query for that.
I have given my code also so far what I have done is here:
<?php
$mysqli = new mysqli("localhost", "root", "", "event");
$result = $mysqli->query("SHOW TABLES");
while ( $row = $result->fetch_row() )
{
$table = $row[0];
echo '<h3>',$table,'</h3>';
$result1 = $mysqli->query("SELECT * FROM $table where ayear='2014'");
if($result1)
{
echo '<table cellpadding="0" cellspacing="0" class="db-table">';
$column = $mysqli->query("SHOW COLUMNS FROM $table");
echo '<tr>';
while($row3 = $column->fetch_row() )
{
echo '<th>'.$row3[0].'</th>';
}
echo '</tr>';
while($row2 = $result1->fetch_row() )
{
echo '<tr>';
foreach($row2 as $key=>$value) {
echo '<td>',$value,'</td>';
}
echo '</tr>';
}
echo '</table><br />';
}
}
$mysqli->close();
?>
Here are the results
(to be filled in)
Here are the expected results
id Date No_of_Days Particulars user_id user_common_id ayear
-- -------- ---------- ---------------- -------- -------------- -----
1 20/07/2013 1 Film Show on... 16 311 1
2 29/08/2013 1 Drama on ... 16 318 1
3 08/03/2014 1 Video Conferen.. 10 621 1
解决方案 First I present this little chunk, could be useful for others later.
select table_name,column_name,ordinal_position from information_schema.columns
where table_schema = 'event'
order by table_name,ordinal_position
Now on to yours.
$mysqli = new mysqli("localhost", "root", "", "event");
$result = $mysqli->query("SHOW TABLES");
$dontDisplay = array('user_id','user_common_id','ayear');
while ( $row = $result->fetch_row() )
{ $table = $row[0];
echo '<h3>',$table,'</h3>';
$iterationOrdIgnore=array();
$result1 = $mysqli->query("SELECT * FROM $table where ayear='2014'");
if($result1)
{ $ff = $result1->fetch_fields();
$iLoop=0;
foreach ($ff as $oneF) {
if (in_array($oneF->name, $dontDisplay)) {
$iterationOrdIgnore[]=$iLoop;
}
$iLoop++;
}
echo '<table cellpadding="0" cellspacing="0" class="db-table">';
$column = $mysqli->query("SHOW COLUMNS FROM $table where field not in ('user_id','user_common_id','ayear')");
echo '<tr>';
while($row3 = $column->fetch_row() )
{ echo '<th>'.$row3[0].'</th>';
}
echo '</tr>';
while($row2 = $result1->fetch_row() )
{ echo '<tr>';
$iLoop=0;
foreach($row2 as $key=>$value) {
if (!in_array($iLoop, $iterationOrdIgnore)) {
echo "<td>".$value."</td>";
}
$iLoop++;
}
echo '</tr>';
}
echo '</table><br />';
}
}
$mysqli->close();
I used the $dontDisplay array for the column names not to display.
Each table you work against can be expected to have columns in different ordinal positions (of course). So the array $iterationOrdIgnore held the ordinal positions for the table you are working on ... to ignore.
fetch_fields() is used against the resultset to get the column names to drive the above $iterationOrdIgnore.
When the column values are echoed, the ones to ignore, are ignored.
Also, keep a close eye on these two lines far apart from one another.
$dontDisplay = array('user_id','user_common_id','ayear');
and
$column = $mysqli->query("SHOW COLUMNS FROM $table where field not in ('user_id','user_common_id','ayear')");
Combine them somehow for less error-prone cases where you modify one, and not the other. But this was a quick attempt to show it works. It can certainly be cleaned up.
See Manual page for fetch_fields and in_array().
Schema (for others to test)
create table t1
( id int auto_increment primary key,
colQ int not null,
colR int not null,
particulars varchar(50) not null,
user_id int not null,
user_common_id int not null,
ayear int not null
);
insert t1(colq,colr,particulars,user_id,user_common_id,ayear) values (1,1,'blah blah 1',1,1,2014),(2,2,'blah blah 2',2,2,2013),(3,3,'blah blah 3',3,3,2012);
create table t2
( -- different table ordering of columns
id int auto_increment primary key,
particulars varchar(50) not null,
ayear int not null,
colQ int not null,
user_id int not null,
colR int not null,
user_common_id int not null
);
insert t2(colq,colr,particulars,user_id,user_common_id,ayear) values (4,4,'Show 1',1,1,2014),(9,9,'Show 2',2,2,2013),(13,13,'Show 3',3,3,2012);
insert t2(colq,colr,particulars,user_id,user_common_id,ayear) values (99,99,'2014 2014 2014',1,1,2014),(9,9,'Show 2',2,2,2013),(13,13,'Show 3',3,3,2012);
这篇关于如何使用mysql查询在我的数据库中仅查找特定表和特定列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
登录
关闭
扫码关注1秒登录
发送“验证码”获取
|
15天全站免登陆