Bollate

HTML

[ANSWERED] php – how to split sql request into two sections (challenge)

By

Nov 20, 2022 , , , ,

Solution 1 :

Basically, we are using 2 SELECT statements and use UNION to combine them into one result set.

SELECT question, type, Topic, Skill, imagename, answerA, answerB, answerC, answerD, correctanswer FROM goodquestions WHERE type = 'mathnocalc' ORDER BY RAND() LIMIT 0,10

The above Query will return 10 random rows of type mathnocalc. That you will be sure about. I am using the above query as nested one to UNION with another one.

SELECT * FROM (SELECT question, type, Topic, Skill, imagename, answerA, answerB, answerC, answerD, correctanswer FROM goodquestions WHERE type = 'mathnocalc' ORDER BY RAND() LIMIT 0,10) as cat1
UNION
SELECT * FROM (SELECT question, type, Topic, Skill, imagename, answerA, answerB, answerC, answerD, correctanswer FROM goodquestions WHERE type = 'mathcalc' ORDER BY RAND() LIMIT 0,10) as cat2

Try the above query and let me know the result.

For displaying the results based on categories, you can simply split the query into two:

Example Logic:

$queryNoCalc = 'SELECT question, type, Topic, Skill, imagename, answerA, answerB, answerC, answerD, correctanswer FROM goodquestions WHERE type = 'mathnocalc' ORDER BY RAND() LIMIT 0,10';
$noCalcResult variable will store the result set of above query.

Similarly,
$queryCalc = 'SELECT question, type, Topic, Skill, imagename, answerA, answerB, answerC, answerD, correctanswer FROM goodquestions WHERE type = 'mathcalc' ORDER BY RAND() LIMIT 0,10';
$calcResult variable will store the result set of above query.

Now, you can use the seperate results in different DIV.

<div class="no-calc">
  <h3>NO CALULATOR ALLOWED</h3>
  while ($noCalcResult):
    Do the stuff;
  endwhile;
</div>

<div class="calc">
  <h3>CALULATOR ALLOWED</h3>
  while ($queryCalc):
    Do the stuff;
  endwhile;
</div>

Hope you understand the concept.

Problem :

I have the code below. Essentially, it takes math questions from a database and lays them out for an user to answer. There are two sections: mathcalc and mathnocalc. Right now, the program outputs both of them randomly.

Sometimes there can be 15 mathcalc and only 5 mathnocalc. This is a problem to me. I need to be able to specify that the program should only output 10 mathcalc and only 10 mathnocalc.

Furthermore, I want to be able to split up the output so that the first page is only mathcalc (it can be more than 1 page. I just need to print out all the mathnocalc first and then print out all the mathcalc after. essentially two groups).

I was wondering how I would incorporate that with my existing code. This is definitely challenging but I was wondering if anyone would be able to help me.

Updated Full code:

<?php 

session_start();

$email = $_SESSION['email'];

if (empty($email)) {
    echo "Not logged in";
}

else {
        require '../functions/convert_functions.php';
        $servername = "localhost";
        $username = "root";
        $password = "";
        $database = "questionbank";
        $answerkey = array();
        $typelist = array();
        $topiclist = array();
        $skilllist = array();

        $counter = 0;

        // Create connection
        $conn = mysqli_connect($servername, $username, $password, $database);
        $queryNoCalc = "SELECT question, type, Topic, Skill, imagename, answerA, answerB, answerC, answerD, correctanswer FROM goodquestions WHERE type = 'mathnocalc' ORDER BY RAND() LIMIT 0,10";
        $queryCalc = "SELECT question, type, Topic, Skill, imagename, answerA, answerB, answerC, answerD, correctanswer FROM goodquestions WHERE type = 'mathcalc' ORDER BY RAND() LIMIT 0,10";
        
        $noCalcResult = $conn->query($queryNoCalc);
        $CalcResult = $conn->query($queryCalc);
        
        if (($noCalcResult->num_rows > 0) && ($noCalcResult->num_rows > 0)){
            // output data of each row
            echo '<form method="post">';
            echo ' <div class="flex-container>';
            echo '<h3>NON CALULATOR SECTION</h3>';
            while ($row = mysqli_fetch_array($noCalcResult)){
                echo "<div class='flex-child'>";
                echo "<p style='font-size:0.50em;color:#0e3c68;font-weight:bold;'>" . $row['question'] . "</p>";
                $correctanswer = $row['correctanswer'];
                $temptype = $row['type'];
                $temptopic = $row['Topic'];
                $tempskill = $row['Skill'];
           

                array_push($answerkey, "$correctanswer");
                array_push($typelist, "$temptype");
                array_push($topiclist, "$temptopic");
                array_push($skilllist, "$tempskill");

                if (empty($row['imagename'])) {

                }

                else {

                    

                    echo '  
                                <tr>  
                                    <td>  
                                            <img src="data:image/jpeg;base64,'.$row['imagename'] .'" height="125" width="125" class="img-thumnail" />  
                                    </td>  
                                </tr>  
                            ';  
                    
                }
                
                if ($row['type'] == 'mathnocalc') {

                    $typemessage = "No Calculator";


                }

                else {

                    $typemessage = "Calculator Allowed";

                }
                echo  '

                
                <div>

                <input type="radio" id="answerA-' . $counter . '" name= "answer-' . $counter .'" value="answerA" required> 
                <label style="font-size:0.40em;" for="answerA-' . $counter . '"> ' . $row['answerA'] . ' </label><br>
                <input type="radio" id="answerB-' . $counter . '" name="answer-' . $counter .'" value="answerB"> 
                <label style="font-size:0.40em;" for="answerB-' . $counter . '"> ' . $row['answerB'] . ' </label><br>
                <input type="radio" id="answerC-' . $counter . '" name="answer-' . $counter .'" value="answerC"> 
                <label style="font-size:0.40em;" for="answerC-' . $counter . '"> ' . $row['answerC'] . ' </label><br>
                <input type="radio" id="answerD-' . $counter . '" name="answer-' . $counter .'" value="answerD">
                <label style="font-size:0.40em;" for="answerD-' . $counter . '"> ' . $row['answerD'] . ' </label><br>
                <input type="hidden" name="id" value="' . $counter . '">
                <p style="font-size:0.45em;"> ' . $typemessage . '</p>

                </div>
            ';
            
            echo'</div>';
            $counter++;   

            } //while end

        echo '</div>';
        
        echo '<div class="flex-container">';

        echo '<h3>CALULATOR SECTION</h3>';
        while($row = mysqli_fetch_array($CalcResult)) {
            
            echo "<div class='flex-child'>";
            echo "<p style='font-size:0.50em;color:#0e3c68;font-weight:bold;'>" . $row['question'] . "</p>";
            $correctanswer = $row['correctanswer'];
            $temptype = $row['type'];
            $temptopic = $row['Topic'];
            $tempskill = $row['Skill'];

            array_push($answerkey, "$correctanswer");
            array_push($typelist, "$temptype");
            array_push($topiclist, "$temptopic");
            array_push($skilllist, "$tempskill");

            if (empty($row['imagename'])) {

            }

            else {
    

            echo '  
                    <tr>  
                        <td>  
                            <img src="data:image/jpeg;base64,'.$row['imagename'] .'" height="125" width="125" class="img-thumnail" />  
                        </td>  
                    </tr>  
            ';  
                        
            }
                    
            if ($row['type'] == 'mathnocalc') {

                $typemessage = "No Calculator";


            }

            else {

                $typemessage = "Calculator Allowed";

            }
                echo  '

                    
                <div>

                    <input type="radio" id="answerA-' . $counter . '" name= "answer-' . $counter .'" value="answerA" required> 
                    <label style="font-size:0.40em;" for="answerA-' . $counter . '"> ' . $row['answerA'] . ' </label><br>
                    <input type="radio" id="answerB-' . $counter . '" name="answer-' . $counter .'" value="answerB"> 
                    <label style="font-size:0.40em;" for="answerB-' . $counter . '"> ' . $row['answerB'] . ' </label><br>
                    <input type="radio" id="answerC-' . $counter . '" name="answer-' . $counter .'" value="answerC"> 
                    <label style="font-size:0.40em;" for="answerC-' . $counter . '"> ' . $row['answerC'] . ' </label><br>
                    <input type="radio" id="answerD-' . $counter . '" name="answer-' . $counter .'" value="answerD">
                    <label style="font-size:0.40em;" for="answerD-' . $counter . '"> ' . $row['answerD'] . ' </label><br>
                    <input type="hidden" name="id" value="' . $counter . '">
                    <p style="font-size:0.45em;"> ' . $typemessage . '</p>

                </div>
                ';
                
                echo'</div>';
                $counter++;   
                
            
            }

                echo '</div>';
                echo '<input type="submit" name="checkanswer" value="Submit">';
                echo'</form>';   

            } 
            
            else {
                echo "0 results";
            
            }
   
        if(isset($_POST["answer"])) {
            
            foreach($answerkey as $output){
                echo $output . "<br>";
            }      
        
        }   

    
}

        

     
        
?>

<style>

.flex-container {
display: flex;
flex-wrap: wrap;
}

.flex-container > div {
font-size: 30px;

width: calc(50% - 2em);
  margin: 1em;
}

.test {
flex: 1 0 41%; /* explanation below */
font-size: 20px; 
}

.img {
 height: 100px;
 flex: 1 0 41%;
 width: 100px;
 }
 </style>

<html>

    <head>
    <title>Ensemble Education</title>

    <a>
        <form method ="post" action="#form-anchor" id="form-anchor">  
        <input type="submit" name="answer" value="Click for Answer Key">
    </a>
    </form>

    </head>
    
</html>


<?php



    if(isset($_POST["checkanswer"])) {
        
        $counter--;

        $sql = "SELECT Version FROM mathanswers WHERE email = '$email'";
        $result = $conn->query($sql);

        if ($result->num_rows > 0) {

            while($row = mysqli_fetch_array($result)) {

                $version = $row['Version'];

            }

            $version++;

        }

        else
        {

            $version = 1;


        }

        while ($counter != 0) {


            $answer = $_POST["answer-$counter"];
            
            $newcorrect = $answerkey[$counter];

           
             

            if (is_correct($answer, $newcorrect)) {

                $type = $typelist[$counter];
                $topic = $topiclist[$counter];
                $skill = $skilllist[$counter];
                $newtype = fix_type($type);

                $sql = "INSERT INTO mathanswers (Email, type, Topic, Skill, Correct, Version) VALUES ('$email', '$newtype', '$topic', '$skill', '1', '$version')"; 
                $result = $conn->query($sql);

                
            }

            else {

                $type = $typelist[$counter];
                $topic = $topiclist[$counter];
                $skill = $skilllist[$counter];
                $newtype = fix_type($type);

                $sql = "INSERT INTO mathanswers (Email, type, Topic, Skill, Correct, Version) VALUES ('$email', '$newtype', '$topic', '$skill', '0', '$version')"; 
                $result = $conn->query($sql);


            }
            
            $counter--;
        }
        
        
        $URL="../studentprofile.php";
        echo "<script type='text/javascript'>document.location.href='{$URL}';</script>";
        echo '<META HTTP-EQUIV="refresh" content="0;URL=' . $URL . '">';
       
        
    }  




?>

EDIT

Here is the updated formatting error that results.
Current output

Here is what I want it to look like.
Desired Output

Comments

Comment posted by FanoFN

What is the problem with the current output? And how your expected output look like?

Comment posted by Rushi M

@tcadidot0 the current output spews out both mathcalc and mathnocalc without any structure. I would like my output to print out ALL the mathcalc questions first and THEN the mathnocalc questions

Comment posted by stackoverflow.com/questions/28857920/…

This should be what you are looking for.

Comment posted by stackoverflow.com/questions/31495446/…

stackoverflow.com/questions/31495446/…

Comment posted by Strawberry

As an aside, seriously consider whether your design is optimal. ‘Normal’ly, we’d have a separate table for answers, with a row for each answer, a column indicating if a given answer is correct, and a column indicating to which of ‘A’, ‘B’, ‘C’, or ‘D’ the answer belongs.

Comment posted by Rushi M

check out my comment

Comment posted by Harish ST

If you are having trouble following nested queries then why not try seperate query for each type and dispaly it inside corresponding Divs? Like Create Two Divs with Headings on it, then store the query results in seperate variables and loop through the result seperately. I know this is not optimized solution, but it would make it simple and easier to understand, so that you can build on top of it. Please let me know if you have any queries.

Comment posted by Rushi M

not quite sure how I would do this. Do you mind editing?

Comment posted by Harish ST

Updated the answer. Please check

Comment posted by Rushi M

check my comment above

By

Related Post

HTML

[ANSWERED] html – Hidden Div Text Reappears?

Nov 20, 2022
HTML

[ANSWERED] c# – InvalidCastException when reading mshtml.HTMLDocument.Script property

Nov 20, 2022
HTML

[ANSWERED] HTML & Javascript – make text appear when dropdown menu option selected

Nov 20, 2022

You missed

HTML

[ANSWERED] javascript – Text box to display updated text and save on button click

Nov 20, 2022
HTML

[ANSWERED] javascript – Pass html tag to angularjs variable from controller

Nov 20, 2022
HTML

[ANSWERED] html – bootstrap mobile menu not scrolling instead the background is scrolling

Nov 20, 2022
HTML

[ANSWERED] html – How to play live stream YouTube videos using CEF4Delphi?

Nov 20, 2022