Hi all. I have two tables (question and answer) with q_id as the link. I would like to output the data that each table holds alternately like this:

[Question 1]
[Question 1 Choice of Answers]

[Question 2]
[Question 2 Choice of Answers]

...and so on. I've managed to filter the answer sets by q_id but now they do not tally with the question. I attached an image of the output. The $id alternates between the questions and answer sets. Commenting out the $id++; line has the same outcome.

question3

I hope someone can point me in the right direction. Thanks in advance for the help.

-Atikah-

TABLES

CREATE TABLE IF NOT EXISTS `question` (
  `q_id` int(10) NOT NULL AUTO_INCREMENT,
  `q_qstn_no` int(11) NOT NULL,
  `q_text` varchar(300) NOT NULL,
  `q_chpt` int(11) NOT NULL,
  PRIMARY KEY (`q_id`)
)

CREATE TABLE IF NOT EXISTS `answer` (
  `a_id` int(6) NOT NULL AUTO_INCREMENT,
  `q_id` int(10) NOT NULL,
  `a_text` varchar(255) NOT NULL,
  `a_value` tinyint(1) NOT NULL,
  PRIMARY KEY (`a_id`)
)

INSERT INTO `answer` (`a_id`, `q_id`, `a_text`, `a_value`) VALUES
(1, 1, 'Data', 1),
(2, 1, 'System data', 0),
(3, 1, 'Information', 0),
(4, 1, 'Storage', 0),
(7, 2, 'Integrated buses for data and instructions', 0),
(8, 2, 'Faster for a given circuit complexity because instruction fetches and data access contend for a single memory pathway', 0),
(9, 2, 'CPU can both read an instruction and perform data memory access at the same time without a cache', 1),
(10, 2, 'Efficient execution due to the non-overlapping of Operand Fetch and Instruction Fetch', 0),
(11, 3, 'I, II and IV', 0),
(12, 3, 'I and II', 1),
(13, 3, 'III and IV', 0),
(14, 3, 'II and III', 0),
(15, 4, 'True', 1),
(16, 4, 'False', 0);

OUTPUT PAGE

<!-- ANSWER CHOICE IN SUBTABLE -->
<?php
ini_set('display_errors',1);
error_reporting(E_ALL ^ E_NOTICE);
session_start();

if(isset($_SESSION['idno']))
{
?>

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<form id="form1" name="form1" method="post" action="">
  <p>Questions</p>
  <table width="900" border="1" cellpadding="10">
  <?php
  include("dbconn.php");
        //$id = $_SESSION['s_id'];
        $sql  = "SELECT q_id, q_qstn_no, q_text FROM question"; 
        $query = mysql_query($sql) or die("MySQL Error: " . mysql_error());

        $row = mysql_num_rows($query);
        if($row != 0){
        while($data = mysql_fetch_assoc($query)){

        $row1 = mysql_fetch_array($query);
        $id = $row1["q_id"];
        echo $id;

        //$sql_id = "SELECT q_id FROM question";
        //$query_id = mysql_query($sql_id) or die("MySQL Error: " . mysql_error());

  ?>
  <tr>
    <td width="25" align="center" valign="top"><?php echo $data['q_qstn_no']; ?></td>
    <td><?php echo $data['q_text']; ?>

    <br><table width="750" border="1">
    <?php
        $sql2 = "SELECT a_id, a_text, a_value FROM answer WHERE answer.q_id = '$id'";
        $query2 = mysql_query($sql2) or die("MySQL Error: " . mysql_error());
        $row_ans = mysql_num_rows($query2);

        if($row_ans != 0){
        while($data_ans = mysql_fetch_assoc($query2)){
            ?>
  <tr>
    <td><label><input name="answer" type="radio" value="<?php echo $data_ans['a_text']; ?>">
    <?php echo $data_ans['a_text']; ?></label></td>
  </tr>
    <?php
     $id++;
    }
  }
  ?>
</table>

  </td>
  </tr>
    <?php
    }
  }
  ?>
  </table>
</form>
</body>
</html>
<?php
}
else
{
header("Location:login.php");
}
?>

I smell an INNER JOIN coming on...

I have to leave the office right now, or else I would take an in depth look at it. I'll take a look at home later if no one else has come up with anything by then.

Where do you get the list of questions to be displayed? In any case it is probably an array of question ID's which you have to iterate through.

// array of question IDs to be displayed
// I just made them up for this example
$q_id_arr = array(1,2,3,4,5);

// loop through the array of question IDs
foreach($q_id_arr as $q_id) {

    // select the question
    $query_q = "SELECT q_qstn_no, q_text FROM question WHERE q_id='$q_id'";

    // read the question and display it
    ...

    // select the possible answers
    $query_a = "SELECT a_id, a_text, a_value FROM answer WHERE answer.q_id='$q_id'";

    // read and display the possible answers and radio buttons etc
    ...

}

And change the query for inserting answers so you do not insert into autoincremented field (once you have many records you wont be able to assure unique values yourself):

INSERT INTO `answer` (``q_id`, `a_text`, `a_value`) VALUES
(1, 'Data', 1),
(1, 'System data', 0),
...
commented: :) +1

Where do you get the list of questions to be displayed?

Not sure if I understood you correctly, but I used a while loop (line 29) fetching from table 'question'. Thanks for the step by step guide. I'll have a look at it and try to change my code :)

pixelsoul: I would really appreciate it. I'm also looking at broj1's solution in the meantime.

but I used a while loop (line 29) fetching from table 'question'.

Sorry, I overlooked this. I see the problem. Your code is probably OK, only remove the code on line 57:

$id++;

You do not have to increment the question id since it is being read form the question table.

The id is incremented with the use of while loop, right? I've removed the line but the output is still the same.

I REALLY highly doubt this will work from just slapping it in a running it, but I was going more on showing my idea on how I would possibly construct it. I tend to really focus on the SQL queries because I have found that it can really make the code more simplistic when using graceful SQL query. Using an INNER JOIN for the tables would be ideal. It only uses one query to the MySQL database to gather all the data you need and then you are free to run with it after that. Here is an example of what I am talking about.

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
<body>
<form id="form1" name="form1" method="post" action="">
<p>Questions</p>
<table width="900" border="1" cellpadding="10">
    <?php
    include("dbconn.php");

    $sql  = "
    SELECT
    q.q_id, q.q_qstn_no, q.q_text, a.a_id, a.a_text, a.a_value

    FROM
    question q
    INNER JOIN
    answer a ON q.q_id = a.q_id

    WHERE
    q.q_id = '$id'";

    $query = mysql_query($sql) or die("MySQL Error: " . mysql_error());
    $row = mysql_num_rows($query);

    if($row != 0){
        while($data = mysql_fetch_assoc($query)){
            $row1 = mysql_fetch_array($query);
            $id = $row1["q_id"];
            echo $id;
            ?>

            <tr>
                <td width="25" align="center" valign="top"><?php echo $data['q.q_qstn_no']; ?></td>
                <td><?php echo $data['q.q_text']; ?>
                <br>
            <table width="750" border="1">        
            <tr>
                <td><label><input name="answer" type="radio" value="<?php echo $data['a.a_text']; ?>">
                <?php echo $data['a.a_text']; ?></label>
                </td>
            </tr>        
            </table>
           <?php 
        } // end while loop
    } // end if
    ?>
</table>
</form>
</body>
</html>

I went a long time without realizing the power of a query. I had web apps that would have multiple calls to the DB when I could have wrapped them up into just a couple, or possible even one. I did write this rather quickly as I am hoping to get to the gym today :)

broj1: I tried building the page again with your solution. It solved the inconsistent question - answer matching but Question 1 is output twice and couldn't manage to get Question 2 to 4 displayed. Echoing $q_id_arr gives me the word 'Array'. Is that supposed to happen?

I don't know what I did wrong on the answers' table either. The spacing is a bit off. I probably need to get some sleep first, been on this for seven hours.

question11

<!-- ANSWER CHOICE IN SUBTABLE -->
<?php
ini_set('display_errors',1);
error_reporting(E_ALL ^ E_NOTICE);
session_start();

if(isset($_SESSION['idno']))
{
?>

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<form id="form1" name="form1" method="post" action="">
  <p>Questions</p>
  <table width="900" border="1" cellpadding="6">
  <?php
  include("dbconn.php");

    $sql  = "SELECT q_id FROM question"; 
    $query = mysql_query($sql) or die("MySQL Error: " . mysql_error());
    $q_id_arr = mysql_fetch_array($query);

    // loop through the array of question IDs
    foreach($q_id_arr as $q_id) {

    // select the question
    $query_q = "SELECT q_qstn_no, q_text FROM question WHERE q_id='$q_id'";

    // read the question and display it
    $read_q = mysql_query($query_q) or die("MySQL Error: " . mysql_error());
    $row_q = mysql_num_rows($read_q);
        if($row_q != 0){
        while($data_q = mysql_fetch_assoc($read_q)){
    ?>

    <tr><td width="25" align="center" valign="top"><?php echo $data_q['q_qstn_no']; ?></td>
    <td><?php echo $data_q['q_text']; 

    // select the possible answers
    $query_a = "SELECT a_id, a_text, a_value FROM answer WHERE answer.q_id='$q_id'";

    // read and display the possible answers and radio buttons etc
    $read_a = mysql_query($query_a) or die("MySQL Error: " . mysql_error());
    $row_a = mysql_num_rows($read_a);
        if($row_a != 0){
        while($data_a = mysql_fetch_assoc($read_a)){
    ?>

<!-- START OF SUBTABLE -->
    <br><table width="750" border="1">
  <tr>
    <td><label><input name="answer" type="radio" value="<?php echo $data_a['a_text']; ?>"><?php echo $data_a['a_text']; ?></label></td>
    </tr><?php
        }
    }
  ?>
</table>
<!-- END OF SUBTABLE -->
  </tr>
    <?php
        }
    }
}// for each
  ?>
  </table>
</form>
</body>
</html>

<?php
}
else
{
header("Location:login.php");
}
?>

Meh, sorry. Disregard what I had put there. I did not think that through enough because I was in a rush and I just looked it over again... You still would need two separate queries most likely and an inner join would not work :-\

pixelsoul: I don't know how I can overlook your previous post with the code. Wasn't digesting the above post either this morning :| But yes, I'll need separate queries for this. I've tried using INNER JOIN and even UNION. The output wasn't what I wanted.

Things at work are a little crazy today or else I would have taken another look at it. If I get sometime towards the end of my day here I will look it over again and see what else I can come up with. Broj1 is heading in the right direction though.

Yes, broj1's solution is the closest to what I'm imagining it to be. I haven't had the chance to look at the code again either. Hopefully I'll find something after a bit of a time out :)

Sory to be quiet such a long time, I was away.

$q_id_arr gives me the word 'Array'. Is that supposed to happen?

No that is not OK. Put this temporary debug code on line 27:

die(print_r($q_id_arr, 1));

This will display the content of the $q_id_arr array (array of all question IDs) and stop the script. Please post the output.

OK, I think I know the cause of the problem. The mysql_fetch_array returns array of arrays so each $q_id is an array, too. I think it should work if you change the line 32 like this:

$query_q = "SELECT q_qstn_no, q_text FROM question WHERE q_id='{$q_id[0]}'";

Let me know if this works.

The output is still the same as I posted previously; question 1 outputs twice. At the moment I have line 32 and 45 changed like you suggested. I have a feeling that it's the loops that I'm not getting right. Did I implement if and while loops (lines 37, 38 and 50, 51) correctly?

The debug code gives me this:

Array ( [0] => 1 [q_id] => 1 )

Here you go bro. I got really frustrated trying to work with your original code so I just started it from scratch and made it as close to your as possible, but changed some of the stuff as if I was writing it. I left everything pretty much the same so you should be able to just drop it in and it should work hopefully.

<!-- ANSWER CHOICE IN SUBTABLE -->
<?php
ini_set('display_errors',1);
error_reporting(E_ALL ^ E_NOTICE);
session_start();
if(isset($_SESSION['idno']))
{
?>

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Untitled Document</title>
</head>
<body>
    <form id="form1" name="form1" method="post" action="">
    <p>Questions</p>
    <table width="900" border="1" cellpadding="6">

    <?php
        // db connect
        include("dbconn.php");

        // db query for questions
        $sql_q  = "SELECT q_id, q_qstn_no, q_text FROM question";
        $query_q = mysql_query($sql_q) or die("MySQL Error: " . mysql_error());

        // start loop for questions
        while($data_q = mysql_fetch_array($query_q, MYSQL_ASSOC)){

        /*
        echo "<pre>";
        print_r($data_q);
        echo "</pre>";
        */

            echo "<tr><td width='25' align='center' valign='top'>". $data_q['q_qstn_no'] ."</td>";
            echo "<td>". $data_q['q_text'] ."<br />";

            // db query for answers
            $sql_a = "SELECT a_id, a_text, a_value FROM answer WHERE q_id=".$data_q['q_id'];
            $query_a = mysql_query($sql_a) or die("MySQL Error: " . mysql_error());

            // start loop for answers
            while($data_a = mysql_fetch_array($query_a, MYSQL_ASSOC)){

                echo "<br /><table width='750' border='1'>";
                echo "<tr><td><label><input name='answer' type='radio' value=". $data_a['a_text'] .">". $data_a['a_text'] ."</label></td></tr>";
                echo "</table>";
            }
            echo "</tr>";

        }

        mysql_free_result($query_q);
        mysql_free_result($query_a);
        mysql_close($link);
    ?>

    </table>
    </form>
</body>
</html>

<?php
}
else
{
header("Location:login.php");
}
?>

Now time for me to have a drink and then sleep :)

commented: Using this code. Thanks! :) +1

Errrr oops, and obviously change this line to what you use for your mysql_connect statement.

mysql_close($link);

Onre of the things pixelsoul did in his post above is he separated html and php code to get more clarity. I also highly recommend this approach. If you mix html and php too much it is harder to spot the errors in the code.

Now, no matter which approach you take, if things still do not work please post a few rows of questions and answers tables, preferably as an SQL insert file. You can do this from phpmyadmin by selecting a table, chosing a Export menu and exporting with default options.

I actually wanted to ask if it's better to use echo followed by HTML table structure etc. That is separating HTML and PHP code, right? I will try my best to practise it from now on, it does look much simpler.

With that said, pixelsoul's solution did the job. Both of you guys' suggestions brought me to some reading and hence knowledge :) Thank you very much for your guys' time. Really appreciate it.

I actually wanted to ask if it's better to use echo followed by HTML table structure etc. That is separating HTML and PHP code, right?

Yes. Separating enables you to keep blocks of php code together, e.g the loops. If you have to produce html code within blocks then you echo them (such as table rows). I am glad that your code works now.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.