Select Multiple Rows from a column.

[abyssal]

Hello. I want to select multiple rows from a table in SQL.

 

(Noob solution probably, I tried my best).

Here's what I tried:

$result=mysql_query("SELECT username FROM answers WHERE qid='$qid'");
$rowuser=mysql_fetch_row($result);

$resultvote=mysql_query("SELECT answervotes FROM answers WHERE qid='$qid'");
$rowvote=mysql_fetch_row($resultvote);

$resulttext=mysql_query("SELECT answertext FROM answers WHERE qid='$qid'");
$rowtext=mysql_fetch_row($resulttext);

$query=mysql_query("SELECT answerscount FROM questions WHERE qid='$qid'");
$row=mysql_fetch_row($query);
$n=$row[0];

for ($i=0; $<=$n; $i++)
{
echo $rowtext[$i];
echo $rowanswer[$i];
echo $rowvote[$i];
}

 

 

The problem is it only returns me one value for each of the rows.

 

Any ideas?

[AbraCadaver]

Much easier than that.  You need to find some mysql/php tutorials:

 

$result = mysql_query("SELECT username,  answervotes, answertext, answerscount FROM answers WHERE qid='$qid'");

while($row = mysql_fetch_assoc($result)) {
   echo $row['username'];
   echo $row['answervotes'];
   echo $row['answertext'];
   echo $row['answerscount'];
}

[Psycho]

A few things.

 

1. You appear to be trying to get multiple columns for the same record (or records), so you just need to include all the column names in the SELECT portion of the query.

 

2: In the query you are specifying a WHERE clause using "WHERE qid='$qid'". If there is only one record matching that value, then it will only return one record. If you want all records matching multiple values you can use "WHERE qid IN ('$qid1', '$qid2', '$qid3', '$qid4')"

 

3: You are only extracting one record from the results. So, even if there are multiple records returned from the query, you are only seeing the first one. You need to use a while() loop to process all the results. If only one record should be returned, then you don't need a while() loop

$query = "SELECT username, answervotes, answertext, answerscount 
          FROM answers
          WHERE qid = '$qid'";
$result = mysql_query($query);

//If only ONE record is expected
$rowuser = mysql_fetch_assoc($result);
echo "Username: {$rowuser['username']}<br>\n";
echo "Answer Votes: {$rowuser['answervotes']}<br>\n";
echo "Answer Text: {$rowuser['answertext']}<br>\n";
echo "Answer Count: {$rowuser['answerscount']}<br>\n";

//IF MULTIPLE records are expected
while($rowuser = mysql_fetch_assoc($result))
{
    echo "Username: {$rowuser['username']}<br>\n";
    echo "Answer Votes: {$rowuser['answervotes']}<br>\n";
    echo "Answer Text: {$rowuser['answertext']}<br>\n";
    echo "Answer Count: {$rowuser['answerscount']}<br>\n";
}

[abyssal]

Thanks guys. The only problem is that answerscount is not from the answers table, but from questions. How can I select that one too and include it into the query ?

[Psycho]

Thanks guys. The only problem is that answerscount is not from the answers table, but from questions. How can I select that one too and include it into the query ?

 

Ah, I didn't catch that. So, am I correct to assume that there is one, and only one, record from each table matching the "qid"? If so, this will work:

 

$query = "SELECT username, answervotes, answertext, answerscount 
          FROM answers
          JOIN questions ON answers.qid = questions.qid
          WHERE qid = '$qid'";
$result = mysql_query($query);

//If only ONE record is expected
$rowuser = mysql_fetch_assoc($result);
echo "Username: {$rowuser['username']}<br>\n";
echo "Answer Votes: {$rowuser['answervotes']}<br>\n";
echo "Answer Text: {$rowuser['answertext']}<br>\n";
echo "Answer Count: {$rowuser['answerscount']}<br>\n";

 

If there is a many to one or many to many relationship, you need to provide more specifics of what you are trying to do.

[abyssal]

Nope. Here's another problem. In the questions table the qid row is different for every entry. But in the answers table, there can be more answers to the same question (or qid), so there can be more rows with the same qid.

[Psycho]

If there is a many to one or many to many relationship, you need to provide more specifics of what you are trying to do.

 

So, you are wanting to get the question AND all the related answers? The last query I provided will do that, but the logic is backwards on how I would normally do it. I would use this query:

$query = "SELECT username, answervotes, answertext, answerscount 
          FROM questions
          JOIN answers USING(qid)
          WHERE questions.qid = '$qid'";

 

Also, that query will return multiple records (one for each answer), but each record will contain the data for the question as well.