CODEWITHSUNDEEP
mysql
Naveen Kumar
Naveen Kumar

Reputation: 145

how to select data from two tables in mysql

I have two tables in my SQL Server database. The first is questions and second is question_options.each question 4 options There is a column qid in both tables. my db structure: questuons table:

qid    q_text     discription
1    what is ip    some data
2    what is ipv   same data

and question_options table like this:

   oid qid   options     correct_answer
    1   1    option1       0
    2   1    option2       0
    3   1    option3       1
    4   1    option4       0
    5   2    example1      0
    6   2    example4      1
    7   2    example3      0
    8   2    example2      0

how can fetch questions and question_options table data and how can display fetched data somthing like this:

<div id="qid">qid</div>
<div id ="q_text">q_text</div>
<div id="options1">option1</div>
<div id="options2">option2</div>
<div id="options3>option3</div>
<div id="options4>option4</div>
<div id="correct_answer">correct_answer</div>
<div id="discription">discription</div>

how to select qid,q_text from table1 and 4 options where qid from table2 with one sql statement?

Upvotes: 0

Views: 86

Answers (3)

Vivek
Vivek

Reputation: 803

You can fetch the data using join over QID and use the SUBSTRING to subtract the character string as below: If you want to fetch all the correct answer you can use:

select 
q.qid,
q.q_text,
qo.options,
if(qo.correct_answer = 1,substring(qo.options,length(qo.options)),null) as correct_answer,
q.description
from questions q,question_options qo
where q.qid = qo.qid

If you want to fetch data for correct_answer only then you can use:

select 
q.qid,
q.q_text,
qo.options,
if(qo.correct_answer = 1,substring(qo.options,length(qo.options)),null) as correct_answer,
q.description
from questions q,question_options qo
where q.qid = qo.qid
and if(qo.correct_answer = 1,1,null) is not null

If you want to get the data for single question you can and one more condition at last:

and q.qid = 1

Demo

Upvotes: 1

Sajib Talukder
Sajib Talukder

Reputation: 99

Query should be like below

$query = SELECT * FROM questuons LEFT JOIN question_options ON questuons.qid=question_options.qid where question_options.correct_answer= '1'";

and php code should be like below

$result = $DBconn->query($query );
while($row=$result->fetch_array()) {
  echo $row['q_text'];
}

Try with the above. Hope it will work.

Upvotes: 0

Mike Foxtech
Mike Foxtech

Reputation: 1651

select 
 q.qid, 
 q.q_text,  
 qo.options, 
 IF(qo.correct_answer = 1, substring(qo.options, 7, 1), 0) 
 q.discription
from questions q
join question_options qo on q.qid=qo.qid

Upvotes: 0

Related Questions