Jump to content

[SOLVED] ORDER BY SQL not ordering

webguync

By webguync
in PHP Coding Help

Recommended Posts

webguync Advanced Member

webguync

Posted
Posted

Hello, I have some data I am pulling from a MySQL database and displaying in an HTML table.

 

for some reason in the following code, the SQL to order the data in the table by the column 'employee_id', isn't working. Is just orders exactly as it is in the MySQL table. Can anyone tell what I am doing wrong?

 

Here is the code:

 


<?php

/* DB INFO  */
$dbhost = "localhost";
$dbuser = "DBUser";
$dbpass = "DBPass";
$dbname = "DBNmae";
$tablename = "table_name";
$idfield = "id";
function stripslashes2( $string ) {
    if(get_magic_quotes_gpc()) {
        return stripslashes($string);
    } else {
        return $string;
    }
}


mysql_pconnect( $dbhost, $dbuser, $dbpass ) or die ( "Unable to connect to MySQL server" );
mysql_select_db( "$dbname" );
mysql_query( "SELECT * FROM table_name  ORDER BY employee_id ASC" ); // Set this to latin2 if you're using latin2 collacation in your database


/*mysql field name /      Field title, field type, is required?*/

$fields["employee_id"]=    array( "Employee ID", "textfield", "" );
$fields["employee_name"]=    array( "Employee Name", "textfield", "" );
$fields["assessor_id"]=    array( "Assessor ID", "textfield", "" );
$fields["assessor_name"]=    array( "Assessor Name", "textfield", "" );
$fields["score1"]=    array( "Score 1", "textfield", "" );
$fields["score2"]=    array( "Score 2", "textfield", "" );
$fields["score3"]=    array( "Score 3", "textfield", "" );
$fields["score4"]=    array( "Score 4", "textfield", "" );
$fields["score5"]=    array( "Score 5", "textfield", "" );
$fields["score6"]=    array( "Score 6", "textfield", "" );
$fields["score7"]=    array( "Score 7", "textfield", "" );
$fields["date_created"]=    array( "Date Created", "textfield", "" );
$fields["delete"]=    array( " Delete", "delete_checkbox", "" );


if ( !empty( $_POST["Update"] ) ) {
    if ( empty( $_POST["keys"] ) )
        die( "Update identifer is empty" );

    $rows = 1;
    $deleted=0;
    $updated=0;
    foreach( $_POST["keys"] AS $ids ) {

        if(empty($_POST["delete"][$ids]))
        {
            $sql = "update $tablename ";
            foreach( $fields AS $key => $val ) {
            if($val[1]!="delete_checkbox")
            {
                if ( empty( $_POST["$key"]["$ids"] ) AND !empty( $val[2] ) )
                    $errors[] = "$val[0] in the $rows row is required";

                $updated_value = isset( $_POST["$key"]["$ids"] )? mysql_real_escape_string( stripslashes2($_POST["$key"]["$ids"]) ):'';
                $set[] = sprintf( "`%s`='%s'" , $key , $updated_value );
            }
        }
        $main[] = $sql . " SET " . implode( "," , $set ) . sprintf( " WHERE $idfield='%d'" , $ids ) ;
        unset( $set );
        $rows++;
        $updated++;

        }
        else
        {
        $main[]=sprintf("DELETE from $tablename WHERE $idfield='%d'" , $ids );
        $deleted++;
        }
    }

    if ( empty( $errors ) ) {
        foreach( $main AS $query ) {
            mysql_query( $query ) or die( mysql_error() );
        }
    } else {
        array_unique( $errors );
        print implode( "<br />" , $errors );
    }

    // header("Location: Update_Delete?ok=1");

    if(!empty($updated))
    print "Updated number of results: $updated ";

    if(!empty($deleted))
    print "Deleted row(s): $deleted ";


}

?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Page Title</title>
<link href="css/report.css" rel="stylesheet" type="text/css" />
</head>
<body><?php
if ( !empty( $_GET["ok"] ) )
    print "Updated succesfully";

$sqlquery = "select * from $tablename";
$result_query = mysql_query( $sqlquery ) or die( mysql_error() . " error in query:" . htmlspecialchars( $sqlquery ) );

echo '<form action="delete.php" method="post" name="Form">';
print "<table><tr><th> # </th>";

foreach( $fields AS $key => $val )
print "<th>$val[0]</th>";
print "</tr>";

if ( mysql_num_rows( $result_query ) > 0 ) {
    $rownumber = 1;
    while ( $rows_query = mysql_fetch_assoc( $result_query ) ) {
        print "<tr>\n";
        print "<td>$rownumber.</td>";
        print "<input type=\"hidden\" name=\"keys[]\" value=\"{$rows_query[$idfield]}\">";
        foreach( $fields AS $key => $value ) {
            if ( $value[1] == "textfield" )
                echo "<td><input name=\"{$key}[{$rows_query[$idfield]}]\" type=\"text\" value=\"" . ( isset( $rows_query["$key"] )?htmlspecialchars( $rows_query["$key"] ):"" ) . "\"></td>\n";
            elseif ( $value[1] == "checkbox" ) {
                echo "<td><input name=\"{$key}[{$rows_query[$idfield]}]\" type=\"checkbox\" value=\"1\"" . ( !empty( $rows_query["$key"] )?" checked=\"checked\"":"" ) . "></td>\n";
            } elseif ( $value[1] == "textarea" ) {
                echo "<td> <textarea name=\"{$key}[{$rows_query[$idfield]}]\" cols=\"45\" rows=\"5\" wrap=\"virtual\">" . ( isset( $rows_query["$key"] )?htmlspecialchars( $rows_query["$key"] ):"" ) . "</textarea></td>\n";
            } elseif ( $value[1] == "delete_checkbox" ) {
                echo "<td><input name=\"delete[{$rows_query[$idfield]}]\" type=\"checkbox\" value=\"1\"></td>\n";
            }



        }
        print "</tr>\n";
        $rownumber++;
    }
} else
    echo "No result in query table ...";

print "</table>\n";
echo '<input name="Update" type="submit" value="Update">
</form>
';

?>

</body></html

Link to comment
https://forums.phpfreaks.com/topic/171296-solved-order-by-sql-not-ordering/
Share on other sites
webguync Advanced Member

webguync

Posted
  • Author
Posted

the results are being ordered by score_id which is a field in the MySQL set to auto increment. So for example the data is being sorted like this:

 

1  SASM  Sally Smith

2  ANWI  Annie Williams

3  BIJO    Bill Jones

4  CHTO  Chris Thompson

 

and I want to sort by employee ID so it would display:

 

1  ANWI  Annie Williams

2  BIJO    Bill Jones

3  CHTO  Chris Thompson

4  SASM  Sally Smith

 

 

PFMaBiSmAd Advanced Member

PFMaBiSmAd

Posted
Posted

In your code there are two SELECT queries.

 

The first one has an ORDER BY but because you are not assigning the result resource from the mysql_query(...) to a variable, nothing in your code is using the result of that query.

 

The second one is just "select * from $tablename" and it has no ORDER BY in it.

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.