How To Dual-Sort Query Results In WordPress Using get_posts

Author: erics, September 12th, 2019

Needed to build a feature for a Bookstore tool to have an optional sequence number for the Custom Post Type “books” that would allow items with a seq number to float to the top of the list and be sorted by seq #. The rest of the books would show underneath, sorted alphabetically.

    $querytop = get_posts(array(
        'post_type'     => 'books',
        'numberposts'   => -1, 
        'orderby'       => 'meta_value',
        'meta_key'      => 'book_seq',
        'order'         => 'ASC',
        'meta_query' => array(
            // meta query takes an array of arrays, watch out for this!
            array(
                'key'     => 'book_seq',
                'value'   => array('', 'NULL'),
                'compare' => 'NOT IN'
            )   
        )   

    )); 
    $querybottom = get_posts(array(
        'post_type'     => 'books',
        'numberposts'   => -1, 
        'orderby'       => 'post_title',
        'order'         => 'ASC',
        'meta_query' => array(
            // meta query takes an array of arrays, watch out for this!
            array(
                'key'     => 'book_seq',
                'compare' => 'NOT EXISTS'
            )   
        )   
    )); 

    $query = array_merge($querytop, $querybottom);

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

$querytop = get_posts(array(

'post_type' => 'books',

'numberposts' => -1,

'orderby' => 'meta_value',

'meta_key' => 'book_seq',

'order' => 'ASC',

'meta_query' => array(

// meta query takes an array of arrays, watch out for this!

array(

'key' => 'book_seq',

'value' => array('', 'NULL'),

'compare' => 'NOT IN'

)

));

$querybottom = get_posts(array(

'post_type' => 'books',

'numberposts' => -1,

'orderby' => 'post_title',

'order' => 'ASC',

'meta_query' => array(

// meta query takes an array of arrays, watch out for this!

array(

'key' => 'book_seq',

'compare' => 'NOT EXISTS'

)

));

$query = array_merge($querytop, $querybottom);

Categories: How-To's, Technology Tags: bottom, Compare, cpt, Exists, Filter, Float, get_posts, howto, key, meta_key, meta_value, NOT EXISTS, NOT IN, Order, OrderBy, post_type, Query, Seq, Sequence, sort, tips, top, Value, WordPress | No comments

How To List Running AWS Instances in all Regions Sorted by LaunchTime and Email in a Monospaced Font

Author: erics, July 1st, 2019

#!/bin/sh

DATE=`date`

(
echo "From: Your Full Name <you@yourdomain.com>"
echo "To: you@yourdomain.com"
echo "Subject: AWS Instances Report for All Customers as of $DATE"
cat <<EOT
MIME-Version: 1.0
Content-Type: text/html
Content-Disposition: inline
<html>
<body>
<pre style="font: monospace">
EOT
for profile in customer1 customer2 customer3; do
	for region in `aws ec2 describe-regions --output text | cut -f3`; do
		echo -e "\nListing Instances for profile $profile in region: '$region'..."
		aws ec2 describe-instances \
		--filters "Name=instance-state-name,Values=running" \
		--query 'Reservations[*].Instances[*].[InstanceId, PublicIpAddress, LaunchTime, InstanceType, Tags[?Key==`Name`] | [0].Value]| sort_by(@, &@[0][2])' \
		--region $region \
		--profile $profile \
		--output table
	done
done
cat <<EOT
</pre>
</body>
</html>
EOT
) | /usr/sbin/sendmail -t

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

#!/bin/sh

DATE=`date`

(

echo "From: Your Full Name <you@yourdomain.com>"

echo "To: you@yourdomain.com"

echo "Subject: AWS Instances Report for All Customers as of $DATE"

cat <<EOT

MIME-Version: 1.0

Content-Type: text/html

Content-Disposition: inline

<html>

<body>

EOT

for profile in customer1 customer2 customer3; do

for region in `aws ec2 describe-regions --output text | cut -f3`; do

echo -e "\nListing Instances for profile $profile in region: '$region'..."

aws ec2 describe-instances \

--filters "Name=instance-state-name,Values=running" \

--query 'Reservations[*].Instances[*].[InstanceId, PublicIpAddress, LaunchTime, InstanceType, Tags[?Key==`Name`] | [0].Value]| sort_by(@, &@[0][2])' \

--region $region \

--profile $profile \

--output table

done

cat <<EOT

</pre>

</body>

</html>

EOT

) | /usr/sbin/sendmail -t

Categories: How-To's, Technology Tags: AWS, aws ec2, Date, EC2, Email, Font, Format, howto, html, LaunchTime, mail, monospace, monospaced, Multiple, Region, Regions, SendMail, sort, Sort By, time, tips | No comments

How To Select the Max Numeric in a Varchar Column in MySQL

Author: erics, October 2nd, 2015

I needed to automate the addition of new users with a sequential member’s ID in WordPress. Additionally, there were non-numeric entries in that column that had to be ignored. The wp_users.user_login column is a varchar(60) and so does not naturally handle numeric operations well at all. The solution is a combination of REGEXP and cast(): […]

Categories: How-To's, Technology Tags: Cast, Char, Column, Filter, howto, Max, mysql, Numeric, Regex, regexp, select, sort, SQL, tips, Varchar, Where, WordPress | No comments

How To Sort Text as a Number in MySQL

Author: erics, July 11th, 2013

select theField from theTable order by theField+0; ~OR~ select theField from theTable order by CAST(theField AS UNSIGNED);

Categories: How-To's, Technology Tags: 0, Add, Cast, howto, Number, sort, String, text(), tips, Zero | No comments

How To Disable Sort in DataTables

Author: erics, July 10th, 2013

To turn off Sort completely, use the ‘bSort’: false option. http://datatables.net/usage/features#bSort To have sorting turned off to begin with, but the user can still click on the columns to sort, use the ‘aaSorting’: [] (i.e. empty array) option. http://datatables.net/examples/basic_init/table_sorting.html Happy Coding!

Categories: How-To's, Technology Tags: aaSorting, bSort, DataTable, Disable, howto, sort, Table, tips, turn off | No comments

How To Return Line Numbers In MySQL

Author: erics, October 14th, 2011

SELECT @rownum := @rownum + 1 AS Rank, T1.* FROM (select @rownum := 0) R, ( 
   SELECT Category, Item, Value FROM Inventory 
   WHERE lastUpdated = CURDATE() 
   ORDER BY Value DESC
) AS T1
WHERE T1.Item = "Camera"

1

2

3

4

5

6

SELECT @rownum := @rownum + 1 AS Rank, T1.* FROM (select @rownum := 0) R, (

SELECT Category, Item, Value FROM Inventory

WHERE lastUpdated = CURDATE()

ORDER BY Value DESC

) AS T1

WHERE T1.Item = "Camera"

Categories: How-To's, Technology Tags: howto, Line number, mysql, Query, Row number, Sequence number, sort, SQL, tips | No comments

How To Select Random Records Using MySQL

Author: erics, September 28th, 2011

SELECT *
FROM yourTable
WHERE yourField='WhatYouWishToFindGoesHere'
ORDER BY RAND()
LIMIT 5

1

2

3

4

5

SELECT *

FROM yourTable

WHERE yourField='WhatYouWishToFindGoesHere'

ORDER BY RAND()

LIMIT 5

Categories: How-To's, Technology Tags: howto, Limit, mysql, Order, ORDER BY, rand, Random, sort, SQL, tips | No comments

How to Sort Arrays in PHP

Author: erics, September 29th, 2009

There are 4 “key” functions for dealing with PHP array sorting. These four have the significant feature of keeping the associated key/value relationships intact. asort() – sort by value ascending arsort()– sort by value descending ksort()– sort by key ascending krsort() – sort by key descending For more information check out this php man page […]

Categories: How-To's, Technology Tags: array, howto, php, sort, sorting, tips | No comments

How To Dual-Sort Query Results In WordPress Using get_posts

How To List Running AWS Instances in all Regions Sorted by LaunchTime and Email in a Monospaced Font

How To Select the Max Numeric in a Varchar Column in MySQL

How To Sort Text as a Number in MySQL

How To Disable Sort in DataTables

How To Return Line Numbers In MySQL

How To Select Random Records Using MySQL

How to Sort Arrays in PHP

Our Link Network

Business

Client Websites

Fine Dining

Friends & Family

Galleries

Health

Resources

Technology

Travel

The Archives

Latest Ramblings…

Various Topics of No Interest