StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POarrange sql statement according to meta value not working
primarykey
Id
16093955
data
AcceptedAnswerId
16094696
AnswerCount
1
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2013-04-18T22:11:40.677
FavoriteCount
0
LastActivityDate
2013-04-19T22:21:37.990
LastEditDate
LastEditorUserId
0
OwnerUserId
1229265
ParentId
0
PostTypeId
1
Score
0
ViewCount
144
LastEditorDisplayName
text
Body
i'm pulling my hair out here: I have a sql statement that is part of an ajax call: <pre><code>SELECT $wpdb->commentmeta.meta_value, $wpdb->comments.comment_ID, $wpdb->comments.comment_date, $wpdb->comments.user_id, $wpdb->comments.comment_content, $wpdb->comments.comment_author FROM $wpdb->commentmeta JOIN $wpdb->comments ON $wpdb->comments.comment_ID = $wpdb->commentmeta.comment_id WHERE $wpdb->commentmeta.meta_key = '_commentsvote' AND $wpdb->comments.comment_date >='" . $current_year . "-" . $current_month . "-01' AND $wpdb->comments.comment_date <='" . $current_year . "-" . $current_month . "-31' ORDER BY $wpdb->commentmeta.meta_value DESC </code></pre> the statement works great, and returns results, however, the order is off. now, i know this is because the meta_value is actually a varchar, and not an int. so, the internets told me to do either a convert or cast: <pre><code>Convert: SELECT $wpdb->commentmeta.meta_value, $wpdb->comments.comment_ID, $wpdb->comments.comment_date, $wpdb->comments.user_id, $wpdb->comments.comment_content, $wpdb->comments.comment_author FROM $wpdb->commentmeta JOIN $wpdb->comments ON $wpdb->comments.comment_ID = $wpdb->commentmeta.comment_id WHERE $wpdb->commentmeta.meta_key = '_commentsvote' AND $wpdb->comments.comment_date >='" . $current_year . "-" . $current_month . "-01' AND $wpdb->comments.comment_date <='" . $current_year . "-" . $current_month . "-31' ORDER BY CONVERT(INT, $wpdb->commentmeta.meta_value) DESC CAST SELECT $wpdb->commentmeta.meta_value, $wpdb->comments.comment_ID, $wpdb->comments.comment_date, $wpdb->comments.user_id, $wpdb->comments.comment_content, $wpdb->comments.comment_author FROM $wpdb->commentmeta JOIN $wpdb->comments ON $wpdb->comments.comment_ID = $wpdb->commentmeta.comment_id WHERE $wpdb->commentmeta.meta_key = '_commentsvote' AND $wpdb->comments.comment_date >='" . $current_year . "-" . $current_month . "-01' AND $wpdb->comments.comment_date <='" . $current_year . "-" . $current_month . "-31' ORDER BY CAST($wpdb->commentmeta.meta_value AS INT) DESC </code></pre> I keep getting sql errors on both of these when i run it as a sql statement (obviously, i convert the $wpdp to just say wp_) out of wordpress. the error is non specific - i.e. "you have an error near as int" for example. so, two questions, first one is wp specific, and the second is a sql: <ol> <li>i'm pretty sure there's a way to write all this as an arg array i can pass into wp_query, but i'm unsure of how to do a join. code examples here would be super helpful.</li> <li>why why WHY is this sql statement unwilling to convert that last colomn to an int?</li> </ol> Thanks Wp and SQL geniuses for reading!
Tags
<mysql><wordpress>
Title
arrange sql statement according to meta value not working
singulars
PostAcceptedAnswerId
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. This table or related slice is empty.
UserOwnerUserId
1. USraingod
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId
1. This table or related slice is empty.

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.