nested select statements

problem
i came across this while trying to put together a complex sql query that would find specific items within a data set. at first i did this by writing a query and pulling the info into an array in php. then creating a data set from that to build another query from:

$sql = "SELECT * FROM table WHERE column = value";
$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result) {
	if ($row["column"] == "some value") $array[] = $row;
}
$sql = "SELECT * FROM table WHERE column IN (".implode(",",$array).")";
$result = mysql_query($sql);

solution
you can see how having to do this a few times can become rather tedious to hone in on a small data set. as of MySQL 5, however, you can nest SELECT statements to produce very complex queries that can help narrow down your result:

SELECT model
FROM cars
WHERE make IN (
	SELECT *
	FROM automakers
	WHERE automaker = "Honda"
	)

conclusion
while this is only a simple representation of the capabilities, you can see how nesting a few SELECT statements can reduce the number of subsequent queries you need to make in order to get the result set that you desire.

appending and prepending elements

intro
with AJAX user interfaces becoming more and more popular, users are getting used to things happening in real time on websites. take, for example, leaving a comment on a blog post. clicking “post comment” can submit a form that your server language (php) can handle and then reload the page with the comment posted. however this makes the entire page reload, which can take up more time than necessary, not to mention server resources to rebuild the entire page’s content. enter, [intlink id="109" type="post"]XMLHttpRequest[/intlink] and a little AJAX fun….

scenario
let’s assume you already have your [intlink id="109" type="post"]XMLHttpRequest[/intlink] function written and that it sends information to a postComment() function that will add the comment to the blog post. once you gave gotten your responseXML from the request you want to add the comment to the list of existing comments in real time.

solution
create the div that contains the comments and make sure to give it a unique id:

<div id="comment_container">
    <p>comment 1</p>
    <p>comment 2</p>
</div>

then simply get the dom element of the container div so you can add the element from the responseXML:

var new_comment = request.responseXML;
var comments = document.getElementById("comment_container");

now you have to decide if you want to add the element to the top or the bottom of the list. if you are sorting the comments with the newest at the bottom, you want to append the new comment:

comments.appendChild(new_comment);

this will add the dom element to the end of the list, attaching it to the beginning is a little trickier. there is no prependChild() method in javascript. instead you will have to use the insertBefore() method:

comments.insertBefore(new_comment, comments.firstChild);

combine this with some cool graphic effects (like fading in the new comment) and you can start to create a really appealing interface for your users.

sorting an associated array

intro
so, lets say you’ve got an associative array that you want to sort, a simple sort() might not always suffice depending on the array architecture. if you have an array that looks something like the one below, and you want to sort by the timestamp, you will have hard time doing so with something like sort():

array (
  1 => array (
      [timestamp] => 123456789
      [name] => "name"
      [message] => "hi there"
  )
  2 => array (
      [timestamp] => 123456987
      [name] => "name2"
      [message] => "hi yourself"
  )
)

solution
php has a great function called uasort() that allows you to define your own sorting scheme for the data. so, you write a function, lets call it mySort(), and then use the uasort() to reference the mySort function as follows:

uasort($myArray, "mySort");

in the case of the array above, i would write a function to sort by the timestamp as follows:

function mySort($a, $b) {
	if ($a["timestamp"] == $b["timestamp"]) return 0;
	return ($a["timestamp"] > $b["timestamp"]) ? -1 : 1;
}

$a and $b represent the two items to be compared, in this case $myArray[0] and $myArray[1]. if the two timestamps are equal there is no need to make a position swap so 0 is returned (which means no swap is necessary), otherwise it returns a -1 or a 1 (move the item up or down) base on whether its greater than the next item or not. since there is no build in sort function in php to apply a sort to a value thats nested deep within an array, these types of sorts come in handy all the time.