Shortcode for Custom Query WordPress
- SOLVED
We have added tables to our WP database. I can execute on the database and return the correct result using this SQL:
SELECT
COUNT(DISTINCT L.rack_desc)
FROM
wp_transactions T,
wp_location L
WHERE
T.location_id=L.location_id
AND L.location_type= 'Widget'
AND T.date= '2011-03-15'
AND T.trans_type= 'Inventory'
Please translate this into a proper WP query and provide as a shortcode I can add to a plugin.
example:
function rack-count () {
<strong>YOUR MAGIC GOES HERE</strong>
}
add_shortcode('rc', 'rack-count');
Answers (4)
Utkarsh Kukreti answers:
function rack_count () {
global $wpdb;
return $wpdb->get_var("SELECT COUNT(DISTINCT L.rack_desc) FROM wp_transactions T, wp_location L WHERE T.location_id=L.location_id AND L.location_type= 'Widget' AND T.date= '2011-03-15' AND T.trans_type= 'Inventory'");
}
add_shortcode('rc', 'rack_count');
Patricia Moff comments:
crashes whole website
Utkarsh Kukreti comments:
Crashes? Did you get any error message? Also, try my updated answer.
Patricia Moff comments:
Hi, tried the update, it also crashes website. Maybe crash isn't the right word, the browser returns a blank screen for all URLs.
The correct answer is 4 when I run the SQL on the database.
I just can't figure this one out :(
Patricia Moff comments:
T.location_id=L.location_id is a JOIN, (I think) is that helpful?
Utkarsh Kukreti comments:
Try my latest edit. You can't have `-` in function names.
Patricia Moff comments:
There is a beautiful little 4 on the page. Thank you so much! what do I do now to pay you?
Utkarsh Kukreti comments:
I think you would have an option to Vote for my answer somewhere on the page.
Ozh RICHARD answers:
Granted your SQL query is valid, is the state of the art answer to your question, making good use of the API and WordPress good practices.
function pmoff_rack_count( $attr, $content ) {
global $wpdb;
// Make sure table names are not hardcoded (ie work on any WP setup)
$table_trans = $wpdb->prefix.'transactions';
$table_loc = $wpdb->prefix.'location';
// Get shortcode parameters :
// Date. Default if not provided: today's date
$date = ( isset( $attr['date'] ) ) ? $attr['date'] : date( 'Y-m-d' );
// Trans_type. Default value: 'Inventory'
$trans_type = ( isset( $attr['trans_type'] ) ) ? $attr['trans_type'] : 'Inventory';
// Set up query and make sure it's always secure (no malformed SQL injection etc...)
$query = $wpdb->prepare( "
SELECT
COUNT(DISTINCT L.rack_desc)
FROM
$table_trans T,
$table_loc L
WHERE
T.location_id=L.location_id
AND L.location_type= 'Widget'
AND T.date= '2011-03-15'
AND T.trans_type= 'Inventory'
" );
// Get and return result
return $wpdb->get_var( $query );
}
// Register a new shortcode
// Usage :
// [rc]
// [rc date="2011-03-15"]
// [rc date="2001-03-15" trans_type="Something"]
add_shortcode( 'rc', 'pmoff_rack_count' );
I think comments make things self-explanatory, but feel free to require more information. If that still "crash" your website, then I think the query is to blame :)
Patricia Moff comments:
Hey Ozh!
I bought your book when it came out! Awesome, by the way. Thanks for answering my question.
Svilen Popov answers:
function rack-count () {
global $wpdb;
$results = $wpdb->get_results("SELECT COUNT(DISTINCT L.rack_desc) FROM wp_transactions T, wp_location L WHERE T.location_id=L.location_id AND L.location_type= 'Widget' AND T.date= '2011-03-15' AND T.trans_type='Inventory'");
foreach ($results as $result) {
// output something
}
}
add_shortcode('rc', 'rack-count');
Patricia Moff comments:
also crashes website :(
AdamGold answers:
function rack_count() {
global $wpdb;
$result = $wpdb->get_var($wpdb->prepare("SELECT COUNT(DISTINCT l.rack_desc) FROM wp_transactions t, wp_location l WHERE t.location_id=l.location_id AND l.location_type= 'Widget' AND t.date= '2011-03-15' AND t.trans_type='Inventory'"));
return $result;
}
add_shortcode('shortcode-name', 'rack_count');
Almost :D Utkarsh was ahead.