Destramic

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

hey guys i'm using command prompt to execute the following: httpd.exe -k uninstall but it comes back with i've even removed maria-db as i thought that may have been the problem...no uninstall in c:\apache24 directories also no sign of apache in my add/remove programs listings. does anyone please have any idea on how i can uninstall apache please? thank you

hey guys...I'm currently building a web server and to my excitement I'm reading up about which one is best to install apache or nginx?...or even both? as i imagine a lot of you have your own web server with a load more experience on both engines than me...hopefully can give me a answer into which one i should really choose?...although i am more swayed to use nginx as it supposedly runs the quickest. what i need for my server is a web-socket as well as PHP and MariaDB. hope you guys can give me some good advise. Thanks

works like a dream even works if i put parameters in like so <?php $pattern = "hello[/name/hello[/s:second*]][/bye]"; or $pattern = "hello[/name/hello[/s:second]][/bye]"; or $pattern = "hello[/name/hello[/:second(1|2)]][/bye]"; thank you...you've been a great help

something like this <form method="POST" action="page.php"> Mail: <select name="mail"> <option value="admin@yourdomain">Admin</option> <option value="postmaster@yourdomain">Post Master</option> </select> <input type="submit" name="submit" /> </form> if (isset($_POST['submit'])) { $mail = $_POST['mail']; // send mail }

$value would be the value of the drop down list... but $value would be something like this: $_POST['to']

hey guys i'm having a slight problem with getting the correct match, which i know is down to my regex pattern. with my first result i get [/name/hello[/second] but im after the result of [/name/hello[/second]] with the closing bracket at the end. is it possible for me to match that? $pattern = "hello[/name/hello[/second][/bye]"; if (preg_match_all('/\[(.*?)\]/', $pattern, $matches)) { print_r($matches) } Array ( [0] => Array ( [0] => [/name/hello[/second] // --- > [/name/hello[/second]] [1] => [/bye] ) [1] => Array ( [0] => /name/hello[/second [1] => /bye ) ) thank you

well i think the way you've suggested is more a practical...dunno why i always try to over complicate things. thanks requinix

it doesn't need to be overly strict...it would probably be better if i explained what i wanted to do. ok well for my mvc routing system i want to know if a route i add is a domain or not ie. Router::add_route('shop.bisi.bid', array('controller' => 'shop', 'action' => 'items' )); then i can add that route into a sub domain array hope this explains things a little better...thanks

I'm guessing regex would be best for this as i can't see a php function for the job, but what i want to do is check if a string matches as being a valid domain. does anyone know a good pattern or a better solution to this please? I've seen a lot of patterns when searching but with little experience in regex i wouldn't know which one would be the strongest pattern. thank you