CODEWITHSUNDEEP
phpmysql
Benjamin Plonk
Benjamin Plonk

Reputation: 25

Query not returning in order

If you require any additional information for me to help you assist me please let me know. The project requires to be password protected, though.

I am utterly baffled at the moment because I have a functioning piece of code in this section of an uncompleted project, or so I thought. I am looking for any and all advice that will help solve this problem.

I am not getting a 'sane' result, I am doing the same thing twice and getting different results. Both pieces of code return ALL of the answers to the SAME question successfully, but one of them is displaying them in order 17, 18, 19, 16 within the exact block of code, while the second displays 16, 17, 18, 19 in proper order.

In the second piece of code as well, which is part of a more detailed view of a given question and all answers associated with it (just a more detailed view of the first code), the edit functions will edit the correct code, but the delete check boxes fail and delete the wrong item. This pattern does NOT coincide with the visual ordering of the malfunctioning code, either, and appears quite random. This may be the same or a different problem, and I am looking into this.

As follows with two sections of code, one of the sections is displaying rows of the data of all of the answers to a question:

    $query2 = "SELECT * FROM ST_Answers
    WHERE referencingQuestionID = '$permIDNum'
    ORDER BY 'permanentAnsNumber'";
    $result2 = mysql_query($query2);
    $ret = "";
    $letter = "a";

    $ret .= "<div class='lq answerListItem'>";
    $ret .= "<span class='lqh2 answerNumber'> &nbsp; </span>";
    $ret .= "<span class='lqh2 answerTextBody'>Answer Text Body</span>";
    $ret .= "<span class='lqh2 answerSummaryText'>Answer Summary Text</span>";
    $ret .= "<span class='lqh2 answerNextQuestion'>Next Q#</span>";
    $ret .= "<span class='lqh2 correct'>Correct?</span>";
    $ret .= "</div>";


    $i = 0;
    $n = mysql_numrows($result2);
    while( $i < $n ){
            $permAID = mysql_result($result2,$i,"permanentAnsNumber");
        $aText = mysql_result($result2,$i,"answerTextBody");
        $aSummary = mysql_result($result2,$i,"answerSummaryText");
        $nextQID = mysql_result($result2,$i,"nextQuestionID");
        $correctA = mysql_result($result2,$i,"correctAnswer");

        $ret .= "<div class='lq answerListItem'>";
        if($letter != "a") { $ret .= "<br/>"; }
        $ret .= "<span class='lq answerNumber'> &nbsp; </span>";
            $ret .= "<span class='lq answerTextBody'>" . $letter . ") $aText </span>";
        $ret .= "<span class='lq answerSummaryText'>" . $aSummary . "</span>";
        $ret .= "<span class='lq answerNextQuestion'>" . $nextQID . "</span>";
        $ret .= "<span class='lq correct'>" . $correctA . "</span>";
        $letter++;
        $ret .= "</div>";
        $i++;
    }

This second piece of code, is displaying the same data but a bit more using the SAME query, but instead of displaying items out of order, it displays them properly in the correct order, yet the code is almost exactly the same!

$query2 = "SELECT * FROM ST_Answers
    WHERE referencingQuestionID='$permID' 
    ORDER BY 'permanentAnsNumber'";
$result2 = mysql_query($query2);
$ret .= "<div id='answerContainer'><h3>Associated Answers: </h3>";

$i = 0;
$n = mysql_numrows($result2);
// NOTE: This is displaying everything in correct order
// TODO: Reference this
while( $i < $n ){
        $permAID = mysql_result($result2,$i,"permanentAnsNumber");
    $aText = mysql_result($result2,$i,"answerTextBody");
    $aSummary = mysql_result($result2,$i,"answerSummaryText");
    $nextQID = mysql_result($result2,$i,"nextQuestionID");
    $correctA = mysql_result($result2,$i,"correctAnswer");

    $ret .= "<div class='answerRow'>";
        $ret .= "<span class='perAID'>" . "<h5>Answer ID(Warning do not change):<textarea rows='1' cols='4' name='ids[]'>" . $permAID . "</textarea></span></h5>";
        $ret .= "<span class='aText'>" . "Answer Text: <br /><textarea name='aTxt[]' rows='5' cols='40'>" . $aText . "</textarea></span><br />";
        $ret .= "<span class='aSummary'>" . "Answer Summary: <br /><textarea name='aSum[]' rows='5' cols='40'>" . $aSummary . "</textarea></span><br />";
        $ret .= "<span class='nextQID'>Question this leads to: <textarea name='nxtQID[]' rows='1' cols='4'>" . $nextQID . "</textarea></span><br />";
        $ret .= "<span class='correctA'>" . "Answer point value: <textarea name='ansCorrect[]' rows='1' cols='2'>" . $correctA . "</textarea></span><br />";
        $ret .= "<span class='del'><h6>Delete? <input type='checkbox' name='delete[]'></h6></span><br />";
    $ret .= "</div><br />";

    $i++;
}

Does anyone have any ideas on where to even begin with this? I'm entirely stumped.

Upvotes: 2

Views: 81

Answers (4)

veena s kurup
veena s kurup

Reputation: 151

Use string and variable concatenation methods while trying to use php variables inside your query or string. Quotes are not required after 'ORDERBY'. take care that too.. This changes may help you here.

change the query :

$query2 = "SELECT * FROM ST_Answers WHERE referencingQuestionID = '".$permIDNum."' ORDER BY permanentAnsNumber";

Upvotes: 0

Benjamin Plonk
Benjamin Plonk

Reputation: 25

As Peterm stated, you can't have quotes in this code around the 'ORDER BY' specifier. Once fixed in this piece of code as well as a parent piece of code higher up, everything displays correctly.

Upvotes: 0

peterm
peterm

Reputation: 92805

Your problem is that you use quotes around a column name. Introducing that way a constant literal string to ORDER BY you effectively eliminate the ordering of the resultset.

Change

$query2 = "SELECT * FROM ST_Answers WHERE referencingQuestionID = '$permIDNum' 
           ORDER BY 'permanentAnsNumber'";
                    ^                  ^

to

$query2 = "SELECT * FROM ST_Answers WHERE referencingQuestionID = '$permIDNum' 
           ORDER BY permanentAnsNumber";

Here is SQLFiddle demo.

On a side note: learn and use prepared statements with either PDO or MySQLi. Mysql_* extension is deprecated.

Upvotes: 3

Hayk Aramyan
Hayk Aramyan

Reputation: 323

I advice you change your style, try to use this

$query2 = "SELECT * FROM ST_Answers WHERE referencingQuestionID='$permID' ORDER BY permanentAnsNumber";
$result2 = mysql_query($query2);
$ret .= "<div id='answerContainer'><h3>Associated Answers: </h3>";

while($row = mysql_fetch_assoc($result2)) {

    $ret .= "<div class='answerRow'>";
        $ret .= "<span class='perAID'>" . "<h5>Answer ID(Warning do not change):<textarea rows='1' cols='4' name='ids[]'>" . $row["permanentAnsNumber"] . "</textarea></span></h5>";
        $ret .= "<span class='aText'>" . "Answer Text: <br /><textarea name='aTxt[]' rows='5' cols='40'>" . $row["answerTextBody"] . "</textarea></span><br />";
        $ret .= "<span class='aSummary'>" . "Answer Summary: <br /><textarea name='aSum[]' rows='5' cols='40'>" . $row["answerSummaryText"] . "</textarea></span><br />";
        $ret .= "<span class='nextQID'>Question this leads to: <textarea name='nxtQID[]' rows='1' cols='4'>" . $row["nextQuestionID"] . "</textarea></span><br />";
        $ret .= "<span class='correctA'>" . "Answer point value: <textarea name='ansCorrect[]' rows='1' cols='2'>" . $row["correctAnswer"] . "</textarea></span><br />";
        $ret .= "<span class='del'><h6>Delete? <input type='checkbox' name='delete[]'></h6></span><br />";
    $ret .= "</div><br />";

}

Upvotes: 0

Related Questions