Jump to content

[SOLVED] ORDER BY SQL not ordering


webguync

Recommended Posts

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

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

 

 

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.

×
×
  • 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.