Destramic

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

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

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

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 =/

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

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!

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

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

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

no views?

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

just what i'm after thanks your for effort barand much appreciated

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 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

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

thank you!...would of helped if i read first =/....what i said is what it was...sorry guys. a good idea about integrating my authentication to my framework though although this session business leaves me a slight problem still....how do i disconnect from the database?...ideally i want the disconnection to be the last script to be executed...only have one connection to my whole site...before i would connect and disconnect per query =/...i've even tried a destruct in my db class but nahh session handler plays up again. what would be my best approach here please?

ok i've almost solved the problem...although its all still a bit confusing here is the code i used to call the session_handler which is in my bootstrap public function initialize_session_handler() { echo "here"; $registry = new Registry; $db = $registry->db; $session_handler = new Session_DB_Handler; $session_handler->set_db_adapter($db); $session_handler->set_lifetime(1); session_set_save_handler( array($session_handler, 'open'), array($session_handler, 'close'), array($session_handler, 'read'), array($session_handler, 'write'), array($session_handler, 'destroy'), array($session_handler, 'clean') ); $session = new session; $session->start(); $session->wtf = "now"; echo "here"; } index.php where bootstap is called and initializes my methhods <?php define('DS', DIRECTORY_SEPARATOR); define('PARENT_DIRECTORY_PATH', dirname(dirname(__FILE__))); define('PUBLIC_DIRECTORY', PARENT_DIRECTORY_PATH . DS . 'public' . DS); define('PRIVATE_DIRECTORY', PARENT_DIRECTORY_PATH .DS . 'private' . DS ); use Config\Bootstrap as Bootstrap; require_once PRIVATE_DIRECTORY . 'library' . DS . 'utility' . DS . 'autoloader.class.php'; new Autoloader; $bootstrap = new Bootstrap; $bootstrap->config; $bootstrap->database->connect(); $bootstrap->headers; $bootstrap->timezone; $bootstrap->error_reporting; $bootstrap->error_handler; $bootstrap->session_handler; // session handler is loaded here $bootstrap->geoip; $bootstrap->authentication; //$bootstrap->statistics; $bootstrap->routes; $bootstrap->view_helpers; $bootstrap->framework; // $bootstrap->database->close(); ok well i commented out the closure of the database and now sessions are added to the database as the suppose to do....if i execute the closure of the database then i have no connection on the session handler write (wtf!) only thing i can think of is that the session write happens at the end of php like a destructor? but this is how my html page looks when print_r($db_adpater) hello [html here] hello DB\Driver\MySQLi Object ( [_identifier:protected] => mysqli Object ( [affected_rows] => 2 [client_info] => **************** [client_version] => 50011 [connect_errno] => 0 [connect_error] => [errno] => 0 [error] => [error_list] => Array ( ) [field_count] => 1 [host_info] => Localhost via UNIX socket [info] => [insert_id] => 0 [server_info] => 5.5.5-10.0.20-MariaDB [server_version] => 50505 [stat] => Uptime: 172349 Threads: 1 Questions: 110793 Slow queries: 10 Opens: 1634 Flush tables: 1 Open tables: 400 Queries per second avg: 0.642 [sqlstate] => 00000 [protocol_version] => 10 [thread_id] => 6093 [warning_count] => 0 ) [_config:protected] => Array ( [host] => localhost [username] => *** [password] => *** ) [_table:protected] => [_result:protected] => [_statements:protected] => Array ( ) [_database:protected] => ) surly both echo's of hello and the print_r() executed from the session write should be at the top of my page before the html as it's executed well before the framework...the first echo hello is at the top! honestly this is messed up or i should just give up php now

here's the session handler: the connection to the database is done before everything on my site and closes last...ensuring there is only one connection <?php namespace Session; class Session_DB_Handler { protected $_db; protected $_lifetime; public function __construct($config = array()) { ini_set('session.cookie_lifetime', 0); ini_set('session.gc_maxlifetime', 3600); ini_set('session.gc_probability', 1); ini_set('session.gc_divisor', 1000); session_set_cookie_params(0, '/', $_SERVER['HTTP_HOST'], false, true); session_name('SESSION_ID'); } public function open($session_path, $session_name) { return true; } public function read($session_id) { $parameters = array( 'session_id' => $session_id ); $query = "SELECT data FROM sessions WHERE session_id = :session_id AND UTC_TIMESTAMP() < DATE_ADD(modified, INTERVAL lifetime SECOND)"; $db = $this->_db; $db->bisi; $result = $db->execute($parameters, $query); $row = $result->fetch_row(); $row_count = $result->count(); $result->free(); if ($row_count === 1) { return $row['data']; } return null; } public function write($session_id, $data) { $parameters = array( 'session_id' => $session_id, 'data' => $data, 'lifetime' => $this->get_lifetime() ); $query = "INSERT INTO sessions (session_id, data, lifetime) VALUES (:session_id, :data, :lifetime) ON DUPLICATE KEY UPDATE data = :data, lifetime = :lifetime"; $db = $this->_db; $db->bisi; $db->execute($parameters, $query); } public function destroy($session_id = null) { $parameters = array( 'id' => $session_id, ); $query = "DELETE FROM session WHERE session_id = :session_id"; $db = $this->_db; $db->bisi; $db->execute($parameters, $query); } public function close() { return true; } public function clean() { $query = "DELETE FROM sessions WHERE UTC_TIMESTAMP() > DATE_ADD(modified, INTERVAL lifetime SECOND)"; $db = $this->_db; $db->bisi; $db->execute($query); } public function get_lifetime() { return $this->_lifetime; } public function set_lifetime($lifetime, $period = "Days") { switch (strtolower($period)) { default: case 'seconds': break; default: case 'minutes': $lifetime = $lifetime * 60; break; default: case 'hours': $lifetime = $lifetime * 60 * 60; break; default: case 'days': $lifetime = $lifetime * 60 * 60 * 24; break; default: case 'weeks': $lifetime = $lifetime * 60 * 60 * 24 * 7; break; default: case 'months': $lifetime = $lifetime * 60 * 60 * 24 * 30.4368; break; default: case 'years': $lifetime = $lifetime * 60 * 60 * 365.242; break; } $this->_lifetime = $lifetime; return $this; } public function set_db_adapter($db_adapter) { $this->_db = $db_adapter; } } thank you for your help

hey guys i'm using the session_set_save_handler() function but when trying to write to a session i no longer have a connection to my database... public function initialize_session_handler() { $registry = new Registry; $db = $registry->db; $session_handler = new Session_DB_Handler; $session_handler->set_db_adapter($db); $session_handler->set_lifetime(1); print_r($session_handler); // property $_db stats mysqli connection session_set_save_handler( array($session_handler, 'open'), array($session_handler, 'close'), array($session_handler, 'read'), array($session_handler, 'write'), array($session_handler, 'destroy'), array($session_handler, 'clean') ); } when print_r($session_handler) it shows the class property $_db has a mysqli connection: Session\Session_DB_Handler Object ( [_db:protected] => DB\Driver\MySQLi Object ( [_identifier:protected] => mysqli Object ( [affected_rows] => 0 [client_info] => mysqlnd 5.0.11-dev - ********* - $Id: *********** $ [client_version] => 50011 [connect_errno] => 0 [connect_error] => [errno] => 0 [error] => [error_list] => Array ( ) [field_count] => 0 [host_info] => Localhost via UNIX socket [info] => [insert_id] => 0 [server_info] => 5.5.5-10.0.20-MariaDB [server_version] => 50505 [stat] => Uptime: 120613 Threads: 1 Questions: 73251 Slow queries: 0 Opens: 1606 Flush tables: 1 Open tables: 400 Queries per second avg: 0.607 [sqlstate] => 00000 [protocol_version] => 10 [thread_id] => 3215 [warning_count] => 0 ) [_config:protected] => Array ( [host] => localhost [username] => user [password] => pass ) [_table:protected] => [_result:protected] => [_statements:protected] => Array ( ) [_database:protected] => ) [_lifetime:protected] => 86400 ) but if i try to write to a session after initializing the session handler $_db property is null in my session handler class...i've also tried to use $_db as a static property...i just can't understand why it's null when I'm trying to write does anyone have any ideas as to why please? thank you

good point =/...not one needed after your help SELECT CONCAT(FORMAT(((SUM(positive + neutral) - negative) / count(*) * 100), 2), '%') AS `seller_feedback_percentage` FROM users_feedback WHERE user_id = '1' AND seller = '1' worked like a charm...although i was getting the wrong percentage so i had to put brackets in to get the correct reading...thanks barand

users_feedback ---------------------------- `user_feedback_id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `item_id` int(11) NOT NULL, `question` varchar(90) NOT NULL DEFAULT '0', `seller` int(1) NOT NULL DEFAULT '0', `buyer` int(1) NOT NULL DEFAULT '0', `positive` int(1) NOT NULL DEFAULT '0', `neutral` int(1) NOT NULL DEFAULT '0', `negative` int(1) NOT NULL DEFAULT '0' -----------------------------------------------------------------

hey guys i'm working on a query but i'm getting a null on a column which i'm not sort of understanding,, SELECT @total := count(user_id), @positive := sum(case when positive = 1 then 1 else 0 end), @neutral := sum(case when neutral = 1 then 1 else 0 end), @negative := sum(case when negative = 1 then 1 else 0 end), (((@positive + @neutral) - @negative) + @total * 100) AS `sellers_feedback_percentage` FROM users_feedback WHERE user_id = '1' AND seller = '1 @positive, @neutral and @negative all have the correct value but when trying to make a percentage out of the amounts the sellers_feedback_percentage returns null...only way i can think of doing it is for me to do a subquery for each @positive, @neutral and @negative whats it the best way to return the percentage please? thank you

Oh yeah i didn't think about that!...thanks for the great idea

Brilliant! Haha

hey guys i have a few questions regarding my website that has the use of multiple databases....at the moment my site is ran off one mysqli connection and before executing a query i change the database depending on if its my authentication script, geoip, framework etc. what i'm worried about is performance issues....i could have a new connection for each database or continue to have one connection for the whole site and change database when needed...what is the best practice please? thank you.

worked a like a charm, thank you