Archiv der Kategorie ‘MYSQL‘

 
 

Recent Snippets Part 1

Here are a few snippets that I’ve used recently. They are javascript, mysql, php and some apache directives:

Select rows from a table in random order:

SELECT * FROM `table` ORDER BY `field` RAND();

Fix broken images on a page, good for use with ajax:

1
2
3
4
5
function fixBrokenImages(someDiv){
   someDiv.find("img").bind("error", function(){
         $(this).attr("src", "no-image.jpg");
   });
}

I use this snippet locally to prevent caching in mamp:

1
2
3
4
5
6
7
8
9
<FilesMatch "\.(html|htm|js|css|jpg|png|gif)$">
FileETag None
<IfModule mod_headers.c>
Header unset ETag
Header set Cache-Control "max-age=0, no-cache, no-store, must-revalidate"
Header set Pragma "no-cache"
Header set Expires "Wed, 11 Jan 1984 05:00:00 GMT"
</IfModule>
</FilesMatch>

Working on something with jsfiddle-esque mod rewrite happening, this basically takes up to 3 get vars, but the urls can look like this: mysite.com/one/two/three

1
2
3
4
5
6
7
8
RewriteEngine on
 
RewriteCond %{REQUEST_FILENAME} -f
RewriteRule ^ - [L]
 
RewriteRule ^([^/]+)/?$ index.php?folder=$1 [L]
RewriteRule ^([^/]+)/([^/]+)/?$ index.php?folder=$1&name=$2 [NC]
RewriteRule ^([^/]+)/([^/]+)/([^/]+)/?$ index.php?folder=$1&name=$2&revision=$3 [NC]

CSS3 selector with jQuery to alter css on even li tags:

$("li:nth-child(even)").css({backgroundColor : red, border : 1px solid black"});

CSS3 selectors with jQuery to select elements based on attribute values:

$("<p>I start with a<\/p>").insertBefore("img[src^='a']");
 
$("<p>I contain with a<\/p>").insertBefore("img[src*='a']");
 
$("<p>I end with a<\/p>").insertBefore("img[src$='a']");

Get the levenshtein distance between two strings:

echo levenshtein ( "zevan" , "kevin" );
// result is 2

Get the metaphone key for a string:

echo metaphone("zevan");
// result is SFN

I have a bunch more of these that I’ll post either over the weekend or on monday.

Replace Single Word or String in MYSQL

Someone asked me this one today on IM right before they were about to write a php script for it.

“How do you replace a single word/substring in a given field of a table?”

Here is how it’s done:

UPDATE theTable SET theField = REPLACE(theField, "replace me", "with something else");

Easy enough, no reason to write nasty php to loop through each row etc…