MySQL Group BY Order By

I was having a problem selecting the highest id of a table in a group.  This was on a hosted server and I figured it was due to the mode that MySQL was in.

My TABLE looked something like this:

################

# id # title # category #

—————————–

# 1 # hello # cmi #

# 2 # test # cmi #

I wanted rows two as it had the highest id in the group so natually what I did was:

SELECT *, MAX(id) FROM mytable GROUP BY category

Alternatively, I thought

SELECT * FROM mytable GROUP BY category ORDER BY id

Neither worked

So I ended up doing something like this:

SELECT * FROM (SELECT MAX(id) AS max FROM mytable GROUP BY category) t1 LEFT JOIN mytable t2 ON t1.max = t2.id

The query in the parentheses gets executed first.  What this is doing is selecting the highest id within a group of categories and returning to us the id of each each row.

Then from that set, we move to the main query and join the table back onto the first query by the matched ids giving whatever information we were originally after.

A little bit of a performance hit, but it works.

Resolved: jQuery Cycler not Working with IE PNGs

For those of you who have read my stuff before, either here or elsewhere, know that I despise Internet Explorer with a passion.  Nevertheless, until the powers that be (Google) stop supporting it so everyone else can follow suit, us burdened developers must still make our clients happy.

To fuel my anger against IE, I spent hours trying to get the jQuery Cycle plugin working with some PNGs that were laid on top of some other PNGs.  I’m not sure if them being laid on other PNGs had anything to do with it…really didn’t care….should just work…..but it didn’t.

I tried revising the jQuery plugin with no luck.  So I decided to write a custom solution.  I haven’t made this all pretty for you guys to use, so sorry about that.  This is just a case study and I’ll be happy to help you through your problem later.  The issue was explained quite well here: http://stackoverflow.com/questions/1156985/jquery-cycle-ie7-transparent-png-problem; which basically says the problem is that IE can’t handle multiple filters at one time.  So when you are fading your image, an alpha filter is applied to the image and it disregards any other filter you have…which if you have toiled through documents you probably have the Microsoft filter I’m about to mention already applied to your images, scratching your head, and cussing at an inanimate computer screen.

To fix this, we “simply” need to wrap our images in a div and fade the div.

Let’s look at our slideshow html:

<div id="prev"><a href="#"></a></div>
<div id="slides" style="visibility:hidden">
<div style="position:absolute;"><img style="filter:progid:DXImageTransform.Microsoft.AlphaImageLoader(src='image1.png'); margin-top:'.$tmargin.'px" src="image1.png" alt="Thumbnail Image" title="Thumbnail Image" /></div>
<div style="position:absolute;"><img style="filter:progid:DXImageTransform.Microsoft.AlphaImageLoader(src='image2.png'); margin-top:'.$tmargin.'px" src="image2.png" alt="Thumbnail Image" title="Thumbnail Image" /></div>
<div style="position:absolute;"><img style="filter:progid:DXImageTransform.Microsoft.AlphaImageLoader(src='image3.png'); margin-top:'.$tmargin.'px" src="image3.png" alt="Thumbnail Image" title="Thumbnail Image" /></div>
</div>
<div id="next""><a href="#"></a></div>
</div>

To start off with, we make our slides div invisible. We do this so we have time to hide out image after the page loads. If we don’t, there’s a good change that you’ll see all your images dumped down the screen.
Then we wrap out list of images in a div that is absolutely positioned.
Then we apply the ridiculously stupid Microsoft filter to the images.
Now we add a little magic:

<script>
jQuery(document).ready( function(){
	var set = 0;
	var ci = -1;
	var timer;
	function createLoop(dir){
		var id = 'slides';
		var el = 'div';
		var time = 5000;
		var lp = document.getElementById(id).getElementsByTagName(el);
		if(set == 0){
			jQuery("#showcase #next").click( function(){ clearTimeout(timer); createLoop(); } );
			jQuery("#showcase #prev").click( function(){ clearTimeout(timer); createLoop("reverse"); } );
		}
		set = 1;
		if(dir == "reverse"){
			ci = ci-1;
			if(ci < 0){ ci = lp.length-1; }
		}else{
			ci = ci+1;
			if(ci > lp.length-1){ ci = 0; }
		}
		for(var y=0; y<lp.length; y++){
			if(y != ci){ jQuery(lp[y]).fadeOut('slow'); }
		}
		jQuery(lp[ci]).fadeIn('slow');
		timer = setTimeout(function(){ createLoop(); }, time);
	}
	var lp = document.getElementById('slides').getElementsByTagName('div');
	for(var i=1; i<lp.length; i++){
		lp[i].style.display = 'none';
		if(i == lp.length-1){ document.getElementById('slides').style.visibility = 'visible'; setTimeout(function(){ createLoop(); }, 5000); }
	}
});
</script>

You’ll need to change these three lines:

var id = 'slides';
var el = 'div';
var time = 5000;
id is the id of the wrapper that is around the img and img div wrapper.
el is the img wrapper. In our case it's a div.
time is the milliseconds until next fade.
We are using a little jquery in there, so you'll need to include jQuery in your file.
Take a look at that and chew it over...it works.  I know that. :)

Change AddThis Facebook Description and Title

This is a super cheesy hack, but nonetheless, it got me through an website build.

So, as I’m sure you’ve read, using addthis.com api custom descriptions and title does not work with Facebook.  Facebook insists on scanning your page for it’s own content and description.

Lucky for us, we can specify what URL is scans.

Using the information found here: http://developers.facebook.com/docs/share we can see that we can pass our own link to facebook.  So that’s exactly what we’ll do.

Let’s look at our AddThis code

<div id="add_this">
<div>
<a href="http://www.addthis.com/bookmark.php?v=250&amp;username=xa-4c22c53745c89a46"><img src="http://s7.addthis.com/static/btn/sm-share-en.gif" width="83" height="16" alt="Bookmark and Share" style="border:0"/></a>
<a><img src="/images/social_email.png" title="Email Us"/></a>
<a href="http://www.facebook.com/sharer.php?u=http%3A%2F%2Fwww.allebrum.com%2Fsharefacebook.html&t=Share%20This" target="_blank"><img style="margin:0 3px 0 2px;" src="/images/social_facebook.png" title="Like Us On Facebook"  /></a>
</div>
<script type="text/javascript" src="http://s7.addthis.com/js/250/addthis_widget.js#username=xa-4c22c1bc64ddaee6"></script>
</div>

You’ll want to make your own code on the addthis.com website.
Then you can make modifications to it like we did. Take a look at the facebook url. We added in the href=”” line.
the u= specifies the url that you want to share. You’ll have to make sure that it is an encoded url like you see above.
Now, sharefacebook.html is not the page that I really want to share at all. It’s just a dummy page that facebook can scan and get the information that I actually want it to have.
Let’s take a look at sharefacebook.html

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="title" content="Allebrum!" />
<meta name="description" content="Allebrum's cool new Content Management system to have first release soon!" />
<title>Untitled Document</title>
</head>

<body>
<script>
document.location.href = "http://www.allebrum.com/";
</script>
</body>
</html>

Notice the two title and description meta tags. That is where you will specify your custom Title and Description that will show up on Facebook.
Facebook will scan this page.
You’ll want to replace this line: document.location.href = “http://www.allebrum.com/&#8221;; with a url that goes to the actual page you want your users to see when they click on the facebook shared link.

jQuery UI Themes Messed Up

If you have included the jQuery UI, and are trying to use one of their themes, and it keeps showing up all messed up, you may need to include a few other files that you don’t know about.

Make sure you download the entire development bundle.  Inside the development bundle, you’ll find some additional css files in there.

Let’s say you are using the datepicker.

You’ll want to include css files in this order:

development-core/css/core.css

development-core/css/datepicker.css

theme/smoothness/jquery-1.8.2.customer.css

PHP move_uploaded_files permission denied

This turned out to be a fairly simple fix after much frustration.

I did this in ssh, so if you don’t have that, you’ll have to find another article.

open ssh, cd to your web root.

type ls -l

to see all the permissions.  You’ll see come stuff like -rw-ww- owner group 4096 time filename

you’ll need to change your upload directory to have the same group and owner as your other web files.

So let’s get started

mkdir data

chown owner

chgrp group

chmod 777 data

That’s it.

replace “data” with your folder name

replace “owner” and “group” to match the settings from when you did ls -l

lftp crontab linux options how to

first lets make a working directory. Open up ssh or go to the command console and type

cd /var/

Create a directory here

mkdir test

now create a file with vi (or use whatever editor you like, I personally hate vi but it’s what it on my server)

vi myFile.exe

Now type in the following:

#!/bin/bash
/usr/local/bin/lftp -e ‘lcd /var/test/ && set ftp:ssl-protect-data yes && set ftp:passive-mode yes && open ftp://server:port -d -u “username”,”password” && mirror “.” && exit’
If using vi, type ESC then :wq to write the file.  you should now be back at the command console.
If you type ls you should see the file listed now.
Now type:
chmod u+x myFile.exe
Now your file is ready to execute.  You can type ./myFile.exe from the command line and it should run the lftp commands.  Just a note, the mirror command above will copy all the files (not recursively).  The lcd command changes the local directory where files will be downloaded to.
You’ll notice that I have the full path to the lftp program.  You’ll need that.  If you don’t know where it’s installed you can do:
find / -name ‘lftp’
Now let’s create the crontab. Type:
crontab -e
That should open up your crontab file. Enter a new line and enter:
30 18 * * * unbuffer /var/test/myFile.exe > /var/test/log.txt
That should do it.
Now, all the files on your ftp server will be downloaded to your computer at 6:30pm every day.  Pretty nifty!

Transferring WordPress to new server

Just wanted to make a note here as there are plenty of tutorial on transferring wordpress to another server.

Dont’ forget to do a find and replace on the .sql file that you backed up.

Also, after the transfer, my site would just hang.  After trying to track down the error, I renamed my wp-config.php file to wp-config.php.bak.  Reloaded the site, and let wordpress create me a new one.  I did not run the install as all my information was already there.  A quick refresh and everything was working!

Previous Older Entries

Follow

Get every new post delivered to your Inbox.