This is my table structure
这是我的表格结构。
id name value
1 questions 50
2 duration 120
id名值1问题50 2持续120
Now, i want to display the value according to the name. I am unable to do the sql statement correct. This is what i have done:
现在,我要根据名称显示值。我无法正确地执行sql语句。这就是我所做的:
$qry = "SELECT * FROM (
SELECT (SELECT value FROM pq_examsettings) as duration,
(SELECT value FROM pq_examsettings) as questions
) ";
$result= mysql_query($qry);
$row = mysql_fetch_array($result);
$duration = $row['duration'];
$questions = $row['questions'];
Somebody help me on how i can do my query such that my $duration and $questions variables display 120 and 50.
有人可以帮助我如何进行查询,使$duration和$questions变量显示120和50。
3 个解决方案
#1
4
give this a try,
给这一个尝试,
SELECT MAX(CASE WHEN name = 'questions' THEN value END) questions,
MAX(CASE WHEN name = 'duration' THEN value END) duration
FROM pq_examsettings
#2
0
I'm not familiar enough with MySQL to know a simple way to transpose rows to columns and return the results in one select-- looks like JW gave an answer that does that.
我对MySQL还不够熟悉,不知道如何将行转换为列并在一个选择中返回结果——看起来JW给出了这样的答案。
Another option would be to just make two database calls:
另一种选择是只进行两次数据库调用:
$qry = "SELECT value FROM PQ_EXAMSETTINGS WHERE NAME = 'duration'";
$result= mysql_query($qry);
$row = mysql_fetch_array($result);
$duration = $row['value'];
$qry = "SELECT value FROM PQ_EXAMSETTINGS WHERE NAME = 'questions'";
$result= mysql_query($qry);
$row = mysql_fetch_array($result);
$questions = $row['value'];
#3
0
Use mysqli or pdo functions the mysql_* functions are officially deprecated:
使用mysqli或pdo函数,mysql_*函数被正式弃用:
$mysqli = new mysqli($dbserver, $dbmanager, $dbpass, $dbname);
if($mysqli->connect_errno) {
echo "Error connecting to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
$query = $mysqli->query("SELECT * FROM pq_examsettings");
if($query->num_rows){
$new_row = array();
while($row = $query->fetch_assoc()){
foreach($row as $key => $value){
if($key == 'name'){
$array_key = $value;
}
if($key == 'value'){
$new_row[$array_key] = $value;
}
}
}
$duration = $new_row['duration'];
$questions = $new_row['questions'];
}
#1
4
give this a try,
给这一个尝试,
SELECT MAX(CASE WHEN name = 'questions' THEN value END) questions,
MAX(CASE WHEN name = 'duration' THEN value END) duration
FROM pq_examsettings
#2
0
I'm not familiar enough with MySQL to know a simple way to transpose rows to columns and return the results in one select-- looks like JW gave an answer that does that.
我对MySQL还不够熟悉,不知道如何将行转换为列并在一个选择中返回结果——看起来JW给出了这样的答案。
Another option would be to just make two database calls:
另一种选择是只进行两次数据库调用:
$qry = "SELECT value FROM PQ_EXAMSETTINGS WHERE NAME = 'duration'";
$result= mysql_query($qry);
$row = mysql_fetch_array($result);
$duration = $row['value'];
$qry = "SELECT value FROM PQ_EXAMSETTINGS WHERE NAME = 'questions'";
$result= mysql_query($qry);
$row = mysql_fetch_array($result);
$questions = $row['value'];
#3
0
Use mysqli or pdo functions the mysql_* functions are officially deprecated:
使用mysqli或pdo函数,mysql_*函数被正式弃用:
$mysqli = new mysqli($dbserver, $dbmanager, $dbpass, $dbname);
if($mysqli->connect_errno) {
echo "Error connecting to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
$query = $mysqli->query("SELECT * FROM pq_examsettings");
if($query->num_rows){
$new_row = array();
while($row = $query->fetch_assoc()){
foreach($row as $key => $value){
if($key == 'name'){
$array_key = $value;
}
if($key == 'value'){
$new_row[$array_key] = $value;
}
}
}
$duration = $new_row['duration'];
$questions = $new_row['questions'];
}