Sorting a Alhpa numeric array

[mallen]

I have this query and I want the files to be displayed in order. The problem is they are Alpha numeric. I have tried sorting them also and nothing seems to work. Sometimes a few files that should be at the top are at the bottom. Notice some have spaces also. The files follow this format.

 

ABC123AB.gif

ABC123AB.jpg

DCG567CD.gif

DCG567CD.jpg

FG LMN WST.gif

FG LMN WST.gif

 

global $wpdb;
	$out = "";
	$query = "SELECT * FROM files WHERE `prod_id` = '". $this->curProduct ."' ORDER BY `file_name` ASC";
	$files = $wpdb->get_results($query, ARRAY_A);

 

[PFMaBiSmAd]

If after ignoring the spaces, all the filenames are the same length, so that they could be sorted as strings, then you could do this in the query by sorting on a copy of the values with the spaces removed.

 

Otherwise, you would need to use natcasesort in php.

 

Edit: post an example of the incorrect output you are getting, because it's likely you have some leading white-space in with the data that is causing this.

 

[mallen]

I think what is messing me up is the ordering the file by name. Then turning around sorting the ARRAY. I tried all the sort functions using sort($files);  but I didn't get the result I was looking for.  The files with the spaces I don't think is an issue. Because when the list is out of order those are not at the bottom or top of the list. I checked the database. Making sure there was not a space before the file name.

[PFMaBiSmAd]

sorting the ARRAY.

 

Sorting what array? The code you posted doesn't contain any php array or any php code attempting to operate on that data.

 

Without the code that produces the incorrect result, sample or actual input data, the incorrect output from that data, and the expected output from that data, its not possible to help with coding problems.

[mallen]

global $wpdb;
$out = "";
$query = "SELECT * FROM files WHERE `prod_id` = '". $this->curProduct ."' ORDER BY `file_name` ASC";
$files = $wpdb->get_results($query, ARRAY_A);
if(count($files) >0)
	{
		$out .= "<h2>Title Here</h2>";
		$out .= "<table id='xxx'>\n";
		$row = 0;
		foreach($files as $file)
		{
			if($row % 2 == 1)
				$out .= '<tr>';
			else
				$out .= '<tr class="even">';
			$out .= '<td width="50"><a href="...../media/'. trim($file['file_name']) .'" target="_blank"><img src="/_image.png" alt="icon" /></a></td>';	
			$out .= '<td>'. $file['file_name'] . '</td>';
			$out .= '</tr>';
			$row++;
		}
		$out .= "</table>";
	}

	return $out;
}

[salathe]

Edit: post an example of the incorrect output you are getting

 

Do this, please.

[mallen]

CAD435FG.gif

CAD435FG.jpg

DCG567CD.gif

DCG567CD.jpg

EFG324DF.gif

EFG324DF.gif

FG LMN WST.gif

FG LMN WST.gif

ABC123AB.gif

ABC123AB.jpg

 

No matter how I sort it it will put most of it in order like this except ABC123AB.gif, ABC123AB.jpg which should be at the top.

[Psycho]

The problem is most likely as suggested earlier - there are probably white-space characters before some of the values that is causing the problem. You won't see that unless you look at the source code and if you copied/pasted this from the browser (and not the code) it isn't apparent from what you just posted. Do a var_dump() on the results and post the output here.

$query = "SELECT * FROM files WHERE `prod_id` = '". $this->curProduct ."' ORDER BY `file_name` ASC";
$files = $wpdb->get_results($query, ARRAY_A);
var_dump($files);

 

[mallen]

I ran the var_dump() and some of the files names do have a extra space before them. What is strange is in the database, when I look at them in PHPMyAdmin there is no spaces. Can I add a trim function somewhere to these results?

[Jessica]

http://lmgtfy.com/?q=mysql+trim

[Psycho]

I ran the var_dump() and some of the files names do have a extra space before them. What is strange is in the database, when I look at them in PHPMyAdmin there is no spaces. Can I add a trim function somewhere to these results?

 

White-spaces have some idiosyncrasies when displayed in a browser which is why you need to verify the content of variables accordingly. If you aren't going to use var_dump or something like echo "[" . $var . "]"; you definitely need to be looking at the source of the HTML and not the display.

 

So, having said all that you should do two things:

 

1. You should almost always trim data before storing it. It should be standard procedure and you should only NOT trim when you have made a conscious decision that it makes sense not to. So, any code you have now that creates those records you should go back an implement appropriate trim()'s. In fact, since you aren't trimming you might have some validations that are broken. E.g. if you are testing if a field was empty using $_POST['var'] == '' or empty($_POST['var']), etc. that would allow the value to pass with just spaces entered. So, do your trim before any validations.

 

2. Once you know that any new values entered into the DB will be appropriately trimmed you should go back and run a single query to trim any existing values using the MySQL function of the same name as used in PHP.

[mallen]

Yes I am working on code that someone else developed and I am in the process of cleaning up such forms that created these spaces. I have cleaned out the spaces on one product to test it. It still is not ordering the files. Upon searching for answers I came across the fact the letters are treated as ASCII? Is that why the mixture of numbers and letters do not give me an accurate list?

[Psycho]

Upon searching for answers I came across the fact the letters are treated as ASCII? Is that why the mixture of numbers and letters do not give me an accurate list?

 

No, that should not be an issue based upon the values you have (it might with accented characters based upon the collation used on your table). You need to provide exact details for us to help. Please provide the exact query you are using and a var_dump() of the results. If the results are too numerous to post, then provide a subset for us to view.

[xyph]

Numbers can be ASCII as well.

[mallen]

My tables are file_name  and file_type

one is varchar utf_general_ci

the other is latin1_swedish_ci

 

below is a sample vardump

 

["file_id"]=> string(2) "63" ["prod_id"]=> string(1) "6" ["file_name"]=> string(11) "AB20F84.gif" ["file_type"]=> string(3) "GIF" } [1]=> array(4) {

["file_id"]=> string(2) "64" ["prod_id"]=> string(1) "6" ["file_name"]=> string(11) "AB20F84.jpg" ["file_type"]=> string(15) "JPG" } [2]=> array(4) {

["file_id"]=> string(2) "55" ["prod_id"]=> string(1) "6" ["file_name"]=> string(14) "AB20BPS250.gif" ["file_type"]=> string(3) "GIF" } [3]=> array(4) {

["file_id"]=> string(2) "56" ["prod_id"]=> string(1) "6" ["file_name"]=> string(14) "AB20BPS250.jpg" ["file_type"]=> string(15) "JPG" } [4]=> array(4) {

["file_id"]=> string(2) "57" ["prod_id"]=> string(1) "6" ["file_name"]=> string(13) "AB20XV250.gif" ["file_type"]=> string(3) "GIF" } [5]=> array(4) {

["file_id"]=> string(2) "58" ["prod_id"]=> string(1) "6" ["file_name"]=> string(13) "AB20XV250.jpg" ["file_type"]=> string(15) "JPG" } [6]=> array(4) {

["file_id"]=> string(2) "59" ["prod_id"]=> string(1) "6" ["file_name"]=> string(13) "AB20PS175.gif" ["file_type"]=> string(3) "GIF" } [7]=> array(4) {

["file_id"]=> string(2) "60" ["prod_id"]=> string(1) "6" ["file_name"]=> string(13) "AB20PS175.jpg" ["file_type"]=> string(15) "JPG" } [8]=> array(4) {

["file_id"]=> string(2) "61" ["prod_id"]=> string(1) "6" ["file_name"]=> string(13) "AB20PS250.gif" ["file_type"]=> string(3) "GIF" } [9]=> array(4) {

["file_id"]=> string(2) "62" ["prod_id"]=> string(1) "6" ["file_name"]=> string(13) "AB20PS250.jpg" ["file_type"]=> string(15) "JPG" } }

 

[The Little Guy]

Give this a try:

 

$query = "SELECT * FROM files WHERE `prod_id` = '$this->curProduct' ORDER BY LENGTH(file_name), file_name asc";

 

OR:

 

$query = "SELECT * FROM files WHERE `prod_id` = '$this->curProduct' ORDER BY CAST(file_name as unsigned), file_name asc";

[mallen]

I tried both of these and still didn't work. I mean it reorganized them but not in the order I need.  I tried adding sort($files);  thinking that maybe that is causing the issue.

[PFMaBiSmAd]

You need to find and fix the problem with the data that is stored in your database table. That will require you to trim the offending leading characters in the data that is stored in your database table. The suggestion that The Little Guy posted has nothing to do with fixing your problem.

 

P.S. The sample print_r that you posted doesn't show any of the problem data. 

[mallen]

I did remove the offending leading characters in the data that is stored in my database table.

[PFMaBiSmAd]

Then what current problem are you having, i.e. we only see the information that you supply in your posts.

[mallen]

Here is an another var_dump to show what results I am getting. Notice from file AB51MN50.jpg and below. This is where it is not in order.

 

["file_id"]=> string(2) "30" ["prod_id"]=> string(1) "5" ["file_name"]=> string(13) "AB51HPS70.gif ["file_type"]=> string(15) "GIF } [14]=> array(4) {

["file_id"]=> string(2) "38" ["prod_id"]=> string(1) "5" ["file_name"]=> string(13) "AB51MN100.jpg" ["file_type"]=> string(3) "JPG" } [15]=> array(4) {

["file_id"]=> string(2) "39" ["prod_id"]=> string(1) "5" ["file_name"]=> string(13) "AB51MN100.gif ["file_type"]=> string(15) "GIF } [16]=> array(4) {

["file_id"]=> string(2) "42" ["prod_id"]=> string(1) "5" ["file_name"]=> string(30) "AB51MN175 w.jpg" ["file_type"]=> string(3) "JPG" } [17]=> array(4) {

["file_id"]=> string(2) "46" ["prod_id"]=> string(1) "5" ["file_name"]=> string(30) "AB51MN175 w.gif ["file_type"]=> string(15) "GIF } [18]=> array(4) {

["file_id"]=> string(2) "43" ["prod_id"]=> string(1) "5" ["file_name"]=> string(30) "AB51MN175 w.jpg" ["file_type"]=> string(3) "JPG" } [19]=> array(4) {

["file_id"]=> string(2) "47" ["prod_id"]=> string(1) "5" ["file_name"]=> string(30) "AB51MN175 w.gif ["file_type"]=> string(15) "GIF } [20]=> array(4) {

["file_id"]=> string(2) "52" ["prod_id"]=> string(1) "5" ["file_name"]=> string(30) "AB51MN175 w.jpg" ["file_type"]=> string(3) "JPG" } [21]=> array(4) {

["file_id"]=> string(2) "53" ["prod_id"]=> string(1) "5" ["file_name"]=> string(30) "AB51MN175 w.gif ["file_type"]=> string(15) "GIF } [22]=> array(4) {

["file_id"]=> string(2) "44" ["prod_id"]=> string(1) "5" ["file_name"]=> string(13) "AB51MN175.jpg" ["file_type"]=> string(3) "JPG" } [23]=> array(4) {

["file_id"]=> string(2) "45" ["prod_id"]=> string(1) "5" ["file_name"]=> string(13) "AB51MN175.gif ["file_type"]=> string(15) "GIF } [24]=> array(4) {

["file_id"]=> string(2) "50" ["prod_id"]=> string(1) "5" ["file_name"]=> string(16) "AB51MN175eue.jpg" ["file_type"]=> string(3) "JPG" } [25]=> array(4) {

["file_id"]=> string(2) "51" ["prod_id"]=> string(1) "5" ["file_name"]=> string(16) "AB51MN175eue.gif ["file_type"]=> string(15) "GIF } [26]=> array(4) {

["file_id"]=> string(2) "34" ["prod_id"]=> string(1) "5" ["file_name"]=> string(12) "AB51MN50.jpg" ["file_type"]=> string(3) "JPG" } [27]=> array(4) {

["file_id"]=> string(2) "35" ["prod_id"]=> string(1) "5" ["file_name"]=> string(12) "AB51MN50.gif ["file_type"]=> string(15) "GIF } [28]=> array(4) {

["file_id"]=> string(2) "36" ["prod_id"]=> string(1) "5" ["file_name"]=> string(12) "AB51MN70.jpg" ["file_type"]=> string(3) "JPG" } [29]=> array(4) {

["file_id"]=> string(2) "37" ["prod_id"]=> string(1) "5" ["file_name"]=> string(12) "AB51MN70.gif ["file_type"]=> string(15) "GIF } [30]=> array(4) {

["file_id"]=> string(2) "48" ["prod_id"]=> string(1) "5" ["file_name"]=> string(13) "AB51PS150.jpg" ["file_type"]=> string(3) "JPG" } [31]=> array(4) {

["file_id"]=> string(2) "49" ["prod_id"]=> string(1) "5" ["file_name"]=> string(13) "AB51PS150.gif ["file_type"]=> string(15) "GIF }

 

[Jessica]

Alphanumerically, AB51MN50.jpg should come after AB51MN175eue.gif

You need natural case sorting, but even that may not do it.

AB51MN1...

AB51MN5...

See?

[mallen]

I have tried these queries:

$query = "SELECT * FROM files WHERE `prod_id` = '". $this->curProduct ."' ORDER BY LENGTH";
$query = "SELECT * FROM files WHERE `prod_id` = '$this->curProduct' ORDER BY CAST(file_name as unsigned), file_name asc";
$query = "SELECT * FROM files WHERE `prod_id` = '$this->curProduct' ORDER BY LENGTH(file_name), file_name asc";
$query = "SELECT * FROM files WHERE `prod_id` = '$this->curProduct' ORDER BY CAST(file_name as signed), file_name ASC";
$query = "SELECT * FROM files WHERE `prod_id` = '$this->curProduct' ORDER BY CAST(file_name as alphanumeric) ASC";
$query = "SELECT * FROM files WHERE `prod_id` = '". $this->curProduct ."' ORDER BY LENGTH(alphanumeric)";

[Psycho]

I don't believe MySQL has a natural sorting capability. So, you might need to query the records, dump into an array and then sort using PHP's natural sorting capability

 

$query = "SELECT * FROM files WHERE `prod_id` = '{$this->curProduct}'";
$result = mysql_query($query);
$data = array();
while($row = mysql_fetch_assoc($result))
{
    $data[$row['file_name']] = $row;
}

//Perform natural sort on array key
ksort($data, SORT_NATURAL);

print_r($data);

[mallen]

Not sure if you saw my post further up but here is the entire block of code. Note the sort on $files

global $wpdb;
$out = "";
$query = "SELECT * FROM files WHERE `prod_id` = '". $this->curProduct ."' ORDER BY `file_name` ASC";
$files = $wpdb->get_results($query, ARRAY_A);
ksort($files, SORT_NATURAL);
if(count($files) >0)

{

$out .= "<h2>Title Here</h2>";

$out .= "<table id='xxx'>\n";

$row = 0;

foreach($files as $file)


{

if($row % 2 == 1)

$out .= '<tr>';

else


$out .= '<tr class="even">';



$out .= '<td width="50"><a href="...../media/'. trim($file['file_name']) .'" target="_blank"><img src="/_image.png" alt="icon" /></a></td>';



$out .= '<td>'. $file['file_name'] . '</td>';



$out .= '</tr>';


$row++;

}



$out .= "</table>";


}

return $out;



}