0

I am trying to get 5 questions per page with answers (one to many relational for questions and answers table) but, i am getting the number of records per page for this join table, is there anyway to limit the results based on questions table for pagination.

<?php
$topic_id = $_GET['topic_id'];
$answers_data = [];
$questions_data = [];
if (isset($_GET["page"])) { $page  = $_GET["page"]; } else { $page=1; }; 
$num_rec_from_page = 5;
$start_from = ($page-1) * $num_rec_per_page;
$sql = "SELECT questions.q_id,questions.question,answers.answers,answers.answer_id FROM questions INNER JOIN answers ON questions.q_id = answers.q_id WHERE topic_id='$topic_id' LIMIT $start_from, $num_rec_from_page";
$result = $connection->query($sql);
while($row = mysqli_fetch_assoc($result)) {
$data[] = $row;
}//While loop

foreach($data as $key => $item) {
    $answers_data[$item['q_id']][$item['answer_id']] = $item['answers'];
}
foreach($data as $key => $item) {
    $questions_data[$item['q_id']] = $item['question'];
}
?>

I am get results for above query data using 2 for-each loops as below.

<?php
$question_count= 0;
foreach ($answers_data as $question_id => $answers_array) {
$question_count++;
$q_above_class = "<div class='uk-card-default uk-margin-bottom'><div class='uk-padding-small'><span class='uk-article-meta'>Question :".$question_count."</span><p>";
$q_below_class = "</p></span><div class='uk-padding-small'>";

echo $q_above_class.$questions_data[$question_id].$q_below_class;

$answer_count = 0;
foreach($answers_array as $key => $answer_options) {
$answer_count++;
$answer_options = strip_tags($answer_options, '<img>');
$ans_above_class="<a class='ansck'><p class='bdr_lite uk-padding-small'><span class='circle'>".$answer_count."</span>";
$ans_below_class = "</p></a>";
  echo $ans_above_class.$answer_options.$ans_below_class; 
}
echo "</div></div></div>";   
}
?>

Is there any idea, how can i limit the results per page, based on questions table.

  • where is `$topic_id` assigned? What isn't working the way you'd like it to as compared to what you're getting now? You're not checking for errors on the query, so if there's anything wrong in there, you won't know what they were, if any. – Funk Forty Niner Apr 01 '18 at 23:52
  • I am getting topic_id from url, the code is working fine but i can't able to limit the results. example: if i have 5 questions and each having 4 answer options, i am getting only 5 records instead of 5 questions with 4 answers (20 records). – Prudhvi Mallavarapu Apr 01 '18 at 23:58
  • You'll probably have to use something like a correlated sub-query. Where you select the questions with the limit and then joint it on the answers in the main query. the field `topic` is in question right? Also what is `questions.q_id` and `questions.question_id` mistake? – ArtisticPhoenix Apr 02 '18 at 00:03
  • Thanks for catching, originally it was question_id and i entered here as q_id for readability, Not an error. – Prudhvi Mallavarapu Apr 02 '18 at 00:10
  • `as q_id for readability` really... {shaking my head} Please put exactly what you have, all changing things does is create confusion. – ArtisticPhoenix Apr 02 '18 at 01:33

1 Answers1

0

something like this

SELECT
    q.q_id,
    q.question,
    a.answers,
    a.answer_id
FROM
    (
        SELECT
            q_id, question
        FROM
            questions
        WHERE
            topic_id=:topic_id           
        LIMIT
            $start_from, $num_rec_from_page
    ) AS q
JOIN
    answers AS a ON q.q_id = a.question_id

A few questions/thoughts/notes.

  • you had question.q_id and question.question_id which seems like an error. So I just went with q_id the other one is more typing (which I don't like) I had a 50-50 chance I figured... so

  • you had just topic_id so I can't be sure what table it's from, I'm assuming it's from table "question"? It makes a big difference as we really need the where condition on the sub-query where the limit is.

  • Inner Join, is the same thing as a Join, so I just put Join because I'm lazy. I found this previous post (click here) on SO that talks about it

  • :topic_id I parameterized your query, I don't do variable concatenation and SQLInjection vulnerability stuff. (aka. please use prepared statements) Named placeholders are for PDO, that's what I like using, you can pretty much just replace it with a ? for mysqli

  • as I said with INNER JOIN, I'm lazy so I like aliasing my tables with just 1 character, so that was what I did. ( I think you don't even need the AS part, but I'm not "that" lazy). Sometimes I have to use 2, which really irritates me, but whatever

With a sub-query, you can just limit the rows from that table, then you join the results of that query back to the main query like normal. This way you pull 5 or what have you from question table, and then {n} rows from answer based only on the join to the results of the inner query.

Cant really test it, but in theory it should work. You'll have to go though the results, and group them by question. Because you will get {n} rows that have the same 5 questions joined in them. With PDO, you could do PDO::FETCH_GROUP I don't think Mysqli has an equivalent method so you'll have to do it manually, but it's pretty trivial.

UPDATE

Here is a DB fiddle I put to gather you can see it does exactly what you need it to

https://www.db-fiddle.com/f/393uFotgJVPYxVgdF2Gy2V/3

Also I put a non-subquery below it to show the difference.

As for things like small syntax errors and table/column names, well I don't have access to your DB, you're going to have to put some effort in to adapt it to your setup. The only information I have is what you put in the question and so your question had the wrong table/column name. I already pointed several of these issues out before. I'm not saying that to be mean or condescending, it's just a blunt fact.

UPDATE1

Based on you comment. in 1st query the question is redundant

This is just the way the database works, To explain it is very simple, in my example I have 5 questions that match with 8 answers. In any database (not including NoSQL like mongoDB) you can't realistically nest data. In other words you cant pull it like this.

  question1
      answer1
      answer2
      answer3

You have to pull it flat, and the way that happens is like this

  question1 answer1
  question1 answer2
  question1 answer3

This is just a natural consequence of how the Database works when joining data. Now that that is out of the way, what do we do about it. Because we want the data nested like in the first example.

  1. We can pull the question, iterate (loop) over the result and do a query for each question and add the data to a sub-element.
    • Advantage It's easy to do
    • Disadvantage While this works it's undesirable because we will be making 6 connections to the database (1 to get 5 questions, 1 to get answers for each of the 5 questions), it requires 2 while loops to process the results, and actually more code.

Psudo code instructions (i don't feel like coding this)

       init data variable
       query for our 5 questions
       while each questions as question
           - add question to data
           - query for answers that belong to question
           - while each answers as answer
               -- add answer to nested array in data[question]
       return data
  1. We can process the results and build the structure we want.
    • Advantage We can pull the data in one request
    • Disadvantage we have to write some code, in #1. we still have to write code, and in fact we have to write more code, because we have to process the DB results 6x (2 while loop) here we need 1 while loop.

Psudo code instructions (for comparison)

       init data variable
       query for our 5 questions and their answers
       while each questions&answers as row
           - check if question is in data
             -- if no, add question with a key we can match to it
           - remove data specific to question (redundant data)
           - add answers to data[question]
       return data

As you can see the basic instructions for the second one are no more complex then the first (same number of instruction). This is just assuming each line has the same complexity. Obviously a SQL query or a while loop is more complex then an if condition. You'll see below how I convert this psudo code to real code. I actually often write psudo code when planing a project.

Anyway, this is what we need to do. (using the previous SQL or the first one in the fiddle). Here is your normal "standard" loop to pull data from the DB

 $data = [];
 while($row = mysqli_fetch_assoc($result)) {
     $data[] = $row;
 }//While loop

We will modify this just a bit (it's very easy)

//query for our 5 questions and their answers(using SQL explained above)
//init data variable
$data = [];
//while each questions&answers as row
while($row = mysqli_fetch_assoc($result)) {
   // - create a key based of the question id, for convenience
   $key = 'question_'.$row['q_id'];
   // - check if question is in data
   if(!isset( $data[$key] ) ){
       //--if no, add question with a key we can match to it
       $data[$key] = [
            'q_id' => $row['q_id'],
            'question' => $row['question'],
            'children' => []  //you can call this whatever you want, i choose "children" because there is a field named "answers"
       ];
   }
   //- remove data specific to question (redundant data) [optional]
   unset($data['q_id'], $data['question']);
   //- add answers to data[question]
   $data[$key]['answers'][] = $row;
}
//return data

So what does this look like: For the first while, the standard one, we get this with as you called it redundant data.

[
    ["q_id" => "4", "question" => "four", "answers"=>"4", "answer_id"=>"4"],
    ["q_id" => "5", "question" => "five", "answers"=>"5", "answer_id"=>"5"],
    ["q_id" => "5", "question" => "five", "answers"=>"5", "answer_id"=>"6"],
]

For the second one, with our harder code (that's not really hard) we get this:

[
    ["q_id" => "4","question" => "four","children" = ["answers"=>"4","answer_id"=>"4"]],
    [
        "q_id" => "5",
        "question" => "five",
        "children" = [
            "answers"=>"5",
            "answer_id"=>"5"
        ],[
            "answers"=>"5",
            "answer_id"=>"6"
        ]
    ],
]

I expanded the second question so you can see the nesting. This is also a good example of why it had redundant data, and what is happening in general. As you can see there is no way to represent 8 rows with 5 shared question without have some redundant data (without nesting them).

The last thing I would like to mention is my choice of $key. We could have used just q_id with no question_ bit added on. I do this for 2 reasons.

  1. It's easier to read when printing it out.
  2. There are several array_* (and other) functions in PHP that will reset numeric keys. Because we are storing important data here, we don't want to lose that information. The way to do this is to use strings. You can just cast it to a string (int)$row['q_id'], but in some cases the keys can still get removed. An example is when JSON encoding, there is a flag JSON_FORCE_OBJECT that forces numeric keys to be an object {"0":"value} but it acts global. In any case it can happen where you lose the keys if they are just numbers, and I don't much care for that happening. So I prefix them to prevent that.
  3. It's not hard to do something like preg_match('/question_([0-9]+)/', $key, $match) or $id = substr($key, 9); to pull it back off of there,We have the q_id in the array,It's no harder to check isset($data['question_1']) then isset($data['1']), and it looks better.

So for minimum difficulty we can be sure we won't lose our ID's to some code over site (unless we use usort instead of uasort) but I digress..

ArtisticPhoenix
  • 21,464
  • 2
  • 24
  • 38
  • I tried with Inner SELECT statement as you mentioned but the Query is not executing and it showing the error as unknown column in the field list. I will try different approach, Thanks. – Prudhvi Mallavarapu Apr 02 '18 at 00:56
  • What unknown column, that's an easy error to fix, why give up so quick. Especially when this is pretty much the only way to do it would having to do 6 sql queries. The problem with questions about DB is I don't have the DB, so I have to just imagine what it is. If you could setup the 2 tables and some test data in a fiddle like this one https://www.db-fiddle.com/ it would be so much easier. – ArtisticPhoenix Apr 02 '18 at 01:07
  • @PrudhviMallavarapu - I added a DB fiddle that shows it does exactly what I said it dose, Everything I copied from your question, so you'll have to figure out what column you got wrong. I cant see your DB so there is no way for me to know, I'm not Psychic. – ArtisticPhoenix Apr 02 '18 at 01:21
  • @PrudhviMallavarapu - besides I imagine that the JOIN has your `JOIN answers AS a ON q.q_id = a.question_id` field in `originally it was question_id and i entered here as q_id for readability` it. So which is it, `q_id or question_id` and for which tables, one / both. etc... Sorry it's just frustrating to waste time... – ArtisticPhoenix Apr 02 '18 at 01:35
  • I seen your example (https://www.db-fiddle.com/f/393uFotgJVPYxVgdF2Gy2V/3) but in 1st query the question is redundant and in the 2nd query not getting all the answer options (answer options are not unique). If this was not filtered out in single query, can you suggest if i can limit the questions count before or after the query. The table structure was correct as you entered. – Prudhvi Mallavarapu Apr 03 '18 at 19:53
  • I am expecting my code to get results like an examination paper, each page need 5 questions with all answer options. – Prudhvi Mallavarapu Apr 03 '18 at 20:10
  • `in 1st query the question is redundant`, that is the way the DB works, the only other way would be pull the question loop though and pull the data, which would result in 6queries instead of one. Which is poor for performance. I can show you how to group the results. – ArtisticPhoenix Apr 03 '18 at 20:58
  • Also you can't expect to pull an `examination paper` right out of the DB with no work. I can help you fix the query and format the results in a way that makes logical sense, a way you can use them later. And I will show you the right way (IMO) to do things based on my over 8 years of experience. But, the rest is beyond the scope of this question. – ArtisticPhoenix Apr 03 '18 at 21:59
  • I stored them in 2 objects earlier, now i will try this. Thanks for the code and explanation @ArtistcPhoenix. – Prudhvi Mallavarapu Apr 05 '18 at 03:35