Database Query Using Attributes and Shortcode WordPress
- SOLVED
We have added tables to our WP database. Based on excellent advice I received here 2 nights ago, I am now attempting a more complicated query:
This is the query I have hacked together so far:
function room_current_select_location_number_of_racks( $attr, $content ) {
// Get shortcode parameters :
// Date Range (by month). <strong>Default: Last Calendar Month</strong>
//YOUR MAGIC GOES HERE
// Room_desc. <strong>Default value: ALL Rooms</strong> (if no parameter is provided, it returns all rooms)
//$room_desc = ( isset( $attr['room_desc'] ) ) ? $attr['room_desc'] : '???????????';
//Item_category. <strong>Default value: ALL Item Categories</strong> (if no parameter is provided, it returns all categories)
//$item_category = ( isset( $attr['item_category'] ) ) ? $attr['item_category'] : '???????????';
// Set up query and make sure it's always secure (no malformed SQL injection etc...)
$query = $wpdb->prepare( "
SELECT
I.item_desc,
SUM(T.quantity)
FROM
wp_transactions T,
wp_location L,
wp_items I
WHERE
T.item_id=I.item_id
AND T.location_id=L.location_id
AND I.item_category= '<strong>???</strong>' // 'Red Widgets, Blue Widgets, etc
AND L.room_desc='<strong>???</strong>' //ROOM 4A, ROOM 4B, etc
AND T.date= '<strong>???</strong>' // 03/2011, 05/2011 etc
GROUP BY
I.item_desc
ORDER BY
I.item_name
" );
return $wpdb->get_var( $query );
}
// Register a new shortcode
// Usage :
// [19]
// [19 room_desc="ROOM 4A"] returns Room 4A, and defaults values: ALL item_category, date=Last Calendar Month
// [19 item_category="Blue Widgets"] returns Blue Widgets, and default values: ALL room_desc, date=Last Calendar Month
//[19 item_category="Green Widgets" month='03' year='2011'] returns Green Widgets, for March, 2011 and default value: ALL room_desc
add_shortcode( '19', 'room_current_select_location_number_of_racks' );
The correct response is in this format:
possible shortcode: [19 item_category="Green Widgets" month='03' year='2011']
Returns:
Pinky Widgets 40
Fuzzy Widgets 80
Large Green Widgets 120
Jumbo Widge 10
I did my best to get this query started, hope there are enough clues for you to work on.
Answers (2)
Ozh RICHARD answers:
Well, I have the feeling I made most of it the other day.. :)
Don't forget to globalise $wpdb
function room_current_select_location_number_of_racks( $attr, $content ) {
global $wpdb;
// Get shortcode parameters :
// Date Range (by month). Default: Last Calendar Month
$date = ( isset( $attr['date'] ) ) ? $attr['date'] : date('m/Y',strtotime("last month"));
// Room_desc. Default value: ALL Rooms (if no parameter is provided, it returns all rooms)
$room_desc = ( isset( $attr['room_desc'] ) ) ? "AND L.room_desc = '" . $attr['room_desc'] . "'" : '';
//Item_category. Default value: ALL Item Categories (if no parameter is provided, it returns all categories)
$item_category = ( isset( $attr['item_category'] ) ) ? "AND I.item_category = '" .$attr['item_category'] . "'": '';
// Set up query and make sure it's always secure (no malformed SQL injection etc...)
$query = $wpdb->prepare( "
SELECT
I.item_desc,
SUM(T.quantity)
FROM
wp_transactions T,
wp_location L,
wp_items I
WHERE
T.item_id=I.item_id
AND T.location_id=L.location_id
$item_category // 'Red Widgets, Blue Widgets, etc
$room_desc //ROOM 4A, ROOM 4B, etc
AND T.date= '$date' // 03/2011, 05/2011 etc
GROUP BY
I.item_desc
ORDER BY
I.item_name
" );
return $wpdb->get_var( $query );
}
// Register a new shortcode
// Usage :
// [19]
// [19 room_desc="ROOM 4A"] returns Room 4A, and defaults values: ALL item_category, date=Last Calendar Month
// [19 item_category="Blue Widgets"] returns Blue Widgets, and default values: ALL room_desc, date=Last Calendar Month
//[19 item_category="Green Widgets" month='03/2011'] returns Green Widgets, for March, 2011 and default value: ALL room_desc
add_shortcode( '19', 'room_current_select_location_number_of_racks' );
Things to check regarding your query :
* You wanted to have [19 .... month="03" year="2011"]. But what if the user enters only month="03" ? or only year="2011" ? To be more foolproof I elected to use a parameter with a format like month="03/2011"
* I'm not sure having a shortcode with only digits is a good idea. Just to be sure, I'll add a letter, something like [r19] or anything.
Patricia Moff comments:
Ozh,
The above query doesn't return any responses (nothing on page)
When I execute SQL on the database:
SELECT
I.item_desc,
SUM(T.quantity)
FROM
wp_transactions T,
wp_location L,
wp_items I
WHERE
T.item_id=I.item_id
AND T.location_id=L.location_id
AND I.item_category='Green Widgets'
AND L.room_desc='ROOM 4A'
AND T.date= '2011-6-01'
GROUP BY
I.item_desc
ORDER BY
I.item_name
It returns this:
Pinky Widgets 20
Fuzzy Widgets 40
Large Green Widgets 60
Jumbo Widge 10
(also, I left out the global stuff because the plugin is for internal use only :)
Patricia Moff comments:
The number for the shortcodes are just for testing at this point, but good advice. I didn't know how the date=xxx would work, so I just made it up- your way is better obviously.
any more ideas, since it didn't return anything?
Ozh RICHARD comments:
(Damn, I don't even know how to reply to a reply...)
The problem seems to be the date.
- your shortcode example was: month='03' year='2011'
- but your working SQL query has: AND T.date= '2011-6-01'
Should the shortcode be something like [19 .... date="yyyy-mm-dd"] with a default date being, like, yesterday?
Regarding the global $wpdb stuff: you need it. Internal use or whatever doesn't apply, the function needs it.
Ozh RICHARD comments:
Also, I've not changed the $wpdb->get_var() you suggested but I think it may not work here. When you run your query within phpMyAdmin, does it return ONE result, or 4 results?
If more than one, you need to replace:
return $wpdb->get_var( $query );
with:
$results = $wpdb->get_results( $query );
var_dump( $results );
return "ok...";
Depending on what the var_dump() prints (watch out, could be anywhere in the page, even in something not visible so you may have to view-source of the page) we'll be able to know what kind of results you get, format it nicely and have a proper return()
Patricia Moff comments:
Ozh,
OK, I changed your code abit... just this part...
WHERE
T.item_id=I.item_id
AND T.location_id=L.location_id
AND <strong>I.</strong>item_category<strong>='Green Widgets'</strong>
AND <strong>L</strong>.room_desc<strong>='ROOM 4A'</strong>
AND T.date= '2011-06-01'
The return should be:
Pinky Widgets 20
Fuzzy Widgets 40
Large Green Widgets 60
Jumbo Widge 10
But it's just:
Pinky
Is that due to using: return $wpdb->get_var( $query ); ?
Patricia Moff comments:
Ok, when using this:
WHERE
T.item_id=I.item_id
AND T.location_id=L.location_id
AND I.item_category
AND L.room_desc
AND T.date= '$date'
it returns this:
array(0) { } ok...
When using this:
WHERE
T.item_id=I.item_id
AND T.location_id=L.location_id
AND I.item_category='Live Mice'
AND L.room_desc='ROOM 4A'
AND T.date= '2011-06-01
it returns this:
array(4) { [0]=> object(stdClass)#142 (2) { ["item_desc"]=> string(6) "Pinky Widgets " ["SUM(T.quantity)"]=> string(2) "20" } [1]=> object(stdClass)#203 (2) { ["item_desc"]=> string(6) "Fuzzy Widgets " ["SUM(T.quantity)"]=> string(2) "40" } [2]=> object(stdClass)#205 (2) { ["item_desc"]=> string(6) "Large Green Widgets" ["SUM(T.quantity)"]=> string(2) "60" } [3]=> object(stdClass)#204 (2) { ["item_desc"]=> string(8) "Jumbo Widge " ["SUM(T.quantity)"]=> string(2) "10" } } ok...'
These reports are always by month, with 'last month' being the last accurate count.
Ozh RICHARD comments:
Make sure you revert to my initial code:
not :
WHERE
T.item_id=I.item_id
AND T.location_id=L.location_id
AND I.item_category
AND L.room_desc
AND T.date= '$date'
but :
WHERE
T.item_id=I.item_id
AND T.location_id=L.location_id
$item_category // 'Red Widgets, Blue Widgets, etc
$room_desc //ROOM 4A, ROOM 4B, etc
AND T.date= '$date' // 03/2011, 05/2011 etc
Patricia Moff comments:
here's your page:
It will be restricted by IP address at the server level when it's ready. This is just a dev server
http://reports.laynelabs.com/animals/test-return/
Ozh RICHARD comments:
Patricia, Here's some updated code
function room_current_select_location_number_of_racks( $attr, $content ) {
global $wpdb;
// Get shortcode parameters :
// Date. Default: current day
$date = ( isset( $attr['date'] ) ) ? $attr['date'] : date( 'Y-m-d' );
// Room_desc. Default value: ALL Rooms (if no parameter is provided, it returns all rooms)
$room_desc = ( isset( $attr['room_desc'] ) ) ? "AND L.room_desc = '" . $attr['room_desc'] . "'" : '';
//Item_category. Default value: ALL Item Categories (if no parameter is provided, it returns all categories)
$item_category = ( isset( $attr['item_category'] ) ) ? "AND I.item_category = '" .$attr['item_category'] . "'": '';
// Set up query and make sure it's always secure (no malformed SQL injection etc...)
$query = $wpdb->prepare( "
SELECT
I.item_desc,
SUM(T.quantity) as sum
FROM
wp_transactions T,
wp_location L,
wp_items I
WHERE
T.item_id=I.item_id
AND T.location_id=L.location_id
$item_category
$room_desc
AND T.date= '$date'
GROUP BY
I.item_desc
ORDER BY
I.item_name
" );
$results = $wpdb->get_results( $query );
$return = '';
foreach( $results as $result ) {
$return .= $result->item_desc . ' : ' . $result->sum . "</br>\n";
}
return $return;
}
// Register a new shortcode
// Usage :
// [19]
// [19 room_desc="ROOM 4A"] returns Room 4A, and defaults values: ALL item_category, date=Last Calendar Month
// [19 item_category="Blue Widgets"] returns Blue Widgets, and default values: ALL room_desc, date=Last Calendar Month
// [19 item_category="Green Widgets" date='2011-06-01'] returns Green Widgets, for March, 2011 and default value: ALL room_desc
add_shortcode( '19', 'room_current_select_location_number_of_racks' );
Changes:
- slight change in the SQL query, adding a "as sum" to allow easier output
- removed comments in the SQL query, that was very dumb from me to include them :))
- since you're running test queries with a date = "yyyy-mm-dd", I've changed the function to use that as well, defaulting to today's date
Try it with [19 item_category='Live Mice' room_desc='ROOM 4A' date= '2011-06-01']
We're gonna get this baby working :)
Patricia Moff comments:
Went back to global table names...
function room_current_select_location_number_of_racks( $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';
$table_items = $wpdb->prefix.'items';
// Get shortcode parameters :
// Date Range (by month). Default: Last Calendar Month
$date = ( isset( $attr['date'] ) ) ? $attr['date'] : date('m/Y',strtotime("last month"));
// Room_desc. Default value: ALL Rooms (if no parameter is provided, it returns all rooms)
$room_desc = ( isset( $attr['room_desc'] ) ) ? "AND L.room_desc = '" . $attr['room_desc'] . "'" : '';
//Item_category. Default value: ALL Item Categories (if no parameter is provided, it returns all categories)
$item_category = ( isset( $attr['item_category'] ) ) ? "AND I.item_category = '" .$attr['item_category'] . "'": '';
// Set up query and make sure it's always secure (no malformed SQL injection etc...)
$query = $wpdb->prepare( "
SELECT
I.item_desc,
SUM(T.quantity)
FROM
$table_trans T,
$table_loc L,
$table_items I
WHERE
T.item_id=I.item_id
AND T.location_id=L.location_id
$item_category
$room_desc
AND T.date= '$date'
GROUP BY
I.item_desc
ORDER BY
I.item_name
" );
$results = $wpdb->get_results( $query );
var_dump( $results );
return "ok...";
}
// Register a new shortcode
// Usage :
// [19]
// [19 room_desc="ROOM 4A"] returns Room 4A, and defaults values: ALL item_category, date=Last Calendar Month
// [19 item_category="Blue Widgets"] returns Blue Widgets, and default values: ALL room_desc, date=Last Calendar Month
//[19 item_category="Green Widgets" month='03/2011'] returns Green Widgets, for March, 2011 and default value: ALL room_desc
add_shortcode( '19', 'room_current_select_location_number_of_racks' );
returns:
array(0) { } ok..
Patricia Moff comments:
does it matter that the database stores dates like this? 0000-00-00?
Patricia Moff comments:
http://reports.laynelabs.com/animals/test-return/
that's correct!!!!
Ozh RICHARD comments:
Yeepee :)
Ozh RICHARD comments:
Unrelated but you'll want to fix this: you're using get_settings() in your code, which is deprecated and you should use get_option() instead (see the error generated when viewing source of http://reports.laynelabs.com/animals/test-return/ )
Patricia Moff comments:
http://reports.laynelabs.com/animals/test-return/
I think you're code is good! But requires a date attribute, which is fine with me. Thank you soooooo much.
Patricia Moff comments:
yeah, I saw that when I turned on the debug. This theme is really old and has been hacked on steadily for years.
So what's the deal with 'Last Month'? Is it possible to do this?
Ozh RICHARD comments:
The deal with "last month" is possible of course, it just depends on how your tables and queries run.
Just make a test SQL query that works with a whole month instead of a particular day and I'll adapt the function's code
Patricia Moff comments:
Geez, that took me a long time for a simple task:)
Duplicated all 2011-06-01 data with new entries dated 2011-06-15
so running the month of 06/2011 should show double the sum
Patricia Moff comments:
Ozh,
Too sleepy (in California), will work on this in the morning. sorry for the delay..
P
Ozh RICHARD comments:
No problem Patricia, your question is practically solved anyway. Feel free to continue this anytime on [email protected] this week-end (I'm a timezone ahead of you, week-end in 4 hours now :)
Ivaylo Draganov answers:
Hello,
as far I understand you need just to have the shortcode attributes parsed (assuming your query is working and you know what to do with the results). Try something like this:
<?php
function room_current_select_location_number_of_racks( $attr ) {
// Set default values for attributes, mix them with input attributes and extract them into variables
extract( shortcode_atts( array(
'room_desc' => '',
'item_category' => '',
'month' => date( 'm' ), // for example, this attribute will default to the current month
'year' => date ( 'Y' ), // and this one to the current year
), $atts ) );
// Set up query and make sure it's always secure (no malformed SQL injection etc...)
$query = $wpdb->prepare( "
SELECT
I.item_desc,
SUM(T.quantity)
FROM
wp_transactions T,
wp_location L,
wp_items I
WHERE
T.item_id=I.item_id
AND T.location_id=L.location_id
AND I.item_category= '{$item_category}' // 'Red Widgets, Blue Widgets, etc
AND L.room_desc='{$room_desc}' //ROOM 4A, ROOM 4B, etc
AND T.date= '{$month}/{$year}' // 03/2011, 05/2011 etc
GROUP BY
I.item_desc
ORDER BY
I.item_name
" );
// do whatever you wish with the results
return $wpdb->get_var( $query );
}
// Register a new shortcode
// Usage :
// [19]
// [19 room_desc="ROOM 4A"] returns Room 4A, and defaults values: ALL item_category, date=Last Calendar Month
// [19 item_category="Blue Widgets"] returns Blue Widgets, and default values: ALL room_desc, date=Last Calendar Month
//[19 item_category="Green Widgets" month='03' year='2011'] returns Green Widgets, for March, 2011 and default value: ALL room_desc
add_shortcode( '19', 'room_current_select_location_number_of_racks' );
You need to set default values for the attributes, so that your query works even if the shortcodes is called without any attributes.
Also, the $content variable is only necessary if you want to enclose content with your shortcode (e.g.<em> [shortcode]some content[/shortcode]</em>)
Patricia Moff comments:
So I MUST have a specific default value? The default value can't be ALL (or ignore this WHERE ...?
Ivaylo Draganov comments:
It is not obligatory to have default values. Ozh's code is doing it well - he's including clauses to the WHERE based on whether there's a value in the shortcode.
Patricia Moff comments:
Ok, I tested it two ways. Get a wierd 'server down' message from teh browser when I tested as shown above, so I removed the opening <?php and tested again, this time the website loads, but the page only loads the header and a small bit of the body, no footer. hmmmm
any ideas?
Ivaylo Draganov comments:
Turn on debugging to find out what's causing the blank page. To do so, add this line to your <em>wp-config.php</em>:
define ( 'WP_DEBUG', true );
I've added the opening <?php to enable syntax highlighting in my text editor, it should be removed if there's already an opening tag in your file.
Patricia Moff comments:
Notice: Undefined index: nonPublic in /Hosting/reports.laynelabs.com/wp-content/plugins/wp-post-to-pdf/wp-post-to-pdf.php on line 352 Notice: Undefined index: onSingle in /Hosting/reports.laynelabs.com/wp-content/plugins/wp-post-to-pdf/wp-post-to-pdf.php on line 355 Notice: Undefined index: query in /Hosting/reports.laynelabs.com/wp-content/plugins/wp-post-to-pdf/wp-post-to-pdf.php on line 371 Notice: Undefined variable: atts in /Hosting/reports.laynelabs.com/wp-content/plugins/layne-reports/layne-reports.php on line 228 Notice: Undefined variable: wpdb in /Hosting/reports.laynelabs.com/wp-content/plugins/layne-reports/layne-reports.php on line 236 Fatal error: Call to a member function prepare() on a non-object in /Hosting/reports.laynelabs.com/wp-content/plugins/layne-reports/layne-reports.php on line 236
Patricia Moff comments:
Sorry, I think just this part is for you:
Notice: Undefined variable: atts in /Hosting/reports.laynelabs.com/wp-content/plugins/layne-reports/layne-reports.php on line 222 Notice: Undefined variable: wpdb in /Hosting/reports.laynelabs.com/wp-content/plugins/layne-reports/layne-reports.php on line 230 Fatal error: Call to a member function prepare() on a non-object in /Hosting/reports.laynelabs.com/wp-content/plugins/layne-reports/layne-reports.php on line 230
Ivaylo Draganov comments:
Alright, I took Ozh's code and modified it a little bit. I've included a few lines at the bottom that print the results of the query in the browser. See what you get now:
[[LINK href="http://pastebin.com/CdVQHRg2"]]http://pastebin.com/CdVQHRg2[[/LINK]]
<em>global $wpdb</em> is necessary for the query to work.