cs.punk

April 12, 2013

All the dependant subqueries can be replaced with an addtional JOIN and using IN for the attribute_id, however why are you joining the table establishment_attribute with the table accomodations when seems that the right relationship is with the table establishment using the establishment_id field, moreover when you are joining the table establishments with accomodations using the field establishments.child_id

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.

April 11, 2013

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

April 11, 2013

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 ;

December 28, 2012

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?

October 6, 2012

lol at the puzzle pieces xD

August 20, 2012

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

August 13, 2012

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

August 13, 2012

			$('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?

July 17, 2012

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

July 10, 2012

In the first place i dont see why you even need to seperate these tables. its pretty obvious that all information submited here can be for a single person.

there is no need for a relational dataase. just create one table insert all the data and use sql to call only the data with a particular height value.

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.

July 9, 2012

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

July 9, 2012

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:

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

July 8, 2012

I'd like PHP to implement object-orientated interfaces that can communicate directly with my coffee maker and toaster. Thank you.

July 8, 2012

Oh. And realize that spending more time learning stuff now means less time learning stuff later. Odds are you won't be doing just one little thing now only to never touch it again, right?

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.

July 8, 2012

Just to add to this, ASP is a hybrid client-side/server-side, if you wanted to try something like that out.

Some people love it, others can't stand it.

Personally, I like direct control over client-side scripting if only because behaviour can be different between major browsers. I kind of prefer the separation. I can understand the potential simplicity of combining them, though.

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.

July 8, 2012

I'd suggest learning the basics of PHP first and then try to attempt to write a similar script or understand a third party script.

July 8, 2012

I'm curious to how you tackle adding 'small/medium' additions to an existing system that you are not familiar with.

Do you try to write code as soon as possible and start debuging your way from there? Or do you spend a lengthy amount of time first analyzing how the system works and then take it from there?

I usually try the former approach but it's tempting to just write code that'll get the job 'done' somewhat and fix any major issues, this however doesn't guarantee it's secured and doesn't have any hiccups when used in a obscure way.

Cheers

July 4, 2012

Honestly,I think w3school is not a good resource for php,

they didn't even teach what is scoping rules for php?

w3fool.com

July 4, 2012

I've recently considered learning other frameworks/platforms and I'm curious to know if perhaps someone has tried any other web frameworks (Django/ASP.net/Ruby on rails) and how you would compare them to PHP?

I'm currently learning Django (in python) and so far it seems sufficiently capable as well as being 'easy to use' compared with PHP, the whole data flow seems a bit 'messy' for now but I'm sure that's just due to my recent introduction to it.

Cheers.

July 2, 2012

Seems like I've answered my own question. I added this:

$('img').mouseout(function(event)
		{  
			event.stopPropagation();
		});

And the behavior stops.

This would mean however I would need to add the above code to every child element within the div... Is there a more effective way to do prevent this from happening?

July 2, 2012

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<head>
<title>Test Jquery</title>
<script src="jquery-1.7.2.js" type="text/javascript"></script>
<script type='text/javascript'>
$(document).ready(
    function()
    {


		$("div.ratingBar").on("mouseover", 'img', function(event)
		{
			console.log("MouseOVER occured on img.");
			console.log("...");

		});

		$('div.ratingBar').mouseout(function(event)
		{  
			var html_id = event.target.id;

			console.log("MouseOUT occured of ratingBar");
			console.log("ID ACTUAL: " + html_id);
			console.log("...");
		});
}
);
</script>
</head>
<body>

<div class='mainDiv'
style='background: none repeat scroll 0% 0% pink; border-style: solid; border-width: 20px; border-color: rgb(0, 255, 0);'>
	<div class='ratingBar' id='rating_3' style='width: 300px; background: none repeat scroll 0% 0% blue; border-style: solid; border-width: 20px; border-color: rgb(115 0, 204);'>
		<img id='3_1' src='star.gif' /><img id='3_2' src='star.gif' /><img id='3_3' src='star.gif' /><img id='3_4' src='star.gif' />
	</div>
</div>

</body>
</html>

I'm running into a Jquery issue here (potentially it might just be normal javascript behaviour too). I've tried to replicate the behavior with the above code. I'm using using Firefox 12 btw.

If you move you mouse cursor to one of the images (star.gif) and directly move to the next image, the mouseout event of the 'ratingBar' div fires (yet it hasn't 'left the div, imo)? There are no gaps between the images however.

Is this due to perhaps event propagation due to the image's 'mouseout' event firing?

July 2, 2012

Hello everyone.

I'm sort of ish new to Jquery and was thinking perhaps others are too and wanna group up to create some form of nifty widget/layout/ui with Jquery, perhaps it could be something cool to list on our portfolio?

I'm thinking a group of 3 people, mainly noobs like me, who are willing to learn with others and share your insight etc.

Cheers!

June 27, 2012

Do you have a date associated with each image?

If so you could run a sql query that sorts the images by date in reverse, and hence return the first/last 5 results.

June 27, 2012

Hey guys... After battling with php's mail function I decided to try use the Pear Mail extension.

I manually downloaded the Mail/Mail_Mime extensions and extracted them into a folder on my host. My host doesn't support pear as im on a shared hosting account and so forth...

I've included a simple text file as an attachment but it's showing up as raw base64 on the email. Any ideas as to whats going on?

Anyway heres the script:

<?php
require_once "pear/Mail-1.2.0/Mail.php";
require_once "pear/Mail_Mime-1.8.5/Mail/mime.php";

$mail = Mail::factory("mail");

$message = new Mail_mime();
$message->setTXTBody("This is a test!");
$message->addAttachment("circle.txt"); 
$body = $message->get();

$headers = array("From"=>"me@example.com", "Subject"=>"Test Mail");
$mail->send("aoeu@gmail.com", $headers, $body);

This is the actual output in gmail:

--=_d4f892aa77bb2f7f126290d6354a013c
Content-Transfer-Encoding: quoted-printable

Content-Type: text/plain; charset="ISO-8859-1"

This is a test!

--=_d4f892aa77bb2f7f126290d6354a013c

Content-Transfer-Encoding: base64

Content-Type: application/octet-stream

Content-Disposition: attachment

CiAgICAgICAgICAgIC8vIENJUkNMRQogICAgICAgICAgICB1c2VyUHJpbWl0aXZlcyA9IG5ldyBW

ZXJ0ZXhQb3NpdGlvbkNvbG9yW3RyaUNvdW50XTsKCiAgICAgICAgICAgIGZvciAoaW50IGkgPSAw

OyBpIDwgdHJpQ291bnQgLSAxOyBpKyspCiAgICAgICAgICAgIHsKICAgICAgICAgICAgICAgIGRv

dWJsZSBhbmdsZSA9IChpIC8gKHRyaUNvdW50IC0gMS4wRikpICogMiAqIE1hdGguUEk7CiAgICAg

ICAgICAgICAgICB1c2VyUHJpbWl0aXZlczJbaV0gPSBuZXcgVmVydGV4UG9zaXRpb25Db2xvcigp

OwogICAgICAgICAgICAgICAgZG91YmxlIHggPSBjZW50cmUuWCArIE1hdGguQ29zKGFuZ2xlKSAq

IHJhZGl1czsKICAgICAgICAgICAgICAgIGRvdWJsZSB5ID0gY2VudHJlLlkgKyBNYXRoLlNpbihh

bmdsZSkgKiByYWRpdXM7CgogICAgICAgICAgICAgICAgdXNlclByaW1pdGl2ZXNbaV0uUG9zaXRp

b24gPSBuZXcgVmVjdG9yMygoZmxvYXQpeCwgKGZsb2F0KXksIDApOwogICAgICAgICAgICAgICAg

dXNlclByaW1pdGl2ZXNbaV0uQ29sb3IgPSBDb2xvci5QdXJwbGU7CiAgICAgICAgICAgIH0KICAg

ICAgICAgICAgdXNlclByaW1pdGl2ZXNbdHJpQ291bnQgLSAxXSA9IHVzZXJQcmltaXRpdmVzMlsw

XTs=

--=_d4f892aa77bb2f7f126290d6354a013c--

And heres the 'original - raw email' :

                                                                                                                                                                                                                                                               
Delivered-To: oaeu@gmail.com
Received: by 10.152.127.232 with SMTP id nj8csp106163lab;
        Wed, 27 Jun 2012 12:07:38 -0700 (PDT)
Received: by 10.204.132.87 with SMTP id a23mr7469021bkt.134.1340824058608;
        Wed, 27 Jun 2012 12:07:38 -0700 (PDT)
Return-Path: <me@example.com>
Received: from moutng.kundenserver.de (moutng.kundenserver.de. [212.227.126.171])
        by mx.google.com with ESMTP id iu12si17714822bkc.92.2012.06.27.12.07.38;
        Wed, 27 Jun 2012 12:07:38 -0700 (PDT)
Received-SPF: neutral (google.com: 212.227.126.171 is neither permitted nor denied by best guess record for domain of me@example.com) client-ip=212.227.126.171;
Authentication-Results: mx.google.com; spf=neutral (google.com: 212.227.126.171 is neither permitted nor denied by best guess record for domain of me@example.com) smtp.mail=me@example.com
Received: from icpu968.kundenserver.de (infong1008.kundenserver.de [212.227.17.205])
by mrelayeu.kundenserver.de (node=mreu0) with ESMTP (Nemesis)
id 0MOELI-1SpRlO0gh2-005VFI; Wed, 27 Jun 2012 21:07:38 +0200
Received: from 188.29.137.212 (IP may be forged by CGI script)
    by icpu968.kundenserver.de with HTTP
    id 49mGfE-1RwEXn0QtZ-00yy68; Wed, 27 Jun 2012 21:07:38 +0200
X-Sender-Info: <380737050@icpu968.kundenserver.de>
Date: Wed, 27 Jun 2012 21:07:38 +0200
Message-Id: <49mGfE-1RwEXn0QtZ-00yy68@icpu968.kundenserver.de>
Precedence: bulk
To: aoeu@gmail.com
Subject: Test Mail
From: me@example.com
X-Provags-ID: V02:K0:XkW5H0+gB04F8ve/l0PJ4pVPIsl+BCKqgHQuyM6f2gT
SZYp6SqOt6uKoiZbpnkg/M3dDNk2ew8RFYTEftVdeS7zkpcMzt
4Jw15uh42lSdCY/spjGtWLwrICvCdIPHdJy8kvbyH32tS+M+L+
ivhozQkMPrgDEhwYCtutaxKXInNadwkYQ1OBQr+aKC5Yeo9WoH
Q2xG3eHqTZ1zt/v0kTzte3CWjTrmUJLRzoYg/Y1XiJtc2GvpCr
lmqrBwUf62w02Yqm8bQv+ZDSpNLE+yKtLPAbYv1S8lzzpE+cFj
SQWQHfVwkJgtHX/rfefSfzVHd+kh+kTnkJSGKGDx+Y+ZPTDzzV
yqL3L2M4f/mgeZEOZdICk0tTxaEJDVooCvmlHvfaEwQe1qHauA
U3fVbzzRntoighqj9PK4W371JazLYyQyTyW4gPmI43B7C3hpFn
EXV9p

--=_d4f892aa77bb2f7f126290d6354a013c
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset="ISO-8859-1"

This is a test!
--=_d4f892aa77bb2f7f126290d6354a013c
Content-Transfer-Encoding: base64
Content-Type: application/octet-stream
Content-Disposition: attachment

CiAgICAgICAgICAgIC8vIENJUkNMRQogICAgICAgICAgICB1c2VyUHJpbWl0aXZlcyA9IG5ldyBW
ZXJ0ZXhQb3NpdGlvbkNvbG9yW3RyaUNvdW50XTsKCiAgICAgICAgICAgIGZvciAoaW50IGkgPSAw
OyBpIDwgdHJpQ291bnQgLSAxOyBpKyspCiAgICAgICAgICAgIHsKICAgICAgICAgICAgICAgIGRv
dWJsZSBhbmdsZSA9IChpIC8gKHRyaUNvdW50IC0gMS4wRikpICogMiAqIE1hdGguUEk7CiAgICAg
ICAgICAgICAgICB1c2VyUHJpbWl0aXZlczJbaV0gPSBuZXcgVmVydGV4UG9zaXRpb25Db2xvcigp
OwogICAgICAgICAgICAgICAgZG91YmxlIHggPSBjZW50cmUuWCArIE1hdGguQ29zKGFuZ2xlKSAq
IHJhZGl1czsKICAgICAgICAgICAgICAgIGRvdWJsZSB5ID0gY2VudHJlLlkgKyBNYXRoLlNpbihh
bmdsZSkgKiByYWRpdXM7CgogICAgICAgICAgICAgICAgdXNlclByaW1pdGl2ZXNbaV0uUG9zaXRp
b24gPSBuZXcgVmVjdG9yMygoZmxvYXQpeCwgKGZsb2F0KXksIDApOwogICAgICAgICAgICAgICAg
dXNlclByaW1pdGl2ZXNbaV0uQ29sb3IgPSBDb2xvci5QdXJwbGU7CiAgICAgICAgICAgIH0KICAg
ICAgICAgICAgdXNlclByaW1pdGl2ZXNbdHJpQ291bnQgLSAxXSA9IHVzZXJQcmltaXRpdmVzMlsw
XTs=
--=_d4f892aa77bb2f7f126290d6354a013c--

June 26, 2012

You can just echo the PHP variable into a JS variable declaration:
var someVar = '<?php echo htmlspecialchars($someVar) ?>';
Added the escaping to ensure no characters within the string would break the JS syntax.

How would I insert this into $(document).ready() function which is loaded via <script type="text/javascript" src="js.js"></script>?

I'm guessing I'm just gonna have to rewrite my system to have the $(document).ready() dynamically in each page and just echo the 'javascript code' in there.

If anyone has any other ideas please let me know

Sign In

cs.punk

Posts

Joined

Last visited

Content Type

Profiles

Forums

Posts posted by cs.punk

Exists clauses in where is taking 60+ seconds to run

Exists clauses in where is taking 60+ seconds to run

Exists clauses in where is taking 60+ seconds to run

Select Records Even If Duplicates Using Where Clause

What Does Your Workspace Look Like?

A quick critique please?

jQuery .append help

Prevent event bubbling with jquery

How do I deal with foreign characters

Join two tables with common comulmns and sort by field name

Join two tables with common comulmns and sort by field name

Join two tables with common comulmns and sort by field name

PHP 6(or 5.5) Wishlist

How do you go about adding a small addition to a big system?

Has anyone tried other web frameworks other than in php

Looking for simple news script

How do you go about adding a small addition to a big system?

Good Programming and Web Design Books

Has anyone tried other web frameworks other than in php

Div's mouseout event firing after leaving image within div (Jquery)

Div's mouseout event firing after leaving image within div (Jquery)

Let's do something cool in jquery? Noobs welcome :D

Displaying recent uploaded images

Pear Email script showing base64 instead of attachment

Dynamically edit js function with aid php

Browse

Activity

Important Information