How do I display data retrieved from mysql db in a specific order using php

Question

I am trying to display questions, along with the users answer in a specific order, NOT ASC or DESC, but as defined by the "question_order" column.

I have the following tables in a mysql db:

questions (qid, question_text)
answers   (aid, uid, answer)
usermeta  (userid, question_order)

"questions" table contains the questions
"answers" table contains every users answers to all questions
"usermeta" table contains the sort order for the questions in "question_order".

"question_order" is unique per user and is in the db as a pipe delimited list. (i.e.: 85|41|58|67|21|8|91|62,etc.)

PHP Version 5.3.27

If this entire procedure can be better accomplished using a completely different method, then please let me know.

My PHP ability is limited. With that said, below is what I have at the moment after several hours of playing ...

    $sql = "
        SELECT
                *
        FROM
                ".USERMETA_TABLE."
        WHERE
                userid = {$userid}
    ";

    $result = $db->query($sql) OR sql_error($db->error.'<br />'.$sql);
    $row = $result->fetch_assoc();

    $order_array = explode('|', $row['question_order']);

    $sql = "
        SELECT
                *
        FROM
                ".QUESTIONS_TABLE."
    ";

    $result = $db->query($sql) OR sql_error($db->error.'<br />'.$sql);
    $row = $result->fetch_assoc();


    // my attempt at sorting the questions. the $order_array 
    // does not have a unique id so I am kind of lost as to 
    // how to make this work

    usort($myArray, function($order_array, $row) {
        return $order_array - $row['qid'];
    });


    $sql = "
        SELECT
                *
        FROM
                ".QUESTIONS_TABLE."
    ";

    $result = $db->query($sql) OR sql_error($db->error.'<br />'.$sql);

    while ( $row = $result->fetch_assoc() )
    {
        $sql = "
            SELECT
                    *
            FROM
                    ".ANSWERS_TABLE."
            WHERE
                    uid = {$userid}
                AND
                    qid = ".$row['qid']."
            LIMIT
                    1
        ";

        $result2 = $db->query($sql) OR sql_error($db->error.'<br />'.$sql);
        $row2 = $result2->fetch_assoc();

        echo '      <p>'.$row['question_text'].'</p>'."\n";
        echo '      <p>'.$row2['answer'].'</p>'."\n";
    }

The list of questions have to be sorted by the question_order which is a pipe delimited list of question mumbers, like 45|87|65|85|14|7|62|...etc. Not DESC or ASC. — Mark
– Mark, Commented Jun 22, 2014 at 9:44
The problem is that you cannot use a simple ORDER BY SQL clause because you don't have a normalised datatabase, isn't it? Is it too late to fix the DB design? Otherwise, your database is not going to be more helpful than storing stuff in plain files. — Álvaro González
– Álvaro González, Commented Jun 22, 2014 at 10:04
@ÁlvaroG.Vicario I don't think so, but I am no expert :) I just need to list the questions, along with the answers, in the order specified in the "question_order" column. The numbers in the "question_order" column correlate to the question numbers. — Mark
– Mark, Commented Jun 22, 2014 at 10:34

segfault · Accepted Answer · 2014-06-22 09:22:35Z

1

Filter out the data when retrieving from db.

Use:- SELECT * FROM [TABLE_NAME] ORDER BY qid DESC

Then in PHP you can use session variables and modify the values accordingly.

answered Jun 22, 2014 at 9:22

segfault

5042 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shamal Sandeep · Accepted Answer · 2014-06-22 09:18:56Z

0

If you want to order using ID you can use

SELECT * FROM [TABLE_NAME] ORDER BY qid DESC

this will order in descending order If you want in ascending order use ASC

answered Jun 22, 2014 at 9:18

Shamal Sandeep

2631 gold badge3 silver badges9 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:10:59Z

0

I went ahead and altered the DB by adding a table to store the question numbers, user sort order, and student user id:

student_id, sort_order, question_id
1               1               8
1               2               2
1               3               97

and then was able to select it all with the following statement:

SELECT q.* 
FROM 
questions q
JOIN questions_sorting_order qso
ON q.id = qso.question_id
ORDER BY qso.sort_order
WHERE qso.student_id = $student_id

...works great.

Thanks to FuzzyTree for his help on this at: How do I sort data from a mysql db according to a unique and predetermined order, NOT asc or desc

edited May 23, 2017 at 12:10

CommunityBot

11 silver badge

answered Jun 24, 2014 at 5:50

Mark

6472 gold badges9 silver badges27 bronze badges

Collectives™ on Stack Overflow

How do I display data retrieved from mysql db in a specific order using php

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related