Page 1 of 1
sorting array after mysql query
Posted: Tue Jul 06, 2010 3:05 pm
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
Re: sorting array after mysql query
Posted: Tue Jul 06, 2010 3:21 pm
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
?>
Re: sorting array after mysql query
Posted: Tue Jul 06, 2010 3:27 pm
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.
Re: sorting array after mysql query
Posted: Tue Jul 06, 2010 3:39 pm
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";
Re: sorting array after mysql query
Posted: Tue Jul 06, 2010 3:53 pm
by wurdup
When I use that I get:
FUNCTION *database name*.decrypt does not exist
These functions are PHP functions.
Re: sorting array after mysql query
Posted: Tue Jul 06, 2010 3:58 pm
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.
Re: sorting array after mysql query
Posted: Tue Jul 06, 2010 4:16 pm
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.
Re: sorting array after mysql query
Posted: Tue Jul 06, 2010 5:05 pm
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?
Re: sorting array after mysql query
Posted: Tue Jul 06, 2010 5:08 pm
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.
Re: sorting array after mysql query
Posted: Tue Jul 06, 2010 5:21 pm
by wurdup
it's fine i removed the order by and letter and doing it manually.