Destramic

October 3, 2015

i'm wanting to check if the web-socket is open...i'm using a xml http request...unless there's is a better way you can suggest

using the code below returns undefined

function web_socket_open(href){
	    var request = new XMLHttpRequest(href);
	    
	    request.onreadystatechange = function() { 
	        if (request.readyState === 4 && request.status === 200) {
	        	   return true;
	        }

	        return false;
	    }  
	}

	console.log(web_socket_open('http://127.0.0.1:8080'));

what am i doing wrong here please?

thank you

October 3, 2015

thank you....flex works well although i'm unable to to get the ul into 3 rows.

http://jsfiddle.net/destramic/ztdpou8w/8/

i used:

li .row-two{
	order: 2;
}

but all li's appear inline

how can i achieve a break please?

thank you

October 2, 2015

hey guys im trying to make grid for a image cropper script im making...but im having a bit of trouble getting it to work/look the way i want it to. i'm not the best when it comes to css.

you can see my grid here:

https://jsfiddle.net/destramic/ztdpou8w/

the problem i have is where you stretch the grid too much or shrink it too small everything goes out of place...i'm more concerned about the border separation when stretching.

how can i change size of grid but also making it looking consistent please?

thank you

September 18, 2015

here's a tutorial i found which seems decent enough to get you started:

http://www.inmotionhosting.com/support/edu/website-design/using-php-and-mysql/php-insert-database

if you need any help post in relevant section.

welcome to phpfreaks

September 18, 2015

do you mean cache?

any you array should be:

$test = array(
    'dog', 
    'cow', 
    'shee'
);

or are you using a template engine like smarty or some other nonsense?

September 17, 2015

a lot of work still to be done here but just in case someone else is learning how to encrypt db details i now have added a method to encrypt and decrypt array (mysql rows)

ensuring the column name init vector is called : {column name}_init_vector

<?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);

:happy-04: :happy-04: :happy-04:

September 17, 2015

ok brilliant...i'll stick with just the one key then...umm and as for sql injection this is just a test i've got all that sorted...but thanks for pointing out

September 17, 2015

brilliant thank you

i've changed everything you've told me :happy-04:

the only thing i may be a bit concerned about is storing the master keys inside another table which is relationship with the users table by the user_id

but it works brilliantly....i can use this on users address and even sessions.

<?php

const ENCRYPTION_ALGORITHM = 'AES-128-CBC';

class AES
{
    public function 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;
    }
}

$aes = new AES;
$server_master_key = $aes->master_key();

// destramic
// ricky
$name = "destramic";
$encryption = $aes->encrypt($name, $server_master_key);

//print_r($encryption);

//echo $aes->decrypt($encryption['ciphertext'], $encryption['init_vector'], $server_master_key);


// mysql stuff

$link = mysqli_connect("127.0.0.1", "root", "root", "test");

//$insert = "INSERT INTO `users`(`name`, `name_init_vector`)
 //          VALUES ('" . $encryption['ciphertext'] ."', '" . $encryption['init_vector'] . "')";


//$result  = mysqli_query($link, $insert);
//$user_id = mysqli_insert_id($link);

//$insert_key = "INSERT INTO `encryption_keys` (`encryption_key`, `user_id`)
//               VALUES ('" . $server_master_key ."', '" . $user_id . "')";

//$result = mysqli_query($link, $insert_key);

$select = "SELECT u.name,
                  u.name_init_vector,
                  ek.encryption_key
          FROM users u
          LEFT JOIN encryption_keys ek ON ek.user_id = u.user_id";
$result = mysqli_query($link, $select);
$rows = mysqli_fetch_all($result, MYSQLI_ASSOC);

//print_r($rows);

$decrypted_rows = array();

foreach ($rows as $key => $array)
{
    foreach ($array as $column => $value)
    {
        $init_vector = $column . '_init_vector';
        
        if (array_key_exists($init_vector, $array) &&
            array_key_exists('encryption_key', $array))
        {
            $init_vector = $array[$init_vector];
            $master_key  = $array['encryption_key'];
            
            $decrypted_value               = $aes->decrypt($value, $init_vector, $master_key);
            $decrypted_rows[$key][$column] = $decrypted_value;
        }
    }
}

print_r($decrypted_rows);

can't honestly say how much i appropriate yours and the other guys time and help on here!!!

September 17, 2015

yep i'm with you...thank you for your post

although how do i use AES_CBC_DECRYPT() and AES_CBC_DECRYPT()?

i have the extension php_openssl.dll included but i get

Fatal error: Call to undefined function AES_CBC_ENCRYPT() in C:\nginx\html\aes.class.php on line 34

also is openssl_random_pseudo_bytes() fine for the 16 random bytes?

thank you

September 16, 2015

i've been reading up about security for users data in my db come across aes (hopefully im on the right track for good security?).

now i may be over complicating things (as usual) but when a user creates a account i was planning on creating a mysql_aes_key with the users password (after it has been password hashed) creating a user content key

then to use my encrypt method to encrypt the user content key with a random master key, creating the user content key encryption

also i wanted to create a master key for each user inside a table as well as storing the user content key in the users db row.

here is what i've come up with or sourced should i say:

<?php

class AES
{
    public function random_string($length = 60)
    {
        if (!is_numeric($length))
        {
            return null;
        }
    
        $bytes = openssl_random_pseudo_bytes($length, $cryptographically_strong);
        $hex   = bin2hex($bytes);
    
        if (!$cryptographically_strong)
        {
            $this->random_string($length);
        }
    
        return $hex;
    }
    
    public function mysql_aes_key($key)
    {
    	$new_key = str_repeat(chr(0), 16);
    	
    	for($i=0,$len=strlen($key);$i<$len;$i++)
    	{
    		$new_key[$i%16] = $new_key[$i%16] ^ $key[$i];
    	}
    	
    	return $new_key;
    }
    
    public function encrypt($val, $key)
    {
    $key = $this->mysql_aes_key($key);
	$pad_value = 16-(strlen($val) % 16);
	$val = str_pad($val, (16*(floor(strlen($val) / 16)+1)), chr($pad_value));
	 $encryption = mcrypt_encrypt(MCRYPT_RIJNDAEL_128, $key, $val, MCRYPT_MODE_ECB, mcrypt_create_iv( mcrypt_get_iv_size(MCRYPT_RIJNDAEL_128, MCRYPT_MODE_ECB), MCRYPT_DEV_URANDOM));
     return $encryption;
    }
    
    public function decrypt($val, $key)
    {
    	$key = $this->mysql_aes_key($key);
	   $val = mcrypt_decrypt(MCRYPT_RIJNDAEL_128, $key, $val, MCRYPT_MODE_ECB, mcrypt_create_iv( mcrypt_get_iv_size(MCRYPT_RIJNDAEL_128, MCRYPT_MODE_ECB), MCRYPT_DEV_URANDOM));
	   return rtrim($val);
    }
}


$master_key       = "354fdedcc54d356bf681571e3dcacf73dd818656ffddc779d33b925e0b3f32fae01642fe7719bb9504c80de4f4193933f36948770f2ac832b5364514";
$users_password   = "helloworldthisismypassword";


$aes = new AES;
// $aes->random_string(); // master key
$user_content_key = $aes->mysql_aes_key($users_password);

$user_content_key_encrypted = $aes->encrypt($user_content_key, $master_key);

//echo $a = $aes->encrypt('ricky', $user_content_key);
//echo $aes->decrypt($a, $user_content_key);

// inserts and selects perfect
    
    
$link = mysqli_connect("127.0.0.1", "root", "root", "test");
$insert = "INSERT INTO `users`(`password`, `encryption_key`, `name`, `age`) 
           VALUES ('" . $users_password."','" . $user_content_key . "',AES_ENCRYPT('ricky', '" . $user_content_key_encrypted . "'),AES_ENCRYPT('28', '" . $user_content_key_encrypted . "'))";
//$result = mysqli_query($link, $insert);

$select = "SELECT AES_DECRYPT(name, '" . $user_content_key_encrypted . "') AS name,
                  AES_DECRYPT(age, '" . $user_content_key_encrypted . "') AS age
          FROM users";
$result = mysqli_query($link, $select);
$rows = mysqli_fetch_array($result);

//print_r($rows);


// select 2

$select2 = "SELECT @content_key := AES_ENCRYPT(u.encryption_key, ek.encryption_key),
                   AES_DECRYPT(u.name, @content_key) as `name`
            FROM users u
            LEFT JOIN encryption_keys ek ON ek.user_id = u.user_id";

$result2 = mysqli_query($link, $select2);
$rows2 = mysqli_fetch_array($result2);

//print_r($rows2);

inserting a user and selecting works fine...but im not sure if what im trying to do on my 2nd select query is secure (although it doesnt work)....but i would like to do something like this:

SELECT @content_key := AES_ENCRYPT(u.encryption_key, ek.encryption_key),
                   AES_DECRYPT(u.name, @content_key) as `name`
            FROM users u
            LEFT JOIN encryption_keys ek ON ek.user_id = u.user_id

am i on the right track with what im trying to do here please guys?...advise would be great thank you

September 14, 2015

thats the plan

got this for data...so i can cache...the json for one page is like 4000 rows so caching it should save some memory and cpu

<?php

namespace Utility;

use \Exception as Exception;
define('DS', DIRECTORY_SEPARATOR);

class Cache 
{    
    protected $_cache_directory = "C:\\nginx\html\cache\\";
    protected $_lifetime        = 108000;
    protected $_extension       = ".txt";
    protected $_extensions      = array('cache', 'txt', 'csv');    
    public $_filename;
 
    public function save($data, $san = true)
    {
        if (is_null($data))
        {
            return false;
        }        
        
        $filename = $this->get_filename();
        $file     = pathinfo($filename);

        try 
        {
            if (!$this->make_directory($file['dirname']))
            {
                throw new Exception('');
            }
        }
        catch (Exception $exception)
        {
            
        }
        
        $extension = $file['extension'];
        $file      = fopen($filename, "w+");
        
        if ($extension === 'csv')
        { 
             $start = true;
            
            foreach ($data as $csv_data) 
            {
                if ($start) 
                {
                    $keys  = array_keys($csv_data);
                    $start = false;
                    
                    fputcsv($file, $keys);
                }
                
                fputcsv($file, $csv_data);
            }
        }
        else
        {
            fwrite($file, $data);
        }
        
        fclose($file);
        
        $lifetime = time() + $this->get_lifetime();
        touch($filename, $lifetime);
    }
    
    public function load($filename = null)
    {
        if (is_null($filename))
        {
            $filename = $this->get_filename();
        }
        
        if (!file_exists($filename))
        {
            return false;
        }
        
        $file      = pathinfo($filename);
        $extension = $file['extension'];
        
        if ($extension === 'csv')
        {
            $start   = true;
            $headers = array();
            $data    = array();
            
            $file = fopen($filename,'r');
            
            while(($csv_data = fgetcsv($file)) !== false)
            {
                if($start)
                {
                    $headers = array_values($csv_data);
                    $start   = false;
                    
                    continue;
                }
             
                $data[] = array_combine($headers, $csv_data);
            }
            
            fclose($file);
        }
        else
        {
            $data = file_get_contents($filename);
        }

        return $data;
    }
    
    public function remove($filename = null)
    {
        if (is_null($filename))
        {
            return false;
        }
        
        if(!file_exists($filename))
        {
            return false;
        } 
              
        unlink($filename);
        
        return true;
    }
    
    public function is_cached($filename = null)
    {  
        if (is_null($filename))
        {
           return false;
        }
       
        echo $filename = $this->format_filename($filename);
        $this->set_filename($filename);
        
        if (!file_exists($filename))
        {
            return false;
        }

         
        $time = filemtime($filename);
       
        if ($time < time())
        {
            $this->remove($filename);
            return false;
        }
        
        return true;
    }
    
    public function remove_all()
    {
        $files = glob($this->get_cache_directory() . "*");
        
        foreach($files as $file)
        { 
            if(is_file($file))
            {
                unlink($file);
            }
        }
    }
       
    protected function format_filename($filename)
    {
        $filename = $this->get_cache_directory() . $filename;
        
        if (DS === '/')
        {
            $replace = "\\";
        }
        else
        {
            $replace = "/";
        }
             
        if (strpos($filename, $replace))
        {
            $filename = str_replace($replace, DS, $filename);
        }
       
        if (strpos($filename, DS.DS))
        {
            $filename = str_replace(DS.DS, DS, $filename);
        }
        
        $info = pathinfo($filename);

        if (!in_array($info['extension'], $this->_extensions))
        {
            $filename = $info['dirname'] . DS . $info['filename'] . $this->_extension;
        }
        
        return $filename;
    }

    protected function make_directory($directory)
    {
        if (!is_dir($directory))
        {
            mkdir($directory, 0755, true);
            
            return true;
        }
        
        return false;
    }
    
    protected function get_cache_directory()
    {
        return $this->_cache_directory;
    }
        
    protected function set_filename($filename)
    {
        $this->_filename = $filename;
    }
    
    protected function get_filename()
    {
        return $this->_filename;
    }
    
    public function extension($extension)
    {
        if (in_array($extension, $this->_extensions))
        {
            $this->_extension = '.' . $extension;
        }
    }
    
    public function lifetime($lifetime, $period = "days")
    {
        $period = strtolower($period);
        
        switch($period)
        {
            case 'seconds':
                break;
        
            case 'minutes':
                $lifetime = $lifetime * 60;
                break;
        
            case 'hours':
                $lifetime = $lifetime * 60 * 60;
                break;
        
            default:
            case 'days':
                $lifetime = $lifetime * 60 * 60 * 24;
                break;
        
            case 'weeks':
                $lifetime = $lifetime * 60 * 60 * 24 * 7;
                break;
        
            case 'months':
                $lifetime = $lifetime * 60 * 60 * 24 * 30;
                break;
        
            case 'years':
                $lifetime = $lifetime * 60 * 60 * 24 * 365;
                break;
        }
            
        $this->_lifetime = $lifetime;
    }
    
    protected function get_lifetime()
    {
        return $this->_lifetime;
    }
}

$cache = new Cache;


if ($cache->is_cached('json/categories.csv'))
{
    print_r($cache->load());
}
else
{
    // database shizzle
    $data = array(array('name' => 'ricky', 'age' => '28'));
    $cache->save($data);
}   
 ?>

September 14, 2015

thank you guys...i was trying to over complicate things ie. trying to csv a array like so and return it

array('1', '2', '2', array('5', '6', '7'))

but all i really needed it for was db rows!

but thank you guys...just what i needed....nice a simple...dunno why i tried over thinking a script for something i don't need

:happy-04:

September 13, 2015

hey guys can anyone point me to a good script/tutorial which can convert a singular/multidimensional array into csv please?

also i'd want to be able to convert back from csv to php array also...be happy if someone could point me into the right direction...thank you

September 13, 2015

ok well this is embarrassing my json isn't valid

basically i have 3 columns like so:

"name":"10"      Singles"

10" Singles - but because of the quote make which represents inch, json fails.

strip slashes worked like a charm....thank you

September 13, 2015

ok i put my json data in the meta tag

and got it via js

$("meta[name='sub-sub-categories']").attr('content');

now i have the same problem...i get only 2500 rows and then it cutts off =/

September 13, 2015

well the data originally comes from the database then i use

	public function encode($data = array())
	{
	    $utf8 = new UTF8();
	    $data = $utf8->encode($data);
	    
		$json = json_encode($data);
        $json = htmlentities(stripslashes($json), ENT_QUOTES);

        return $json;
	}

to encode to json so i can use the data via js....and i call the data like so:

sub_category.data('sub-categories');

the after encoded the json looks like a perfect json string....but when added to the data attribute it only has about 3/4 of the data and not formed correctly

September 12, 2015

hey guys i have a data attribute with a json string of about 291478 characters long so far and counting...but im thinking the data attribute has a max length (although im unable to find any details on this so far) as my json string gets cut off 3/4's of the way through

when i echo string outside data attribute it shows perfectly fine...does anyone know the max length?

also i need to find somewhere else to store this json string...the data attribute had been perfect up until now!

September 12, 2015

ok barand i'll give it a whirl...been busy reconstructing my database after all your advise...hopefully be able to play around with query again tomorrow

September 6, 2015

yes its only is to produce one row by i.item_id

September 5, 2015

with a lot of playing about i've narrowed it down to these columns causing the problem

(i.quantity - count(ip.item_id)) AS `quantity_available`,
COUNT(ip.item_id) AS `quantity_sold`,
io2.quantity AS `quantity_offer`,
COUNT(DISTINCT io.item_offer_id) AS `offer_count`,
CONCAT(FORMAT(((SUM(uf.positive + uf.neutral) - uf.negative) / count(uf.user_feedback_id) * 100), 2), '%') AS `sellers_feedback_percentage`,
ROUND(SUM((uf.positive * 1) + (uf.neutral * 0.5)) - (uf.negative * 1), 0) AS `sellers_feedback_score`,
COUNT(DISTINCT b.bid_id) AS `bid_count`,
COUNT(DISTINCT uiw.user_item_wish_id) AS `wish_count`,
COUNT(DISTINCT uiwa.user_item_watch_id) AS `watch_count`,

when these lines are taken away from my query i get the correct results...why when i add them the highest bidder changes?

thank you

September 4, 2015

no views? $:-\$

September 2, 2015

hey guys i've made alterations to my query below, previously it had a lot of sub queries which was unnecessary as it can all be achieved by a join instead.

but this query brings back the wrong highest_bidder and highest_bidders_id which is brought from my bid and users table

if i do a simple query like this:


SELECT b.bid_id,
u.user_id, u.username, b.price
FROM bids b
LEFT JOIN users u ON u.user_id = b.user_id
ORDER BY b.price DESC,
         b.created_timestamp DESC,
        b.bid_id DESC
LIMIT 1

than i get the correct results as i should with this big query below.

SELECT i.item_id,
                         i.user_id,
				         i.title,
				         i.buy_now,
				         i.auction,
                         i.condition,
                         i.offers_accepted,
                         i.free_delivery,
                         i.free_delivery_condition,
                         i.collection,
                         i.collection_only,
		                 i.p_and_p,
                         i.buy_now_price,
                         i.quantity,
                         i.description,
                         (i.quantity - count(ip.item_id)) AS `quantity_available`,
                         COUNT(ip.item_id) AS `quantity_sold`,
                         io2.offer_price,
                         u.username AS `seller`,
                         DATE_FORMAT(u.timestamp, '%d/%m/%y') AS `member_since`,
                         @bid_increment := bi.increment AS `bid_increment`,
                         @current_auction_price := IF (b.price IS NULL, i.starting_price, b.price) AS `current_auction_price`,
                         TRUNCATE((@current_auction_price + @bid_increment), 2) AS `minimum_bid`,
                         ua.town_city,
                         ua.country,
                         ua.continent,
                         u2.username AS `highest_bidder`,
                         u2.user_id AS `highest_bidder_id`,
                         io2.quantity AS `quantity_offer`,
                         LOWER(c.country_code) AS `sellers_country_code`,
                         COUNT(DISTINCT io.item_offer_id) AS `offer_count`,
                         CONCAT(FORMAT(((SUM(uf.positive + uf.neutral) - uf.negative) / count(uf.user_feedback_id) * 100), 2), '%') AS `sellers_feedback_percentage`,
				         ROUND(SUM((uf.positive * 1) + (uf.neutral * 0.5)) - (uf.negative * 1), 0) AS `sellers_feedback_score`,
                         COUNT(DISTINCT b.bid_id) AS `bid_count`,
                         COUNT(DISTINCT uiw.user_item_wish_id) AS `wish_count`,
                         COUNT(DISTINCT uiwa.user_item_watch_id) AS `watch_count`,
                         CONCAT_WS(', ', ua.town_city, ua.county, ua.country) AS `location`,
                         NOW() AS `server_time`,
				         @timestamp := CONVERT_TZ(DATE_ADD(i.start_timestamp, INTERVAL concat(i.listing_duration) DAY), '+00:00', '+00:00') AS `timestamp`,
                         DATE_FORMAT(@timestamp, '%D, %M at %h:%i %p') AS `end_timestamp`,
                         (SELECT COUNT(reported_item_id)
                          FROM reported_items
                          WHERE item_id = i.item_id
                          AND user_id = :user_id) AS `reported`,
                         (SELECT COUNT(item_id)
                          FROM user_item_wishes 
                          WHERE item_id = i.item_id
                          AND user_id = :user_id) AS `wished`,
                         (SELECT COUNT(item_id) 
                          FROM user_item_watches 
                          WHERE item_id = i.item_id
                          AND user_id = :user_id) AS `watched`,
                         (SELECT COUNT(user_follower_id) 
                          FROM user_followers
                          WHERE user_id = i.user_id
                          AND follower_id = :user_id) AS `followed`,
                         (SELECT COUNT(DISTINCT(ip_address)) 
                          FROM statistics 
                          WHERE DATE_ADD(visited, INTERVAL 2 HOUR) >= now()) AS `period_last_visited`,
                         (SELECT COUNT(DISTINCT(ip_address)) 
                          FROM statistics 
                          WHERE uri = '/item/1') AS `views`,
                          IF (i.start_timestamp >= now(), true, false) AS `ended`,
                          CASE WHEN (:latitude IS NOT NULL AND 
				                     :longitude IS NOT NULL AND
				                     u.latitude IS NOT NULL AND
				                     u.longitude IS NOT NULL)
				               THEN 
                                    @distance := (SELECT (IF(:distance_unit = 'Kilometers', 6371, 3959) *
                                                  2 * ASIN(SQRT(POWER(SIN((:latitude- u.latitude) *
                                                  pi()/180 / 2), 2) + COS(:latitude * 
		                                          pi()/180) * COS(u.latitude * pi()/180) * 
		                                          POWER(SIN((:longitude - u.longitude) * 
		                                          pi()/180 / 2), 2)))))
				         END,
                         @distance_unit := IF (@distance >= 1, IF (@distance < 2, REPLACE (:distance_unit, 's', ''), @distance_unit), :distance_unit),
				         IF (@distance, CONCAT(TRUNCATE(@distance, 0), space(1) , @distance_unit), 'Unknown Distance') AS `distance`
				  FROM items i
				  LEFT JOIN users u ON u.user_id = i.user_id
                  LEFT JOIN item_purchases ip ON ip.item_id = i.item_id
                  LEFT JOIN user_item_wishes uiw ON uiw.item_id = i.item_id
                  LEFT JOIN user_item_watches uiwa ON uiwa.item_id = i.item_id
                  LEFT JOIN user_addresses ua ON ua.user_id = i.user_id
                  LEFT JOIN item_addresses ia ON ia.user_address_id = ua.user_address_id
                  LEFT JOIN countries c ON c.country_name = ua.country
                  LEFT JOIN item_offers io ON io.item_id = i.item_id
                  LEFT JOIN users_feedback uf ON uf.user_id = i.user_id
                  LEFT JOIN item_offers io2 ON io2.user_id = i.user_id
                  LEFT JOIN bids b ON b.item_id = i.item_id
                  LEFT JOIN users u2 ON u2.user_id = b.user_id
                  LEFT JOIN bid_increments bi ON b.price BETWEEN bi.price_from AND bi.price_to
                  WHERE i.item_id = :item_id
                  AND ia.user_address_id = i.user_address_id
                  AND uf.seller = '1'
                  ORDER BY b.price DESC,
                           b.created_timestamp DESC,
                           b.bid_id DESC

the joins and the order by are correct so i dont understand why the query is producing the wrong highest bidder if anyone know why please?

also any general advice on this query, how i should do things would be greatly welcomed.

thank you

August 31, 2015

just what i'm after

thanks your for effort barand much appreciated

August 29, 2015

hey guys,

what im trying to do is bring back a result of either a increment or a price + an increment...in this query item_id 2 doesn't exist so i want to return the result of a increment.

if i change item_id 2 to 1 then i get the result of price + a increment as the item_id exists. is it possible to get a result of just the increment without doing another query if this query returns no row count please?

increments and bids have no relation

think i may be asking a impossible/stupid question :suicide:

thank you

SELECT
       CASE WHEN b.price IS NULL
       THEN (SELECT increment FROM bid_increments ORDER BY increment DESC LIMIT 1)
       ELSE (b.price + bi.increment)
       END AS `minimum_bid`                 
FROM bids b
JOIN bid_increments bi
WHERE b.item_id = 2 
AND b.price BETWEEN bi.price_from AND bi.price_to
ORDER BY b.created_timestamp DESC
LIMIT 1

August 16, 2015

i have a more basic question, why are you trying to use database based session data handling? using the default file based session data handling, insuring that you are storing the session data files in a location of your choosing and with permissions set so that only your hosting account can access the files, is adequate in all but a very few cases.

you also have a problem with database based session security if you are using the same database connection that your application code is using and ANY of your code allows sql injection, someone can grab all the session data. if the reason you are doing this is for a perceived security problem, to make this as secure as possible, you would need a separate database with a separate connection username that only has access to that database and you would want to encapsulate the database connection into your session handler so that no other code can use possibly it.

i hope my code protects me from sql injection and csfr...but you have a good point and i don't really have an answer as to why i chosen to use a database instead of the traditional way...just when i read up about it i thought it was a good idea...i'm currently in the middle of building a web server so i can run everything at home...so putting sessions onto a separate database is something i can consider for extra security....or like you said go back to the having sessions in a directory.

really appreciate your help guys.

although this answers my question about...i'll set a new database up for the sessions and can connect and close in the methods

Sign In

Destramic

Posts

Joined

Last visited

Content Type

Profiles

Forums

Posts posted by Destramic

xml http request

image cropper grid

image cropper grid

Hello freaks

how to pass an array to mysql stored procedure

aes

aes

aes

aes

aes

csv

csv

csv

data attribute

data attribute

data attribute

data attribute

order by

order by

order by

order by

order by

is it possible?

is it possible?

i must be missing something...

Browse

Activity

Important Information