CODEWITHSUNDEEP
phpmysqlarraysusort
Mark
Mark

Reputation: 647

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

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";
    }

Upvotes: 1

Views: 172

Answers (3)

Mark
Mark

Reputation: 647

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

Upvotes: 0

segfault
segfault

Reputation: 504

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.

Upvotes: 1

Shamal Sandeep
Shamal Sandeep

Reputation: 263

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

Upvotes: 0

Related Questions