{"id":5744,"date":"2009-12-08T21:00:24","date_gmt":"2009-12-09T02:00:24","guid":{"rendered":"http:\/\/klishis.com\/notreally\/?p=5744"},"modified":"2009-12-08T22:09:56","modified_gmt":"2009-12-09T03:09:56","slug":"ha-take-that-mysql","status":"publish","type":"post","link":"https:\/\/klishis.com\/notreally\/archives\/5744","title":{"rendered":"HA! Take THAT MySQL!"},"content":{"rendered":"

I’ve learned something new! MUAHAHAHA!<\/p>\n

I’ve modified the search resultsof my library database search<\/a> so that for most search results, you can sort the results by clicking on the heading.<\/p>\n

If you aren’t a geek, you’ll want to go and play with the search, rather than read than read the code bits that follow.<\/p>\n

I got that using “switch” <\/p>\n

if(isset($_GET[‘sort’]))
\n{
\nswitch($_GET[‘sort’])
\n{
\ncase ‘a’:
\n$order_by = LastName ASC, FirstName, Series, Year’;
\n$a = “{$_SERVER[‘PHP_SELF’]}?sort=b”;
\nbreak;<\/p>\n

case ‘b’:
\n$order_by = ‘LastName DESC, FirstName, Series, Year’;
\n$a = “{$_SERVER[‘PHP_SELF’]}?sort=a”;
\nbreak;<\/p>\n

default:
\n$order_by = ‘LastName, FirstName, Series, Year’;
\n$sort = ‘a’;
\nbreak;
\n}<\/p>\n

$sort = $_GET[‘sort’];
\n} else {
\n$order_by = ‘LastName, FirstName, Series, Year’;
\n$sort = ‘a’;
\n}\n<\/p><\/blockquote>\n

Isn’t that nice?<\/p>\n

But even better, I managed to get a more accurate sort for my “title” results. I got it to ignore a, an, and the when they start a book title. Which means the title results are now truly alphabetical.<\/p>\n

ORDER BY trim(LEADING ‘The ‘ FROM trim(LEADING ‘A ‘ FROM trim(LEADING ‘An ‘ FROM Title))) DESC<\/p><\/blockquote>\n

Isn’t that lovely? <\/p>\n

You wouldn’t believe how long it took me to find that. Most of what I came across wanted me to modify the book title to place the definite articles at the end of the title followed by a comma, or in a separate column, or even create a separate column for sorting, all of which were overkill for what I wanted.<\/p>\n

But trim(LEADING ‘The ‘ FROM trim(LEADING ‘A ‘ FROM trim(LEADING ‘An ‘ FROM X))) DESC worked like a charm.<\/p>\n","protected":false},"excerpt":{"rendered":"

I’ve learned something new! MUAHAHAHA! I’ve modified the search resultsof my library database search so that for most search results, you can sort the results by clicking on the heading. If you aren’t a geek, you’ll want to go and play with the search, rather than read than read the code bits that follow. I […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[17],"tags":[880],"class_list":["post-5744","post","type-post","status-publish","format-standard","hentry","category-computers-technology","tag-php-mysql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/pefxA-1uE","_links":{"self":[{"href":"https:\/\/klishis.com\/notreally\/wp-json\/wp\/v2\/posts\/5744","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/klishis.com\/notreally\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/klishis.com\/notreally\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/klishis.com\/notreally\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/klishis.com\/notreally\/wp-json\/wp\/v2\/comments?post=5744"}],"version-history":[{"count":0,"href":"https:\/\/klishis.com\/notreally\/wp-json\/wp\/v2\/posts\/5744\/revisions"}],"wp:attachment":[{"href":"https:\/\/klishis.com\/notreally\/wp-json\/wp\/v2\/media?parent=5744"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/klishis.com\/notreally\/wp-json\/wp\/v2\/categories?post=5744"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/klishis.com\/notreally\/wp-json\/wp\/v2\/tags?post=5744"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}