sorting problem

[predator]

Hey,
Take a look at this piece of code

		
		  <?
		  for($index = 0; $index< $rows; $index++) { 
			$PlayerID = mysql_result($result, $index, "PlayerID"); 
			$Firstname = mysql_result($result, $index, "Firstname"); 
			$Lastname = mysql_result($result, $index,"Lastname");
			$Drafted = mysql_result($result, $index, "Drafted");


		preg_match("|([0-9]+)/([0-9]+)\s*\(([0-9]+[stndrdh]{2}).*\).*(\w+)$|Ui", $Drafted, $matches); 

       $round = $matches[1];
       $overall = $matches[3];
       $team = $matches[4];
	  ?>
      <tr> 
        <td><? echo "$round"; ?></td>
		<td><? echo "$overall"; ?></td>
		<td><? echo "$team"; ?></td>

In the variabel $overall I get values that are like "27th" "3rd" "7th" "125th" etc... and I want them to sort_numeric , so 7th comes before 27th and so on.

I have however not managed to do so, so if anyone could show how I should adjust my code, it would be greatly appreciated

Perhaps this could be solved in my SQL-query?
It looks like this

	  $draft = "SELECT PlayerID, Firstname, Lastname, Drafted FROM 
player WHERE Drafted LIKE '%/$Year%' AND 
NationID='1' ORDER BY Drafted ASC";

could I changed it so it sorts Drafted numeric instead of asc? It would also do the trick

thanks

[khoang82]

Well, I am not sure how you have your table set up but If you field is in TXT format, then 27th will be infront of 7th because 2 is before 7 in asccii value. But do confirm this. Run a test query in your db.

what you could do is to write a php function that first analyize each number and find the number with the longest length. Then It will go to the numbers again and one by one adding 0 in front of each number so that all the lenth are all equal. Then run this array of numbers on any sorting algorithm and it will work.
example would be
27th, 7th, 123th, 1234th
convert into
0027th, 0007th, 0123th, 1234th

If you have a small amount of data, then this method is fine. Or else, it might slow your program down. You should try to get rid of the "th" in every number and save those number in your DB as integer. Then the DB would do it for you in much faster time. I hope that helps

[predator]

It's a huge database/site ... and I have the distinct feeling that there must be an easier way to get around this.
Wouldn't SORT_NUMERIC do the trick? I don't how to use it on my code though.

and yes, I gather the info drafted from a VARCHAR in my mysqldb

[khoang82]

Hm.. I have never use that php flag before. But heck, it works well.

Here is a test script i just wrote to check it out

<?php
$array = array("1th", "2th", "12th", "111th", "13th");
echo 'original array<BR>';
print_r($array);
echo '<BR>sorted array<BR>';
array_multisort($array, SORT_NUMERIC);
print_r ($array);
?
Here is the out come

original array
Array ( [0] => 1th [1] => 2th [2] => 12th [3] => 111th [4] => 13th )
sorted array
Array ( [0] => 1th [1] => 2th [2] => 12th [3] => 13th [4] => 111th )

[predator]

yes that works, the question is how I should change my code and use sort_numeric, can't get it to work 🙁

[Sxooter]

Note sure about MySQL, but in pgsql you could do something like

order by substring(fieldname,'[[:digit:]]+')::int;

[Sxooter]

create table p (stuff text);
CREATE TABLE
marlowe=# insert into p values ('27th');
INSERT 4531899 1
marlowe=# insert into p values ('3rd');
INSERT 4531900 1
marlowe=# insert into p values ('13th');
INSERT 4531901 1
marlowe=# select * from p order by substring(stuff,'[[:digit:]]+')::int;

stuff

3rd
13th
27th
(3 rows)

marlowe=# select * from p order by stuff;

stuff

13th
27th
3rd
(3 rows)

[khoang82]

I am not sure what your question is anymore.

You already have a DB with data already and your code already set $overall to value such as "27th", "13th", and etc... and now you want to sort them right???

Khoa Nguyen

[predator]

yes

I want the variable $overall to be sorted like in your example

[Sxooter]

isn't there some kind of regex capability in MySQL equivalent to the one I listed in postgresql? That would seem far more robust than doing it in with some predefined array setup.

[khoang82]

well have your code put the value into an array.

<?
for($index = 0; $index< $rows; $index++) {
$PlayerID = mysql_result($result, $index, "PlayerID");
$Firstname = mysql_result($result, $index, "Firstname");
$Lastname = mysql_result($result, $index,"Lastname");
$Drafted = mysql_result($result, $index, "Drafted");

        preg_match("|([0-9]+)/([0-9]+)s*(([0-9]+[stndrdh]{2}).*).*(w+)$|Ui", $Drafted, $matches); 

       $round = $matches[1];
       //$overall = $matches[3]; 
       array_push($overall, $matches[3]);
       $team = $matches[4];
      ?>
      array_multisort($overall, SORT_NUMERIC); 

//NOW $OVERALL WILL BE AN ARRAY OF THE RIGHT ORDER FOR YOU
<tr>
<td><? echo "$round"; ?></td>
<td><? echo "$overall"; ?></td>
<td><? echo "$team"; ?></td>

[predator]

Yes, I tried something like that but I received an error, the same error is received when I try your code.

Warning: array_multisort(): Argument #1 is expected to be an array or a sort flag in

[khoang82]

<?
$overall = array(); //decl array
for($index = 0; $index< $rows; $index++) {
$PlayerID = mysql_result($result, $index, "PlayerID");
$Firstname = mysql_result($result, $index, "Firstname");
$Lastname = mysql_result($result, $index,"Lastname");
$Drafted = mysql_result($result, $index, "Drafted");

preg_match("|([0-9]+)/([0-9]+)s(([0-9]+[stndrdh]{2}).).*(w+)$|Ui", $Drafted, $matches);

$round = $matches[1];
//$overall = $matches[3];
array_push($overall, $matches[3]);
$team = $matches[4];
?>
array_multisort($overall, SORT_NUMERIC);
//NOW $OVERALL WILL BE AN ARRAY OF THE RIGHT ORDER FOR YOU
<tr>
<td><? echo "$round"; ?></td>
<td><? echo "$overall"; ?></td>
<td><? echo "$team"; ?></td>

[khoang82]

acutally the problem is more involved now.

Because multi_sort sorted your data to be in certain order. However, the rest of your data's integraty fails.

In simple terms, multi_sort re-order your $overall but it did not reorder round and team to go with their correspondent $overall value. So your triple is mess up.

[khoang82]

There are 2 appraches I could think of right now.

1) have a query for all your $overall values. Use a for loop and and push all the values into an array. Run multi_sort
Then write a for loop that go thourgh each element in the $overall array and write a query to acquire the $team and $round value correspondent to a certain value in your $overall array. This approache is a little bit slow but its much faster.
2) use the method you are using right now. Though change to load all the overall values into $oveall array and all the teams into $team array and all the rounds into $round array. Then write a sort function that takes in 3 arrays. Let's uses bubble sort for the sake of easiness. everytime you move an index value of 1 arrray, do the same to the other 2. At the end, you should have the three array with their data integrety hold. Though this is O(N*N). Kindda slow for big DB

[Sxooter]

Another solution might be to run a cron job that reads the text field, rips out the number and inserts it into an int field, then order by that.

[predator]

thanks guys

after a lot of testing I finally decided to re-design the db anyway.
Instead of having one column: Drafted I know have four Dyear, Dround, Doverall, Dteam ... and now it is much easier to sort.

Still... I have just a minor problem. Kind of weird I guess.
In order not to manually move info from the drafted column to the other four I have this script below

   for($index = 0; $index< $rows; $index++) { 
			$PlayerID = mysql_result($result, $index, "PlayerID"); 
			$Drafted = mysql_result($result, $index, "Drafted");

		preg_match("|([0-9]+)/([0-9]+)\s*\(([0-9]++).*\).*(\w+)$|Ui", $Drafted, $matches); 

$round = $matches[1];
$year = $matches[2];
$overall = $matches[3];
$team = $matches[4];


 $idraft = "UPDATE player SET Dyear = '$year', Dround = '$round', Doverall = '$overall', Dteam = '$team' 
WHERE Drafted LIKE '%/%' AND PlayerID='$PlayerID'";
	  $result2 = mysql_query($idraft,$db_link);

}

The preg_match("|([0-9]+)/([0-9]+)\s(([0-9]++).).*(\w+)$|Ui", $Drafted, $matches);

divides the following "1/2000 (20th overall) by Boston" to
1
2000
20th
Boston

The weird thing is that that preg_match worked while testing it on my windows ISS server, but not when I tried to run it on the main (apache) server.

It works however if I change the preg_match to:
preg_match("|([0-9]+)/([0-9]+)\s(([0-9]+[stndrdh]{2}).).*(\w+)$|Ui", $Drafted, $matches);

but this way I receive "20th" instead of "20" ... so now I'm asking you if you could possibly tell me what I should change in my preg_match in order to get only 20 and not using the same preg_match that I had above (which does not work on an apache-server, or a different php-version).