Selecting multiple rows from one table

Question

This is my table structure

id name value
1 questions 50
2 duration 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:

$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.

If this is a new project, look into using the mysqli extension rather than mysql. — Pier-Luc Gendreau
– Pier-Luc Gendreau, Commented May 1, 2013 at 16:01
Thanks @Pier-LucGendreau. I have noticed that they are deprecating mysql_ functions. I will convert to the mysqli extension. — mutiemule
– mutiemule, Commented May 3, 2013 at 12:33

John Woo · Accepted Answer · 2013-05-01 15:58:53Z

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

answered May 1, 2013 at 15:58

John Woo

265k70 gold badges508 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

tylert · Accepted Answer · 2013-05-01 16:05:34Z

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.

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'];

This also works well but the best way on my opinion, it is the way JW has done it.

Memolition · Accepted Answer · 2013-05-01 16:19:00Z

0

Use mysqli or pdo functions the mysql_* functions are officially deprecated:

$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'];
}

edited May 1, 2013 at 16:19

answered May 1, 2013 at 15:59

Memolition

4941 gold badge5 silver badges12 bronze badges

4 Comments

Ed Gibbs Over a year ago

The SELECT * query won't help here - the question was how to get both values as two columns in a single row. Your query will deliver the results in two rows.

Memolition Over a year ago

waa you can make a foreach statement

Memolition Over a year ago

sorry, now it works as you want. and as I said try not to use mysql_* functions

mutiemule Over a year ago

@Memolition this works now. Thanks and thanks for the heads up on mysqli.

Collectives™ on Stack Overflow

Selecting multiple rows from one table

3 Answers 3

Comments

1 Comment

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

4 Comments

Related