webguync Posted August 21, 2009 Share Posted August 21, 2009 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 Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted August 21, 2009 Share Posted August 21, 2009 Cannot really help you unless you post an example of what you are getting for results verses what you expect. Quote Link to comment Share on other sites More sharing options...
webguync Posted August 21, 2009 Author Share Posted August 21, 2009 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 Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted August 21, 2009 Share Posted August 21, 2009 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. Quote Link to comment Share on other sites More sharing options...
webguync Posted August 21, 2009 Author Share Posted August 21, 2009 OK, I see. I added an ORDER BY to the second SELECT query and it is working as intended now. Thanks for the assistance! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.