5kyy8lu3 Posted April 2, 2014 Share Posted April 2, 2014 Hi. Any time I have POST data, I wrap it with php's trim() function to strip whitespace to avoid sorting issues and such in the database. (mysql5 / php5) Despite using php's trim() function, I believe some special characters or spaces are ending up at the beginning of the string. Even editing that row with phpmyadmin doesn't fix the problem which is what's weird. (meaning I manually retype the value in the column in phpmyadmin, then save it, and it still won't sort correctly) Basically, every row sorts correctly except one row that ends up at the very end. If I use mysql's TRIM() in the order by clause like so: ORDER BY TRIM(name) ASC, it actually sorts correctly. So how is something sneaking past php's trim() that mysql's trim() is able to fix? Any ideas? That column is using latin1_swedish_ci collation Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted April 2, 2014 Share Posted April 2, 2014 please stick to ONE thread for any problem. other than the phpmyadmin edit not fixing it (are you sure you deleted any leading and trailing characters?), a common problem we see is people not actually using the output from the php trim function, so posting your php code would be the first step. where did these values originate from, i.e. how did they originally get inserted into the database table? was a file read? was something copy/pasted into a form and if so where was the source copied from, a word processor, a web page...? it's also possible that the order by term in the query is doing a type conversion and the type of this particular data is causing it to change value. we will be able to see the query when you post your code, but also post an example of some of the correctly sorted data and post this incorrectly sorted value. Quote Link to comment Share on other sites More sharing options...
5kyy8lu3 Posted April 2, 2014 Author Share Posted April 2, 2014 (edited) I just inserted a brand new row using phpmyadmin and that row isn't sorting correctly either. This is weird. The page is rather large with formatting so I cut all the fat out and left the actual function code. <?php session_start(); require("library.php"); $DBA = new DBA; if ( $_GET['post'] == 1 ) { $_SESSION['brewery'] = trim($_POST['brewery']); $q = 'UPDATE beers SET brewery="' . $_SESSION['brewery'] . '" WHERE ID="' . $_SESSION['ID'] . '"'; $results = $DBA->Query($q); $_SESSION['insert_success'] = 'New brewery successfully added!'; header("Location: addbrewery.php"); die(); } else { echo '<html> <head> <title>Add Brewery</title> </head> <body>'; if ( $_SESSION['insert_success'] != '' ) { echo $_SESSION['insert_success']; unset($_SESSION['insert_success']); } echo ' <form action="addbrewery.php?post=1" method="POST"> <input type="text" name="brewery"> <input type="submit" value="Add Brewery!"> </form> </body> </html>'; } ?> Here's a screenshot of the sorting not working for my test row in phpmyadmin (and I USED phpmyadmin to INSERT this specific row) Edited April 2, 2014 by 5kyy8lu3 Quote Link to comment Share on other sites More sharing options...
5kyy8lu3 Posted April 2, 2014 Author Share Posted April 2, 2014 (edited) The data was typed by hand into an HTML form. I even typed the data into phpmyadmin's insert page to insert a row and that did the same thing. When I do this, it doesn't sort correctly: SELECT brewery FROM beers ORDER BY brewery ASC; But when I do this, it sorts correctly: SELECT brewery FROM beers ORDER BY TRIM(brewery) ASC; So I'm fairly sure it's related to invisible characters, but I can't figure out where or how they're getting in there. (or how they're sneaking past php's trim() function) Edited April 2, 2014 by 5kyy8lu3 Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted April 2, 2014 Share Posted April 2, 2014 it looks like the newly inserted/updated data is probably okay, but the previously existing data (everything else) is where the white-space/non-printing characters are at, which is why i asked - where did these values originate from, i.e. how did they originally get inserted into the database table? was a file read? was something copy/pasted into a form and if so where was the source copied from, a word processor, a web page...? Quote Link to comment Share on other sites More sharing options...
5kyy8lu3 Posted April 2, 2014 Author Share Posted April 2, 2014 (edited) Oooooh, I see what you're asking now. A lot of it was scraped from a website. Some was copy/pasted, and the rest was typed manually. If it's the existing data that's not working correctly, I'm curious why using TRIM on the ORDER BY clause fixes the problem? NEVERMIND, duh, it's trimming the original data's whitespace or whatnot. That was a dumb question, sorry. I didn't realize until after I pushed the button to post. I think I'm going to write a quick script to go through each row and clean it Edited April 2, 2014 by 5kyy8lu3 Quote Link to comment Share on other sites More sharing options...
Solution 5kyy8lu3 Posted April 2, 2014 Author Solution Share Posted April 2, 2014 Well, I just did this: UPDATE beers SET brewery=TRIM(brewery); And it fixed my problems! I really appreciate the help! 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.