Category 'MySQL / SQL'

WordPress: get_posts () and orderby

negli argomenti passati negli inputs. Using get_posts() , you can select a sequence of exact post using the parameter post__in in the arguments passed in the inputs. . The order, however, is not consistent with the sequence shown as WordPress performs checks on the clause ORDER BY of the select, in spite of version 2.8 is available the value none .

Continued ...

WordPress snippet: extract meta value with get_posts ()

Working with a Custom Post Type ( CPT ) it is natural to use the internal functions of WordPress to view and filter the posts of this kind. , risulta comodissimo poter creare loop personali sulle proprie tipologie di post. Whether you use the get_posts() is that it creates an object directly WP_Query , it's very comfortable to create loops on their own personal types of posts. WordPress also allows you to make filters to tassionomie personal, on the count, order, coming up to the meta data. Very useful, in fact, is the process that allows you to specify where in terms of what kind of meta data and compare it with rules.

Continued ...

Bachi beginning of the year: WordPress and WPML get_page_by_path update ()

The year 2012 began with a few hours of deep-debugging because of two (known) bugs quite annoying. , con la globale (e famosa) omonima istanza $wpdb . The first, inter alia quite dated, present in the core of WordPress that concerns the method update() of class wpdb , with the global (and popular) homonymous instance $wpdb . The fault appears when you try to update a field to NULL . Despite numerous complaints, the WordPress development team seems to find no solution to the annoying problem. In fact, to date, the only solution is to write down the SQL on its own.

Continued ...

MySQL: select random paging

Select a set of rows from a table at random is very simple, just use ORDER BY RAND() in our select statement. Some also prefer to use different techniques, ranging from the use of PHP to write select details. The reason is that ORDER BY RAND() is slow because MySQL creates a temporary table with all the results and then assigns each row a random index, returning a result ordered (random)!

Continued ...

Very short snippet: increment a field in SQL

In the latest release of WP Bannerize I introduced a new field in the table that indicates how many times a banner is "clicked". To perform the task of valore = valore +1 in SQL I used:

1
`campo` = `campo` + 1 WHERE id = id_record; UPDATE table set `field` = `field` + 1 WHERE id = id_record;

Continued ...

Very short snippet: get the number of posts, Page and Attachment

As many of you know well, the table wp_posts WordPress is used to store posts, pages and attachments. The type of the element stored is determined by the field post_type . . With the select proposed below, you can "count" individually the various types of elements in memrizzate wp_posts .

Continued ...

Very short trick: fix the dates by MySQL

. You could find yourself unable to change the time setting on a Web Server or working on a MySQL table where a field TIMESTAMP is set to ON UPDATE CURRENT_TIMESTAMP . So it becomes necessary, in the face of a select, adjust the clock recovered with the correct time zone. For example, if our server is located in Los Angeles, with a time zone 9 hours back, simply use:

1
2
campo_timestamp , INTERVAL 9 HOUR ) AS `local_timestamp` SELECT DATE_ADD (campo_timestamp, INTERVAL 9 HOUR) AS `` local_timestamp
FROM mytable

è la stessa data 9 ore avanti. The field campo_timestamp contains the actual date of the server while the alias local_timestamp is the same on 9 hours ahead.

Continued ...

Very short trick: duplicate or clone one or more rows in MySQL

To duplicate the whole of the contents of a table on itself just use:

1
* FROM MYTABLE INSERT INTO SELECT * FROM MYTABLE MYTABLE

You can also use the clausa of WHERE , if necessary:

1
* FROM MYTABLE WHERE a = b MYTABLE INSERT INTO SELECT * FROM MyTable WHERE a = b

If the table has any index or unique key, which would cause an error, you can filter the fields to be duplicated:

1
field1 , field2 , ... ) SELECT field1 , field2 , ... FROM MYTABLE INSERT INTO MYTABLE (field1, field2, ...) SELECT field1, field2, ... FROM MYTABLE

Even you can duplicate a record and modify a value, for example, I duplicate a field and add 1:

1
field1 , field2 , ... ) SELECT ( field1 + 1 ) , field2 , ... FROM MYTABLE INSERT INTO MYTABLE (field1, field2, ...) SELECT (field1 + 1), field2, ... FROM MYTABLE

Continued ...