I have two tables, which will eventually exist on different databases, but for now I just want to concentrate on getting this to work on one. I have one table, which we can call 'A' that has encrypted information using mcrypt, but as base64 encoded values. Every row has a separate IV associated with it stored on another table which we can call 'B'.
If I want to do a SELECT statement in php to get all the US states that equal "Ohio" after being decrypted in table A, how would I do this and at the same time get it to select using every row in table B to provide the IV for decryption?
For clarification:
Table A:
ID | encrypted value
Table B:
ID | IV
ID on table A is associated with ID on table B.
Join the tables and pass the encrypted value and the corresponding IV to the decryption function.
SELECT a.id, decrypt(a.encrypted_value, b.iv) AS state
FROM tableA as a
JOIN tableB as b ON a.id = b.id
HAVING state = 'Ohio'