Jump to content

Destramic

Members
  • Posts

    960
  • Joined

  • Last visited

Everything posted by Destramic

  1. 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
  2. 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
  3. 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
  4. 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
  5. 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
  6. 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
  7. 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!
  8. 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!
  9. 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
  10. 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?
  11. 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
  12. ok well i back to the drawing board it is...any suggestions are welcome thank you all
  13. 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?
  14. 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
  15. 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
  16. 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
  17. 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
  18. 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 =/
  19. hey guys I'm trying to join tables together but i want to be able to have multiple conditions for the same column. ie: WHERE sv.value = 'Alarm Clock' AND s.specific_id = '15' AND sv.value = 'New' AND s.specific_id = '19' now i know this is complicated as you can't see my table structure and table data but here is the query i'm trying to execute which returns 0 results when having multiple conditions from same column... 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 ) AS `s` LEFT JOIN specific_values sv ON sv.specific_id = s.specific_id LEFT 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 if i take away AND sv.value = 'Alarm Clock' AND s.specific_id = '15' -------OR --------- AND sv.value = 'New' AND s.specific_id = '19' then it'll works i'll be happy to post table structures and data if needed...hopefully you can see what i'm trying to do and tell me if it's possible or not and what it is i need to be doing. any help would be appreciated and like i said i can provide data if need. thank you
  20. removing DEFAULT CHARSET=utf16 worked a charm thanks barand
  21. thank you...ok i installed 5.7.10...but i still get same error when executing query =/
  22. hey guys im trying to set to columns as a full text index but im receiving an error: this is where the fault lies but i dunno why im getting the error...im using innodb engine for the table on mariadb 10 FULLTEXT `items_FULLTEXT` (`description`, `title`), this is the sql i'm executing which is failing....i know innodb now support fulltext so where am i going wrong here please? CREATE TABLE `items` ( `item_id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `category_id` int(11) NOT NULL, `sub_category_id` int(11) NOT NULL, `sub_sub_category_id` int(11) DEFAULT NULL, `sub_sub_sub_category_id` int(11) DEFAULT NULL, `user_address_id` int(11) DEFAULT NULL, `condition_id` int(11) DEFAULT NULL, `title` varchar(90) NOT NULL, `description` text NOT NULL, `auction` int(11) NOT NULL, `buy_now` tinyint(1) NOT NULL DEFAULT '0', `starting_price` decimal(15,2) NOT NULL, `listing_duration` enum('1','3','7','10','30') NOT NULL, `buy_now_price` decimal(15,2) NOT NULL, `quantity` int(11) NOT NULL DEFAULT '1', `offers_accepted` tinyint(1) NOT NULL DEFAULT '0', `start_timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `dispatch_time` enum('Same Day','1 Working Day','2 Working Days','3 Working Days') NOT NULL, `returns` int(1) NOT NULL DEFAULT '0', `return_policy` text, `free_delivery` int(1) DEFAULT '0', `free_delivery_condition` enum('Town/City','County','Country','Continent','MENA Region','Worldwide') DEFAULT NULL, `collection` int(1) DEFAULT '0', `collection_only` int(1) DEFAULT '0', `created` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `personal_delivery` int(1) DEFAULT '0', `personal_delivery_distance` int(10) DEFAULT '0', `persnal_delivery_price` decimal(12,2) DEFAULT '0.01', `draft` int(1) NOT NULL DEFAULT '0', PRIMARY KEY (`item_id`), FULLTEXT `items_FULLTEXT` (`description`, `title`), UNIQUE KEY `items_item_id_UNIQUE` (`item_id`), KEY `items_user_id_INDEX` (`user_id`), KEY `items_category_id_INDEX` (`category_id`), KEY `items_sub_category_id_INDEX` (`sub_category_id`), KEY `items_user_address_id_INDEX` (`user_address_id`), KEY `items_condition_id_INDEX` (`condition_id`), CONSTRAINT `items_category_id_FOREIGN_KEY` FOREIGN KEY (`category_id`) REFERENCES `categories` (`category_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `items_condition_id_FOREIGN_KEY` FOREIGN KEY (`condition_id`) REFERENCES `conditions` (`condition_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `items_sub_category_id_FOREIGN_KEY` FOREIGN KEY (`sub_category_id`) REFERENCES `sub_categories` (`sub_category_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `items_user_address_id_FOREIGN_KEY` FOREIGN KEY (`user_address_id`) REFERENCES `user_addresses` (`user_address_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `items_user_id_FOREIGN_KEY` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=1243 DEFAULT CHARSET=utf16; thank you
  23. i changed all ports to 80 and i managed to get it to work perfectly now....thank you guys for your help
  24. ok i turned off my windows firewall completely but im still getting no joy...if you could please help... here is the rule i did for my firewall opened port 8080 on my router followed by my nginx config containing 192.168.1.80 error_log logs/error.log; events { worker_connections 1024; } http { keepalive_timeout 300; proxy_read_timeout 300; proxy_connect_timeout 300; fastcgi_read_timeout 300; server { listen 8080; server_name 192.168.1.80; client_max_body_size 500M; index index.html index.htm index.php; root c:/nginx/html; location / { autoindex on; } location = /favicon.ico { log_not_found off; } location ~ .php$ { fastcgi_pass 127.0.0.1:9000; include fastcgi.conf; fastcgi_index index.php; fastcgi_read_timeout 300; } } } hope you can tell me where i've gone wrong? thank you
  25. yeah i tried ngrok but would like to do it so my ip can be used as the web address....ngrok created a url like so: http://29749e5e.ngrok.io
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.