StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The short version is that you can't <code>CONVERT</code> to <code>INT</code> - the proper type would be <code>SIGNED</code>. Read more about <a href="http://dev.mysql.com/doc/refman/5.5/en/cast-functions.html#function_convert" rel="nofollow"><code>CAST</code> and <code>CONVERT</code></a>. That said there are some other changes that you should make to make the code more readable and more secure. </p> <p>First, if you use table aliases you cut down on the need to reference <code>$wpdb</code> so many times and it can make even more complex queries more readable (if you are joining to a meta table more than once on different keys for example). </p> <p>More importantly you should use <code>$wpdb->prepare()</code> to escape your SQL - inserting variables into the statement is asking for a SQL injection. It probably also makes sense to leverage <code>BETWEEN</code> and <code>DATE_ADD</code> for your <code>WHERE</code> clause - not all months have 31 days. If you do want to pass in the start and end date, you should still use <code>%s</code> as a placeholder in the SQL and use <code>prepare()</code> to populate it.</p> <pre><code>global $wdbp; $sql = <<<SQL SELECT votes.meta_value, c.comment_ID, c.comment_date, c.user_id, c.comment_content, c.comment_author FROM {$wpdb->comments} c JOIN {$wpdb->commentmeta} votes ON votes.comment_id = c.comment_ID AND votes.meta_key = '_commentsvote' WHERE c.comment_date BETWEEN %s AND DATE_ADD(%s, INTERVAL 1 MONTH) ORDER BY CONVERT(votes.meta_value, SIGNED) SQL; $date = "{$current_year}-{$current_month}-01"; $query = $wpdb->prepare( $sql, $date, $date ); $results = $wpdb->get_results( $query ); </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload