View the report in ascending order

[rhodarose]

Good day!

I have a mysql_query code to sleect the date from the database and my problem is the output is not in ascending order. Here is my code:

 $query = "SELECT plt_no FROM plt_transact WHERE plt_date BETWEEN '" . $_POST["from_date"] . "' AND '" . $_POST["to_date"] . "' "; 

I try this code

 $query = "SELECT plt_no FROM plt_transact WHERE plt_date BETWEEN '" . $_POST["from_date"] . "' AND '" . $_POST["to_date"] . "' ORDER BY plt_no ASC "; 

But it did not work...where i can put the code for Asc.

Thank you

[Schmoopy]

Assuming that the date field you want to use is plt_date, just replace it with the following code

 $query = "SELECT plt_no FROM plt_transact WHERE plt_date BETWEEN '" . $_POST["from_date"] . "' AND '" . $_POST["to_date"] . "' ORDER BY plt_date ASC "; 

[rhodarose]

Assuming that the date field you want to use is plt_date, just replace it with the following code

 $query = "SELECT plt_no FROM plt_transact WHERE plt_date BETWEEN '" . $_POST["from_date"] . "' AND '" . $_POST["to_date"] . "' ORDER BY plt_date ASC "; 

I tried it but still the plt_no was not in ascending order. Thank you

[james438]

what does the processed query look like? try inserting

echo "$query";

just after

$query = "SELECT plt_no FROM plt_transact WHERE plt_date BETWEEN '" . $_POST["from_date"] . "' AND '" . $_POST["to_date"] . "' ORDER BY plt_no ASC ";

and let us know what comes up.

Update: I tried out your code and various other formats, but I can't seem to recreate the problem on this end. The results come out just as they should and in the right order. Just a thought, but could it be you are inserting 'datetime' into a field formatted for 'date'?

[rhodarose]

what does the processed query look like? try inserting

echo "$query";

just after

$query = "SELECT plt_no FROM plt_transact WHERE plt_date BETWEEN '" . $_POST["from_date"] . "' AND '" . $_POST["to_date"] . "' ORDER BY plt_no ASC ";

and let us know what comes up.

Update: I tried out your code and various other formats, but I can't seem to recreate the problem on this end. The results come out just as they should and in the right order. Just a thought, but could it be you are inserting 'datetime' into a field formatted for 'date'?

This is the output:
SELECT d.operation_name, SUM(d.input_qty) AS inputqty, d.input_unit, SUM(d.output_qty) AS outputqty, d.output_unit, d.shift FROM clt_traceability d, clt_transact t WHERE ((t.clt_date = '2010-11-24' AND d.shift = '10-6') || (t.clt_date = '2010-11-25' AND d.shift = '6-2') || (t.clt_date = '2010-11-25' AND d.shift = '2-10')) AND d.clt_transact_id = t.clt_transact_id GROUP BY d.operation_name, d.output_unit, d.input_unit, d.shift ORDER BY d.operation_name, FIELD(d.shift,'10-6','6-2','2-10')
then the output

[james438]

no, that is the wrong output. I can't see any way that it is possible for you to get that output. The query you just echoed is too different from $query.

First, plt_no should = "plt_no", not

d.operation_name, SUM(d.input_qty) AS inputqty, d.input_unit, SUM(d.output_qty) AS outputqty, d.output_unit, d.shift

and plt_transact should = "plt_transact" not

clt_traceability d, clt_transact t

BETWEEN is not even listed in the query you echoed. The query you posted is also more complicated. If you want us to look at the more complicated query or even the simpler query we need you to post the data types as well. Possibly the output too, but at present I don't see a need to see a portion of the output. If you want you can post it as well. It couldn't hurt and could help.

[rhodarose]

no, that is the wrong output. I can't see any way that it is possible for you to get that output. The query you just echoed is too different from $query.

First, plt_no should = "plt_no", not

d.operation_name, SUM(d.input_qty) AS inputqty, d.input_unit, SUM(d.output_qty) AS outputqty, d.output_unit, d.shift

and plt_transact should = "plt_transact" not

clt_traceability d, clt_transact t

BETWEEN is not even listed in the query you echoed. The query you posted is also more complicated. If you want us to look at the more complicated query or even the simpler query we need you to post the data types as well. Possibly the output too, but at present I don't see a need to see a portion of the output. If you want you can post it as well. It couldn't hurt and could help.

Here is the full code:

if($_POST["start_date"])
        {
        $query = "SELECT plt_no FROM plt_transact WHERE plt_date = '" . $_POST["start_date"] . "'";
        $result_loop = mysql_query($query);
        
        $date = $_POST["start_date"];
                
        $Batch_yield = 0;
        $buff_input = 0;
        $core_output = 0;
        $loopctr = 0;
        $totalloop = 0;
        
        /***Header****/
        if($result_loop) if(mysql_num_rows($result_loop) > 0)
            {
            $totalloop = mysql_num_rows($result_loop);
            
            $plt_no = mysql_result($result_loop,0,"plt_no");
            $query = "SELECT d.operation_name, SUM(d.input_qty) AS inputqty, d.input_unit, SUM(d.output_qty) AS outputqty, d.output_unit FROM traceability d, plt_transact t WHERE t.plt_no = '$plt_no' AND d.plt_transact_id = t.plt_transact_id GROUP BY d.operation_name, d.output_unit, d.input_unit ORDER BY d.operation_name";
            $result = mysql_query($query);
            if($result)
                {
                echo "<table cellspacing='2' style='font-family: arial narrow; font-size: 12px; border-width: 2px 2px 2px 2px; border-style: solid;'>";
                
                echo "<tr>";
                echo "<tr><b> Date: &nbsp;  " . $date . "</b></tr>";
                echo "<th class='tdclass'>PLT #</th>";
                $total_row = mysql_num_rows($result);
                for($ctr=0; $ctr < $total_row; $ctr++)
                    {
                    $opname = mysql_result($result,$ctr,"operation_name");
                    $i = strpos($opname," ",0);
                    $opname = substr($opname,$i);
                    echo "<th colspan='2' class='tdclass'>" . $opname . "<br />(" . mysql_result($result,$ctr,"output_unit") . ")</th>";
                    }
                echo "<th class='tdclass'>PLT Yield</th>";
                echo "</tr>";
                
                echo "<tr>";
                echo "<td class='tdclass'></td>";
                
                for($ctr=0; $ctr < $total_row; $ctr++)
                    {
                    
                    echo "<td class='tdclass'>Input</td>";
                    echo "<td class='tdclass'>Output</td>";
                    
                    }
                
                echo "<td class='tdclass'>";
                echo "</td>";
                    
                echo "</tr>";
                        
                }
            }
        
        $query = "SELECT plt_no FROM plt_transact WHERE plt_date = '" . $_POST["start_date"] . "'";
        $result_loop = mysql_query($query);               
        while($row = mysql_fetch_array($result_loop))
            {
            $loopctr += 1;
            $plt_no = $row["plt_no"];
            $query = "SELECT * FROM plt_transact WHERE plt_no = '$plt_no'";
            $result_no = mysql_query($query);
            if($result_no)
                {
                if(mysql_num_rows($result_no) > 0)
                    {
                    $f_output = 0;
                    $c_output = 0;
                    
                    $query  = "SELECT SUM(t.output_qty) AS f_output FROM traceability t, plt_transact p WHERE t.plt_transact_id = p.plt_transact_id AND t.operation_name='05 F' AND p.plt_no = '$plt_no'";
                    $resultyield = mysql_query($query);
                    if($resultyield)
                        {
                        if(mysql_num_rows($resultyield) > 0) $f_output = mysql_result($resultyield,0,"f_output");
                        }
                    
                    $query  = "SELECT SUM(t.output_qty) AS c_output FROM traceability t, plt_transact p WHERE t.plt_transact_id = p.plt_transact_id AND t.operation_name='08 C' AND p.plt_no = '$plt_no'";
                    $resultyield = mysql_query($query);
                    if($resultyield)
                        {
                        if(mysql_num_rows($resultyield) > 0) $c_output = mysql_result($resultyield,0,"c_output");
                        }
                        
                    $PLT_yield = @($c_output / $f_output) * 100;
                    
                    $query  = "SELECT SUM(t.input_qty) AS buff_input FROM traceability t, plt_transact p WHERE t.plt_transact_id = p.plt_transact_id AND t.operation_name='09 Buff' AND p.plt_no = '$plt_no'";
                    $resultyield = mysql_query($query);
                    if($resultyield)
                        {
                        if(mysql_num_rows($resultyield) > 0) $buff_input = $buff_input + mysql_result($resultyield,0,"buff_input");
                        }
                    
                    $query  = "SELECT SUM(t.output_qty) AS core_output FROM traceability t, plt_transact p WHERE t.plt_transact_id = p.plt_transact_id AND t.operation_name='10 Core' AND p.plt_no = '$plt_no'";
                    $resultyield = mysql_query($query);
                    if($resultyield)
                        {
                        if(mysql_num_rows($resultyield) > 0) $core_output = $core_output + mysql_result($resultyield,0,"core_output");
                        }
                    
                    $query = "SELECT d.operation_name, SUM(d.input_qty) AS inputqty, d.input_unit, SUM(d.output_qty) AS outputqty, d.output_unit FROM traceability d, plt_transact t WHERE t.plt_no = '$plt_no' AND d.plt_transact_id = t.plt_transact_id GROUP BY d.operation_name, d.output_unit, d.input_unit ORDER BY d.operation_name";
                    $result = mysql_query($query);
                    if($result)
                        {
                       
                        
                        echo "<tr>";
                        echo "<td><strong>$plt_no</strong></td>";
                        
                        for($ctr=0; $ctr < $total_row; $ctr++)
                          {
                            echo "<td class='tdclass'>";
                            echo number_format((mysql_result($result,$ctr,"inputqty")),2);  
                            
                            echo "</td>";
                            echo "<td class='tdclass'>";
                            echo number_format((mysql_result($result,$ctr,"outputqty")),2); 
                            
                            echo "</td>";
                          } 
                            
                        echo "<td class='tdclass'><strong>";
                        printf ("%01.2f", $PLT_yield);
                         echo "%</strong></td>";
                     
                        echo "</tr>";
                        
                    
                        }
                    }
                }
            }
            $Batch_yield = @($coreoutput / $buff_input) * 100;
            echo "<tr>";
            echo "<td style='text-align: right; font-weight: bold; font-family: Arial;' class = 'tdclass' colspan='";
            $loopctr = ($total_row * 2) + 2;
            echo "$loopctr'>Batch Yield:&nbsp;&nbsp;"; 
            printf ("%01.2f", $Batch_yield);
            echo "<strong>%";
            echo "</td>";
                 
            echo "</tr>";
            echo "</table>";              
    } 

[james438]

kk, it looks like we are dealing with something else now. You didn't do what I said, which makes things more complicated. Right now I almost suspect that you have a different problem entirely. The problem for me is that I don't know what data types you are working with in your database.

Just looking at your code you just posted I get the impression that you have a program that is writing this script for you. You have if statements that are always true and thus unnecessary thus making the script more complicated than it needs to be.

            if($result)

is meaningless. It will always be true.

        $query = "SELECT plt_no FROM plt_transact WHERE plt_date = '" . $_POST["start_date"] . "'"; 
        $result_loop = mysql_query($query); 
         
        $date = $_POST["start_date"]; 
                 
        $Batch_yield = 0; 
        $buff_input = 0; 
        $core_output = 0; 
        $loopctr = 0; 
        $totalloop = 0;

does not appear to do anything at all.

At least one of your tables has a space in it or is missing a comma. You are pulling data from a table called "traceability t" which does not appear to be referenced at all.

I would suggest that you exit out of your php as opposed to echoing your html/css.

Not to be mean, but you need to put some work into cleaning this up yourself and debug it a little bit before posting it here.