Destramic

brilliant thank you very much requinix. i used this pattern which seems to work fine /(i|s)+:+([A-Za-z0-9_-]\w*)/

hey guys, i'm trying to get back in to the swing of things after a lot of time out from programming, and i'm struggling with this simple regex pattern if i could get some help please. i've decided to go over some of my code and try and re-write things better....starting with my framework. here is my pattern: /^\/?(i|s)?:+([A-Za-z0-9_-])$/ what im trying to do is match: news/i:news_id - returning strings i (if there) and news_id as the string is uri it could contain a forward slash at the beginning. if (preg_match_all('/\/?(i|s)?:+([A-Za-z0-9_-])/', 'my-page/i:foo/:bar', $fixed_parameters)) { print_r($fixed_parameters); } result: Array ( [0] => Array ( [0] => i:f [1] => /:b ) [1] => Array ( [0] => i [1] => ) [2] => Array ( [0] => f [1] => b ) ) idealy what i'd like is a result like this: Array ( [0] => Array ( [0] => i [1] => foo ) [1] => Array ( [0] => [1] => bar ) ) thanks guys

well i have no real exprience what so ever when it comes to api's...but if someone is paying for a key then it makes sense to register the key to a site...ie: phpfreaks.com = phpfreakskey this way you can ensure the key is correct and also that the request for your api is phpfreaks.com, ensuring another site isn't using the key. you'd simply have to create a page www.yoursite.com/api.php?key=phpfreakskey and then validate the key and where the request is sent from....if all checks out then the data can be sent process seems simple to me but i may be overlooking things.. what does your api do exactly?

ok firstly you would need to set a cookie for the page your talking about....here is a example setcookie ('time_visited', time(), 3600, '/' , 'domain.com', true, false); when revisiting this page again you can do something like this: if (isset($_COOKIE['time_visited'])) { $time_visited = $_COOKIE['time_visited']; $last_visited = date('m/d/Y H:i:s', $time_visited); echo "Hi User, you last visited: " . $last_visted; } you may want to look at the manuel here regarding cookies: http://php.net/manual/en/function.setcookie.php i hope this is what you are looking for

good eyes

although looking further into your script i would leave the encryption of passwords to server side (behind closed doors) your want to be using http://php.net/manual/en/function.password-verify.php and http://php.net/manual/en/function.password-hash.php on your passwords and possibly encrypt the password using aes also before-hand

the reason your form isn't working is because of your formhash js function if you open console in your browser you will see it says which point to this line here: p.value = hex_sha512(password.value); once you sort out you js error the form should submit as intended by your js function

sorry for the delay but i haven't been about to reply....but thank you for the great information and helping me to understand aes a lot more than i did, it's greatly appreciated...i'll take on board your advice and sort my coding out, once done i'll post my code for others to see and learn cheers jacques

ok will you help i reviewed the way i was doing things as i wanted to use PKCS#7 instead of OPENSSL_RAW_DATA as i read it is the best method? here is my working (i still have to tidy up a few things with it though) code which is compatible js - php and visa versa...hopefully it can help someone else out who is trying to learn aes and how to encrypt thier data the right way. js version aes.js var crypto = require('crypto'); $encryption_algorithm = 'AES-128-CBC'; $bytes = 16; $master_key = null; module.exports = { generate_master_key: function (length){ try { if (typeof length != 'number'){ throw 'Error: Master key length must be numberic.'; } var bytes = length / 2; return $master_key = crypto.randomBytes(length).toString('hex'); } catch (error){ console.log(error); return null; } }, master_key: function (master_key){ $master_key = master_key; }, encryption: function (bytes){ try{ if (typeof bytes != 'number'){ throw 'Error: Encryption bytes size must be numberic.'; } $bytes = bytes; var bits = bytes * 8; $encryption_algorithm = 'AES-' + bits + '-CBC'; } catch (error){ console.log(error); return null; } }, encrypt: function (data){ if (Object.prototype.toString.call(data) === '[object Object]' || Object.prototype.toString.call(data) === '[object Array]'){ var encrypted = {}; if (Object.prototype.toString.call(data) === '[object Array]'){ encrypted = []; } for (key in data){ if (Object.prototype.toString.call(data[key]) === '[object Object]' || Object.prototype.toString.call(data[key]) === '[object Array]'){ encrypted[key] = {}; if (Object.prototype.toString.call(data[key]) === '[object Array]'){ var array = []; for (i in data[key]){ array.push(encrypt_data(data[key][i])); } encrypted[key] = array; } else { for (key2 in data[key]){ if (Object.prototype.toString.call(data[key][key2]) === '[object Array]'){ var array = []; for (i in data[key][key2]){ array.push(encrypt_data(data[key][key2][i])); } encrypted[key][key2] = array; } else { encrypted[key][key2] = encrypt_data(data[key][key2]); } } } } else { if (Object.prototype.toString.call(encrypted) === '[object Array]'){ encrypted.push(encrypt_data(data[key])); } else { encrypted[key] = encrypt_data(data[key]); } } } return encrypted; } else { return encrypt_data(data); } return data; }, decrypt: function (ciphertext, init_vector){ if (typeof ciphertext != 'undefined' && typeof init_vector != 'undefined'){ } else if (Object.prototype.toString.call(ciphertext) === '[object Object]' || Object.prototype.toString.call(ciphertext) === '[object Array]'){ var data = ciphertext; if (data['ciphertext'] && data['init_vector']){ return decrypt_data(data['ciphertext'], data['init_vector']); } else { var decrypted = {}; if (Object.prototype.toString.call(data) === '[object Array]'){ decrypted = []; } for (key in data){ if (data[key]['ciphertext'] && data[key]['init_vector']) { if (Object.prototype.toString.call(decrypted[key]) === '[object Array]'){ decrypted.push(decrypt_data(data[key]['ciphertext'], data[key]['init_vector'])); } else { decrypted[key] = decrypt_data(data[key]['ciphertext'], data[key]['init_vector']); } } else if (Object.prototype.toString.call(data[key]) === '[object Object]' || Object.prototype.toString.call(data[key]) === '[object Array]'){ decrypted[key] = {}; for (key2 in data[key]){ if (data[key][key2]['ciphertext'] && data[key][key2]['init_vector']) { if (Object.prototype.toString.call(data[key]) === '[object Array]'){ var array = []; for (i in data[key]){ array.push(decrypt_data(data[key][i]['ciphertext'], data[key][i]['init_vector'])); } decrypted[key][key2] = array; } else { decrypted[key][key2] = decrypt_data(data[key][key2]['ciphertext'], data[key][key2]['init_vector']); } } else{ decrypted[key][key2] = data[key][key2]; } } } else { decrypted.push(data[key]); } } } return decrypted; } return null; } }; function encrypt_data(data){ try { data = new Buffer(data); var init_vector = crypto.randomBytes($bytes), cipher = crypto.createCipheriv($encryption_algorithm, $master_key, init_vector), ciphertext = Buffer.concat([cipher.update(data), cipher.final()]); return ({'ciphertext' : ciphertext.toString('hex'), 'init_vector' : init_vector.toString('hex') }); } catch(error){ console.log(error); return null; } } function decrypt_data(ciphertext, init_vector){ try { ciphertext = new Buffer(ciphertext, "hex"); init_vector = new Buffer(init_vector, "hex"); var decipher = crypto.createDecipheriv($encryption_algorithm, $master_key, init_vector), data = Buffer.concat([decipher.update(ciphertext), decipher.final()]); return data.toString(); } catch(error){ console.log(error); return null; } } test.js var aes = require('./aes'); aes.master_key('e16cd89767a18c53'); console.log(aes.decrypt({ 1: { 'name' : {"ciphertext":"96c0c88e0c9278122dbf50ca0723d9d3","init_vector":"5cf483f898caced497d8c77600245e20"}, 'age' : {"ciphertext":"d64a9d200cea8a15faca7a2f111e98cb","init_vector":"e14c56b1fa718be8f18ce09eb2759b1f"} }, 2: { 'name' : {"ciphertext":"55508883c450d78e676fa0bd8f40f8d3","init_vector":"957d7a2f9587672dbc706c0ac9dd6bba"}, 'age' : {"ciphertext":"888fe201c53f390ce634d0980ce7ffa7","init_vector":"0e2e10dd53d1807bfbb36020d851e2a6"} } })); php version <?php class AES { private $_master_key; private $_encryption_algorithm = 'AES-128-CBC'; private $_bytes = 16; public function generate_master_key($length = 16) { if (!is_numeric($length)) { return null; } $length /= 2; $max_attempts = 10; $attempts = 0; do { $bytes = openssl_random_pseudo_bytes($length, $cryptographically_strong); $attempts++; } while (!$cryptographically_strong && $attempts < $max_attempts); if (!$cryptographically_strong) { return false; } return bin2hex($bytes); } public function excryption($bytes) { try { $this->_bytes = bytes; $bits = bytes * 8; $_encryption_algorithm = 'AES-' + bits + '-CBC'; } catch (Exception $exception) { echo $exception->getMessage(); } } public function master_key($master_key) { $this->_master_key = $master_key; } private function encrypt_data($data) { $init_vector = openssl_random_pseudo_bytes(openssl_cipher_iv_length($this->_encryption_algorithm)); $ciphertext = openssl_encrypt($data, $this->_encryption_algorithm, $this->_master_key, false, $init_vector); return json_encode(array('ciphertext' => bin2hex(base64_decode($ciphertext)), 'init_vector' => bin2hex($init_vector) )); } private function decrypt_data($data, $init_vector = null) { if ($this->is_json($data) && is_null($init_vector)) { $data = json_decode($data); if (isset($data->ciphertext) && isset($data->init_vector)) { $ciphertext = $data->ciphertext; $init_vector = $data->init_vector; } } if (isset($ciphertext) && isset($init_vector)) { return openssl_decrypt(base64_encode(hex2bin($ciphertext)), $this->_encryption_algorithm, $this->_master_key, false, hex2bin($init_vector)); } } private function is_json($data) { if (is_string($data) && is_array(json_decode($data, true)) && (json_last_error() === JSON_ERROR_NONE)) { return true; } return false; } public function encrypt($data) { try { if (is_array($data)) { $encrypted_array = array(); foreach ($data as $key => $value) { if (is_array($value)) { foreach ($value as $key2 => $value2) { $encrypted_array[$key][$key2] = $this->encrypt($value2); } } else { $encrypted_array[$key] = $this->encrypt($value); } } return $encrypted_array; } else { return $this->encrypt_data($data); } } catch (Exception $exception) { echo $exception->getMessage(); } } public function decrypt($data, $init_vector = null) { try { if (is_array($data)) { $decrypted_array = array(); foreach ($data as $key => $value) { if (is_array($value)) { foreach ($value as $key2 => $value2) { $decrypted_array[$key][$key2] = $this->decrypt_data($value2); } } else { $decrypted_array[$key] = $this->decrypt_data($value); } } return $decrypted_array; } else if (!is_null($data) && !is_null($init_vector)) { return $this->decrypt_data($data, $init_vector); } else if ($this->is_json($data) && is_null($init_vector)) { return $this->decrypt_data($data); } } catch (Exception $exception) { echo $exception->getMessage(); } } } $aes = new AES; $aes->master_key('e16cd89767a18c53'); $data = array( array('name' => 'destramic', 'age' => '28'), array('name' => 'alan', 'age' => '99') ); $encryption = $aes->encrypt($data); print_r($encryption); print_r($aes->decrypt($encryption)); works a charm now and thank you for all your help could i asks just few thing if i may please...would using a 256 bit ecryption be a over kill? also i'm thinking of just encrypting eventhing in my database or is it just worth only encrypting sensitive data like address' passwords etc? oh and i read that storing the master key on a seperate server is the best way...if like me you dont have a sepeate server is a .ini file suffice...if so should i zip it? thank you

ok thank you....but when generating a key from my generate_master_key() in my aes.php i'm able to use any number of bytes i wish? and it encrypts fine the code works perfect also thanks i altered a bit so the init vector returns as hexidecimal after used for encrypting, so it's the same as my php version and then returned back to binary afterwards before decrypting. const CRYPTO = require('crypto'); const CIPHER = "AES-128-CBC"; const MASTER_KEY_HEX = "28c03478bbd1874d5c472dd5d6f00ca0"; const MASTER_KEY = new Buffer(MASTER_KEY_HEX, "hex"); console.log('master key', MASTER_KEY); // encrypt var plaintext = new Buffer("attack at dawn"); var initVector = CRYPTO.randomBytes(16); var cipher = CRYPTO.createCipheriv(CIPHER, MASTER_KEY, initVector); var ciphertext = Buffer.concat([cipher.update(plaintext), cipher.final()]); console.log('ciphertext ' + ciphertext.toString("hex") + ' init_vector ' + initVector.toString('hex')); // decrypt initVector = initVector.toString('binary'); var decipher = CRYPTO.createDecipheriv(CIPHER, MASTER_KEY, initVector); var decryptedPlaintext = Buffer.concat([decipher.update(ciphertext), decipher.final()]); console.log(decryptedPlaintext.toString()); although the problem i have is when trying to decrypt the data i encrypted in js via my php as it returns fail. $master_key = "28c03478bbd1874d5c472dd5d6f00ca0"; $encryption = "AES-128-CBC"; $ciphertext = "bf8d6421f518dac424cac43a7a3e25f2"; $init_vector = "a73251f1bcad23e6bf95b1b8ed41dc96"; $data = openssl_decrypt($ciphertext, $encryption, $master_key, false, hex2bin($init_vector)); if ($data) { echo $data; } else { echo "fail"; } i'd say the problem is using const MASTER_KEY = new Buffer(MASTER_KEY_HEX, "hex"); which then changes key to <Buffer 28 c0 34 78 bb d1 87 4d 5c 47 2d d5 d6 f0 0c a0> instead of 28c03478bbd1874d5c472dd5d6f00ca0 could you please tell me what i'm doing wrong here thank you for your help

after more help from jacques1 i've been tring to use nodejs's crypto...i want to be able to encryt data via js and decrypt via php and visa versa. in my aes.js i have created function so i can encrypt and decrypt data although i get an error on both and i'm completely stuck! to be honest all this encrypting and decrypting is quite confusing to say the least and could really do with some more help...the iv length is set at 32 which is obviously too shore or too long...where am i going wrong here please? here is all my code that i am using for this test.js var aes = require('./aes'), master_key = "5e2a0626516f3108e55e25e4bb6a62835c2f5d2b2b8d194c9acca63ef8beff6bfb947233bd83cfda9021e5a80bc183bcd835180c9955b733fd1a6d9d"; var decrypt = aes.decrypt("yUB2jQe88yWT2yUNHstMCw==", "2a3dc736bc316e9a20566b9a108eb23a", master_key); console.log('decrypt', decrypt); var encrypted = aes.encrypt('destramic', master_key); console.log('encrypt', encrypted); // array from aes.php encrypted and decrypted. //Array //( // [0] => Array // ( // [name] => yUB2jQe88yWT2yUNHstMCw== // [name_init_vector] => 2a3dc736bc316e9a20566b9a108eb23a // [age] => PjDxZq2x5IrhsLcqaJd5JQ== // [age_init_vector] => 01b53388ddcd8352db17e30f44e9b9e8 // ) // // [1] => Array // ( // [name] => LRxu9zAdlkSgS+wmyi4PrQ== // [name_init_vector] => 1c3d3bf95168ab025ca5c63ec3926313 // [age] => ys6yPHqjtutq/PJ5G3r0FQ== // [age_init_vector] => f19b6975eece8d8995e86b0ee6a33569 // ) // //) //Array //( // [0] => Array // ( // [name] => destramic // [age] => 28 // ) // // [1] => Array // ( // [name] => alan // [age] => 99 // ) // //) aes.js - where my problem ara... var crypto = require('crypto'), encryption_algorithem = 'AES-128-CBC'; module.exports = { encrypt: function (text, master_key){ try { var init_vector = new Buffer(crypto.randomBytes(32)); ciphertext = crypto.createCipheriv(encryption_algorithem, master_key, init_vector); init_vector = init_vector.update('hex'); return {'ciphertext' : ciphertext, 'init_vector' : init_vector }; }catch(error){ console.log(error); return null; } }, decrypt: function (ciphertext, init_vector, master_key){ try { var decipher = crypto.createDecipheriv(encryption_algorithem, master_key, init_vector), decrypted = decipher.update(ciphertext, 'bin'); return decrypted; }catch(error){ console.log(error); return null; } } }; aes.php <?php const ENCRYPTION_ALGORITHM = 'AES-128-CBC'; class AES { private $_master_key; public function __construct() { $this->_master_key = "5e2a0626516f3108e55e25e4bb6a62835c2f5d2b2b8d194c9acca63ef8beff6bfb947233bd83cfda9021e5a80bc183bcd835180c9955b733fd1a6d9d"; } public function generate_master_key($length = 64) { if (!is_numeric($length)) { return null; } $max_attempts = 10; $attempts = 0; do { $bytes = openssl_random_pseudo_bytes($length, $cryptographically_strong); $attempts++; } while (!$cryptographically_strong && $attempts < $max_attempts); if (!$cryptographically_strong) { return false; } $hex = bin2hex($bytes); return $hex; } public function encrypt($value, $master_key) { $init_vector = openssl_random_pseudo_bytes(openssl_cipher_iv_length(ENCRYPTION_ALGORITHM)); $ciphertext = openssl_encrypt($value, ENCRYPTION_ALGORITHM, $master_key, false, $init_vector); return array( 'init_vector' => bin2hex($init_vector), 'ciphertext' => $ciphertext ); } public function decrypt($ciphertext, $init_vector, $master_key) { $plaintext = openssl_decrypt($ciphertext, ENCRYPTION_ALGORITHM, $master_key, false, hex2bin($init_vector)); return $plaintext; } public function encrypt_array($array) { $encrypted_array = array(); $master_key = $this->_master_key; foreach ($array as $key => $data) { foreach ($data as $column => $value) { $encryption = $this->encrypt($value, $master_key); $init_vector_column = $column . '_init_vector'; $encrypted_array[$key][$column] = $encryption['ciphertext']; $encrypted_array[$key][$init_vector_column] = $encryption['init_vector']; } } return $encrypted_array; } public function decrypt_array($array) { $decrypted_array = array(); $master_key = $this->_master_key; foreach ($array as $key => $data) { foreach ($data as $column => $value) { $init_vector = $column . '_init_vector'; if (array_key_exists($init_vector, $data)) { $init_vector = $data[$init_vector]; $decrypted_value = $this->decrypt($value, $init_vector, $master_key); $decrypted_array[$key][$column] = $decrypted_value; } } } return $decrypted_array; } } $aes = new AES; $data = array( array('name' => 'destramic', 'age' => '28'), array('name' => 'alan', 'age' => '99') ); $encryption = $aes->encrypt_array($data); print_r($encryption); $decryption = $aes->decrypt_array($encryption); print_r($decryption); i hope someone can educate me a bit more here...thank you

problem came to the spaces in the directory when executing my batch file here is how i fixed it. @ECHO OFF start "r:\localhost\nginx 1.8.1\nginx.exe" start "r:\localhost\php\7.0.3 nts\php-cgi.exe" -b 192.168.1.168:9000 -c "r:\localhost\php\7.0.3 nts\php.ini" ping 192.168.1.168 -n 1>NUL echo starting nginx echo . echo .. echo ... ping 192.168.1.168 >NUL EXIT

hey guys...i'm having a little bit of trouble installing php on a alternative driver...i'm using windows 10 and have unzipped php 7.0.3 in the folder: R:\localhost\php\7.0.3 nts normally i would install it in c:\php but doing it on a alternative drive i'm having trouble loading the extensions. in the php.ini i have taken out the semi colan on the extensions and added a extension directory like so: extension_dir = "R:/localhost/php/7.0.3 nts/ext/" extension=php_openssl.dll etc... and i aslo put in a system variable under paths ;R:\localhost\php\7.0.3 nts\ then when trying to run openssl_random_pseudo_bytes() i get an error: Uncaught Error: Call to undefined function openssl_random_pseudo_bytes() when checking phpinfo() i can see that php.ini path is: Configuration File (php.ini) Path: C:\Windows so my php.ini file ins't being loaded causing the extension not to work i use this batch file to run nginx and php which also executes the php.ini so i'm confued as to why its not working as it should @ECHO OFF start r:\localhost\"nginx 1.8.1"\nginx.exe start r:\localhost\php\"7.0.3 nts"\php-cgi.exe -b 192.168.1.168:9000 -c r:\localhost\php\"7.0.3 nts"\php.ini ping 127.0.0.1 -n 1>NUL echo starting nginx echo . echo .. echo ... ping 192.168.1.168 >NUL EXIT how can i get my php.ini file to load please? thanks guys

no that's just the example i did when you first tought me i'll install the module over the weeked and have a good go at it...thank you for your help!

hey guys i had some really good help off jacques1 a while back about encrypting/decrypting rows in my database via php...now as i've gone further down the line in my project i have stumbled across a problem i didnt forsee. i'm using nodejs which connects to my database for one of my pages...but the data is encryted...i need to also encrypt and decrypt the database rows server side via javascript this is how it's done via php: <?php const ENCRYPTION_ALGORITHM = 'AES-128-CBC'; class AES { private $_master_key; public function __construct() { $this->_master_key = "5e2a0626516f3108e55e25e4bb6a62835c2f5d2b2b8d194c9acca63ef8beff6bfb947233bd83cfda9021e5a80bc183bcd835180c9955b733fd1a6d9d"; } public function generate_master_key($length = 60) { if (!is_numeric($length)) { return null; } $max_attempts = 10; $attempts = 0; do { $bytes = openssl_random_pseudo_bytes($length, $cryptographically_strong); $attempts++; } while (!$cryptographically_strong && $attempts < $max_attempts); if (!$cryptographically_strong) { return false; } $hex = bin2hex($bytes); return $hex; } public function encrypt($value, $master_key) { $init_vector = openssl_random_pseudo_bytes(openssl_cipher_iv_length(ENCRYPTION_ALGORITHM)); $ciphertext = openssl_encrypt($value, ENCRYPTION_ALGORITHM, $master_key, false, $init_vector); return array( 'init_vector' => $init_vector, 'ciphertext' => $ciphertext ); } public function decrypt($ciphertext, $init_vector, $master_key) { $plaintext = openssl_decrypt($ciphertext, ENCRYPTION_ALGORITHM, $master_key, false, $init_vector); return $plaintext; } public function encrypt_array($array) { $encrypted_array = array(); $master_key = $this->_master_key; foreach ($array as $key => $data) { foreach ($data as $column => $value) { $encryption = $this->encrypt($value, $master_key); $init_vector_column = $column . '_init_vector'; $encrypted_array[$key][$column] = $encryption['ciphertext']; $encrypted_array[$key][$init_vector_column] = $encryption['init_vector']; } } return $encrypted_array; } public function decrypt_array($array) { $decrypted_array = array(); $master_key = $this->_master_key; foreach ($array as $key => $data) { foreach ($data as $column => $value) { $init_vector = $column . '_init_vector'; if (array_key_exists($init_vector, $data)) { $init_vector = $data[$init_vector]; $decrypted_value = $this->decrypt($value, $init_vector, $master_key); $decrypted_array[$key][$column] = $decrypted_value; } } } return $decrypted_array; } } $aes = new AES; $data = array( array('name' => 'destramic', 'age' => '28'), array('name' => 'alan', 'age' => '99') ); $encryption = $aes->encrypt_array($data); print_r($encryption); $decryption = $aes->decrypt_array($encryption); print_r($decryption); can anyone please point me in the right direction on how i can achieve this please? (if even possible) thank you!

yeah join the attributes table twice filtering out the items that way seems the only way possible...thanks for the inner join advice. thank you for all your posts in this painful thread guys haha

SELECT DISTINCT(i.item_id), i.title FROM items i LEFT JOIN item_attributes ia ON ia.item_id = i.item_id LEFT JOIN item_attributes ia2 ON ia2.item_id = i.item_id WHERE ia.attribute = 'Clock Type' AND ia.value = 'Wall Clock' AND ia2.attribute = 'Condition' AND ia2.value = 'New' AND MATCH (i.title, i.description) AGAINST ('clock' IN BOOLEAN MODE) how about this?

yeah my plan is to design a auction application...i apologizes cause after re-assessing my thinking and design (thanks to you guys, sorry!)...what i was trying to do wasn't what i really needed. what i was trying to achieve is when a user adds an item, the user will add item attributes (specifics) for that item...ie. manufacturer, color, etc... so...i made a table called item_attributes which contains all attributes of the item specified by the selling user. item_attributes -------------------------- item_attribute_id item_id attribute value ----------------------- and with a simple query like so i was able to get the items i want by the attributes i want... SELECT i.title FROM items i RIGHT JOIN (SELECT item_id FROM item_attributes WHERE attribute IN ('Clock Type') AND value IN ('Wall Clock')) ai ON ai.item_id = i.item_id RIGHT JOIN (SELECT item_id FROM item_attributes WHERE attribute IN ('Condition') AND value IN ('New')) ai2 ON ai2.item_id = ai.item_id IN not really needed as i could just use WHERE the specifications i be banging on about throughout this painful thread is so i can add attributes (specifications) to certain categories, sub categories etc.. so when the user comes to the add item page the specifications will appear depending on categories selected....when they submit item and attributes it all gets added to the database what i've done here seems logical? thank you for your time and patience

ok well i back to the drawing board it is...any suggestions are welcome thank you all

i understand that like i said i stripped tables before dumping...i still believe there is a away for me to do this...i come across the IN() function which seems to be what i need i used it in my query like so: SELECT DISTINCT(i.item_id), i.title FROM items i RIGHT JOIN category_specifics cs ON cs.category_id = i.category_id RIGHT JOIN sub_category_specifics scs ON scs.sub_category_id = i.sub_category_id RIGHT JOIN (SELECT s2.specific_id FROM specifics s2 LEFT JOIN category_specifics cs2 ON cs2.specific_id = s2.specific_id LEFT JOIN sub_category_specifics scs2 ON scs2.specific_id = s2.specific_id ) s ON s.specific_id = cs.specific_id OR s.specific_id = scs.specific_id RIGHT JOIN specific_values sv ON sv.specific_id = s.specific_id RIGHT JOIN item_specific_values isv ON isv.specific_value_id = sv.specific_value_id WHERE MATCH (i.title) AGAINST ('clock' IN BOOLEAN MODE) AND isv.item_id = i.item_id AND (sv.value IN ('Alarm Clock', 'New') AND sv.specific_id IN (15, 19)) GROUP BY isv.specific_value_id clock 1 AND (sv.value IN ('Alarm Clock', 'New') AND sv.specific_id IN (15, 19)) clock 2 AND (sv.value IN ('Wall Clock', 'Used') AND sv.specific_id IN (15, 19)) when searching for specifics match clock 3 - clock 3 and clock 1 show AND (sv.value IN ('Cuckoo Clock', 'New') AND sv.specific_id IN (15, 19)) doesn't return clock 4 as it should AND (sv.value IN ('Alarm Clock', 'Used') AND sv.specific_id IN (15, 19)) doesn't return clock 5 as it should AND (sv.value IN ('Wall Clock', 'Used') AND sv.specific_id IN (15, 19)) please tell me i'm on to something here?

yeah i know the database schema isnt good but the one you have is stripped bare just to get the query can data that i want...no forgien keys, index etc....even stripped data as cats, sub cats have thousands of entries. as show here it displays what specific values are given to an item and the specific id's http://s30.postimg.org/v1y8370m9/Untitled_2.gif for instance i'd like to create a query where i can select any items where the sv.value = 'Alarm Clock' AND s.specific_id = 15 as well as the sv.value = 'New' AND s.specific_id = 19 retrieving clock 1 or any of them that match the specific values and id's hope you can help and understand what it is im trying to do

ok well i've stripper everything back to basics...here is the database dumb (no foreign keys ect etc) : http://pastebin.com/J3chVrQu this is how i designed my table relationship: lines on picture doesn't point to column that they relate to but you can gather from column name....each category has different specifics that specific having values linked to the item via the item_specific_values hope this a lot clearer for you guys now sql so far as we know: SELECT i.title FROM items i LEFT JOIN category_specifics cs ON cs.category_id = i.category_id LEFT JOIN sub_category_specifics scs ON scs.sub_category_id = i.sub_category_id INNER JOIN (SELECT s2.specific_id FROM specifics s2 LEFT JOIN category_specifics cs2 ON cs2.specific_id = s2.specific_id LEFT JOIN sub_category_specifics scs2 ON scs2.specific_id = s2.specific_id ) s ON s.specific_id = cs.specific_id OR s.specific_id = scs.specific_id LEFT JOIN specific_values sv ON sv.specific_id = s.specific_id RIGHT JOIN item_specific_values isv ON isv.item_id = i.item_id WHERE MATCH (i.title, i.description) AGAINST ('clock' IN BOOLEAN MODE) AND isv.specific_value_id = sv.specific_value_id AND cs.category_id = 1 AND scs.sub_category_id = 4 AND ( (sv.value = 'Alarm Clock' AND s.specific_id = 15) OR (sv.value = 'New' AND s.specific_id = 19) ) GROUP BY i.item_id what im trying to achieve is to retrieve an item by adding multiple conditions to the specific value and specific id please ask if you need any more information....thank for your help much appreciated

haha....ok i understand well the results i have returned from the or statement isn't what i was after as its returning sv.values on alarm clock and new....and i want to select sv.values with both conditions only....if i can explain what is it i'm trying to do with this picture please... clock 1 has a sv.value of alarm clock and new, with s.specific_id 15 and 19 how am i able to get this item with those exact conditions? to only return clock 1...is it possible?...is the way I've created my relationship structure or table incorrect? sorry to be a pain but i'm struggling to do what it is im trying to do thank you for your patience

yeah thats the problem there are records there which match my conditions but just aren't showing here i used group_concat with the conditions to what values im dealing with: so i know i have specific_id's of 15 and 19 and values of alarm clock and new... doesn't make sense to me why when i use my query nothing shows what am i missing here? =/ SELECT i.title FROM items i LEFT JOIN category_specifics cs ON cs.category_id = i.category_id LEFT JOIN sub_category_specifics scs ON scs.sub_category_id = i.sub_category_id LEFT JOIN sub_sub_category_specifics sscs ON sscs.sub_sub_category_id = i.sub_sub_category_id LEFT JOIN sub_sub_sub_category_specifics ssscs ON ssscs.sub_sub_sub_category_id = i.sub_sub_sub_category_id INNER JOIN (SELECT s2.specific_id FROM specifics s2 LEFT JOIN category_specifics cs2 ON cs2.specific_id = s2.specific_id LEFT JOIN sub_category_specifics scs2 ON scs2.specific_id = s2.specific_id LEFT JOIN sub_sub_category_specifics sscs2 ON sscs2.specific_id = s2.specific_id LEFT JOIN sub_sub_sub_category_specifics ssscs2 ON ssscs2.specific_id = s2.specific_id ) s ON s.specific_id = cs.specific_id OR s.specific_id = scs.specific_id OR s.specific_id = sscs.specific_id OR s.specific_id = ssscs.specific_id LEFT JOIN specific_values sv ON sv.specific_id = s.specific_id RIGHT JOIN item_specific_values isv ON isv.item_id = i.item_id WHERE MATCH (i.title, i.description) AGAINST ('clock' IN BOOLEAN MODE) AND isv.specific_value_id = sv.specific_value_id AND ( (sv.value = 'Alarm Clock' AND s.specific_id = 15) AND (sv.value = 'New' AND s.specific_id = 19) ) GROUP BY i.item_id

this query and table structure is quite complicated...for me anyways... i've made changes as you've said: SELECT i.title FROM items i LEFT JOIN category_specifics cs ON cs.category_id = i.category_id LEFT JOIN sub_category_specifics scs ON scs.sub_category_id = i.sub_category_id LEFT JOIN sub_sub_category_specifics sscs ON sscs.sub_sub_category_id = i.sub_sub_category_id LEFT JOIN sub_sub_sub_category_specifics ssscs ON ssscs.sub_sub_sub_category_id = i.sub_sub_sub_category_id INNER JOIN (SELECT s2.specific_id FROM specifics s2 LEFT JOIN category_specifics cs2 ON cs2.specific_id = s2.specific_id LEFT JOIN sub_category_specifics scs2 ON scs2.specific_id = s2.specific_id LEFT JOIN sub_sub_category_specifics sscs2 ON sscs2.specific_id = s2.specific_id LEFT JOIN sub_sub_sub_category_specifics ssscs2 ON ssscs2.specific_id = s2.specific_id ) s ON s.specific_id = cs.specific_id OR s.specific_id = scs.specific_id OR s.specific_id = sscs.specific_id OR s.specific_id = ssscs.specific_id LEFT JOIN specific_values sv ON sv.specific_id = s.specific_id RIGHT JOIN item_specific_values isv ON isv.item_id = i.item_id WHERE MATCH (i.title, i.description) AGAINST ('clock' IN BOOLEAN MODE) AND isv.specific_value_id = sv.specific_value_id AND ( (sv.value = 'Alarm Clock' AND s.specific_id = '15') AND (sv.value = 'Used' AND s.specific_id = '19') ) GROUP BY i.item_id i need AND ( (sv.value = 'Alarm Clock' AND s.specific_id = '15') AND (sv.value = 'Used' AND s.specific_id = '19') ) not OR as i want to match items with both specific value and id. query returns no results with both sv.value's and s.specific_id's but returns singularly. i've checked my data inserted into tables which looks fine =/