sorting array after mysql query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
wurdup
Forum Commoner
Posts: 39
Joined: Thu Apr 01, 2010 11:36 am

sorting array after mysql query

Post by wurdup »

I have a mysql query that works:

Code: Select all

$sql= "SELECT user_company FROM users WHERE user_company LIKE '$letter%' ORDER BY user_company";
$productR = mysql_query($sql) or die (mysql_error()); 	
This worked and showed the company alphabetically. Now however I have encrypted the database so now it is sorted by the encryption first letter rather than the decrypted text later. How can I add a function to the query or sort the $productR array using my decrypt($string) function?
I tried' order by decrypt(user_company)' but it threw an error.

Thanks
User avatar
Jade
Forum Regular
Posts: 908
Joined: Sun Dec 29, 2002 5:40 pm
Location: VA

Re: sorting array after mysql query

Post by Jade »

You need to do a sort on the decrypted array of data: http://php.net/manual/en/function.sort.php. The MySQL ENCRYPT function is one way so there's no way for you to decrypt it. Instead you need to use DES_ENCRYPT and then you can use DES_DECRYPT to un-encrypt the data.

Code: Select all

<?php
$sql= "SELECT DES_DECRPT(user_company) as user_company FROM users WHERE user_company LIKE '$letter%'";
$productR = mysql_query($sql) or die (mysql_error());  

$row = mysql_fetch_array($productR); //here is your array of unencrypted user_company names
sort($row); //your array is re-sorted

print_r($row); //now print out all the data
?>
wurdup
Forum Commoner
Posts: 39
Joined: Thu Apr 01, 2010 11:36 am

Re: sorting array after mysql query

Post by wurdup »

Sorry I think you misunderstand the question. I already have an encrypy function of my own: encrypt() and decrypt(). I need to use these functions.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: sorting array after mysql query

Post by John Cartwright »

and what about

Code: Select all

$sql= "SELECT DECRYPT(user_company) AS decypted_company FROM users WHERE user_company LIKE '$letter%' ORDER BY decypted_company";
wurdup
Forum Commoner
Posts: 39
Joined: Thu Apr 01, 2010 11:36 am

Re: sorting array after mysql query

Post by wurdup »

When I use that I get:

FUNCTION *database name*.decrypt does not exist
These functions are PHP functions.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: sorting array after mysql query

Post by John Cartwright »

wurdup wrote:When I use that I get:

FUNCTION *database name*.decrypt does not exist
These functions are PHP functions.
If they are PHP functions, then you obviously cannot mix the two between eachother. Populate your array collection normally, apply your decrypt function (which you generally want to apply at the database level, and not PHP), then perform a sort() or usort() depending on your array structure.
wurdup
Forum Commoner
Posts: 39
Joined: Thu Apr 01, 2010 11:36 am

Re: sorting array after mysql query

Post by wurdup »

Populate your array collection normally, apply your decrypt function (which you generally want to apply at the database level, and not PHP), then perform a sort() or usort()

That's what I'm asking for help with.
wurdup
Forum Commoner
Posts: 39
Joined: Thu Apr 01, 2010 11:36 am

Re: sorting array after mysql query

Post by wurdup »

thanks this works well. I was making the mistake of not putting the ['user_company'] on the $row.

Out of interest why and how would you add a function to the database? What is you're using your own salts etc?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: sorting array after mysql query

Post by John Cartwright »

I removed my last post because I assumed I didn't correctly understand your problem (realized after I had written the post) becuase your LIKE statement is based on the encrypted data. Thus, the returned dataset would not be correctly identified. This is exactly the reason why you want to implement this at the database level since you can selectively query the decrypted data and not have the overhead of having to query your entire dataset and filter in PHP.
wurdup
Forum Commoner
Posts: 39
Joined: Thu Apr 01, 2010 11:36 am

Re: sorting array after mysql query

Post by wurdup »

it's fine i removed the order by and letter and doing it manually.
Post Reply