Jump to content

cs.punk

Members
  • Posts

    437
  • Joined

  • Last visited

Profile Information

  • Gender
    Male
  • Location
    UK

cs.punk's Achievements

Advanced Member

Advanced Member (4/5)

0

Reputation

  1. I don't think I understand your question. My initial dilemma with using JOINS instead of the subqueries however was, there are multiple rows in establishment_attribute with a foreign table id that points back to the accommodation table. In other words, a one to many relationship.
  2. Here the query explained: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY establishments ALL NULL NULL NULL NULL 906 1 PRIMARY accommodation eq_ref PRIMARY PRIMARY 4 glm.establishments.child_id 1 Using where 1 PRIMARY person eq_ref PRIMARY PRIMARY 4 glm.establishments.person_id 1 1 PRIMARY address eq_ref PRIMARY PRIMARY 4 glm.accommodation.address_id 1 1 PRIMARY country eq_ref PRIMARY PRIMARY 4 glm.address.country_id 1 5 DEPENDENT SUBQUERY establishment_attribute ALL NULL NULL NULL NULL 40862 Using where 4 DEPENDENT SUBQUERY establishment_attribute ALL NULL NULL NULL NULL 40862 Using where 3 DEPENDENT SUBQUERY establishment_attribute ALL NULL NULL NULL NULL 40862 Using where 2 DEPENDENT SUBQUERY establishment_attribute ALL NULL NULL NULL NULL 40862 Using where
  3. I have a query thats taking like 60 seconds to run, instead of the usual 0.xx seconds. It is due to my multiple 'exist' clauses on the establishment_attribute table, but why are they taking so long? Any ideas? Here is the query: select * from accommodation inner join address on accommodation.address_id=address.address_id inner join country on address.country_id = country.country_id inner join establishments on accommodation.accommodation_id = establishments.child_id inner join person on establishments.person_id = person.person_id WHERE ( EXISTS( SELECT 1 from establishment_attribute WHERE establishment_id = accommodation.accommodation_id AND attribute_id = 11) OR EXISTS( SELECT 1 from establishment_attribute WHERE establishment_id = accommodation.accommodation_id AND attribute_id = 13) OR EXISTS( SELECT 1 from establishment_attribute WHERE establishment_id = accommodation.accommodation_id AND attribute_id = 12) ) AND ( EXISTS( SELECT 1 from establishment_attribute WHERE establishment_id = accommodation.accommodation_id AND attribute_id = 139) ) Table structure: CREATE TABLE IF NOT EXISTS `accommodation` ( `accommodation_id` int(16) NOT NULL, `name` varchar(255) NOT NULL DEFAULT '', `heading` varchar(150) NOT NULL DEFAULT '', `description` text, `website` tinytext, `active` enum('true','false') NOT NULL DEFAULT 'true', `finalised` tinyint(1) NOT NULL, `directions` text, `comments` text, `currency_id` int( NOT NULL DEFAULT '0', `price_minimum` varchar(255) DEFAULT NULL, `price_maximum` varchar(255) DEFAULT NULL, `pricing_structure` text, `special_deals` text, `check_in` time DEFAULT NULL, `check_out` time DEFAULT NULL, `insert_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `last_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `user_activated` enum('true','false') NOT NULL DEFAULT 'true', `address_id` int(16) NOT NULL, `pageviews` int(16) NOT NULL DEFAULT '0', `bbid` varchar(56) DEFAULT NULL, `lat` float NOT NULL, `lng` float NOT NULL, `note` text, PRIMARY KEY (`accommodation_id`), FULLTEXT KEY `name` (`name`), FULLTEXT KEY `heading` (`heading`), FULLTEXT KEY `description` (`description`), FULLTEXT KEY `name_2` (`name`,`heading`,`description`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS `address` ( `address_id` int(16) NOT NULL AUTO_INCREMENT, `address1` varchar(255) DEFAULT NULL, `address2` varchar(255) DEFAULT NULL, `address3` varchar(255) DEFAULT NULL, `postal_code` varchar(64) DEFAULT NULL, `area` varchar(128) DEFAULT NULL, `city` varchar(128) DEFAULT NULL, `country_id` int(16) DEFAULT NULL, `district` varchar(128) NOT NULL, `suburb` varchar(128) NOT NULL, `establishment_type_id` int(16) NOT NULL, PRIMARY KEY (`address_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3895 ; CREATE TABLE IF NOT EXISTS `establishments` ( `establishment_id` int(16) NOT NULL AUTO_INCREMENT, `child_id` int(16) NOT NULL, `establishment_type_id` int(16) NOT NULL, `person_id` int(16) NOT NULL, PRIMARY KEY (`establishment_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=856 ; CREATE TABLE IF NOT EXISTS `establishment_attribute` ( `establishment_attribute_id` int(16) NOT NULL AUTO_INCREMENT, `establishment_id` int(16) NOT NULL DEFAULT '0', `attribute_id` int(16) NOT NULL DEFAULT '0', PRIMARY KEY (`establishment_attribute_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=180559 ;
  4. SELECT * FROM items WHERE id = 1 OR id = 1 OR id = 1 How can I have this return 3 (the number of OR clauses) records regardless if there are duplicates?
  5. I like it. Looks clean. I'd drop the script-font for all your headers though, doesn't fit imho. Secondly I'd move the 'options/fontsize' thingies downwards (so they're below the header - vertically speaking only).
  6. I think you accidentally marked this as solved. If I guessed correctly you want to surround the current text in the textarea to be surrounded with those tags? I'm guessing you'd need to prepend it first with '' and then append it with ''.
  7. $('input[type=checkbox]').change(function(event) { var c = this.checked ? '#CCCCCC' : '#ECECEC'; $(this).parent().parent().css('color', c); event.stopPropagation(); }); $('table tr').click(function() { alert('test'); }); Can anybody tell me why the click event of the table row gets fired, when the checked state of the check box changes?
  8. I'm kinda lost to how I should deal with foreign characters with regards to PHP/Mysql. First of all I'm receiving a plain text file which I'm guessing could potentially be received in a range of encoding formats. It seems like it's rather difficult to accurately detect the encoding type but there is mb_detect_encoding(). Potentially I could just stick to the 'likely' default encoding saved by notepad which at the moment seems to be ANSI or rather extended ANSI. I'm hoping someone could perhaps give me any pointers to hopefully not spend the next 3 days in a ramble of pages...
  9. Indeed. However like stated, there already is a considerable chunk written. Perhaps it's cutting corners and isn't the best decision in terms of future modifications but I guess it'll have to do.
  10. A mate has just helped me out on this. Gotta learn some advanced SQL me thinks. Heres the query: SELECT height, id, name, age, NULL as 't2.id', NULL as t2.name, NULL as t2.gender FROM t1 UNION SELECT height, NULL, NULL, NULL, id, name, gender FROM t2 ORDER BY height
  11. Hello. Here's my problem. Table 1: height | id | |name | age 130 | 1 | Josh | 18 135 | 2 | Jack | 17 155 | 3 | Jody | 19 Table 2: id | name | gender | height 1 | BOB | MALE | 140 I'd like to 'combine' the tables into 2, and sort them by height. When data isn't available the columns can be null. I'm finding it hard to relate them with the height table however, joins seem to have a 'direct column' relationship. Ideally it'd be all in one table but I'd have to rewrite a large chunk of the system potentially, hoping theres an easier way? Potential output: height | T1.id | T1.name | T1.age | T2.id | T2.name | T2.gender | 130 | 1 | Josh | 18 | NULL | NULL | NULL 135 | 2 | Jack | 17 | NULL | NULL | NULL 140 | NULL | NULL | NULL | 1 | BOB | MALE 155 | 3 | Jody | 19 | NULL | NULL | NULL Heres my SQL query i've got so far SELECT t1.*, t2.* FROM t1 LEFT JOIN t2 ON t1.height = t2.height ORDER BY t1.height LIMIT 0, 30
  12. I'd like PHP to implement object-orientated interfaces that can communicate directly with my coffee maker and toaster. Thank you.
  13. I'd hope so. Past work hasn't really gone further on for me. Perhaps it's directly due to later bugs being found, directly being my fault. And I definitely agree with you however I'm doing low budget projects and it seems even the client is encouraging a 'it works 90% of the time' using '50% of the effort'. Guess I just hang in here until I start getting more structured systems to work on.
  14. You're describing ASP.NET webforms which sucks. ASP.NET MVC is pure server side, and is pretty good, aside from the Microsoft bloat. It's not as to-the-point as something like Symfony, but it gets the job done. Client side stuff is left up to the developer's discretion, although it ships wiith jQuery. It's really just a C# or VB (developer's choice) MVC framework. I really like C#.NET. Though I really want to stay on the open source side of things however. I've heard of Castle MonoRail, though I doubt it's gonna be that useful for employment/etc as its not as popular. Things on my wishlist to try out: Scala (compiles to the JVM) + Play framework (I'm really looking forward to this, heard some great things about it from a colleague) , Ruby on Rails and another 'big' PHP framework like Yii/Zend... Mmmmmmm so little time I don't see that huge of an advantage of one platform to another. But I find it's great fun to learn things, and to see how some 'good parts' of a framework are implemented.
×
×
  • 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.